def votes(request):
qs = Q(content=request.GET["keyword"]) if request.GET.get("keyword") else Q()
qs = qs & Q(conflict=True) if request.GET.get("conscience") else qs
qs = qs & Q(tags_num__gt=0) if request.GET.get("has_tag") else qs
qs = qs & Q(tags__in=[request.GET["tag"]]) if request.GET.get("tag") else qs
votes = SearchQuerySet().filter(qs).models(Vote).order_by("-date", "vote_seq")
votes = paginate(request, votes)
keywords = [x.content for x in SearchQuerySet().filter(category__exact=2).models(Keyword).order_by("-hits")]
standpoints = Standpoint.objects.values("title").annotate(pro_sum=Sum("pro")).order_by("-pro_sum").distinct()
get_params = "&".join(
["%s=%s" % (x, request.GET[x]) for x in ["keyword", "conscience", "has_tag"] if request.GET.get(x)]
)
return render(
request,
"vote/votes.html",
{
"votes": votes,
"conscience": request.GET.get("conscience"),
"keyword": request.GET.get("keyword", ""),
"keyword_obj": keywords,
"hot_keyword": keywords[:5],
"hot_standpoints": standpoints[:5],
"get_params": get_params,
},
)
def votes(request):
qs = Q(
content=request.GET['keyword']) if request.GET.get('keyword') else Q()
qs = qs & Q(conflict=True) if request.GET.get('conscience') else qs
qs = qs & Q(tags_num__gt=0) if request.GET.get('has_tag') else qs
qs = qs & Q(
tags__in=[request.GET['tag']]) if request.GET.get('tag') else qs
votes = SearchQuerySet().filter(qs).models(Vote).order_by(
'-date', 'vote_seq')
votes = paginate(request, votes)
keywords = [
x.content for x in SearchQuerySet().filter(
category__exact=2).models(Keyword).order_by('-hits')
]
standpoints = Standpoint.objects.values('title').annotate(
pro_sum=Sum('pro')).order_by('-pro_sum').distinct()
return render(
request, 'vote/votes.html', {
'votes': votes,
'conscience': request.GET.get('conscience'),
'keyword': request.GET.get('keyword', ''),
'keyword_obj': keywords,
'hot_keyword': keywords[:5],
'hot_standpoints': standpoints[:5]
})
def voter_detail(request, legislator_id, ad):
ly = get_object_or_404(LegislatorDetail.objects, ad=ad, legislator_id=legislator_id)
qs = Q(conflict=True) if request.GET.get("conscience") else Q()
if request.GET.get("decision"):
decisions = {
"agree": Q(decision=1),
"disagree": Q(decision=-1),
"abstain": Q(decision=0),
"notvote": Q(decision__isnull=True),
}
qs = qs & decisions.get(request.GET["decision"], Q())
hsqs = Q(tags_num__gt=0) if request.GET.get("has_tag") else Q()
hsqs = hsqs & Q(content=request.GET["keyword"]) if request.GET.get("keyword") else hsqs
if hsqs != Q():
sqs = SearchQuerySet().filter(hsqs).models(Vote)
votes = ly.votes.select_related("vote", "vote__sitting").filter(qs & Q(vote_id__in=[x.uid for x in sqs]))
else:
votes = ly.votes.select_related("vote", "vote__sitting").filter(qs)
votes = paginate(request, votes)
keywords = [x.content for x in SearchQuerySet().filter(category__exact=2).models(Keyword).order_by("-hits")]
get_params = "&".join(["%s=%s" % (x, request.GET[x]) for x in ["keyword", "conscience"] if request.GET.get(x)])
return render(
request,
"legislator/voter_detail.html",
{
"keyword_obj": keywords,
"hot_keyword": keywords[:5],
"ly": ly,
"origin": len(qs & hsqs),
"votes": votes,
"keyword": request.GET.get("keyword", ""),
"get_params": get_params,
},
)
def biller_detail(request, legislator_id, ad):
qs = Q(
content=request.GET['keyword']) if request.GET.get('keyword') else Q()
ly = get_object_or_404(LegislatorDetail.objects,
ad=ad,
legislator_id=legislator_id)
if len(qs):
bills_id = [
x.uid
for x in SearchQuerySet().filter(qs).models(Bill).order_by('-uid')
]
bills = ly.bills.select_related('bill').prefetch_related(
'bill__laws').filter(legislator_id=ly.id,
role='sponsor',
bill_id__in=bills_id)
else:
bills = ly.bills.select_related('bill').prefetch_related(
'bill__laws').filter(legislator_id=ly.id, role='sponsor')
bills = paginate(request, bills)
keywords = keyword_list(3)
get_params = '&'.join([
'%s=%s' % (x, request.GET[x]) for x in ['keyword']
if request.GET.get(x)
])
return render(
request, 'legislator/biller_detail.html', {
'keyword_obj': keywords,
'hot_keyword': keywords[:5],
'bills': bills,
'ly': ly,
'keyword': request.GET.get('keyword', ''),
'get_params': get_params
})
def votes(request, county):
qs = Q(sitting__county=county)
if request.GET.get('has_tag') == 'yes':
qs = qs & Q(uid__in=Standpoints.objects.exclude(vote__isnull=True).values_list('vote_id', flat=True).distinct())
elif request.GET.get('has_tag') == 'no':
qs = qs & ~Q(uid__in=Standpoints.objects.exclude(vote__isnull=True).values_list('vote_id', flat=True).distinct())
qs = qs & Q(conflict=True) if request.GET.get('conscience') else qs
if request.GET.get('tag'):
vote_ids = Standpoints.objects.filter(county=county, title=request.GET['tag']).values_list('vote', flat=True)
qs = qs & Q(uid__in=vote_ids)
keyword = request.GET.get('keyword', '')
if keyword:
votes = Votes.objects.filter(qs & reduce(operator.and_, (Q(content__icontains=x) for x in keyword.split())))
if votes:
keyword_been_searched(keyword, 'votes', county)
else:
votes = Votes.objects.filter(qs)
votes = votes.extra(
select={
'tags': "SELECT json_agg(row) FROM (SELECT title, pro FROM standpoints_standpoints su WHERE su.vote_id = votes_votes.uid ORDER BY su.pro DESC) row",
},
)\
.order_by('-date', 'vote_seq')
votes = paginate(request, votes)
standpoints = Standpoints.objects.filter(county=county, vote__isnull=False).values('title').annotate(pro_sum=Sum('pro')).order_by('-pro_sum').distinct()
get_params = '&'.join(['%s=%s' % (x, request.GET[x]) for x in ['keyword', 'has_tag', 'tag'] if request.GET.get(x)])
return render(request,'votes/votes.html', {'county': county, 'votes': votes, 'hot_keyword': keyword_list('votes', county)[:5], 'hot_standpoints': standpoints[:5], 'get_params': get_params, 'has_tag': request.GET.get('has_tag')})
def bills(request):
if request.GET.get('keyword'):
bills_uid = [x.uid for x in SearchQuerySet().filter(content=request.GET['keyword']).models(Bill).order_by('-uid')]
bills = Bill.objects.filter(uid__in=bills_uid)
else:
bills = Bill.objects.filter(ad=8)
bills = paginate(request, bills)
keywords = keyword_list(3)
return render(request, 'bill/bills.html', {'bills': bills, 'keyword_obj': keywords, 'hot_keyword': keywords[:5], 'keyword': request.GET.get('keyword', '')})
def bills(request):
if request.GET.get('keyword'):
bills_uid = [x.uid for x in SearchQuerySet().filter(content=request.GET['keyword']).models(Bill)]
bills = Bill.objects.filter(uid__in=bills_uid).prefetch_related('laws').order_by('-uid')
else:
bills = Bill.objects.all().prefetch_related('laws').order_by('-uid')
bills = paginate(request, bills)
keywords = keyword_list(3)
get_params = '&'.join(['%s=%s' % (x, request.GET[x]) for x in ['keyword'] if request.GET.get(x)])
return render(request, 'bill/bills.html', {'bills': bills, 'keyword_obj': keywords, 'hot_keyword': keywords[:5], 'keyword': request.GET.get('keyword', ''), 'get_params': get_params})
def lists(request):
ref = {'create_at': 'id', 'likes': 'likes'}
order_by = ref.get(request.GET.get('order_by'), 'likes')
qs = Q(county=request.GET.get('county')) if request.GET.get(
'county') else Q()
platforms = Platforms.objects.filter(qs).select_related(
'user').prefetch_related('intent_standpoints').order_by('-%s' %
order_by)
platforms = paginate(request, platforms)
return render(request, 'platforms/lists.html', {'platforms': platforms})
def biller_detail(request, legislator_id, ad):
qs = Q(content=request.GET['keyword']) if request.GET.get('keyword') else Q()
bills_id = [x.uid for x in SearchQuerySet().filter(qs).models(Bill).order_by('-uid')]
ly = get_object_or_404(LegislatorDetail.objects, ad=ad, legislator_id=legislator_id)
if len(qs):
bills = ly.bills.select_related('bill').filter(legislator_id=ly.id, role='sponsor', bill_id__in=bills_id)
else:
bills = ly.bills.select_related('bill').filter(legislator_id=ly.id, role='sponsor')
bills = paginate(request, bills)
keywords = keyword_list(3)
return render(request, 'legislator/biller_detail.html', {'keyword_obj': keywords, 'hot_keyword': keywords[:5], 'bills': bills, 'ly': ly, 'keyword': request.GET.get('keyword', '')})
def lists(request, county):
if request.user.is_authenticated():
if request.POST:
if request.POST.get('suggestion_id'):
if request.POST.get('rating') == 'pro':
User_Suggestions.objects.update_or_create(
suggestion_id=request.POST['suggestion_id'],
user=request.user,
defaults={'pro': True})
elif request.POST.get('rating') == 'against':
User_Suggestions.objects.update_or_create(
suggestion_id=request.POST['suggestion_id'],
user=request.user,
defaults={'pro': False})
es_connections['default'].get_unified_index(
).get_index(Suggestions).update_object(
Suggestions.objects.get(uid=request.POST['suggestion_id']))
qs = Q(
county=county,
content=request.GET['keyword']) if request.GET.get('keyword') else Q(
county=county)
qs = qs & Q(pro_count__gt=0) if request.GET.get('pro', '') == 'yes' else qs
qs = qs & Q(against_count__gt=0) if request.GET.get('pro',
'') == 'no' else qs
qs = qs & reduce(operator.or_,
(Q(content=x) for x in request.GET.get('or').split('|')
)) if request.GET.get('or') else qs
constituency = request.GET.get('constituency')
if constituency and constituency != 'all':
suggestion_ids = Councilors_Suggestions.objects.filter(
councilor_id__in=CouncilorsDetail.objects.filter(
county=county, constituency=constituency).values_list(
'id', flat=True)).values_list('suggestion_id',
flat=True).distinct()
qs = qs & Q(uid__in=suggestion_ids)
suggestions = SearchQuerySet().filter(qs).models(Suggestions).order_by(
'-approved_expense')
try:
page_size = int(request.GET.get('page_size', 10))
page_size = 10 if page_size > 51 else page_size
except:
page_size = 10
suggestions = paginate(request, suggestions, page_size)
get_params = '&'.join([
'%s=%s' % (x, request.GET[x])
for x in ['keyword', 'or', 'constituency'] if request.GET.get(x)
])
return render(
request, 'suggestions/lists.html', {
'suggestions': suggestions,
'county': county,
'keyword': request.GET.get('keyword', ''),
'get_params': get_params
})
def county_overview(request):
if request.user.is_authenticated():
if request.POST:
if request.POST.get('suggestion_id'):
if request.POST.get('rating') == 'pro':
User_Suggestions.objects.update_or_create(
suggestion_id=request.POST['suggestion_id'],
user=request.user,
defaults={'pro': True})
elif request.POST.get('rating') == 'against':
User_Suggestions.objects.update_or_create(
suggestion_id=request.POST['suggestion_id'],
user=request.user,
defaults={'pro': False})
es_connections['default'].get_unified_index(
).get_index(Suggestions).update_object(
Suggestions.objects.get(uid=request.POST['suggestion_id']))
qs = Q(
content=request.GET['keyword']) if request.GET.get('keyword') else Q()
suggestions = SearchQuerySet().filter(qs).models(Suggestions).order_by(
'-suggest_year', 'id')
if qs and suggestions:
keyword_been_searched(request.GET['keyword'], 'suggestions')
try:
page_size = int(request.GET.get('page_size', 3))
page_size = 3 if page_size > 51 else page_size
except:
page_size = 3
suggestions = paginate(request, suggestions, page_size)
counties = Suggestions.objects.all()\
.values('county', 'suggest_year')\
.annotate(
sum=Sum('approved_expense'),
count=Count('uid'),
small_purchase=Sum(
Case(
When(approved_expense__lte=10**5, then=1),
output_field=IntegerField(),
default=Value(0)
)
),
)\
.order_by('county', 'suggest_year')
get_params = '&'.join([
'%s=%s' % (x, request.GET[x]) for x in ['keyword']
if request.GET.get(x)
])
return render(
request, 'suggestions/county_overview.html', {
'suggestions': suggestions,
'counties': counties,
'keyword': request.GET.get('keyword', ''),
'get_params': get_params
})
def votes(request):
qs = Q(content=request.GET['keyword']) if request.GET.get('keyword') else Q()
qs = qs & Q(conflict=True) if request.GET.get('conscience') else qs
qs = qs & Q(tags_num__gt=0) if request.GET.get('has_tag') else qs
qs = qs & Q(tags__in=[request.GET['tag']]) if request.GET.get('tag') else qs
votes = SearchQuerySet().filter(qs).models(Vote).order_by('-date', 'vote_seq')
votes = paginate(request, votes)
keywords = [x.content for x in SearchQuerySet().filter(category__exact=2).models(Keyword).order_by('-hits')]
standpoints = Standpoint.objects.values('title').annotate(pro_sum=Sum('pro')).order_by('-pro_sum').distinct()
get_params = '&'.join(['%s=%s' % (x, request.GET[x]) for x in ['keyword', 'conscience', 'has_tag'] if request.GET.get(x)])
return render(request, 'vote/votes.html', {'votes': votes, 'conscience': request.GET.get('conscience'), 'keyword': request.GET.get('keyword', ''), 'keyword_obj': keywords, 'hot_keyword': keywords[:5], 'hot_standpoints': standpoints[:5], 'get_params': get_params})
def intent_sponsor(request, intent_id):
if not request.user.is_authenticated:
return redirect(
reverse('candidates:intent_detail',
kwargs={'intent_id': intent_id}))
intent = get_object_or_404(Intent.objects.select_related('user'),
uid=intent_id,
user=request.user)
sponsors = Intent_Likes.objects.filter(
intent_id=intent_id).order_by('-create_at')
sponsors = paginate(request, sponsors)
return render(request, 'candidates/intent_sponsor.html',
{'sponsors': sponsors})
def bills(request, category, county):
query = Q(county=county)
if request.GET.get('has_tag') == 'yes':
query = query & Q(uid__in=Standpoints.objects.filter(
bill__isnull=False).values_list('bill_id', flat=True).distinct())
elif request.GET.get('has_tag') == 'no':
query = query & ~Q(uid__in=Standpoints.objects.filter(
bill__isnull=False).values_list('bill_id', flat=True).distinct())
if category == 'councilors':
query = query & Q(uid__in=Councilors_Bills.objects.all().values_list(
'bill_id', flat=True).distinct())
elif category == 'city_gov':
query = query & Q(uid__in=Mayors_Bills.objects.all().values_list(
'bill_id', flat=True).distinct())
query = query & Q(uid__in=Standpoints.objects.filter(
title=request.GET['tag'], bill__isnull=False).values_list(
'bill_id', flat=True).distinct()) if request.GET.get(
'tag') else query
keyword = request.GET.get('keyword', '')
if keyword:
bills = Bills.objects.filter(query & reduce(operator.and_, (Q(
abstract__icontains=x) for x in split_unicode_chrs(keyword))))
if bills:
keyword_been_searched(keyword, 'bills', county)
else:
bills = Bills.objects.filter(query)
bills = bills.extra(
select={
'tags': "SELECT json_agg(row) FROM (SELECT title, pro FROM standpoints_standpoints su WHERE su.bill_id = bills_bills.uid ORDER BY su.pro DESC) row",
},
)\
.order_by('-election_year', '-uid')
bills = paginate(request, bills)
standpoints = Standpoints.objects.filter(
county=county,
bill__isnull=False).values_list('title',
flat=True).order_by('-pro').distinct()
get_params = '&'.join([
'%s=%s' % (x, request.GET[x]) for x in ['keyword', 'has_tag', 'tag']
if request.GET.get(x)
])
return render(
request, 'bills/bills.html', {
'county': county,
'keyword_hot': keyword_list('bills', county),
'category': category,
'bills': bills,
'standpoints': standpoints,
'get_params': get_params
})
def voter_detail(request, legislator_id, ad):
ly = get_object_or_404(LegislatorDetail.objects, ad=ad, legislator_id=legislator_id)
qs = Q(conflict=True) if request.GET.get('conscience') else Q()
if request.GET.get('decision'):
decisions = {"agree": Q(decision=1), "disagree": Q(decision=-1), "abstain": Q(decision=0), "notvote": Q(decision__isnull=True)}
qs = qs & decisions.get(request.GET['decision'], Q())
hsqs = Q(tags_num__gt=0) if request.GET.get('has_tag') else Q()
hsqs = hsqs & Q(content=request.GET['keyword']) if request.GET.get('keyword') else hsqs
if hsqs != Q():
sqs = SearchQuerySet().filter(hsqs).models(Vote)
votes = ly.votes.select_related('vote', 'vote__sitting').filter(qs & Q(vote_id__in=[x.uid for x in sqs]))
else:
votes = ly.votes.select_related('vote', 'vote__sitting').filter(qs)
votes = paginate(request, votes)
keywords = [x.content for x in SearchQuerySet().filter(category__exact=2).models(Keyword).order_by('-hits')]
return render(request, 'legislator/voter_detail.html', {'keyword_obj': keywords, 'hot_keyword': keywords[:5], 'ly': ly, 'origin': len(qs & hsqs), 'votes': votes, 'keyword': request.GET.get('keyword', '')})
def lists(request):
ref = {'create_at': 'id', 'likes': 'likes'}
order_by = ref.get(request.GET.get('order_by'), 'likes')
qs = Q()
if request.GET.get('type'):
if request.GET['type'] == 'city':
qs = Q(county__isnull=False)
elif request.GET['type'] == 'candidates':
qs = Q(intent__isnull=False) | Q(candidate__isnull=False)
elif request.GET['type'] == 'councilors':
qs = Q(councilor__isnull=False)
elif request.GET['type'] == 'mayors':
qs = Q(mayor__isnull=False)
platforms = Platforms.objects.filter(qs).prefetch_related(
'intent_standpoints').order_by('-%s' % order_by)
platforms = paginate(request, platforms, 12)
return render(request, 'platforms/lists.html', {'platforms': platforms})
def voter_detail(request, legislator_id, ad):
ly = get_object_or_404(LegislatorDetail.objects, ad=ad, legislator_id=legislator_id)
qs = Q(conflict=True) if request.GET.get('conscience') else Q()
if request.GET.get('decision'):
decisions = {"agree": Q(decision=1), "disagree": Q(decision=-1), "abstain": Q(decision=0), "notvote": Q(decision__isnull=True)}
qs = qs & decisions.get(request.GET['decision'], Q())
hsqs = Q(tags_num__gt=0) if request.GET.get('has_tag') else Q()
hsqs = hsqs & Q(content=request.GET['keyword']) if request.GET.get('keyword') else hsqs
if hsqs != Q():
sqs = SearchQuerySet().filter(hsqs).models(Vote)
votes = ly.votes.select_related('vote', 'vote__sitting').filter(qs & Q(vote_id__in=[x.uid for x in sqs]))
else:
votes = ly.votes.select_related('vote', 'vote__sitting').filter(qs)
votes = paginate(request, votes)
keywords = [x.content for x in SearchQuerySet().filter(category__exact=2).models(Keyword).order_by('-hits')]
get_params = '&'.join(['%s=%s' % (x, request.GET[x]) for x in ['keyword', 'conscience'] if request.GET.get(x)])
return render(request, 'legislator/voter_detail.html', {'keyword_obj': keywords, 'hot_keyword': keywords[:5], 'ly': ly, 'origin': len(qs & hsqs), 'votes': votes, 'keyword': request.GET.get('keyword', ''), 'get_params': get_params})
def bills(request):
if request.GET.get('keyword'):
bills_uid = [
x.uid for x in SearchQuerySet().filter(
content=request.GET['keyword']).models(Bill).order_by('-uid')
]
bills = Bill.objects.filter(uid__in=bills_uid)
else:
bills = Bill.objects.filter(ad=8)
bills = paginate(request, bills)
keywords = keyword_list(3)
return render(
request, 'bill/bills.html', {
'bills': bills,
'keyword_obj': keywords,
'hot_keyword': keywords[:5],
'keyword': request.GET.get('keyword', '')
})
def intents(request, election_year):
ref = {'create_at': 'id', 'likes': 'likes'}
order_by = ref.get(request.GET.get('order_by'), 'likes')
qs = Q(county=request.GET.get('county')) if request.GET.get(
'county') else Q()
qs = qs & Q(election_year=election_year) & ~Q(status='draft')
qs = qs & Q(constituency__in=request.GET.get('constituency').split(
',')) if request.GET.get('constituency') else qs
intents = Intent.objects.filter(qs).order_by('-%s' % order_by)
intents = paginate(request, intents)
intent_counties = Intent.objects.filter(qs).values('county').annotate(
count=Count('county'))
return render(
request, 'candidates/intents.html', {
'intents': intents,
'intent_counties': intent_counties,
'election_year': election_year
})
def biller_detail(request, legislator_id, ad):
qs = Q(content=request.GET["keyword"]) if request.GET.get("keyword") else Q()
bills_id = [x.uid for x in SearchQuerySet().filter(qs).models(Bill).order_by("-uid")]
ly = get_object_or_404(LegislatorDetail.objects, ad=ad, legislator_id=legislator_id)
if len(qs):
bills = ly.bills.select_related("bill").filter(legislator_id=ly.id, role="sponsor", bill_id__in=bills_id)
else:
bills = ly.bills.select_related("bill").filter(legislator_id=ly.id, role="sponsor")
bills = paginate(request, bills)
keywords = keyword_list(3)
get_params = "&".join(["%s=%s" % (x, request.GET[x]) for x in ["keyword"] if request.GET.get(x)])
return render(
request,
"legislator/biller_detail.html",
{
"keyword_obj": keywords,
"hot_keyword": keywords[:5],
"bills": bills,
"ly": ly,
"keyword": request.GET.get("keyword", ""),
"get_params": get_params,
},
)
def bills(request):
if request.GET.get('keyword'):
bills_uid = [
x.uid for x in SearchQuerySet().filter(
content=request.GET['keyword']).models(Bill)
]
bills = Bill.objects.filter(
uid__in=bills_uid).prefetch_related('laws').order_by('-uid')
else:
bills = Bill.objects.all().prefetch_related('laws').order_by('-uid')
bills = paginate(request, bills)
keywords = keyword_list(3)
get_params = '&'.join([
'%s=%s' % (x, request.GET[x]) for x in ['keyword']
if request.GET.get(x)
])
return render(
request, 'bill/bills.html', {
'bills': bills,
'keyword_obj': keywords,
'hot_keyword': keywords[:5],
'keyword': request.GET.get('keyword', ''),
'get_params': get_params
})
try:
councilor = CouncilorsDetail.objects.get(election_year=election_year, councilor_id=councilor_id)
except Exception, e:
return HttpResponseRedirect('/')
query = Q(proposer__id=councilor.id, councilors_bills__petition=False)
primaryonly = request.GET.get('primaryonly', False)
if primaryonly:
query = query & Q(councilors_bills__priproposer=True)
keyword = request.GET.get('keyword', '')
if keyword:
bills = Bills.objects.filter(query & reduce(operator.and_, (Q(abstract__icontains=x) for x in keyword.split()))).order_by('-uid')
if bills:
keyword_been_searched(keyword, 'bills', councilor.county)
else:
bills = Bills.objects.filter(query).order_by('-uid')
bills = paginate(request, bills)
get_params = '&'.join(['%s=%s' % (x, request.GET[x]) for x in ['keyword', 'primaryonly'] if request.GET.get(x)])
return render(request, 'councilors/biller.html', {'keyword_hot': keyword_list('bills', councilor.county), 'county': councilor.county, 'bills': bills, 'councilor': councilor, 'primaryonly': primaryonly, 'category':None, 'index':'councilor', 'get_params': get_params, 'id': 'politics'})
def biller_sp(request, councilor_id, election_year):
councilor = get_object_or_404(CouncilorsDetail.objects, election_year=election_year, councilor_id=councilor_id)
terms_id = tuple(CouncilorsDetail.objects.filter(election_year__lte=election_year, councilor_id=councilor_id).values_list('id', flat=True))
c = connections['default'].cursor()
c.execute(u'''
SELECT json_agg(row)
FROM (
SELECT
CASE
WHEN priproposer = true AND petition = false THEN '主提案'
WHEN petition = false THEN '共同提案'
WHEN petition = true THEN '連署'
) if request.GET.get('tag') else query
keyword = request.GET.get('keyword', '')
if keyword:
bills = Bills.objects.filter(query & reduce(operator.and_, (Q(
abstract__icontains=x) for x in keyword.split())))
if bills:
keyword_been_searched(keyword, 'bills', mayor.county)
else:
bills = Bills.objects.filter(query)
bills = bills.extra(
select={
'tags': "SELECT json_agg(row) FROM (SELECT title, pro FROM standpoints_standpoints su WHERE su.bill_id = bills_bills.uid ORDER BY su.pro DESC) row",
},
)\
.order_by('-uid')
bills = paginate(request, bills)
get_params = '&'.join([
'%s=%s' % (x, request.GET[x]) for x in [
'keyword',
] if request.GET.get(x)
])
return render(
request, 'mayors/biller.html', {
'keyword_hot': keyword_list('bills', mayor.county),
'county': mayor.county,
'bills': bills,
'mayor': mayor,
'get_params': get_params,
'id': 'politics'
})
