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(