from django.http import request from django.shortcuts import redirect, render from django.views import View from django.contrib.auth.mixins import LoginRequiredMixin from settings.views import get_company_data from dashboard.models import * from django.contrib.auth.models import User from django.db.models import Count from django.db.models.functions import ExtractMonth from django.db.models import Count from users.models import * from slider_images.models import * from settings.models import * from django.shortcuts import render from django.utils.safestring import mark_safe import bleach from html import escape from django.conf import settings import os import subprocess import shlex from django.http import HttpResponse from datetime import datetime import pytz from googleapiclient.discovery import build from googleapiclient.http import MediaFileUpload from google.oauth2 import service_account from contextlib import contextmanager from settings.models import DatabaseBackup import json from googleapiclient.errors import HttpError from django.db.models import Q def render_ckeditor_html(ckeditor_html): """ Renders the CKEditor-generated HTML to normal, sanitized HTML. Parameters: ckeditor_html (str): The HTML content generated by CKEditor. Returns: str: Sanitized HTML representation of the CKEditor content. """ # Define the allowed HTML tags and attributes custom_tags = ['p', 'h1', 'h2', 'h3', 'h4', 'h5', 'h6'] allowed_tags = bleach.sanitizer.ALLOWED_TAGS.union(custom_tags) allowed_attrs = bleach.sanitizer.ALLOWED_ATTRIBUTES # Sanitize the HTML content sanitized_html = bleach.clean(ckeditor_html, tags=allowed_tags, attributes=allowed_attrs) return sanitized_html # Dashboard class DashboardView(LoginRequiredMixin,View): def get(self, request): month_list = [] month_list_pending = [] month_list_com_gt = [] month_list_unrech_gt = [] month_list_com_dt = [] month_list_unrech_dt = [] total_count_data = 0 total_count_pending = 0 total_count_comp_gt = 0 total_count_unrech_gt = 0 total_count_comp_dt = 0 total_count_unrech_dt = 0 user_locations= UserLocation.objects.filter(user=request.user.id) user_date= UserInfo.objects.filter(user=request.user.id).values('from_date','to_date') current_grp = User.objects.filter(id=request.user.id).values('groups__id') countries = CountryMaster.objects.all().values('name','id').order_by('name') states = StateMaster.objects.all().values('id','name').order_by('name') allsubareas = SubAreaMaster.objects.all().values('id','name').order_by('name') img_list = [] count = 0 search_for = None districts=[] subdistricts=[] cities=[] areas=[] subareas=[] user_state = request.GET.get('user_state',None) user_district = request.GET.get('user_district',None) user_subdistrict = request.GET.get('user_subdistrict',None) user_city = request.GET.get('user_city',None) user_area = request.GET.get('user_area',None) subarea = request.GET.get('subarea',None) round = request.GET.get('round',None) image_date_from = request.GET.get('image_date_from',None) image_date_to = request.GET.get('image_date_to',None) status = request.GET.get('status',None) img_id = request.GET.get('image_id',None) if user_state: districts = DistrictMaster.objects.filter(state_id = user_state) if user_district: subdistricts = SubDistrictMaster.objects.filter(district_id = user_district) if user_subdistrict: cities = CityMaster.objects.filter(subdistrict_id = user_subdistrict) if user_city: areas = AreaMaster.objects.filter(city_id=user_city) if user_area: subareas = SubAreaMaster.objects.filter(area_id=user_area) query = "" extra_where = "" state_query = Q() district_query = Q() subdistrict_query = Q() city_query = Q() area_query = Q() subarea_query = Q() images = [] if user_state: query += " AND state_id_id = " + user_state if user_district: query += " AND district_id_id = " + user_district if user_subdistrict: query += " AND subdistrict_id_id = " + user_subdistrict if user_city: query += " AND city_id_id = " + user_city if user_area: query += " AND area_id_id = " + user_area if subarea: query += " AND subarea_id_id = " + subarea if not request.user.is_superuser: user_locations= UserLocation.objects.filter(user=request.user.id).values('id','user_id','state_id', 'district_id','subdistrict_id', 'city_id', 'area_id', 'subarea_id').order_by('id') if user_locations.first() is not None: if current_grp[0]["groups__id"] == 2: extra_where = "status_id IN (1, 2, 4) AND (" elif current_grp[0]["groups__id"] == 3: extra_where = "status_id IN (2,3,5) AND (" else: extra_where = " 1 AND (" for user in user_locations: extra_where += "(state_id_id = " + str(user['state_id']) if(user["district_id"] != None): extra_where += " AND district_id_id = " + str(user['district_id']) if(user["subdistrict_id"] != None): extra_where += " AND subdistrict_id_id = " + str(user['subdistrict_id']) if(user["city_id"] != None): extra_where += " AND city_id_id = " + str(user['city_id']) if(user["area_id"] != None): extra_where += " AND area_id_id = " + str(user['area_id']) if(user["subarea_id"] != None): extra_where += " AND subarea_id_id = " + str(user['subarea_id']) extra_where += ") OR" extra_where= extra_where[:-3] extra_where += ")" if user_date and user_date[0]['from_date'] or user_date[0]['to_date']: if user_date[0]['from_date'] and user_date[0]['to_date']: querymonth = "SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE 1 AND ( image_date <= '" + str(user_date[0]['to_date']) + "') AND (image_date >= '" + str(user_date[0]['from_date']) + "')" + " AND " + extra_where + query + " GROUP BY image_month, id ORDER BY image_month" elif user_date[0]['from_date'] or user_date[0]['to_date']: if user_date[0]['from_date'] and user_date[0]['to_date'] == None: querymonth = "SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE image_date >= '" + str(user_date[0]['from_date']) + "'"+ ' AND ' + extra_where + query + " GROUP BY image_month, id ORDER BY image_month" else: querymonth = "SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE image_date <= '" + str(user_date[0]['to_date']) + "'"+ ' AND ' + extra_where + query else: querymonth = "SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE " + extra_where + query + " GROUP BY image_month, id ORDER BY image_month" monthly_data = ImageMaster.objects.raw(querymonth) # Create a list of tuples with month and total count monthly_list = [(data.image_month, data.total_count) for data in monthly_data] for month, count in monthly_list: month_data = {} month_data["Month"]=month month_data["count"]=count month_list.append(month_data) if user_date and user_date[0]['from_date'] or user_date[0]['to_date']: if user_date[0]['from_date'] and user_date[0]['to_date']: queryt = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE 1 AND ( image_date <= '" + str(user_date[0]['to_date']) + "') AND (image_date >= '" + str(user_date[0]['from_date']) + "')" + " AND " + extra_where + queryt + query + " GROUP BY id" elif user_date[0]['from_date'] or user_date[0]['to_date']: if user_date[0]['from_date'] and user_date[0]['to_date'] == None: queryt = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE image_date >= '" + str(user_date[0]['from_date']) + "'"+ ' AND ' + extra_where + queryt + query + " GROUP BY id" else: queryt = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE image_date <= '" + str(user_date[0]['to_date']) + "'"+ ' AND ' + extra_where + queryt + query +" GROUP BY id" else: queryt = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE " + extra_where + query + " GROUP BY id" images = ImageMaster.objects.raw(queryt) total_count_data = sum(image.total_count for image in images) if images else 0 if user_date and user_date[0]['from_date'] or user_date[0]['to_date']: if user_date[0]['from_date'] and user_date[0]['to_date']: querymonthpending = "SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE 1 AND ( image_date <= '" + str(user_date[0]['to_date']) + "') AND (image_date >= '" + str(user_date[0]['from_date']) + "')" + " AND " + extra_where + query + " AND status_id = '1' GROUP BY image_month, id ORDER BY image_month" elif user_date[0]['from_date'] or user_date[0]['to_date']: if user_date[0]['from_date'] and user_date[0]['to_date'] == None: querymonthpending = "SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE image_date >= '" + str(user_date[0]['from_date']) + "'"+ ' AND ' + extra_where + query + " AND status_id = '1' GROUP BY image_month, id ORDER BY image_month" else: querymonthpending = "SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE image_date <= '" + str(user_date[0]['to_date']) + "'"+ ' AND ' + extra_where + query +" AND status_id = '1' GROUP BY image_month, id ORDER BY image_month" else: querymonthpending = "SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE " + extra_where + query + " AND status_id = '1' GROUP BY image_month, id ORDER BY image_month" # querymonthpending = "SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE " + extra_where + " AND status_id = '1' GROUP BY image_month, id ORDER BY image_month" monthly_data_pending = ImageMaster.objects.raw(querymonthpending) # Create a list of tuples with id, month, and total count monthly_list_pending = [(data.id, data.image_month, data.total_count) for data in monthly_data_pending] # Print the monthly list for id, month, count in monthly_list_pending: month_data = {} month_data["ID"] = id month_data["Month"] = month month_data["count"] = count month_list_pending.append(month_data) if user_date and user_date[0]['from_date'] or user_date[0]['to_date']: if user_date[0]['from_date'] and user_date[0]['to_date']: query1 = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE 1 AND ( image_date <= '" + str(user_date[0]['to_date']) + "') AND (image_date >= '" + str(user_date[0]['from_date']) + "')" + " AND " + extra_where + query + " AND (status_id = 1) GROUP BY id" elif user_date[0]['from_date'] or user_date[0]['to_date']: if user_date[0]['from_date'] and user_date[0]['to_date'] == None: query1 = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE image_date >= '" + str(user_date[0]['from_date']) + "'"+ ' AND ' + extra_where + query + " AND (status_id = 1) GROUP BY id" else: query1 = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE image_date <= '" + str(user_date[0]['to_date']) + "'"+ ' AND ' + extra_where + query + " AND (status_id = 1) GROUP BY id" else: query1 = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE " + extra_where + query +" AND (status_id = 1) GROUP BY id" images = ImageMaster.objects.raw(query1) total_count_pending = sum(image.total_count for image in images) if images else 0 if user_date and user_date[0]['from_date'] or user_date[0]['to_date']: if user_date[0]['from_date'] and user_date[0]['to_date']: querymonthcompgt = "SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE 1 AND ( image_date <= '" + str(user_date[0]['to_date']) + "') AND (image_date >= '" + str(user_date[0]['from_date']) + "')" + " AND " + extra_where + query + " AND (status_id = 4) GROUP BY image_month, id ORDER BY image_month" elif user_date[0]['from_date'] or user_date[0]['to_date']: if user_date[0]['from_date'] and user_date[0]['to_date'] == None: querymonthcompgt = "SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE image_date >= '" + str(user_date[0]['from_date']) + "'"+ ' AND ' + extra_where + query + " AND (status_id = 4) GROUP BY image_month, id ORDER BY image_month" else: querymonthcompgt = "SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE image_date <= '" + str(user_date[0]['to_date']) + "'"+ ' AND ' + extra_where + query +" AND (status_id = 4) GROUP BY image_month, id ORDER BY image_month" else: querymonthcompgt = "SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE " + extra_where + query +" AND (status_id = 4) GROUP BY image_month, id ORDER BY image_month" monthly_data_com_gt = ImageMaster.objects.raw(querymonthcompgt) # Create a list of tuples with id, month, and total count monthly_list__com_gt= [(data.id, data.image_month, data.total_count) for data in monthly_data_com_gt] for id, month, count in monthly_list__com_gt: month_data = {} month_data["ID"] = id month_data["Month"] = month month_data["count"] = count month_list_com_gt.append(month_data) if user_date and user_date[0]['from_date'] or user_date[0]['to_date']: if user_date[0]['from_date'] and user_date[0]['to_date']: query2 = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE 1 AND ( image_date <= '" + str(user_date[0]['to_date']) + "') AND (image_date >= '" + str(user_date[0]['from_date']) + "')" + " AND " + extra_where + query + " AND (status_id = 4) GROUP BY id" elif user_date[0]['from_date'] or user_date[0]['to_date']: if user_date[0]['from_date'] and user_date[0]['to_date'] == None: query2 = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE image_date >= '" + str(user_date[0]['from_date']) + "'"+ ' AND ' + extra_where + query + " AND (status_id = 4) GROUP BY id" else: query2 = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE image_date <= '" + str(user_date[0]['to_date']) + "'"+ ' AND ' + extra_where + query +" AND (status_id = 4) GROUP BY id" else: query2 = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE " + extra_where + query +" AND (status_id = 4 ) GROUP BY id" images = ImageMaster.objects.raw(query2) total_count_comp_gt = sum(image.total_count for image in images) if images else 0 if user_date and user_date[0]['from_date'] or user_date[0]['to_date']: if user_date[0]['from_date'] and user_date[0]['to_date']: querymonthcunreachgt = "SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE 1 AND ( image_date <= '" + str(user_date[0]['to_date']) + "') AND (image_date >= '" + str(user_date[0]['from_date']) + "')" + " AND " + extra_where + query + " AND (status_id = 2) GROUP BY image_month, id ORDER BY image_month" elif user_date[0]['from_date'] or user_date[0]['to_date']: if user_date[0]['from_date'] and user_date[0]['to_date'] == None: querymonthcunreachgt = "SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE image_date >= '" + str(user_date[0]['from_date']) + "'"+ ' AND ' + extra_where + query + " AND (status_id = 2) GROUP BY image_month, id ORDER BY image_month" else: querymonthcunreachgt = "SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE image_date <= '" + str(user_date[0]['to_date']) + "'"+ ' AND ' + extra_where + query +" AND (status_id = 2) GROUP BY image_month, id ORDER BY image_month" else: querymonthcunreachgt = "SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id)AS total_count FROM dashboard_imagemaster WHERE " + extra_where + query + " AND (status_id = 2) GROUP BY image_month, id ORDER BY image_month" monthly_data_unreach_gt = ImageMaster.objects.raw(querymonthcunreachgt) # Create a list of tuples with id, month, and total count monthly_list___unreach_gt= [(data.id, data.image_month, data.total_count) for data in monthly_data_unreach_gt] for id, month, count in monthly_list___unreach_gt: month_data = {} month_data["ID"] = id month_data["Month"] = month month_data["count"] = count month_list_unrech_gt.append(month_data) if user_date and user_date[0]['from_date'] or user_date[0]['to_date']: if user_date[0]['from_date'] and user_date[0]['to_date']: query3 = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE 1 AND ( image_date <= '" + str(user_date[0]['to_date']) + "') AND (image_date >= '" + str(user_date[0]['from_date']) + "')" + " AND " + extra_where + query + " AND (status_id = 2) GROUP BY id" elif user_date[0]['from_date'] or user_date[0]['to_date']: if user_date[0]['from_date'] and user_date[0]['to_date'] == None: query3 = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE image_date >= '" + str(user_date[0]['from_date']) + "'"+ ' AND ' + extra_where + query +" AND (status_id = 2) GROUP BY id" else: query3 = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE image_date <= '" + str(user_date[0]['to_date']) + "'"+ ' AND ' + extra_where + query + " AND (status_id = 2) GROUP BY id" else: query3 = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE " + extra_where + query + " AND (status_id = 2 )GROUP BY id" images = ImageMaster.objects.raw(query3) total_count_unrech_gt = sum(image.total_count for image in images) if images else 0 if user_date and user_date[0]['from_date'] or user_date[0]['to_date']: if user_date[0]['from_date'] and user_date[0]['to_date']: querymonthcompdt = "SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE 1 AND ( image_date <= '" + str(user_date[0]['to_date']) + "') AND (image_date >= '" + str(user_date[0]['from_date']) + "')" + " AND " + extra_where + query + " AND (status_id = 5) GROUP BY image_month, id ORDER BY image_month" elif user_date[0]['from_date'] or user_date[0]['to_date']: if user_date[0]['from_date'] and user_date[0]['to_date'] == None: querymonthcompdt = "SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE image_date >= '" + str(user_date[0]['from_date']) + "'"+ ' AND ' + extra_where+ query + " AND (status_id = 5) GROUP BY image_month, id ORDER BY image_month" else: querymonthcompdt = "SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE image_date <= '" + str(user_date[0]['to_date']) + "'"+ ' AND ' + extra_where + query +" AND (status_id = 5) GROUP BY image_month, id ORDER BY image_month" else: querymonthcompdt = "SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE " + extra_where + query + " AND (status_id = 5) GROUP BY image_month, id ORDER BY image_month" monthly_data_com_dt = ImageMaster.objects.raw(querymonthcompdt) # Create a list of tuples with id, month, and total count monthly_list__com_dt= [(data.id, data.image_month, data.total_count) for data in monthly_data_com_dt] for id, month, count in monthly_list__com_dt: month_data = {} month_data["ID"] = id month_data["Month"] = month month_data["count"] = count month_list_com_dt.append(month_data) if user_date and user_date[0]['from_date'] or user_date[0]['to_date']: if user_date[0]['from_date'] and user_date[0]['to_date']: query4 = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE 1 AND ( image_date <= '" + str(user_date[0]['to_date']) + "') AND (image_date >= '" + str(user_date[0]['from_date']) + "')" + " AND " + extra_where + query + " AND (status_id = 5) GROUP BY id" elif user_date[0]['from_date'] or user_date[0]['to_date']: if user_date[0]['from_date'] and user_date[0]['to_date'] == None: query4 = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE image_date >= '" + str(user_date[0]['from_date']) + "'"+ ' AND ' + extra_where + query + " AND (status_id = 5) GROUP BY id" else: query4 = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE image_date <= '" + str(user_date[0]['to_date']) + "'"+ ' AND ' + extra_where + query +" AND (status_id = 5) GROUP BY id" else: query4 = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE " + extra_where + query + " AND (status_id = 5 )GROUP BY id" images = ImageMaster.objects.raw(query4) total_count_comp_dt = sum(image.total_count for image in images) if images else 0 if user_date and user_date[0]['from_date'] or user_date[0]['to_date']: if user_date[0]['from_date'] and user_date[0]['to_date']: querymonthcunreachdt = "SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE 1 AND ( image_date <= '" + str(user_date[0]['to_date']) + "') AND (image_date >= '" + str(user_date[0]['from_date']) + "')" + " AND " + extra_where + query + " AND (status_id = 3) GROUP BY image_month, id ORDER BY image_month" elif user_date[0]['from_date'] or user_date[0]['to_date']: if user_date[0]['from_date'] and user_date[0]['to_date'] == None: querymonthcunreachdt = "SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE image_date >= '" + str(user_date[0]['from_date']) + "'"+ ' AND ' + extra_where + query + " AND (status_id = 3) GROUP BY image_month, id ORDER BY image_month" else: querymonthcunreachdt = "SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE image_date <= '" + str(user_date[0]['to_date']) + "'"+ ' AND ' + extra_where + query +" AND (status_id = 3) GROUP BY image_month, id ORDER BY image_month" else: querymonthcunreachdt = """ SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE status_id = '3' AND """ + extra_where + query + """ GROUP BY id, image_month ORDER BY image_month """ monthly_data_unreach_dt = ImageMaster.objects.raw(querymonthcunreachdt) # Create a list of tuples with id, month, and total count monthly_list___unreach_dt= [(data.id, data.image_month, data.total_count) for data in monthly_data_unreach_dt] for id, month, count in monthly_list___unreach_dt: month_data = {} month_data["ID"] = id month_data["Month"] = month month_data["count"] = count month_list_unrech_dt.append(month_data) if user_date and user_date[0]['from_date'] or user_date[0]['to_date']: if user_date[0]['from_date'] and user_date[0]['to_date']: query5 = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE 1 AND ( image_date <= '" + str(user_date[0]['to_date']) + "') AND (image_date >= '" + str(user_date[0]['from_date']) + "')" + " AND " + extra_where + query + " AND (status_id = 3) GROUP BY id" elif user_date[0]['from_date'] or user_date[0]['to_date']: if user_date[0]['from_date'] and user_date[0]['to_date'] == None: query5 = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE image_date >= '" + str(user_date[0]['from_date']) + "'"+ ' AND ' + extra_where + query + " AND (status_id = 3) GROUP BY id" else: query5 = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE image_date <= '" + str(user_date[0]['to_date']) + "'"+ ' AND ' + extra_where + query +" AND (status_id = 3) GROUP BY id" else: query5 = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE "+extra_where + query +" AND (status_id = 3 )GROUP BY id" images = ImageMaster.objects.raw(query5) total_count_unrech_dt = sum(image.total_count for image in images) if images else 0 else: if query: monthly_data_query = "SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE 1 " + query + " GROUP BY image_month, id ORDER BY image_month" else: monthly_data_query = "SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id) AS total_count FROM dashboard_imagemaster GROUP BY image_month, id ORDER BY image_month" monthly_data = ImageMaster.objects.raw(monthly_data_query) # Create a list of tuples with month and total count monthly_list = [(data.id,data.image_month, data.total_count) for data in monthly_data] for id, month, count in monthly_list: month_data = {} month_data["ID"] = id month_data["Month"]=month month_data["count"]=count month_list.append(month_data) if query: queryt = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE 1 " + query + " GROUP BY id" else: queryt = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster GROUP BY id" images = ImageMaster.objects.raw(queryt) total_count_data = sum(image.total_count for image in images) if images else 0 querymonthpending = "SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE status_id = '1'" + query +" GROUP BY image_month, id ORDER BY image_month" monthly_data_pending = ImageMaster.objects.raw(querymonthpending) # Create a list of tuples with id, month, and total count monthly_list_pending = [(data.id, data.image_month, data.total_count) for data in monthly_data_pending] # Print the monthly list for id, month, count in monthly_list_pending: month_data = {} month_data["ID"] = id month_data["Month"] = month month_data["count"] = count month_list_pending.append(month_data) if query: query1 = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE status_id = 1"+ query + " GROUP BY id" else: query1 = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE status_id = 1 GROUP BY id" images = ImageMaster.objects.raw(query1) total_count_pending = sum(image.total_count for image in images) if images else 0 querymonthcompgt = "SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE status_id = '4'"+ query +" GROUP BY image_month, id ORDER BY image_month" monthly_data_com_gt = ImageMaster.objects.raw(querymonthcompgt) # Create a list of tuples with id, month, and total count monthly_list__com_gt= [(data.id, data.image_month, data.total_count) for data in monthly_data_com_gt] for id, month, count in monthly_list__com_gt: month_data = {} month_data["ID"] = id month_data["Month"] = month month_data["count"] = count month_list_com_gt.append(month_data) if query: query2 = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE status_id = 4 " + query +" GROUP BY id" else: query2 = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE status_id = 4 GROUP BY id" images = ImageMaster.objects.raw(query2) total_count_comp_gt = sum(image.total_count for image in images) if images else 0 querymonthcunreachgt = "SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE status_id = '2'"+ query + " GROUP BY image_month, id ORDER BY image_month" monthly_data_unreach_gt = ImageMaster.objects.raw(querymonthcunreachgt) # Create a list of tuples with id, month, and total count monthly_list___unreach_gt= [(data.id, data.image_month, data.total_count) for data in monthly_data_unreach_gt] for id, month, count in monthly_list___unreach_gt: month_data = {} month_data["ID"] = id month_data["Month"] = month month_data["count"] = count month_list_unrech_gt.append(month_data) if query: query3 = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE status_id = 2"+ query + " GROUP BY id" else: query3 = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE status_id = 2 GROUP BY id" images = ImageMaster.objects.raw(query3) total_count_unrech_gt = sum(image.total_count for image in images) if images else 0 querymonthcompdt = "SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE status_id = '5'"+ query + " GROUP BY image_month, id ORDER BY image_month" monthly_data_com_dt = ImageMaster.objects.raw(querymonthcompdt) # Create a list of tuples with id, month, and total count monthly_list__com_dt= [(data.id, data.image_month, data.total_count) for data in monthly_data_com_dt] for id, month, count in monthly_list__com_dt: month_data = {} month_data["ID"] = id month_data["Month"] = month month_data["count"] = count month_list_com_dt.append(month_data) if query: query4 = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE status_id = 5"+ query + " GROUP BY id" else: query4 = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE status_id = 5 GROUP BY id" images = ImageMaster.objects.raw(query4) total_count_comp_dt = sum(image.total_count for image in images) if images else 0 querymonthcunreachdt = "SELECT id, EXTRACT(MONTH FROM image_date) AS image_month, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE status_id = '3'"+ query + " GROUP BY image_month, id ORDER BY image_month" monthly_data_unreach_dt = ImageMaster.objects.raw(querymonthcunreachdt) # Create a list of tuples with id, month, and total count monthly_list___unreach_dt= [(data.id, data.image_month, data.total_count) for data in monthly_data_unreach_dt] for id, month, count in monthly_list___unreach_dt: month_data = {} month_data["ID"] = id month_data["Month"] = month month_data["count"] = count month_list_unrech_dt.append(month_data) if query: query5 = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE status_id = 3"+ query + " GROUP BY id" else: query5 = "SELECT id, COUNT(id) AS total_count FROM dashboard_imagemaster WHERE status_id = 3 GROUP BY id" images = ImageMaster.objects.raw(query5) total_count_unrech_dt = sum(image.total_count for image in images) if images else 0 # Calculate percentages total_images = total_count_data pending_percentage = (total_count_pending / total_images) * 100 if total_images > 0 else 0 comp_gt_percentage = (total_count_comp_gt / total_images) * 100 if total_images > 0 else 0 unrech_gt_percentage = (total_count_unrech_gt / total_images) * 100 if total_images > 0 else 0 comp_dt_percentage = (total_count_comp_dt / total_images) * 100 if total_images > 0 else 0 unrech_dt_percentage = (total_count_unrech_dt / total_images) * 100 if total_images > 0 else 0 greeting = {} greeting['title'] = "Dashboard" greeting['pageview'] = "Nazox" greeting['total_data']= total_count_data greeting['total_pending']= total_count_pending greeting['comp_gt']= total_count_comp_gt greeting['unreach_gt']= total_count_unrech_gt greeting['comp_dt']= total_count_comp_dt greeting['unreach_dt']= total_count_unrech_dt greeting['pending_percentage'] = pending_percentage greeting['comp_gt_percentage']=comp_gt_percentage greeting['unrech_gt_percentage']=unrech_gt_percentage greeting['comp_dt_percentage']=comp_dt_percentage greeting['unrech_dt_percentage']=unrech_dt_percentage greeting['company_data'] = get_company_data() greeting['monthly_data'] = month_list greeting['month_list_pending'] = month_list_pending greeting['month_list_com_gt']=month_list_com_gt greeting['month_list_unrech_gt']=month_list_unrech_gt greeting['month_list_com_dt']=month_list_com_dt greeting['month_list_unrech_dt']=month_list_unrech_dt greeting["countries"]=countries greeting["states"]=states greeting["districts"]=districts greeting["subdistricts"]=subdistricts greeting["cities"]=cities greeting["areas"]=areas greeting["allsubareas"]=allsubareas greeting["subareas"]=subareas greeting["user_state"]=user_state return render(request, 'menu/index.html',greeting) # users class UsersView(LoginRequiredMixin,View): def get(self, request): greeting = {} greeting['title'] = "Users" greeting['pageview'] = "Nazox" greeting['company_data'] = get_company_data() return render(request, 'menu/users.html',greeting) # homepage class HomepageView(LoginRequiredMixin,View): def get(self, request): photos = Images.objects.all() updatesites = Setting.objects.filter(name="update_siteinput").values("value") # sanitized_html = render_ckeditor_html(updatesites[0]['value']) ckeditor_content = str(updatesites[0]['value']) sanitized_html = render_ckeditor_html(ckeditor_content) context = {'photos':photos,'updatesites':mark_safe(sanitized_html),'title':"Homepage","pageview":"Homepage","company_data":get_company_data() } return render(request, 'menu/homepage/homepage.html',context) # Upload sql file in drive def create_database_backup(filename, current_datetime): formatted_date = datetime.strptime(current_datetime, '%d%m%Y%H%M%S') DatabaseBackup.objects.create(database_url=filename, database_date=formatted_date) # Export Database def download_mysql_export(request): db_name = settings.DATABASES['default']['NAME'] db_user = settings.DATABASES['default']['USER'] db_password = settings.DATABASES['default']['PASSWORD'] db_host = settings.DATABASES['default']['HOST'] db_port = settings.DATABASES['default']['PORT'] ist = pytz.timezone('Asia/Kolkata') current_datetime = datetime.now(ist).strftime('%d_%m_%Y_%H_%M_%S') current_datetime2 = datetime.now(ist).strftime('%d%m%Y%H%M%S') # Generate the temporary file path to store the exported SQL file export_file_path = os.path.join(settings.MEDIA_ROOT, f'{db_name}_export_{current_datetime}.sql') # Escape special characters in password and construct the command escaped_password = shlex.quote(db_password) command = ( f"/usr/bin/mysqldump -u {db_user} -p{escaped_password} -h {db_host} -P {db_port} {db_name} > {export_file_path}" ) # Capture subprocess output and errors completed_process = subprocess.run(command, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE, text=True) filename = export_file_path.split("/")[-1] formatted_date = current_datetime2 # Use the provided format directly if completed_process.returncode != 0: return HttpResponse("Error exporting database.", status=500) # Set full permissions on the generated file os.chmod(export_file_path, 0o777) # Upload the file to Google Drive # credentials_path = settings.DRIVE_API # Update with your service account key file path # ID of the folder where you want to upload the file (update with your folder ID) folderid = settings.DATABASE_BACKUP_PATH folder_id = folderid.split("/")[-1].split("?")[0] credentials_path = settings.DRIVE_API # Authenticate and create the Drive API client creds = service_account.Credentials.from_service_account_file( credentials_path) service = build('drive', 'v3', credentials=creds) # Authorize the Google Drive API # File metadata for the uploaded file file_metadata = { 'name': os.path.basename(export_file_path), # Use the original file name 'parents': [folder_id] } # MediaFileUpload object to upload the file media = MediaFileUpload(export_file_path, mimetype='application/sql') # Upload the file to Google Drive try: file = service.files().create(body=file_metadata, media_body=media, fields='id').execute() # Rest of the code... except HttpError as e: if e.content: try: error_json = json.loads(e.content) error_message = error_json.get('error', {}).get('message') print("An error occurred:", error_message) except json.JSONDecodeError: print("Failed to decode the error response from Google Drive API.") else: print("An HTTP error occurred:", e) except Exception as e: print("An unexpected error occurred:", e) # Optionally, you can remove the temporary file after uploading create_database_backup(filename, formatted_date) os.remove(export_file_path) return redirect('database_backup')