def mayors(request, election_year, county):
coming_ele_year = coming_election_year(county)
if election_year == coming_ele_year:
if request.GET.get('name'):
candidates = Terms.objects.filter(
election_year=election_year, county=county,
type='mayors').order_by(
Case(When(name=request.GET['name'], then=Value(0)), ), '?')
else:
candidates = Terms.objects.filter(election_year=election_year,
county=county,
type='mayors').order_by('?')
else:
candidates = Terms.objects.filter(election_year=election_year,
county=county,
type='mayors').order_by('-votes')
years = Terms.objects.filter(county=county, type='mayors').values_list(
'election_year', flat=True).distinct().order_by('-election_year')
standpoints = populate_standpoints(candidates)
return render(
request, 'candidates/mayors.html', {
'years': years,
'election_year': election_year,
'county': county,
'candidates': candidates,
'standpoints': standpoints
})
def home(request):
election_year = coming_election_year(None)
if request.GET.get('name'):
try:
candidate = Terms.objects.get(election_year=election_year,
name=request.GET['name'])
if candidate.type == 'mayors':
return redirect(
reverse('candidates:mayors',
kwargs={'county': candidate.county}) +
u'?name=%s#%s' %
(request.GET['name'], candidate.candidate_id))
else:
return redirect(
reverse('candidates:district',
kwargs={
'county': candidate.county,
'constituency': candidate.constituency
}) + u'?name=%s#%s' %
(request.GET['name'], candidate.candidate_id))
except:
try:
candidate = Intent.objects.get(election_year=election_year,
name=request.GET['name'])
return redirect(
reverse('candidates:district',
kwargs={
'county': candidate.county,
'constituency': candidate.constituency
}) + u'?intent=%s#%s' %
(request.GET['name'], candidate.uid))
except:
pass
return render(request, 'home.html')
def intent_upsert(request):
election_year = coming_election_year(None)
if not request.user.is_authenticated:
return redirect(reverse('candidates:intent_home'))
try:
instance = Intent.objects.get(user=request.user,
election_year=election_year)
except:
instance = None
if request.method == 'GET':
form = IntentForm(instance=instance)
form.fields[
'name'].initial = request.user.last_name + request.user.first_name
form.fields['links'].initial = [{
'note':
u'Facebook 個人頁面',
'url':
request.user.socialaccount_set.first().get_profile_url()
}, None, None]
if request.method == 'POST':
form = IntentForm(request.POST, instance=instance)
if form.has_changed() and form.is_valid():
intent = form.save(commit=False)
if intent.user_id and request.user.id != intent.user_id:
return redirect(reverse('candidates:intent_home'))
intent.user = request.user
if instance and instance.status == 'intent_apply':
intent.status = 'intent_apply'
if intent.type == 'mayors':
intent.constituency = 0
intent.district = ''
intent.save()
c = connections['default'].cursor()
history = request.POST.copy()
history.pop('csrfmiddlewaretoken', None)
history['links'] = intent.links
history['modify_at'] = timezone.now().strftime('%Y-%m-%d %H:%M:%S')
c.execute(
'''
UPDATE candidates_intent
SET history = (%s::jsonb || COALESCE(history, '[]'::jsonb))
WHERE user_id = %s AND election_year = %s
''', [json.dumps([history]), request.user.id, election_year])
intent_register_achievement(request.user)
return redirect(
reverse(
'candidates:intent_detail',
kwargs={'intent_id':
instance.uid if instance else intent.uid}))
return render(request, 'candidates/intent_upsert.html', {
'form': form,
'election_year': election_year
})
def user_generated_list(request, list_id):
coming_ele_year = coming_election_year(None)
try:
user_list = User_Generate_List.objects.get(uid=list_id, publish=True)
recommend = None
if user_list.recommend == True:
recommend = u'推薦'
elif user_list.recommend == False:
recommend = u'不推薦'
chosen_candidates = user_list.data['candidates']
chosen_intents = user_list.data['intents']
group_candidates, total_count, standpoints = populate_candidates(
coming_ele_year, chosen_candidates, chosen_intents)
except Exception, e:
return HttpResponseRedirect('/')
def constituency(request):
results = validateFields(request.data, ['type', 'county', 'district'])
if results: return Response(results, status=status.HTTP_400_BAD_REQUEST)
d = request.data.copy()
d['county'] = re.sub(u'台', u'臺', d['county'])
coming_ele_year = coming_election_year(d['county'])
constituencies = Elections.objects.get(id=coming_ele_year).data['constituencies']
district = re.sub(u'[鄉鎮市區]$', '', d['district']) if not re.search(re.sub(u'[縣市]$', '', d['county']), d['district']) and len(d['district']) > 2 else d['district']
for region in constituencies[d['county']]['regions']:
if not region.get('villages') or not d.get('villages'):
if district in region['districts_list']:
d['constituency'] = region['constituency']
break
else:
if d['villages'] in region['villages']:
d['constituency'] = region['constituency']
break
return Response(d, status=status.HTTP_200_OK)
def select_county(request, category):
regions = [
{"region": "北部", "counties": ["臺北市", "新北市", "桃園市", "基隆市", "宜蘭縣", "新竹市", "新竹縣"]},
{"region": "中部", "counties": ["臺中市", "苗栗縣", "彰化縣", "雲林縣", "南投縣"]},
{"region": "南部", "counties": ["臺南市", "高雄市", "屏東縣", "嘉義縣", "嘉義市"]},
{"region": "東部", "counties": ["花蓮縣", "臺東縣"]},
{"region": "離島", "counties": ["澎湖縣", "金門縣", "連江縣"]}
] if category != 'votes' else [
{"region": "北部", "counties": ["臺北市"]},
{"region": "南部", "counties": ["高雄市"]},
]
titles = {
"candidates": "找候選人",
"councilors": "找議員",
"bills": "找提案",
"votes": "找表決"
}
election_year = coming_election_year(None)
return render(request, 'common/select_county.html', {'title': titles.get(category, ''), 'category': category, 'regions': regions, 'election_year': election_year})
def districts(request, election_year, county):
coming_ele_year = coming_election_year(county)
intents_count = Intent.objects.filter(
election_year=coming_ele_year,
county=county).exclude(status='draft').count()
districts = Terms.objects.filter(election_year=election_year, county=county, type='councilors')\
.values('constituency', 'district')\
.annotate(candidates=Count('id'))\
.order_by('constituency')
candidates = Terms.objects.filter(election_year=election_year, county=county, type='mayors')\
.order_by('-votes')
standpoints = {}
for candidate in candidates:
terms = Terms.objects.filter(type='mayors', candidate_id=candidate.candidate_id, elected=True)\
.order_by('-election_year')
for term in terms:
c = connections['default'].cursor()
qs = u'''
SELECT json_agg(row)
FROM (
SELECT
s.title,
count(*) as times,
sum(pro) as pro
FROM bills_mayors_bills lv
JOIN standpoints_standpoints s on s.bill_id = lv.bill_id
JOIN bills_bills v on lv.bill_id = v.uid
WHERE lv.mayor_id = %s AND s.pro = (
SELECT max(pro)
FROM standpoints_standpoints ss
WHERE ss.pro > 0 AND s.bill_id = ss.bill_id
GROUP BY ss.bill_id
)
GROUP BY s.title
ORDER BY pro DESC, times DESC
LIMIT 3
) row
'''
c.execute(qs, [
term.uid,
])
r = c.fetchone()
if not standpoints.get(candidate.id):
standpoints.update({
candidate.id: [{
'county': term.county,
'election_year': term.election_year,
'standpoints': r[0] if r else []
}]
})
else:
standpoints[candidate.id].append({
'county':
term.county,
'election_year':
term.election_year,
'standpoints':
r[0] if r else []
})
suggestions = Suggestions.objects.filter(
election_year=election_year,
county=county).aggregate(sum=Sum('approved_expense'))
return render(
request, 'candidates/districts.html', {
'coming_election_year': coming_ele_year,
'intents_count': intents_count,
'election_year': election_year,
'county': county,
'districts': list(districts),
'candidates': candidates,
'suggestions': suggestions,
'standpoints': standpoints
})
def district(request, election_year, county, constituency):
coming_ele_year = coming_election_year(county)
transform_to_constiencies = []
try:
election_config = Elections.objects.get(
id=coming_ele_year).data['constituency_change']
for region in election_config.get(county, []):
if constituency in region.get('from',
{}).get('constituencies', []):
transform_to_constiencies.append(region['constituency'])
except:
constiencies = [constituency]
intents_count = Intent.objects.filter(
election_year=coming_ele_year,
county=county,
constituency__in=transform_to_constiencies).exclude(
status='draft').count()
candidates = Terms.objects.filter(
election_year=election_year,
county=county,
type='councilors',
constituency=constituency).order_by('-votes')
standpoints = {}
for term in [candidates]:
for candidate in term:
if candidate.councilor_terms:
terms_id = tuple(
[x['term_id'] for x in candidate.councilor_terms])
c = connections['default'].cursor()
qs = u'''
SELECT jsonb_object_agg(k, v)
FROM (
SELECT 'votes' as k, json_agg(row) as v
FROM (
SELECT
CASE
WHEN lv.decision = 1 THEN '贊成'
WHEN lv.decision = -1 THEN '反對'
WHEN lv.decision = 0 THEN '棄權'
WHEN lv.decision isnull THEN '沒投票'
END as decision,
s.title,
count(*) as times,
sum(pro) as pro
FROM votes_councilors_votes lv
JOIN standpoints_standpoints s on s.vote_id = lv.vote_id
WHERE lv.councilor_id in %s AND s.pro = (
SELECT max(pro)
FROM standpoints_standpoints ss
WHERE ss.pro > 0 AND s.vote_id = ss.vote_id
GROUP BY ss.vote_id
)
GROUP BY s.title, lv.decision
ORDER BY pro DESC, times DESC
LIMIT 3
) row
UNION ALL
SELECT 'bills' as k, json_agg(row) as v
FROM (
SELECT
s.title,
count(*) as times,
sum(pro) as pro
FROM bills_councilors_bills lv
JOIN standpoints_standpoints s on s.bill_id = lv.bill_id
JOIN bills_bills v on lv.bill_id = v.uid
WHERE lv.councilor_id in %s AND s.pro = (
SELECT max(pro)
FROM standpoints_standpoints ss
WHERE ss.pro > 0 AND s.bill_id = ss.bill_id
GROUP BY ss.bill_id
)
GROUP BY s.title
ORDER BY pro DESC, times DESC
LIMIT 3
) row
) r
'''
c.execute(qs, [terms_id, terms_id])
r = c.fetchone()
standpoints.update({candidate.id: r[0] if r else []})
return render(
request, 'candidates/district.html', {
'coming_election_year': coming_ele_year,
'intents_count': intents_count,
'election_year': election_year,
'county': county,
'constituency': constituency,
'transform_to_constiencies': ','.join(transform_to_constiencies),
'district': candidates[0].district,
'candidates': candidates,
'standpoints': standpoints
})
def user_generate_list(request):
try:
instance = User_Generate_List.objects.get(user=request.user,
uid=request.GET['list_id'])
except:
instance = None
if request.method == 'POST':
fb_page = json.loads(request.POST.get('fb_page', 'null'))
print fb_page
chosen_candidates, chosen_intents = [], []
form = ListsForm(request.POST, instance=instance)
if form.is_valid():
text = request.POST['content']
text = text.strip(u'[ \s]')
text = re.sub(u'[ \n、]', u' ', text)
text = re.sub(u'[ ]+(\d+)[ ]+', u'\g<1>', text)
text = re.sub(u' ([^ \w]) ([^ \w]) ', u' \g<1>\g<2> ',
text) # e.g. 楊 曜=>楊曜, 包含句首
text = re.sub(u'^([^ \w]) ([^ \w]) ', u'\g<1>\g<2> ',
text) # e.g. 楊 曜=>楊曜, 包含句首
text = re.sub(u' ([^ \w]) ([^ \w])$', u' \g<1>\g<2>',
text) # e.g. 楊 曜=>楊曜, 包含句尾
text = re.sub(u' (\w+) (\w+) ', u' \g<1>\g<2> ',
text) # e.g. Kolas Yotaka=>KolasYotaka, 包含句首
text = re.sub(u'^(\w+) (\w+) ', u'\g<1>\g<2> ',
text) # e.g. Kolas Yotaka=>KolasYotaka, 包含句首
text = re.sub(u' (\w+) (\w+)$', u' \g<1>\g<2>',
text) # e.g. Kolas Yotaka=>KolasYotaka, 包含句尾
text = re.sub(u'^([^ \w]) ([^ \w])$', u'\g<1>\g<2>',
text) # e.g. 楊 曜=>楊曜, 單獨一人
text = re.sub(u'^(\w+) (\w+)$', u'\g<1>\g<2>',
text) # e.g. Kolas Yotaka=>KolasYotaka, 單獨一人
for name in text.split():
name = re.sub(u'(.*)[）)。】」]$', '\g<1>', name) # 名字後有標點符號
uid = get_candidates(name)
if uid:
chosen_candidates.extend(uid)
else:
uid = get_intents(name)
if uid:
chosen_intents.extend(uid)
if not request.POST.get('publish'):
coming_ele_year = coming_election_year(None)
recommend = None
if instance:
if instance.recommend == True:
recommend = u'推薦'
elif instance.recommend == False:
recommend = u'不推薦'
group_candidates, total_count, standpoints = populate_candidates(
coming_ele_year, chosen_candidates, chosen_intents)
return render(
request, 'candidates/user_generate_list.html', {
'form': form,
'election_year': coming_ele_year,
'group_candidates': group_candidates,
'standpoints': standpoints,
'user': request.user,
'total_count': total_count,
'recommend': recommend,
'id': 'profile',
'fb_page': fb_page
})
else:
user_list = form.save(commit=False)
if user_list.user_id and request.user.id != user_list.user_id:
return redirect(reverse('candidates:user_generate_list'))
user_list.user = request.user
user_list.publish = True
user_list.data = {
'candidates': chosen_candidates,
'intents': chosen_intents,
'fb_page': fb_page
}
user_list.save()
return redirect(
reverse('candidates:user_generated_list',
kwargs={'list_id': user_list.uid}))
form = ListsForm(instance=instance)
elif instance:
form = ListsForm(instance=instance)
recommend = None
if instance.recommend == True:
recommend = u'推薦'
elif instance.recommend == False:
recommend = u'不推薦'
coming_ele_year = coming_election_year(None)
chosen_candidates = instance.data['candidates']
chosen_intents = instance.data['intents']
group_candidates, total_count, standpoints = populate_candidates(
coming_ele_year, chosen_candidates, chosen_intents)
return render(
request, 'candidates/user_generate_list.html', {
'form': form,
'election_year': coming_ele_year,
'group_candidates': group_candidates,
'standpoints': standpoints,
'user': instance.user,
'total_count': total_count,
'recommend': recommend,
'id': 'profile',
'fb_page': instance.data.get('fb_page')
})
else:
form = ListsForm(instance=instance)
lists = User_Generate_List.objects.filter(user=request.user)
return render(request, 'candidates/user_generate_list.html', {
'form': form,
'lists': lists,
'id': 'profile'
})
def district(request, election_year, county, constituency):
coming_ele_year = coming_election_year(county)
constituencies = {}
try:
election_config = Elections.objects.get(id=election_year).data
constituencies = election_config.get('constituencies', {})
except:
constituencies = {}
if request.GET.get('intent'):
intents = Intent.objects.filter(
election_year=election_year,
county=county,
constituency=constituency).exclude(
Q(status='draft') | Q(candidate_term__isnull=False)).order_by(
Case(When(name=request.GET['intent'], then=Value(0)), ),
'?')
else:
intents = Intent.objects.filter(
election_year=election_year,
county=county,
constituency=constituency).exclude(
Q(status='draft')
| Q(candidate_term__isnull=False)).order_by('?')
years = Terms.objects.filter(
county=county, type='councilors',
constituency=constituency).values_list(
'election_year', flat=True).distinct().order_by('-election_year')
candidates = Terms.objects.filter(
election_year=election_year,
county=county,
type='councilors',
constituency=constituency).select_related(
'candidate', 'intent').order_by('?' if election_year ==
coming_ele_year else '-votes')
if election_year == coming_ele_year:
if request.GET.get('name'):
candidates = Terms.objects.filter(
election_year=election_year,
county=county,
type='councilors',
constituency=constituency).select_related(
'candidate', 'intent').order_by(
Case(When(name=request.GET['name'], then=Value(0)), ),
'?')
else:
candidates = Terms.objects.filter(
election_year=election_year,
county=county,
type='councilors',
constituency=constituency).select_related(
'candidate', 'intent').order_by('?')
else:
candidates = Terms.objects.filter(
election_year=election_year,
county=county,
type='councilors',
constituency=constituency).select_related(
'candidate', 'intent').order_by('-votes')
standpoints = populate_standpoints(candidates)
return render(
request, 'candidates/district.html', {
'years':
years,
'coming_election_year':
coming_ele_year,
'intents':
intents,
'election_year':
election_year,
'county':
county,
'constituency':
constituency,
'constituencies':
constituencies,
'district':
constituencies[county]['regions'][int(constituency) -
1]['district']
if constituencies.get(county) else '',
'cec_data':
constituencies[county]['regions'][int(constituency) - 1]
if constituencies.get(county) else {},
'candidates':
candidates,
'standpoints':
standpoints,
'random_row':
randint(1, len(candidates))
if not request.GET.get('intent') and len(candidates) else 1
})