def votes(request, county, index='normal'):
result = None
if index == 'conscience':
query = Q(sitting__county=county, conflict=True)
else:
query = Q(sitting__county=county)
#--> 依表決結果分類
if 'result' in request.GET:
result = request.GET['result']
query = query & Q(result=result)
#<--
keyword = keyword_normalize(request.GET)
if keyword:
votes = Votes.objects.filter(query & reduce(operator.and_, (Q(
content__icontains=x) for x in keyword.split()))).order_by(
'-date', 'vote_seq')
if votes:
keyword_been_searched(keyword, 'votes')
else:
votes = Votes.objects.filter(query).order_by('-date', 'vote_seq')
return render(
request, 'votes/votes.html', {
'county': county,
'votes': votes,
'index': index,
'keyword': keyword,
'result': result,
'keyword_hot': keyword_list('votes')
})
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 voter_detail(request, legislator_id, index, ad):
votes, notvote, query = None, False, Q()
ad = ad or 8
ly = get_legislator(legislator_id, ad)
if not ly:
return HttpResponseRedirect('/')
#--> 依投票類型分類
decision_query = {"agree": Q(decision=1), "disagree": Q(decision=-1), "abstain": Q(decision=0), "notvote": Q(decision__isnull=True)}
if 'decision' in request.GET:
decision = request.GET['decision']
if decision_query.get(decision):
query = decision_query.get(decision)
#<--
# 脫黨投票
if index == 'conscience':
query = query & Q(legislator_id=ly.id, conflict=True)
else:
query = query & Q(legislator_id=ly.id)
#<--
keyword = keyword_normalize(request.GET)
if keyword:
votes = Legislator_Vote.objects.select_related().filter(query & reduce(operator.and_, (Q(vote__content__icontains=x) for x in keyword.split()))).order_by('-vote__sitting__date')
if votes:
keyword_been_searched(keyword, 2)
else:
votes = Legislator_Vote.objects.select_related().filter(query).order_by('-vote__sitting__date')
vote_addup = votes.values('decision').annotate(totalNum=Count('vote', distinct=True)).order_by('-decision')
return render(request,'legislator/voter_detail.html', {'keyword_obj':keyword_list(2),'ly':ly,'index':index,'votes':votes,'keyword':keyword,'vote_addup':vote_addup,'notvote':notvote})
def proposals(request,keyword_url):
keyword = keyword_normalize(request, keyword_url)
if keyword:
proposal = Proposal.objects.filter(reduce(operator.and_, (Q(content__icontains=x) for x in keyword.split()))).order_by('-sitting__date')
if proposal:
keyword_been_searched(keyword, 1)
else:
proposal = Proposal.objects.all().order_by('-sitting__date')[:100]
return render(request,'proposal/proposals.html', {'proposal':proposal, 'keyword':keyword, 'keyword_obj':keyword_list(1)})
def bills(request, county, index):
query = Q(county=county)
keyword = keyword_normalize(request.GET)
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')
else:
bills = Bills.objects.filter(query).order_by('-uid')
return render(request, 'bills/bills.html', {'county': county, 'index': index, 'keyword_hot': keyword_list('bills'), 'keyword': keyword, 'bills': bills})
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 bills(request, keyword_url, index):
query = Q()
keyword = keyword_normalize(request, keyword_url)
if keyword:
bills = Bill.objects.filter(reduce(operator.or_, (Q(abstract__icontains=x) | Q(summary__icontains=x) for x in keyword.split())))
query = Q(reduce(operator.or_, (Q(abstract__icontains=x) | Q(summary__icontains=x) for x in keyword.split())))
if bills:
keyword_been_searched(keyword, 3)
if index == 'normal':
bills = Bill.objects.filter(query, last_action__isnull=False).order_by('-last_action_at')[:100]
elif index == 'rejected':
bills = Bill.objects.filter(query & Q(ttsmotions__progress='退回程序')).annotate(totalNum=Count('ttsmotions__id')).filter(totalNum__gt=1).order_by('-totalNum')
return render(request,'bill/bills.html', {'index':index, 'keyword_obj':keyword_list(3), 'keyword':keyword, 'bills':bills})
def votes(request, keyword_url, index='normal'):
if index == 'conscience':
query = Q(conflict=True)
else:
query = Q()
keyword = keyword_normalize(request, keyword_url)
if keyword:
votes = Vote.objects.filter(query & reduce(operator.and_, (Q(content__icontains=x) for x in keyword.split()))).order_by('-sitting__date', 'vote_seq')
if votes:
keyword_been_searched(keyword, 2)
else:
votes = Vote.objects.filter(query).order_by('-sitting__date', 'vote_seq')
return render(request,'vote/votes.html', {'votes': votes, 'index':index, 'keyword':keyword, 'keyword_obj':keyword_list(2)})
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 proposer_detail(request, legislator_id):
proposertype = False
ly = get_legislator(legislator_id, ad=8)
if not ly:
return HttpResponseRedirect('/')
query = Q(proposer__id=ly.id, legislator_proposal__priproposer=True)
if 'proposertype' in request.GET:
proposertype = request.GET['proposertype']
if proposertype:
query = Q(proposer__id=ly.id)
keyword = keyword_normalize(request.GET)
if keyword:
proposal = Proposal.objects.filter(query & reduce(operator.and_, (Q(content__icontains=x) for x in keyword.split()))).order_by('-sitting__date')
if proposal:
keyword_been_searched(keyword, 1)
else:
proposal = Proposal.objects.filter(query).order_by('-sitting__date')
return render(request,'legislator/proposer_detail.html', {'keyword_obj':keyword_list(1),'proposal':proposal,'ly':ly,'keyword':keyword,'proposertype':proposertype})
def bills(request, county, index):
query = Q(county=county)
keyword = keyword_normalize(request.GET)
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')
else:
bills = Bills.objects.filter(query).order_by('-uid')
return render(
request, 'bills/bills.html', {
'county': county,
'index': index,
'keyword_hot': keyword_list('bills'),
'keyword': keyword,
'bills': bills
})
def biller_detail(request, legislator_id):
proposertype = False
ly = get_legislator(legislator_id, ad=8)
if not ly:
return HttpResponseRedirect('/')
query = Q(proposer__id=ly.id, legislator_bill__priproposer=True)
if 'proposertype' in request.GET:
proposertype = request.GET['proposertype']
if proposertype:
query = Q(proposer__id=ly.id)
bills = Bill.objects.filter(query)
keyword = keyword_normalize(request.GET)
if keyword:
bills = bills.filter(query & reduce(operator.and_, (Q(abstract__icontains=x) for x in keyword.split())))
if bills:
keyword_been_searched(keyword, 3)
else:
bills = bills.filter(query)
return render(request,'legislator/biller_detail.html', {'keyword_obj':keyword_list(3),'bills':bills,'ly':ly,'keyword':keyword,'proposertype':proposertype})
def votes(request, election_year, county, index='normal'):
result = None
if index == 'conscience':
query = Q(conflict=True)
else:
query = Q()
#--> 依表決結果分類
if 'result' in request.GET:
result = request.GET['result']
query = query & Q(result=result)
#<--
keyword = keyword_normalize(request.GET)
if keyword:
votes = Votes.objects.filter(query & reduce(operator.and_, (Q(content__icontains=x) for x in keyword.split()))).order_by('-date', 'vote_seq')
if votes:
keyword_been_searched(keyword, 'votes')
else:
votes = Votes.objects.filter(query).order_by('-date', 'vote_seq')
return render(request,'votes/votes.html', {'election_year': election_year, 'county': county, 'votes': votes, 'index':index, 'keyword':keyword, 'result':result, 'keyword_hot': keyword_list('votes')})
def bills_category(request, county, index, category):
query = Q(county=county, category=category)
keyword = keyword_normalize(request.GET)
district = request.GET.get('district', None)
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')
else:
bills = Bills.objects.filter(query)
if district and district != 'all':
all_councilor_id_in_district = list(set([i.id for i in CouncilorsDetail.objects.filter(county=county).filter(district__contains=district)]))
bills = bills.filter(proposer__in=all_councilor_id_in_district)
bills = bills.order_by('-uid')
district_list = list(set([i.district for i in CouncilorsDetail.objects.filter(county=county).filter(~Q(district=''))]))
return render(request, 'bills/bills.html', {'category':category, 'county': county, 'index': index, 'keyword_hot': keyword_list('bills'), 'keyword': keyword, 'bills': bills, 'district_list': district_list})
def bills_category(request, county, index, category):
query = Q(county=county, category=category)
keyword = keyword_normalize(request.GET)
district = request.GET.get('district', None)
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')
else:
bills = Bills.objects.filter(query)
if district and district != 'all':
all_councilor_id_in_district = list(
set([
i.id for i in CouncilorsDetail.objects.filter(
county=county).filter(district__contains=district)
]))
bills = bills.filter(proposer__in=all_councilor_id_in_district)
bills = bills.order_by('-uid')
district_list = list(
set([
i.district for i in CouncilorsDetail.objects.filter(
county=county).filter(~Q(district=''))
]))
return render(
request, 'bills/bills.html', {
'category': category,
'county': county,
'index': index,
'keyword_hot': keyword_list('bills'),
'keyword': keyword,
'bills': bills,
'district_list': district_list
})
def biller(request, councilor_id, election_year):
proposertype = False
try:
councilor = CouncilorsDetail.objects.get(election_year=election_year, councilor_id=councilor_id)
except Exception, e:
return HttpResponseRedirect("/")
query = Q(proposer__id=councilor.id)
keyword = keyword_normalize(request.GET)
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")
else:
bills = Bills.objects.filter(query).order_by("-uid")
data = bills.values("category").annotate(totalNum=Count("id")).order_by("-totalNum")
return render(
request,
"councilors/biller.html",
{
"keyword_hot": keyword_list("bills"),
"bills": bills,
"councilor": councilor,
"keyword": keyword,
"proposertype": proposertype,
"data": list(data),
},
)
return render(request, 'councilors/suggestor.html', {'county': councilor.county, 'suggestions': list(suggestions), 'councilor': councilor, 'total_expense': total_expense})
def biller(request, councilor_id, election_year):
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 = keyword_normalize(request.GET)
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')
else:
bills = Bills.objects.filter(query).order_by('-uid')
return render(request, 'councilors/biller.html', {'keyword_hot': keyword_list('bills'), 'county': councilor.county, 'bills': bills, 'councilor': councilor, 'keyword': keyword, 'primaryonly': primaryonly, 'category':None, 'index':'councilor'})
def biller_category(request, councilor_id, election_year, category):
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, category=category)
primaryonly = request.GET.get('primaryonly', False)
if primaryonly:
query = query & Q(councilors_bills__priproposer=True)
keyword = keyword_normalize(request.GET)
if keyword:
return render(request, 'councilors/suggestor.html', {'county': councilor.county, 'councilor': councilor, 'id': 'fund'})
def biller(request, councilor_id, election_year):
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
def biller(request, councilor_id, election_year):
proposertype = False
try:
councilor = CouncilorsDetail.objects.get(election_year=election_year,
councilor_id=councilor_id)
except Exception, e:
return HttpResponseRedirect('/')
query = Q(proposer__id=councilor.id)
keyword = keyword_normalize(request.GET)
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')
else:
bills = Bills.objects.filter(query).order_by('-uid')
data = bills.values('category').annotate(
totalNum=Count('id')).order_by('-totalNum')
return render(
request, 'councilors/biller.html', {
'keyword_hot': keyword_list('bills'),
'bills': bills,
'councilor': councilor,
'keyword': keyword,
'proposertype': proposertype,
'data': list(data)
})
if request.GET.get('has_tag') == 'yes':
query = query & Q(uid__in=Standpoints.objects.exclude(
bill__isnull=True).values_list('bill_id', flat=True).distinct())
elif request.GET.get('has_tag') == 'no':
query = query & ~Q(uid__in=Standpoints.objects.exclude(
bill__isnull=True).values_list('bill_id', flat=True).distinct())
query = query & Q(uid__in=Standpoints.objects.filter(
title=request.GET['tag']).exclude(
bill__isnull=True).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 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(
