from django.http import JsonResponse from django.shortcuts import render from django.views import View from django.contrib.auth.mixins import LoginRequiredMixin from django.db.models import F, FloatField, Count, Sum, ExpressionWrapper, Value from django.db.models.functions import Coalesce, Cast from hr_department.models import Team from program_department.models import Programs, Projects from finance_department.models import Expenditure, Incomes, HRExpenditure, EquipmentExpenditure, TravelExpenditure, IECExpenditure, AccommodationExpenditure, MiscellaneousExpenditure, OfficeExpenditure # Create your views here. class DashboardViews(LoginRequiredMixin, View): def get(self, request): team = Team.objects.all() beneficiaries = Programs.objects.all() projects = Projects.objects.all() liabilities = Expenditure.objects.all() income_amount = Incomes.objects.all() hr_amount = HRExpenditure.objects.all() equipment_amount = EquipmentExpenditure.objects.all() travel_amount = TravelExpenditure.objects.all() iec_amount = IECExpenditure.objects.all() accomodation_amount = AccommodationExpenditure.objects.all() miscellaneous_amount = MiscellaneousExpenditure.objects.all() office_amount = OfficeExpenditure.objects.all() if 'project' in request.GET and request.GET['project'] != "All": income_amount = Incomes.objects.filter(project_id = request.GET['project']) hr_amount = HRExpenditure.objects.filter(expenditure__project_id = request.GET['project']) equipment_amount = EquipmentExpenditure.objects.filter(expenditure__project_id = request.GET['project']) travel_amount = TravelExpenditure.objects.filter(expenditure__project_id = request.GET['project']) iec_amount = IECExpenditure.objects.filter(expenditure__project_id = request.GET['project']) accomodation_amount = AccommodationExpenditure.objects.filter(expenditure__project_id = request.GET['project']) miscellaneous_amount = MiscellaneousExpenditure.objects.filter(expenditure__project_id = request.GET['project']) office_amount = OfficeExpenditure.objects.filter(expenditure__project_id = request.GET['project']) project = request.GET and request.GET['project'] team = team.filter(program_team__project_id = request.GET['project']).distinct() beneficiaries = beneficiaries.filter(project_id = request.GET['project']) liabilities = liabilities.filter(project_id = request.GET['project']) else: project = 'All' if 'start_date' in request.GET and request.GET['start_date'] != "" and 'end_date' in request.GET and request.GET['end_date'] != "": income_amount = income_amount.filter(payment_date__gte = request.GET['start_date'], payment_date__lte = request.GET['end_date']) hr_amount = hr_amount.filter(hr_expence_date__gte = request.GET['start_date'], hr_expence_date__lte = request.GET['end_date']) equipment_amount = equipment_amount.filter(equipment_expence_date__gte = request.GET['start_date'], equipment_expence_date__lte = request.GET['end_date']) travel_amount = travel_amount.filter(travel_expence_date__gte = request.GET['start_date'], travel_expence_date__lte = request.GET['end_date']) iec_amount = iec_amount.filter(iec_expence_date__gte = request.GET['start_date'], iec_expence_date__lte = request.GET['end_date']) accomodation_amount = accomodation_amount.filter(accommodation_expence_date__gte = request.GET['start_date'], accommodation_expence_date__lte = request.GET['end_date']) miscellaneous_amount = miscellaneous_amount.filter(miscellaneous_expence_date__gte = request.GET['start_date'], miscellaneous_expence_date__lte = request.GET['end_date']) office_amount = office_amount.filter(office_expence_date__gte = request.GET['start_date'], office_expence_date__lte = request.GET['end_date']) liabilities = liabilities.filter(expence_date__gte = request.GET['start_date'], expence_date__lte = request.GET['end_date']) start_date = request.GET['start_date'] end_date = request.GET['end_date'] else: start_date = "" end_date = "" income_amount = income_amount.aggregate(income_amount = Sum('received_amount'))['income_amount'] or 0 hr_amount = hr_amount.aggregate(hr_amount = Sum('hr_amount'))['hr_amount'] or 0 equipment_amount = equipment_amount.aggregate(equipment_amount = Sum('equipment_amount'))['equipment_amount'] or 0 travel_amount = travel_amount.aggregate(travel_amount = Sum('travel_amount'))['travel_amount'] or 0 iec_amount = iec_amount.aggregate(iec_amount = Sum('iec_amount'))['iec_amount'] or 0 accommodation_amount = accomodation_amount.aggregate(accommodation_amount = Sum('accommodation_amount'))['accommodation_amount'] or 0 miscellaneous_amount = miscellaneous_amount.aggregate(miscellaneous_amount = Sum('miscellaneous_amount'))['miscellaneous_amount'] or 0 office_amount = office_amount.aggregate(office_amount = Sum('office_amount'))['office_amount'] or 0 expenditure_amount = hr_amount + equipment_amount + travel_amount + iec_amount + accommodation_amount + miscellaneous_amount + office_amount team = team.count() beneficiaries_count = 0 for beneficiary in beneficiaries: if beneficiary.document_apply is None and beneficiary.scheme_apply is None: beneficiaries_count = beneficiaries_count + 1 else: if beneficiary.scheme_apply: beneficiaries_count = beneficiaries_count + len(beneficiary.scheme_apply.split(",")) if beneficiary.document_apply: beneficiaries_count = beneficiaries_count + len(beneficiary.document_apply.split(",")) liabilities = liabilities.annotate( travel_total_amount=Coalesce(Sum(Cast('travel__travel_amount', FloatField())), Value(0.0), output_field=FloatField()), travel_total_tds=Coalesce(Sum( Cast('travel__travel_amount', FloatField()) * Cast('travel__travel_tds_deduction', FloatField()) / 100.0 ), Value(0.0), output_field=FloatField()) ).annotate( total_amount=ExpressionWrapper( Coalesce(Cast(F('hr__hr_amount'), FloatField()), Value(0.0), output_field=FloatField()) + Coalesce(Cast(F('equipment__equipment_amount'), FloatField()), Value(0.0), output_field=FloatField()) + Coalesce(Sum(Cast('travel__travel_amount', FloatField())), Value(0.0), output_field=FloatField()) + Coalesce(Cast(F('iec__iec_amount'), FloatField()), Value(0.0), output_field=FloatField()) + Coalesce(Cast(F('accommodation__accommodation_amount'), FloatField()), Value(0.0), output_field=FloatField()) + Coalesce(Cast(F('miscellaneous__miscellaneous_amount'), FloatField()), Value(0.0), output_field=FloatField()) + Coalesce(Cast(F('office__office_amount'), FloatField()), Value(0.0), output_field=FloatField()), output_field=FloatField() ), total_tds=ExpressionWrapper( (Coalesce(Cast(F('hr__hr_amount'), FloatField()), Value(0.0), output_field=FloatField()) * Coalesce(Cast(F('hr__hr_tds_deduction'), FloatField()), Value(0.0), output_field=FloatField()) / 100.0) + (Coalesce(Cast(F('equipment__equipment_amount'), FloatField()), Value(0.0), output_field=FloatField()) * Coalesce(Cast(F('equipment__equipment_tds_deduction'), FloatField()), Value(0.0), output_field=FloatField()) / 100.0) + F('travel_total_tds') + (Coalesce(Cast(F('iec__iec_amount'), FloatField()), Value(0.0), output_field=FloatField()) * Coalesce(Cast(F('iec__iec_tds_deduction'), FloatField()), Value(0.0), output_field=FloatField()) / 100.0) + (Coalesce(Cast(F('accommodation__accommodation_amount'), FloatField()), Value(0.0), output_field=FloatField()) * Coalesce(Cast(F('accommodation__accommodation_tds_deduction'), FloatField()), Value(0.0), output_field=FloatField()) / 100.0) + (Coalesce(Cast(F('miscellaneous__miscellaneous_amount'), FloatField()), Value(0.0), output_field=FloatField()) * Coalesce(Cast(F('miscellaneous__miscellaneous_tds_deduction'), FloatField()), Value(0.0), output_field=FloatField()) / 100.0) + (Coalesce(Cast(F('office__office_amount'), FloatField()), Value(0.0), output_field=FloatField()) * Coalesce(Cast(F('office__office_tds_deduction'), FloatField()), Value(0.0), output_field=FloatField()) / 100.0), output_field=FloatField() ) ).annotate( net_amount=ExpressionWrapper( F('total_amount') - F('advance') - F('total_tds'), output_field=FloatField() ) ) liabilities_amount = liabilities.filter(payment_status = 0) liablility_amount = 0 for liability in liabilities_amount: liablility_amount = liablility_amount + liability.net_amount assets = liabilities.filter(assets = 1) assets_amount = 0 for asset in assets: assets_amount = assets_amount + asset.net_amount return render(request, "dashboard.html", {'income_amount' : income_amount, 'expenditure_amount' : expenditure_amount, 'team' : team, 'beneficiaries' : beneficiaries_count, 'projects' : projects, 'project' : project, 'start_date' : start_date, 'end_date' : end_date, 'liablility_amount' : liablility_amount, 'assets_amount' : assets_amount}) class ExpenditureChartView(LoginRequiredMixin, View): def get(self, request): # Get counts grouped by state as array hr_amount = HRExpenditure.objects.all() equipment_amount = EquipmentExpenditure.objects.all() travel_amount = TravelExpenditure.objects.all() iec_amount = IECExpenditure.objects.all() accommodation_amount = AccommodationExpenditure.objects.all() miscellaneous_amount = MiscellaneousExpenditure.objects.all() office_amount = OfficeExpenditure.objects.all() if 'project' in request.GET and request.GET['project'] != "All": hr_amount = HRExpenditure.objects.filter(expenditure__project_id = request.GET['project']) equipment_amount = EquipmentExpenditure.objects.filter(expenditure__project_id = request.GET['project']) travel_amount = TravelExpenditure.objects.filter(expenditure__project_id = request.GET['project']) iec_amount = IECExpenditure.objects.filter(expenditure__project_id = request.GET['project']) accommodation_amount = AccommodationExpenditure.objects.filter(expenditure__project_id = request.GET['project']) miscellaneous_amount = MiscellaneousExpenditure.objects.filter(expenditure__project_id = request.GET['project']) office_amount = OfficeExpenditure.objects.filter(expenditure__project_id = request.GET['project']) if 'start_date' in request.GET and request.GET['start_date'] != "" and 'end_date' in request.GET and request.GET['end_date'] != "": hr_amount = hr_amount.filter(hr_expence_date__gte = request.GET['start_date'], hr_expence_date__lte = request.GET['end_date']) equipment_amount = equipment_amount.filter(equipment_expence_date__gte = request.GET['start_date'], equipment_expence_date__lte = request.GET['end_date']) travel_amount = travel_amount.filter(travel_expence_date__gte = request.GET['start_date'], travel_expence_date__lte = request.GET['end_date']) iec_amount = iec_amount.filter(iec_expence_date__gte = request.GET['start_date'], iec_expence_date__lte = request.GET['end_date']) accommodation_amount = accommodation_amount.filter(accommodation_expence_date__gte = request.GET['start_date'], accommodation_expence_date__lte = request.GET['end_date']) miscellaneous_amount = miscellaneous_amount.filter(miscellaneous_expence_date__gte = request.GET['start_date'], miscellaneous_expence_date__lte = request.GET['end_date']) office_amount = office_amount.filter(office_expence_date__gte = request.GET['start_date'], office_expence_date__lte = request.GET['end_date']) hr_amount = hr_amount.aggregate(hr_amount = Sum('hr_amount'))['hr_amount'] or 0 equipment_amount = equipment_amount.aggregate(equipment_amount = Sum('equipment_amount'))['equipment_amount'] or 0 travel_amount = travel_amount.aggregate(travel_amount = Sum('travel_amount'))['travel_amount'] or 0 iec_amount = iec_amount.aggregate(iec_amount = Sum('iec_amount'))['iec_amount'] or 0 accommodation_amount = accommodation_amount.aggregate(accommodation_amount = Sum('accommodation_amount'))['accommodation_amount'] or 0 miscellaneous_amount = miscellaneous_amount.aggregate(miscellaneous_amount = Sum('miscellaneous_amount'))['miscellaneous_amount'] or 0 office_amount = office_amount.aggregate(office_amount = Sum('office_amount'))['office_amount'] or 0 amounts = [ {'name' : 'Human Resource Amount', 'y': hr_amount}, {'name' : 'Equipment Amount', 'y' : equipment_amount}, {'name' : 'Travel Amount', 'y' : travel_amount}, {'name' : 'IEC Amount', 'y' : iec_amount}, {'name' : 'Accomodation Amount', 'y' : accommodation_amount}, {'name' : 'Miscellaneous Amount', 'y' : miscellaneous_amount}, {'name' : 'Office Amount', 'y' : office_amount}, ] return JsonResponse({'amounts': amounts}, safe=False) class IncomeChartView(LoginRequiredMixin, View): def get(self, request): incomes_data = Incomes.objects.all() if 'project' in request.GET and request.GET['project'] != "All": incomes_data = incomes_data.filter(project_id = request.GET['project']) if 'start_date' in request.GET and request.GET['start_date'] != "" and 'end_date' in request.GET and request.GET['end_date'] != "": incomes_data = incomes_data.filter(payment_date__gte = request.GET['start_date'], payment_date__lte = request.GET['end_date']) human_resource = incomes_data.aggregate(human_resource = Sum('human_resource'))['human_resource'] or 0 camp_expenses = incomes_data.aggregate(camp_expenses = Sum('camp_expenses'))['camp_expenses'] or 0 training_expenses = incomes_data.aggregate(training_expenses = Sum('training_expenses'))['training_expenses'] or 0 equipment_expenses = incomes_data.aggregate(equipment_expenses = Sum('equipment_expenses'))['equipment_expenses'] or 0 travel_expenses = incomes_data.aggregate(travel_expenses = Sum('travel_expenses'))['travel_expenses'] or 0 material_expences = incomes_data.aggregate(material_expences = Sum('material_expences'))['material_expences'] or 0 administrative_expenses = incomes_data.aggregate(administrative_expenses = Sum('administrative_expenses'))['administrative_expenses'] or 0 accomodation_expenses = incomes_data.aggregate(accomodation_expenses = Sum('accomodation_expenses'))['accomodation_expenses'] or 0 monitoring_expenses = incomes_data.aggregate(monitoring_expenses = Sum('monitoring_expenses'))['monitoring_expenses'] or 0 miscellaneous_expenses = incomes_data.aggregate(miscellaneous_expenses = Sum('miscellaneous_expenses'))['miscellaneous_expenses'] or 0 incomes = [ {'name' : 'Human Resource Expenses', 'y': human_resource}, {'name' : 'Camp Expenses', 'y' : camp_expenses}, {'name' : 'Training Expenses', 'y' : training_expenses}, {'name' : 'Equipment Expenses', 'y' : equipment_expenses}, {'name' : 'Travel Expenses', 'y' : travel_expenses}, {'name' : 'Material Expences', 'y' : material_expences}, {'name' : 'Administrative Expenses', 'y' : administrative_expenses}, {'name' : 'Accomodation Expenses', 'y' : accomodation_expenses}, {'name' : 'Monitoring Expenses', 'y' : monitoring_expenses}, {'name' : 'Miscellaneous Expenses', 'y' : miscellaneous_expenses}, ] return JsonResponse({ 'income_result': incomes }, safe=False) class IncomeExpenditureChartView(LoginRequiredMixin, View): def get(self, request): incomes_data = Incomes.objects.all() hr_amount = HRExpenditure.objects.all() equipment_amount = EquipmentExpenditure.objects.all() travel_amount = TravelExpenditure.objects.all() iec_amount = IECExpenditure.objects.all() accommodation_amount = AccommodationExpenditure.objects.all() miscellaneous_amount = MiscellaneousExpenditure.objects.all() if 'project' in request.GET and request.GET['project'] != "All": incomes_data = incomes_data.filter(project_id = request.GET['project']) hr_amount = HRExpenditure.objects.filter(expenditure__project_id = request.GET['project']) equipment_amount = EquipmentExpenditure.objects.filter(expenditure__project_id = request.GET['project']) travel_amount = TravelExpenditure.objects.filter(expenditure__project_id = request.GET['project']) iec_amount = IECExpenditure.objects.filter(expenditure__project_id = request.GET['project']) accommodation_amount = AccommodationExpenditure.objects.filter(expenditure__project_id = request.GET['project']) miscellaneous_amount = MiscellaneousExpenditure.objects.filter(expenditure__project_id = request.GET['project']) if 'start_date' in request.GET and request.GET['start_date'] != "" and 'end_date' in request.GET and request.GET['end_date'] != "": incomes_data = incomes_data.filter(payment_date__gte = request.GET['start_date'], payment_date__lte = request.GET['end_date']) hr_amount = hr_amount.filter(hr_expence_date__gte = request.GET['start_date'], hr_expence_date__lte = request.GET['end_date']) equipment_amount = equipment_amount.filter(equipment_expence_date__gte = request.GET['start_date'], equipment_expence_date__lte = request.GET['end_date']) travel_amount = travel_amount.filter(travel_expence_date__gte = request.GET['start_date'], travel_expence_date__lte = request.GET['end_date']) iec_amount = iec_amount.filter(iec_expence_date__gte = request.GET['start_date'], iec_expence_date__lte = request.GET['end_date']) accommodation_amount = accommodation_amount.filter(accommodation_expence_date__gte = request.GET['start_date'], accommodation_expence_date__lte = request.GET['end_date']) miscellaneous_amount = miscellaneous_amount.filter(miscellaneous_expence_date__gte = request.GET['start_date'], miscellaneous_expence_date__lte = request.GET['end_date']) human_resource = incomes_data.aggregate(human_resource = Sum('human_resource'))['human_resource'] or 0 equipment_expenses = incomes_data.aggregate(equipment_expenses = Sum('equipment_expenses'))['equipment_expenses'] or 0 travel_expenses = incomes_data.aggregate(travel_expenses = Sum('travel_expenses'))['travel_expenses'] or 0 material_expences = incomes_data.aggregate(material_expences = Sum('material_expences'))['material_expences'] or 0 accomodation_expenses = incomes_data.aggregate(accomodation_expenses = Sum('accomodation_expenses'))['accomodation_expenses'] or 0 miscellaneous_expenses = incomes_data.aggregate(miscellaneous_expenses = Sum('miscellaneous_expenses'))['miscellaneous_expenses'] or 0 hr_amount = hr_amount.aggregate(hr_amount = Sum('hr_amount'))['hr_amount'] or 0 equipment_amount = equipment_amount.aggregate(equipment_amount = Sum('equipment_amount'))['equipment_amount'] or 0 travel_amount = travel_amount.aggregate(travel_amount = Sum('travel_amount'))['travel_amount'] or 0 iec_amount = iec_amount.aggregate(iec_amount = Sum('iec_amount'))['iec_amount'] or 0 accommodation_amount = accommodation_amount.aggregate(accommodation_amount = Sum('accommodation_amount'))['accommodation_amount'] or 0 miscellaneous_amount = miscellaneous_amount.aggregate(miscellaneous_amount = Sum('miscellaneous_amount'))['miscellaneous_amount'] or 0 incomes = [human_resource, equipment_expenses, travel_expenses, material_expences, accomodation_expenses, miscellaneous_expenses] amounts = [hr_amount, equipment_amount, travel_amount, iec_amount, accommodation_amount, miscellaneous_amount] return JsonResponse({ 'income_result': incomes, 'amount_result' : amounts }, safe=False) class IncomeTypeChartView(LoginRequiredMixin, View): def get(self, request): incomes_data = Incomes.objects.all() if 'project' in request.GET and request.GET['project'] != "All": incomes_data = incomes_data.filter(project_id = request.GET['project']) if 'start_date' in request.GET and request.GET['start_date'] != "" and 'end_date' in request.GET and request.GET['end_date'] != "": incomes_data = incomes_data.filter(payment_date__gte = request.GET['start_date'], payment_date__lte = request.GET['end_date']) income_counts = ( incomes_data .values('income_type') .annotate(total_income=Sum('received_amount')) .order_by('income_type') ) income_result = [ {'name': item['income_type'] or 'Unknown', 'y': item['total_income']} for item in income_counts ] return JsonResponse({ 'income_result': income_result }, safe=False) class CollectDataChartView(LoginRequiredMixin, View): def get(self, request): program_data = Programs.objects.all() if 'project' in request.GET and request.GET['project'] != "All": program_data = program_data.filter(project_id = request.GET['project']) if 'start_date' in request.GET and request.GET['start_date'] != "" and 'end_date' in request.GET and request.GET['end_date'] != "": program_data = program_data.filter(from_date__gte = request.GET['start_date'], from_date__lte = request.GET['end_date']) program_data = list(program_data.values("category").annotate(count=Count("id"))) programs_data = [ {'name': item['category'] or 'Unknown', 'y': item['count']} for item in program_data ] return JsonResponse({ 'program_data': programs_data }, safe=False)