def generateDataset():
count = 14164
for reg in dataframe.itertuples():
if not verifyReviewText(reg.review_text):
print('Review removed because contains an invalid review text.')
elif not verifyJobTitle(reg.job_title):
print('Review removed because contains an invalid job title.')
elif not verifyLocation(reg.location):
print('Review removed because contains an invalid location.')
else:
count += 1
country = util.getCountry(reg.location)
reviews_writer.writerow([
count, reg.job_title, reg.employee_status, reg.location,
country, reg.review_date, reg.review_text
])
print(str(count) + '/' + str(len(dataframe)))
def DefaultCustomDashboard(request,id=0,sector=0,status=0):
"""
# of agreements, approved, rejected, waiting, archived and total for dashboard
"""
program_id = id
countries = getCountry(request.user)
getQuantitativeDataSums = CollectedData.objects.all().filter(indicator__program__id=program_id,achieved__isnull=False).exclude(achieved=None,targeted=None).order_by('indicator__number').values('indicator__number','indicator__name','indicator__id').annotate(targets=Sum('targeted'), actuals=Sum('achieved'))
getFilteredName=Program.objects.get(id=program_id)
getProjectStatus = ProjectStatus.objects.all()
getProjectsCount = ProjectAgreement.objects.all().filter(program__id=program_id, program__country__in=countries).count()
getBudgetEstimated = ProjectAgreement.objects.all().filter(program__id=program_id, program__country__in=countries).annotate(estimated=Sum('total_estimated_budget'))
getAwaitingApprovalCount = ProjectAgreement.objects.all().filter(program__id=program_id, approval='', program__country__in=countries).count()
getApprovedCount = ProjectAgreement.objects.all().filter(program__id=program_id, approval='approved', program__country__in=countries).count()
getRejectedCount = ProjectAgreement.objects.all().filter(program__id=program_id, approval='rejected', program__country__in=countries).count()
getInProgressCount = ProjectAgreement.objects.all().filter(program__id=program_id, approval='in progress', program__country__in=countries).count()
getSiteProfile = SiteProfile.objects.all().filter(projectagreement__program__id=program_id, projectagreement__sector__id=sector)
if (status) =='Approved':
getProjects = ProjectAgreement.objects.all().filter(program__id=program_id, program__country__in=countries,approval='approved')
elif(status) =='Rejected':
getProjects = ProjectAgreement.objects.all().filter(program__id=program_id, program__country__in=countries,approval='rejected')
elif(status) =='In Progress':
getProjects = ProjectAgreement.objects.all().filter(program__id=program_id, program__country__in=countries,approval='in progress')
elif(status) =='Awaiting Approval':
getProjects = ProjectAgreement.objects.all().filter(program__id=program_id, program__country__in=countries,approval='awaiting approval')
else:
getProjects = ProjectAgreement.objects.all().filter(program__id=program_id, program__country__in=countries)
getCustomDashboard = CustomDashboard.objects.all()
return render(request, "customdashboard/visual_dashboard.html", {'getSiteProfile':getSiteProfile, 'getBudgetEstimated': getBudgetEstimated, 'getQuantitativeDataSums': getQuantitativeDataSums,
'country': countries, 'getProjectStatus': getProjectStatus, 'getAwaitingApprovalCount':getAwaitingApprovalCount,
'getFilteredName': getFilteredName,'getProjects': getProjects, 'getApprovedCount': getApprovedCount,
'getRejectedCount': getRejectedCount, 'getInProgressCount': getInProgressCount,
'getCustomDashboard': getCustomDashboard, 'getProjectsCount': getProjectsCount})
def index(request, selected_countries=None, id=0, sector=0):
"""
Home page
get count of agreements approved and total for dashboard
"""
program_id = id
user_countries = getCountry(request.user)
if not selected_countries:
selected_countries = user_countries
selected_countries_list = None
else:
selected_countries_list = ActivityCountry.objects.all().filter(
id__in=selected_countries)
getSectors = Sector.objects.all().exclude(
program__isnull=True).select_related()
#limit the programs by the selected sector
if int(sector) == 0:
getPrograms = Program.objects.all().filter(
funding_status="Funded",
country__in=selected_countries).exclude(agreement__isnull=True)
sectors = Sector.objects.all()
else:
getPrograms = Program.objects.all().filter(
funding_status="Funded",
country__in=selected_countries,
sector=sector).exclude(agreement__isnull=True)
sectors = Sector.objects.all().filter(id=sector)
#get data for just one program or all programs
if int(program_id) == 0:
getFilteredName = None
#filter by all programs then filter by sector if found
if int(sector) > 0:
getSiteProfile = SiteProfile.objects.all().filter(
Q(Q(projectagreement__sector__in=sectors)),
country__in=selected_countries)
agreement_total_count = ProjectAgreement.objects.all().filter(
sector__in=sectors,
program__country__in=selected_countries).count()
complete_total_count = ProjectComplete.objects.all().filter(
project_agreement__sector__in=sectors,
program__country__in=selected_countries).count()
agreement_approved_count = ProjectAgreement.objects.all().filter(
approval='approved',
sector__in=sectors,
program__country__in=selected_countries).count()
complete_approved_count = ProjectComplete.objects.all().filter(
approval='approved',
project_agreement__sector__in=sectors,
program__country__in=selected_countries).count()
agreement_open_count = ProjectAgreement.objects.all().filter(
Q(Q(approval='open') | Q(approval="") | Q(approval=None)),
sector__id__in=sectors,
program__country__in=selected_countries).count()
complete_open_count = ProjectComplete.objects.all().filter(
Q(Q(approval='open') | Q(approval="") | Q(approval=None)),
project_agreement__sector__in=sectors,
program__country__in=selected_countries).count()
agreement_wait_count = ProjectAgreement.objects.all().filter(
Q(approval='in progress') & Q(
Q(approval='in progress') | Q(approval=None)
| Q(approval="")),
sector__in=sectors,
program__country__in=selected_countries).count()
complete_wait_count = ProjectComplete.objects.all().filter(
Q(approval='in progress') & Q(
Q(approval='in progress') | Q(approval=None)
| Q(approval="")),
project_agreement__sector__in=sectors,
program__country__in=selected_countries).count()
getQuantitativeDataSums = CollectedData.objects.all().filter(
Q(agreement__sector__in=sectors),
achieved__isnull=False,
targeted__isnull=False,
indicator__country__in=selected_countries).exclude(
achieved=None,
targeted=None).order_by('indicator__number').values(
'indicator__number', 'indicator__name',
'indicator__id').annotate(targets=Sum('targeted'),
actuals=Sum('achieved'))
else:
getSiteProfile = SiteProfile.objects.all().filter(
country__in=selected_countries)
agreement_total_count = ProjectAgreement.objects.all().filter(
program__country__in=selected_countries).count()
complete_total_count = ProjectComplete.objects.all().filter(
program__country__in=selected_countries).count()
agreement_approved_count = ProjectAgreement.objects.all().filter(
approval='approved',
program__country__in=selected_countries).count()
complete_approved_count = ProjectComplete.objects.all().filter(
approval='approved',
program__country__in=selected_countries).count()
agreement_open_count = ProjectAgreement.objects.all().filter(
Q(Q(approval='open') | Q(approval="") | Q(approval=None)),
program__country__in=selected_countries).count()
complete_open_count = ProjectComplete.objects.all().filter(
Q(Q(approval='open') | Q(approval="") | Q(approval=None)),
program__country__in=selected_countries).count()
agreement_wait_count = ProjectAgreement.objects.all().filter(
Q(approval='in progress') & Q(
Q(approval='in progress') | Q(approval=None)
| Q(approval="")),
program__country__in=selected_countries).count()
complete_wait_count = ProjectComplete.objects.all().filter(
Q(approval='in progress') & Q(
Q(approval='in progress') | Q(approval=None)
| Q(approval="")),
program__country__in=selected_countries).count()
getQuantitativeDataSums = CollectedData.objects.all().filter(
Q(agreement__sector__isnull=True),
achieved__isnull=False,
targeted__isnull=False,
indicator__country__in=selected_countries).exclude(
achieved=None,
targeted=None).order_by('indicator__number').values(
'indicator__number', 'indicator__name',
'indicator__id').annotate(targets=Sum('targeted'),
actuals=Sum('achieved'))
else:
getFilteredName = Program.objects.get(id=program_id)
agreement_total_count = ProjectAgreement.objects.all().filter(
program__id=program_id).count()
complete_total_count = ProjectComplete.objects.all().filter(
program__id=program_id).count()
agreement_approved_count = ProjectAgreement.objects.all().filter(
program__id=program_id, approval='approved').count()
complete_approved_count = ProjectComplete.objects.all().filter(
program__id=program_id, approval='approved').count()
agreement_open_count = ProjectAgreement.objects.all().filter(
program__id=program_id, approval='open').count()
complete_open_count = ProjectComplete.objects.all().filter(
Q(Q(approval='open') | Q(approval="")),
program__id=program_id).count()
agreement_wait_count = ProjectAgreement.objects.all().filter(
Q(program__id=program_id),
Q(approval='in progress')
& Q(Q(approval='in progress') | Q(approval=None)
| Q(approval=""))).count()
complete_wait_count = ProjectComplete.objects.all().filter(
Q(program__id=program_id),
Q(approval='in progress')
& Q(Q(approval='in progress') | Q(approval=None)
| Q(approval=""))).count()
getSiteProfile = SiteProfile.objects.all().filter(
projectagreement__program__id=program_id)
getQuantitativeDataSums = CollectedData.objects.all().filter(
indicator__program__id=program_id, achieved__isnull=False).exclude(
achieved=None,
targeted=None).order_by('indicator__number').values(
'indicator__number', 'indicator__name',
'indicator__id').annotate(targets=Sum('targeted'),
actuals=Sum('achieved'))
#Evidence and Objectives are for the global leader dashboard items and are the same every time
count_evidence = CollectedData.objects.all().filter(
indicator__isnull=False).values(
"indicator__country__country").annotate(
evidence_count=Count('evidence', distinct=True),
indicator_count=Count(
'pk', distinct=True)).order_by('-evidence_count')
getObjectives = CollectedData.objects.all().filter(
indicator__strategic_objectives__isnull=False,
indicator__country__in=selected_countries).exclude(
achieved=None, targeted=None).order_by(
'indicator__strategic_objectives__name').values(
'indicator__strategic_objectives__name').annotate(
indicators=Count('pk', distinct=True),
targets=Sum('targeted'),
actuals=Sum('achieved'))
table = IndicatorDataTable(getQuantitativeDataSums)
table.paginate(page=request.GET.get('page', 1), per_page=20)
print selected_countries_list
return render(request, "index.html", {'agreement_total_count':agreement_total_count,\
'agreement_approved_count':agreement_approved_count,\
'agreement_open_count':agreement_open_count,\
'agreement_wait_count':agreement_wait_count,\
'complete_open_count':complete_open_count,\
'complete_approved_count':complete_approved_count,'complete_total_count':complete_total_count,\
'complete_wait_count':complete_wait_count,\
'programs':getPrograms,'getSiteProfile':getSiteProfile,'countries': user_countries,'selected_countries':selected_countries,'getFilteredName':getFilteredName,'getSectors':getSectors,\
'sector': sector, 'table': table, 'getQuantitativeDataSums':getQuantitativeDataSums,\
'count_evidence':count_evidence,
'getObjectives':getObjectives,
'selected_countries_list': selected_countries_list
})
def index(request,selected_countries=None,id=0,sector=0):
"""
Home page
get count of agreements approved and total for dashboard
"""
program_id = id
user_countries = getCountry(request.user)
if not selected_countries:
selected_countries = user_countries
selected_countries_list = None
else:
selected_countries_list = ActivityCountry.objects.all().filter(id__in=selected_countries)
getSectors = Sector.objects.all().exclude(program__isnull=True).select_related()
#limit the programs by the selected sector
if int(sector) == 0:
getPrograms = Program.objects.all().filter(funding_status="Funded", country__in=selected_countries).exclude(agreement__isnull=True)
sectors = Sector.objects.all()
else:
getPrograms = Program.objects.all().filter(funding_status="Funded", country__in=selected_countries, sector=sector).exclude(agreement__isnull=True)
sectors = Sector.objects.all().filter(id=sector)
#get data for just one program or all programs
if int(program_id) == 0:
getFilteredName=None
#filter by all programs then filter by sector if found
if int(sector) > 0:
getSiteProfile = SiteProfile.objects.all().filter(Q(Q(projectagreement__sector__in=sectors)), country__in=selected_countries)
agreement_total_count = ProjectAgreement.objects.all().filter(sector__in=sectors, program__country__in=selected_countries).count()
complete_total_count = ProjectComplete.objects.all().filter(project_agreement__sector__in=sectors, program__country__in=selected_countries).count()
agreement_approved_count = ProjectAgreement.objects.all().filter(approval='approved', sector__in=sectors, program__country__in=selected_countries).count()
complete_approved_count = ProjectComplete.objects.all().filter(approval='approved', project_agreement__sector__in=sectors, program__country__in=selected_countries).count()
agreement_open_count = ProjectAgreement.objects.all().filter(Q(Q(approval='open') | Q(approval="") | Q(approval=None)), sector__id__in=sectors, program__country__in=selected_countries).count()
complete_open_count = ProjectComplete.objects.all().filter(Q(Q(approval='open') | Q(approval="") | Q(approval=None)), project_agreement__sector__in=sectors, program__country__in=selected_countries).count()
agreement_wait_count = ProjectAgreement.objects.all().filter(Q(approval='in progress') & Q(Q(approval='in progress') | Q(approval=None) | Q(approval="")), sector__in=sectors, program__country__in=selected_countries).count()
complete_wait_count = ProjectComplete.objects.all().filter(Q(approval='in progress') & Q(Q(approval='in progress') | Q(approval=None) | Q(approval="")), project_agreement__sector__in=sectors, program__country__in=selected_countries).count()
getQuantitativeDataSums = CollectedData.objects.all().filter(Q(agreement__sector__in=sectors), achieved__isnull=False, targeted__isnull=False, indicator__country__in=selected_countries).exclude(achieved=None,targeted=None).order_by('indicator__number').values('indicator__number','indicator__name','indicator__id').annotate(targets=Sum('targeted'), actuals=Sum('achieved'))
else:
getSiteProfile = SiteProfile.objects.all().filter(country__in=selected_countries)
agreement_total_count = ProjectAgreement.objects.all().filter(program__country__in=selected_countries).count()
complete_total_count = ProjectComplete.objects.all().filter(program__country__in=selected_countries).count()
agreement_approved_count = ProjectAgreement.objects.all().filter(approval='approved', program__country__in=selected_countries).count()
complete_approved_count = ProjectComplete.objects.all().filter(approval='approved', program__country__in=selected_countries).count()
agreement_open_count = ProjectAgreement.objects.all().filter(Q(Q(approval='open') | Q(approval="") | Q(approval=None)), program__country__in=selected_countries).count()
complete_open_count = ProjectComplete.objects.all().filter(Q(Q(approval='open') | Q(approval="") | Q(approval=None)), program__country__in=selected_countries).count()
agreement_wait_count = ProjectAgreement.objects.all().filter(Q(approval='in progress') & Q(Q(approval='in progress') | Q(approval=None) | Q(approval="")), program__country__in=selected_countries).count()
complete_wait_count = ProjectComplete.objects.all().filter(Q(approval='in progress') & Q(Q(approval='in progress') | Q(approval=None) | Q(approval="")),program__country__in=selected_countries).count()
getQuantitativeDataSums = CollectedData.objects.all().filter(Q(agreement__sector__isnull=True), achieved__isnull=False, targeted__isnull=False, indicator__country__in=selected_countries).exclude(achieved=None,targeted=None).order_by('indicator__number').values('indicator__number','indicator__name','indicator__id').annotate(targets=Sum('targeted'), actuals=Sum('achieved'))
else:
getFilteredName=Program.objects.get(id=program_id)
agreement_total_count = ProjectAgreement.objects.all().filter(program__id=program_id).count()
complete_total_count = ProjectComplete.objects.all().filter(program__id=program_id).count()
agreement_approved_count = ProjectAgreement.objects.all().filter(program__id=program_id, approval='approved').count()
complete_approved_count = ProjectComplete.objects.all().filter(program__id=program_id, approval='approved').count()
agreement_open_count = ProjectAgreement.objects.all().filter(program__id=program_id, approval='open').count()
complete_open_count = ProjectComplete.objects.all().filter(Q(Q(approval='open') | Q(approval="")), program__id=program_id).count()
agreement_wait_count = ProjectAgreement.objects.all().filter(Q(program__id=program_id), Q(approval='in progress') & Q(Q(approval='in progress') | Q(approval=None) | Q(approval=""))).count()
complete_wait_count = ProjectComplete.objects.all().filter(Q(program__id=program_id), Q(approval='in progress') & Q(Q(approval='in progress') | Q(approval=None) | Q(approval=""))).count()
getSiteProfile = SiteProfile.objects.all().filter(projectagreement__program__id=program_id)
getQuantitativeDataSums = CollectedData.objects.all().filter(indicator__program__id=program_id,achieved__isnull=False).exclude(achieved=None,targeted=None).order_by('indicator__number').values('indicator__number','indicator__name','indicator__id').annotate(targets=Sum('targeted'), actuals=Sum('achieved'))
#Evidence and Objectives are for the global leader dashboard items and are the same every time
count_evidence = CollectedData.objects.all().filter(indicator__isnull=False).values("indicator__country__country").annotate(evidence_count=Count('evidence', distinct=True),indicator_count=Count('pk', distinct=True)).order_by('-evidence_count')
getObjectives = CollectedData.objects.all().filter(indicator__strategic_objectives__isnull=False, indicator__country__in=selected_countries).exclude(achieved=None,targeted=None).order_by('indicator__strategic_objectives__name').values('indicator__strategic_objectives__name').annotate(indicators=Count('pk', distinct=True),targets=Sum('targeted'), actuals=Sum('achieved'))
table = IndicatorDataTable(getQuantitativeDataSums)
table.paginate(page=request.GET.get('page', 1), per_page=20)
print selected_countries_list
return render(request, "index.html", {'agreement_total_count':agreement_total_count,\
'agreement_approved_count':agreement_approved_count,\
'agreement_open_count':agreement_open_count,\
'agreement_wait_count':agreement_wait_count,\
'complete_open_count':complete_open_count,\
'complete_approved_count':complete_approved_count,'complete_total_count':complete_total_count,\
'complete_wait_count':complete_wait_count,\
'programs':getPrograms,'getSiteProfile':getSiteProfile,'countries': user_countries,'selected_countries':selected_countries,'getFilteredName':getFilteredName,'getSectors':getSectors,\
'sector': sector, 'table': table, 'getQuantitativeDataSums':getQuantitativeDataSums,\
'count_evidence':count_evidence,
'getObjectives':getObjectives,
'selected_countries_list': selected_countries_list
})
def DefaultCustomDashboard(request, id=0, sector=0, status=0):
"""
# of agreements, approved, rejected, waiting, archived and total for dashboard
"""
program_id = id
countries = getCountry(request.user)
getQuantitativeDataSums = CollectedData.objects.all().filter(
indicator__program__id=program_id, achieved__isnull=False).exclude(
achieved=None, targeted=None).order_by('indicator__number').values(
'indicator__number', 'indicator__name',
'indicator__id').annotate(targets=Sum('targeted'),
actuals=Sum('achieved'))
getFilteredName = Program.objects.get(id=program_id)
getProjectStatus = ProjectStatus.objects.all()
getProjectsCount = ProjectAgreement.objects.all().filter(
program__id=program_id, program__country__in=countries).count()
getBudgetEstimated = ProjectAgreement.objects.all().filter(
program__id=program_id, program__country__in=countries).annotate(
estimated=Sum('total_estimated_budget'))
getAwaitingApprovalCount = ProjectAgreement.objects.all().filter(
program__id=program_id, approval='',
program__country__in=countries).count()
getApprovedCount = ProjectAgreement.objects.all().filter(
program__id=program_id,
approval='approved',
program__country__in=countries).count()
getRejectedCount = ProjectAgreement.objects.all().filter(
program__id=program_id,
approval='rejected',
program__country__in=countries).count()
getInProgressCount = ProjectAgreement.objects.all().filter(
program__id=program_id,
approval='in progress',
program__country__in=countries).count()
getSiteProfile = SiteProfile.objects.all().filter(
projectagreement__program__id=program_id,
projectagreement__sector__id=sector)
if (status) == 'Approved':
getProjects = ProjectAgreement.objects.all().filter(
program__id=program_id,
program__country__in=countries,
approval='approved')
elif (status) == 'Rejected':
getProjects = ProjectAgreement.objects.all().filter(
program__id=program_id,
program__country__in=countries,
approval='rejected')
elif (status) == 'In Progress':
getProjects = ProjectAgreement.objects.all().filter(
program__id=program_id,
program__country__in=countries,
approval='in progress')
elif (status) == 'Awaiting Approval':
getProjects = ProjectAgreement.objects.all().filter(
program__id=program_id,
program__country__in=countries,
approval='awaiting approval')
else:
getProjects = ProjectAgreement.objects.all().filter(
program__id=program_id, program__country__in=countries)
getCustomDashboard = CustomDashboard.objects.all()
return render(
request, "customdashboard/visual_dashboard.html", {
'getSiteProfile': getSiteProfile,
'getBudgetEstimated': getBudgetEstimated,
'getQuantitativeDataSums': getQuantitativeDataSums,
'country': countries,
'getProjectStatus': getProjectStatus,
'getAwaitingApprovalCount': getAwaitingApprovalCount,
'getFilteredName': getFilteredName,
'getProjects': getProjects,
'getApprovedCount': getApprovedCount,
'getRejectedCount': getRejectedCount,
'getInProgressCount': getInProgressCount,
'getCustomDashboard': getCustomDashboard,
'getProjectsCount': getProjectsCount
})