from django.shortcuts import render from dashboard.models import * from django.shortcuts import redirect, render from google.oauth2 import service_account from django.contrib.auth.mixins import LoginRequiredMixin from django.views import View from googleapiclient.discovery import build from django.contrib import messages from django.db.models import Q from nazox.settings import DRIVE_IMAGE_PATH,MAP_API_KEY,MEDIA_ROOT import datetime from django.core.paginator import Paginator import json from django.http import JsonResponse from settings.views import get_company_data from django.http import HttpResponse import pandas as pd import io from settings.models import * from openpyxl import Workbook from openpyxl.styles import Font import ast from users.models import * import os from dateutil.parser import parse from django.conf import settings # import datetime # Create your views here. class AddImages(LoginRequiredMixin,View): def get(self, request): countries = CountryMaster.objects.all().values('name','id').order_by('name') states = StateMaster.objects.all().values('id','name').order_by('name') subareas = SubAreaMaster.objects.all().values('id','name').order_by('name') districts = [] subdistricts = [] cities = [] areas = [] subares = [] statuses = StatusMaster.objects.all().values('id','name').order_by('order_by') context = {"countries":countries, "states":states,"districts": districts,"subdistricts": subdistricts, "cities":cities,"areas":areas, "subareas":subareas,"statuses":statuses, "title":"Upload Data","company_data":get_company_data()} return render(request, 'menu/master/images/addimage.html', context) def post(self, request): if request.method == "POST": user = User.objects.filter(id = request.user.id).values('id') state = request.POST.get('user_state') state = StateMaster.objects.get(id=state) district = request.POST.get('user_district') district = DistrictMaster.objects.get(id=district) subdistrict = request.POST.get('user_subdistrict') subdistrict = SubDistrictMaster.objects.get(id=subdistrict) city = request.POST.get('user_city') city = CityMaster.objects.get(id=city) area = request.POST.get('user_area') area = AreaMaster.objects.get(id=area) subarea = request.POST.get('subarea') subarea = SubAreaMaster.objects.get(id=subarea) if subarea == "": subarea=None round = request.POST.get('round') img_date = request.POST.get('datepicker') drive_url = request.POST.get('drive') survey_area = request.POST.get('survey_area') # if subarea: # subareas = SubAreaMaster.objects.filter(name=subarea[0],area__name=subarea[1]).values('id','area__name') # subarea = subareas[0]["id"] # subarea = subarea if (state != '' and district != '' and city != '' and area != '' and img_date != '' and drive_url != '' and survey_area != '' and round != ''): # Get the folder ID from the URL folder_id = drive_url.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) # Create a query to get all the files and folders inside the folder query = "'{}' in parents and trashed=false".format(folder_id) # Get all the files and folders inside the folder file_list = service.files().list(q=query, pageSize=1000,includeItemsFromAllDrives=True, supportsAllDrives=True).execute().get('files', []) if file_list: Img_count = 0 for file in file_list: file_metadata = service.files().get(fileId=file['id'], fields='*').execute() if 'imageMediaMetadata' in file_metadata: img_name = file_metadata['name'] if 'time' in file_metadata['imageMediaMetadata']: img_cap_date = datetime.datetime.strptime(file_metadata['imageMediaMetadata']['time'], "%Y:%m:%d %H:%M:%S") if 'location' in file_metadata['imageMediaMetadata']: latitude = file_metadata['imageMediaMetadata']['location']['latitude'] longitude = file_metadata['imageMediaMetadata']['location']['longitude'] altitude = file_metadata['imageMediaMetadata']['location']['altitude'] data = ImageMaster.objects.create(country_id = 1, state_id=state, district_id = district,subdistrict_id = subdistrict, city_id = city, area_id =area, subarea_id = subarea,drive_url=drive_url,image_date = img_date,drive_image_id = file['id'],image_name=img_name,image_capture_date=img_cap_date,latitude=latitude,longitude=longitude,altitude=altitude,size_of_survey_area=survey_area, round=round, status_id=1,created_by = user[0]['id']) else: Img_count = Img_count + 1 else: Img_count = Img_count + 1 if Img_count: error_message = f" {Img_count} Image not uploaded" messages.error(request, error_message, extra_tags='alert-dismissible') return redirect('addimage') else: return redirect('dashboard') else: # data = {} messages.error(request, "Some field is empty" ) return redirect('addimage') # return HttpResponse(data) else: return redirect('menu/master/images/addimage.html') def delete_item(request): if request.method == 'POST': item_id = request.POST.get('id') # Perform your delete logic here try: # Example code to delete an item from the database item = ImageMaster.objects.get(id=item_id) item.delete() return JsonResponse({'message': 'Item deleted successfully.'}) except ImageMaster.DoesNotExist: return JsonResponse({'message': 'Item not found.'}, status=404) return JsonResponse({'message': 'Invalid request.'}, status=400) class viewimagelist(LoginRequiredMixin, View): def get(self, request): # Get the current year current_year = datetime.datetime.now().year 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') order_by = request.GET.get('order_by', 'id') # Default sort field if not specified sort_order = request.GET.get('sort_order', 'asc') if current_grp[0]["groups__id"] == 2: status_ids = [1, 2, 4] statuses = StatusMaster.objects.filter(id__in=status_ids).values('id','name').order_by('order_by') elif current_grp[0]["groups__id"] == 3: status_ids = [2,3,5] statuses = StatusMaster.objects.filter(id__in=status_ids).values('id','name').order_by('order_by') else: statuses = StatusMaster.objects.all().values('id','name').order_by('order_by') 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) country_list = [] state_list = [] district_list = [] city_list = [] area_list = [] subarea_list = [] 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 round: if user_state: query += " AND round = " + round if status: query +=" AND status_id =" + status if image_date_from: image_date_from = datetime.datetime.strptime(str(image_date_from), '%Y-%m-%d').date() query += " AND image_date >= '" + str(image_date_from) + "'" if image_date_to: image_date_to = datetime.datetime.strptime(str(image_date_to), '%Y-%m-%d').date() query += " AND image_date <= '" + str(image_date_to) + "'" if img_id: query += " AND id = '" + img_id + "'" else: query += " AND round = " + round if status: query +=" AND status_id =" + status if image_date_from: image_date_from = datetime.datetime.strptime(str(image_date_from), '%Y-%m-%d').date() query += " AND image_date >= '" + str(image_date_from) + "'" if image_date_to: image_date_to = datetime.datetime.strptime(str(image_date_to), '%Y-%m-%d').date() query += " AND image_date <= '" + str(image_date_to) + "'" if img_id: query += " AND id = '" + img_id + "'" if status and not round: if user_state or subarea: query +=" AND status_id =" + status if img_id: query += " AND id = '" + img_id + "'" else: query += " AND status_id = " + status if image_date_from: image_date_from = datetime.datetime.strptime(str(image_date_from), '%Y-%m-%d').date() query += " AND image_date >= '" + str(image_date_from) + "'" if image_date_to: image_date_to = datetime.datetime.strptime(str(image_date_to), '%Y-%m-%d').date() query += " AND image_date <= '" + str(image_date_to) + "'" if img_id: query += " AND id = '" + img_id + "'" if image_date_from and not status and not round: if user_state or subarea: image_date_from = datetime.datetime.strptime(str(image_date_from), '%Y-%m-%d').date() query += " AND image_date >= '" + str(image_date_from) + "'" if image_date_to: image_date_to = datetime.datetime.strptime(str(image_date_to), '%Y-%m-%d').date() query += " AND image_date <= '" + str(image_date_to) + "'" if img_id: query += " AND id = '" + img_id + "'" else: image_date_from = datetime.datetime.strptime(str(image_date_from), '%Y-%m-%d').date() query += " AND (image_date >= '" + str(image_date_from) + "')" if image_date_to: image_date_to = datetime.datetime.strptime(str(image_date_to), '%Y-%m-%d').date() query += " AND (image_date <= '" + str(image_date_to) + "')" if img_id: query += " AND id = '" + img_id + "'" if image_date_to and not status and not round: if user_state or subarea: image_date_to = datetime.datetime.strptime(str(image_date_to), '%Y-%m-%d').date() query += " AND (image_date <= '" + str(image_date_to) + "')" if image_date_from: image_date_from = datetime.datetime.strptime(str(image_date_from), '%Y-%m-%d').date() query += " AND image_date >= '" + str(image_date_from) + "'" if img_id: query += " AND id = '" + img_id + "'" else: image_date_to = datetime.datetime.strptime(str(image_date_to), '%Y-%m-%d').date() query += " AND (image_date <= '" + str(image_date_to) + "')" if image_date_from: image_date_from = datetime.datetime.strptime(str(image_date_from), '%Y-%m-%d').date() query += " AND image_date >= '" + str(image_date_from) + "'" if img_id: query += " AND id = '" + img_id + "'" if img_id and not status and not round and not image_date_from and not image_date_to: if user_state: query +=" AND id =" + img_id else: query += " AND id = " + img_id 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 " extra_where += " ( " 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']: if order_by == "district_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_districtmaster.name as district_name FROM dashboard_imagemaster LEFT JOIN dashboard_districtmaster on dashboard_districtmaster.id = dashboard_imagemaster.district_id_id WHERE 1 AND ( image_date <= '" + str(user_date[0]['to_date']) + "') AND (image_date >= '" + str(user_date[0]['from_date']) + "')" + " AND " +extra_where + query + " ORDER BY district_name "+ sort_order elif order_by == "subdistrict_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_subdistrictmaster.name as subdistrict_name FROM dashboard_imagemaster LEFT JOIN dashboard_subdistrictmaster on dashboard_subdistrictmaster.id = dashboard_imagemaster.subdistrict_id_id WHERE 1 AND ( image_date <= '" + str(user_date[0]['to_date']) + "') AND (image_date >= '" + str(user_date[0]['from_date']) + "')" + " AND " +extra_where + query + " ORDER BY subdistrict_name "+ sort_order elif order_by == "city_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_citymaster.name as city_name FROM dashboard_imagemaster LEFT JOIN dashboard_citymaster on dashboard_citymaster.id = dashboard_imagemaster.city_id_id WHERE 1 AND ( image_date <= '" + str(user_date[0]['to_date']) + "') AND (image_date >= '" + str(user_date[0]['from_date']) + "')" + " AND " +extra_where + query + " ORDER BY city_name "+ sort_order elif order_by == "area_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_areamaster.name as area_name FROM dashboard_imagemaster LEFT JOIN dashboard_areamaster on dashboard_areamaster.id = dashboard_imagemaster.area_id_id WHERE 1 AND ( image_date <= '" + str(user_date[0]['to_date']) + "') AND (image_date >= '" + str(user_date[0]['from_date']) + "')" + " AND " +extra_where + query + " ORDER BY area_name "+ sort_order elif order_by == "subarea_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_subareamaster.name as subarea_name FROM dashboard_imagemaster LEFT JOIN dashboard_subareamaster on dashboard_subareamaster.id = dashboard_imagemaster.subarea_id_id WHERE 1 AND ( image_date <= '" + str(user_date[0]['to_date']) + "') AND (image_date >= '" + str(user_date[0]['from_date']) + "')" + " AND " +extra_where + query + " ORDER BY subarea_name "+ sort_order elif order_by == "status": query = "SELECT dashboard_imagemaster.*, dashboard_statusmaster.name as status_name FROM dashboard_imagemaster LEFT JOIN dashboard_statusmaster on dashboard_statusmaster.id = dashboard_imagemaster.status_id WHERE 1 AND ( image_date <= '" + str(user_date[0]['to_date']) + "') AND (image_date >= '" + str(user_date[0]['from_date']) + "')" + " AND " +extra_where + query + " ORDER BY status_name "+ sort_order else: query = "SELECT * 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 data_sort_field = order_by.replace("text-center", "").strip() order_by_clause = " ORDER BY " + data_sort_field + " " + sort_order # Append the ORDER BY clause to the base query query = query + order_by_clause 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: if order_by == "district_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_districtmaster.name as district_name FROM dashboard_imagemaster LEFT JOIN dashboard_districtmaster on dashboard_districtmaster.id = dashboard_imagemaster.district_id_id WHERE image_date >= '" + str(user_date[0]['from_date']) + "'"+ ' AND ' + extra_where + query + " ORDER BY district_name "+ sort_order elif order_by == "subdistrict_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_subdistrictmaster.name as subdistrict_name FROM dashboard_imagemaster LEFT JOIN dashboard_subdistrictmaster on dashboard_subdistrictmaster.id = dashboard_imagemaster.subdistrict_id_id WHERE image_date >= '" + str(user_date[0]['from_date']) + "'"+ ' AND ' + extra_where + query + " ORDER BY subdistrict_name "+ sort_order elif order_by == "city_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_citymaster.name as city_name FROM dashboard_imagemaster LEFT JOIN dashboard_citymaster on dashboard_citymaster.id = dashboard_imagemaster.city_id_id WHERE image_date >= '" + str(user_date[0]['from_date']) + "'"+ ' AND ' + extra_where + query + " ORDER BY city_name "+ sort_order elif order_by == "area_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_areamaster.name as area_name FROM dashboard_imagemaster LEFT JOIN dashboard_areamaster on dashboard_areamaster.id = dashboard_imagemaster.area_id_id WHERE image_date >= '" + str(user_date[0]['from_date']) + "'"+ ' AND ' + extra_where + query + " ORDER BY area_name "+ sort_order elif order_by == "subarea_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_subareamaster.name as subarea_name FROM dashboard_imagemaster LEFT JOIN dashboard_subareamaster on dashboard_subareamaster.id = dashboard_imagemaster.subarea_id_id WHERE image_date >= '" + str(user_date[0]['from_date']) + "'"+ ' AND ' + extra_where + query + " ORDER BY subarea_name "+ sort_order elif order_by == "status": query = "SELECT dashboard_imagemaster.*, dashboard_statusmaster.name as status_name FROM dashboard_imagemaster LEFT JOIN dashboard_statusmaster on dashboard_statusmaster.id = dashboard_imagemaster.status_id WHERE image_date >= '" + str(user_date[0]['from_date']) + "'"+ ' AND ' + extra_where + query + " ORDER BY status_name "+ sort_order else: query = "SELECT * FROM dashboard_imagemaster WHERE image_date >= '" + str(user_date[0]['from_date']) + "'"+ ' AND ' + extra_where + query else: if order_by == "district_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_districtmaster.name as district_name FROM dashboard_imagemaster LEFT JOIN dashboard_districtmaster on dashboard_districtmaster.id = dashboard_imagemaster.district_id_id WHERE image_date <= '" + str(user_date[0]['to_date']) + "'"+ ' AND ' + extra_where + query + " ORDER BY district_name "+ sort_order elif order_by == "subdistrict_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_subdistrictmaster.name as subdistrict_name FROM dashboard_imagemaster LEFT JOIN dashboard_subdistrictmaster on dashboard_subdistrictmaster.id = dashboard_imagemaster.subdistrict_id_id WHERE image_date <= '" + str(user_date[0]['to_date']) + "'"+ ' AND ' + extra_where + query + " ORDER BY subdistrict_name "+ sort_order elif order_by == "city_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_citymaster.name as city_name FROM dashboard_imagemaster LEFT JOIN dashboard_citymaster on dashboard_citymaster.id = dashboard_imagemaster.city_id_id WHERE image_date <= '" + str(user_date[0]['to_date']) + "'"+ ' AND ' + extra_where + query + " ORDER BY city_name "+ sort_order elif order_by == "area_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_areamaster.name as area_name FROM dashboard_imagemaster LEFT JOIN dashboard_areamaster on dashboard_areamaster.id = dashboard_imagemaster.area_id_id WHERE image_date <= '" + str(user_date[0]['to_date']) + "'"+ ' AND ' + extra_where + query + " ORDER BY area_name "+ sort_order elif order_by == "subarea_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_subareamaster.name as subarea_name FROM dashboard_imagemaster LEFT JOIN dashboard_subareamaster on dashboard_subareamaster.id = dashboard_imagemaster.subarea_id_id WHERE image_date <= '" + str(user_date[0]['to_date']) + "'"+ ' AND ' + extra_where + query + " ORDER BY subarea_name "+ sort_order elif order_by == "status": query = "SELECT dashboard_imagemaster.*, dashboard_statusmaster.name as status_name FROM dashboard_imagemaster LEFT JOIN dashboard_statusmaster on dashboard_statusmaster.id = dashboard_imagemaster.status_id WHERE image_date <= '" + str(user_date[0]['to_date']) + "'"+ ' AND ' + extra_where + query + " ORDER BY status_name "+ sort_order else: query = "SELECT * FROM dashboard_imagemaster WHERE image_date <= '" + str(user_date[0]['to_date']) + "'"+ ' AND ' + extra_where + query data_sort_field = order_by.replace("text-center", "").strip() order_by_clause = " ORDER BY " + data_sort_field + " " + sort_order # Append the ORDER BY clause to the base query query = query + order_by_clause else: # Prepare the query if query: if order_by == "district_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_districtmaster.name as district_name FROM dashboard_imagemaster LEFT JOIN dashboard_districtmaster on dashboard_districtmaster.id = dashboard_imagemaster.district_id_id WHERE "+ extra_where + query + " ORDER BY district_name "+ sort_order elif order_by == "subdistrict_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_subdistrictmaster.name as subdistrict_name FROM dashboard_imagemaster LEFT JOIN dashboard_subdistrictmaster on dashboard_subdistrictmaster.id = dashboard_imagemaster.subdistrict_id_id WHERE "+ extra_where + query + " ORDER BY subdistrict_name "+ sort_order elif order_by == "city_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_citymaster.name as city_name FROM dashboard_imagemaster LEFT JOIN dashboard_citymaster on dashboard_citymaster.id = dashboard_imagemaster.city_id_id WHERE "+ extra_where + query + " ORDER BY city_name "+ sort_order elif order_by == "area_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_areamaster.name as area_name FROM dashboard_imagemaster LEFT JOIN dashboard_areamaster on dashboard_areamaster.id = dashboard_imagemaster.area_id_id WHERE "+ extra_where + query + " ORDER BY area_name "+ sort_order elif order_by == "subarea_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_subareamaster.name as subarea_name FROM dashboard_imagemaster LEFT JOIN dashboard_subareamaster on dashboard_subareamaster.id = dashboard_imagemaster.subarea_id_id WHERE "+ extra_where + query + " ORDER BY subarea_name "+ sort_order elif order_by == "status": query = "SELECT dashboard_imagemaster.*, dashboard_statusmaster.name as status_name FROM dashboard_imagemaster LEFT JOIN dashboard_statusmaster on dashboard_statusmaster.id = dashboard_imagemaster.status_id WHERE "+ extra_where + query + " ORDER BY status_name "+ sort_order else: query = "SELECT * FROM dashboard_imagemaster WHERE "+ extra_where + query data_sort_field = order_by.replace("text-center", "").strip() order_by_clause = " ORDER BY " + data_sort_field + " " + sort_order # Append the ORDER BY clause to the base query query = query + order_by_clause else: if order_by == "district_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_districtmaster.name as district_name FROM dashboard_imagemaster LEFT JOIN dashboard_districtmaster on dashboard_districtmaster.id = dashboard_imagemaster.district_id_id WHERE YEAR(image_date) = " + str(current_year)+" AND " +extra_where + " ORDER BY district_name "+ sort_order elif order_by == "subdistrict_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_subdistrictmaster.name as subdistrict_name FROM dashboard_imagemaster LEFT JOIN dashboard_subdistrictmaster on dashboard_subdistrictmaster.id = dashboard_imagemaster.subdistrict_id_id WHERE YEAR(image_date) = " + str(current_year)+" AND " +extra_where + " ORDER BY subdistrict_name "+ sort_order elif order_by == "city_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_citymaster.name as city_name FROM dashboard_imagemaster LEFT JOIN dashboard_citymaster on dashboard_citymaster.id = dashboard_imagemaster.city_id_id WHERE YEAR(image_date) = " + str(current_year)+" AND " +extra_where + " ORDER BY city_name "+ sort_order elif order_by == "area_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_areamaster.name as area_name FROM dashboard_imagemaster LEFT JOIN dashboard_areamaster on dashboard_areamaster.id = dashboard_imagemaster.area_id_id WHERE YEAR(image_date) = " + str(current_year)+" AND " +extra_where + " ORDER BY area_name "+ sort_order elif order_by == "subarea_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_subareamaster.name as subarea_name FROM dashboard_imagemaster LEFT JOIN dashboard_subareamaster on dashboard_subareamaster.id = dashboard_imagemaster.subarea_id_id WHERE YEAR(image_date) = " + str(current_year)+" AND " +extra_where + " ORDER BY subarea_name "+ sort_order elif order_by == "status": query = "SELECT dashboard_imagemaster.*, dashboard_statusmaster.name as status_name FROM dashboard_imagemaster LEFT JOIN dashboard_statusmaster on dashboard_statusmaster.id = dashboard_imagemaster.status_id WHERE YEAR(image_date) = " + str(current_year)+" AND " +extra_where + " ORDER BY status_name "+ sort_order else: query = "SELECT * FROM dashboard_imagemaster WHERE YEAR(image_date) = " + str(current_year)+" AND " +extra_where data_sort_field = order_by.replace("text-center", "").strip() order_by_clause = " ORDER BY " + data_sort_field + " " + sort_order # Append the ORDER BY clause to the base query query = query + order_by_clause # Append the ORDER BY clause to the query if query.strip(): # Check if query is not empty or only whitespace images = ImageMaster.objects.raw(query) else: print("Empty query") else: 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']: if order_by == "district_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_districtmaster.name as district_name FROM dashboard_imagemaster LEFT JOIN dashboard_districtmaster on dashboard_districtmaster.id = dashboard_imagemaster.district_id_id WHERE (image_date <= '" + str(user_date[0]['to_date']) + "') AND (image_date >= '" + str(user_date[0]['from_date']) + "')" + query + " ORDER BY district_name "+ sort_order elif order_by == "subdistrict_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_subdistrictmaster.name as subdistrict_name FROM dashboard_imagemaster LEFT JOIN dashboard_subdistrictmaster on dashboard_subdistrictmaster.id = dashboard_imagemaster.subdistrict_id_id WHERE (image_date <= '" + str(user_date[0]['to_date']) + "') AND (image_date >= '" + str(user_date[0]['from_date']) + "')" + query + " ORDER BY subdistrict_name "+ sort_order elif order_by == "city_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_citymaster.name as city_name FROM dashboard_imagemaster LEFT JOIN dashboard_citymaster on dashboard_citymaster.id = dashboard_imagemaster.city_id_id WHERE (image_date <= '" + str(user_date[0]['to_date']) + "') AND (image_date >= '" + str(user_date[0]['from_date']) + "')" + query + " ORDER BY city_name "+ sort_order elif order_by == "area_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_areamaster.name as area_name FROM dashboard_imagemaster LEFT JOIN dashboard_areamaster on dashboard_areamaster.id = dashboard_imagemaster.area_id_id WHERE (image_date <= '" + str(user_date[0]['to_date']) + "') AND (image_date >= '" + str(user_date[0]['from_date']) + "')" + query + " ORDER BY area_name "+ sort_order elif order_by == "subarea_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_subareamaster.name as subarea_name FROM dashboard_imagemaster LEFT JOIN dashboard_subareamaster on dashboard_subareamaster.id = dashboard_imagemaster.subarea_id_id WHERE (image_date <= '" + str(user_date[0]['to_date']) + "') AND (image_date >= '" + str(user_date[0]['from_date']) + "')" + query + " ORDER BY subarea_name "+ sort_order elif order_by == "status": query = "SELECT dashboard_imagemaster.*, dashboard_statusmaster.name as status_name FROM dashboard_imagemaster LEFT JOIN dashboard_statusmaster on dashboard_statusmaster.id = dashboard_imagemaster.status_id WHERE (image_date <= '" + str(user_date[0]['to_date']) + "') AND (image_date >= '" + str(user_date[0]['from_date']) + "')" + query + " ORDER BY status_name "+ sort_order else: query = "SELECT * FROM dashboard_imagemaster WHERE (image_date <= '" + str(user_date[0]['to_date']) + "') AND (image_date >= '" + str(user_date[0]['from_date']) + "')" + query data_sort_field = order_by.replace("text-center", "").strip() order_by_clause = " ORDER BY " + data_sort_field + " " + sort_order # Append the ORDER BY clause to the base query query = query + order_by_clause 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: if order_by == "district_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_districtmaster.name as district_name FROM dashboard_imagemaster LEFT JOIN dashboard_districtmaster on dashboard_districtmaster.id = dashboard_imagemaster.district_id_id WHERE image_date >= '" + str(user_date[0]['from_date']) + "'" + query + " ORDER BY district_name "+ sort_order elif order_by == "subdistrict_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_subdistrictmaster.name as subdistrict_name FROM dashboard_imagemaster LEFT JOIN dashboard_subdistrictmaster on dashboard_subdistrictmaster.id = dashboard_imagemaster.subdistrict_id_id WHERE image_date >= '" + str(user_date[0]['from_date']) + "'" + query + " ORDER BY subdistrict_name "+ sort_order elif order_by == "city_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_citymaster.name as city_name FROM dashboard_imagemaster LEFT JOIN dashboard_citymaster on dashboard_citymaster.id = dashboard_imagemaster.city_id_id WHERE image_date >= '" + str(user_date[0]['from_date']) + "'" + query + " ORDER BY city_name "+ sort_order elif order_by == "area_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_areamaster.name as area_name FROM dashboard_imagemaster LEFT JOIN dashboard_areamaster on dashboard_areamaster.id = dashboard_imagemaster.area_id_id WHERE image_date >= '" + str(user_date[0]['from_date']) + "'" + query + " ORDER BY area_name "+ sort_order elif order_by == "subarea_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_subareamaster.name as subarea_name FROM dashboard_imagemaster LEFT JOIN dashboard_subareamaster on dashboard_subareamaster.id = dashboard_imagemaster.subarea_id_id WHERE image_date >= '" + str(user_date[0]['from_date']) + "'" + query + " ORDER BY subarea_name "+ sort_order elif order_by == "status": query = "SELECT dashboard_imagemaster.*, dashboard_statusmaster.name as status_name FROM dashboard_imagemaster LEFT JOIN dashboard_statusmaster on dashboard_statusmaster.id = dashboard_imagemaster.status_id WHERE image_date >= '" + str(user_date[0]['from_date']) + "'" + query + " ORDER BY status_name "+ sort_order else: query = "SELECT * FROM dashboard_imagemaster WHERE image_date >= '" + str(user_date[0]['from_date']) + "'" + query data_sort_field = order_by.replace("text-center", "").strip() order_by_clause = " ORDER BY " + data_sort_field + " " + sort_order # Append the ORDER BY clause to the base query query = query + order_by_clause else: if order_by == "district_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_districtmaster.name as district_name FROM dashboard_imagemaster LEFT JOIN dashboard_districtmaster on dashboard_districtmaster.id = dashboard_imagemaster.district_id_id WHERE image_date <= '" + str(user_date[0]['to_date']) + "'" + query + " ORDER BY district_name "+ sort_order elif order_by == "subdistrict_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_subdistrictmaster.name as subdistrict_name FROM dashboard_imagemaster LEFT JOIN dashboard_subdistrictmaster on dashboard_subdistrictmaster.id = dashboard_imagemaster.subdistrict_id_id WHERE image_date <= '" + str(user_date[0]['to_date']) + "'" + query + " ORDER BY subdistrict_name "+ sort_order elif order_by == "city_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_citymaster.name as city_name FROM dashboard_imagemaster LEFT JOIN dashboard_citymaster on dashboard_citymaster.id = dashboard_imagemaster.city_id_id WHERE image_date <= '" + str(user_date[0]['to_date']) + "'" + query + " ORDER BY city_name "+ sort_order elif order_by == "area_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_areamaster.name as area_name FROM dashboard_imagemaster LEFT JOIN dashboard_areamaster on dashboard_areamaster.id = dashboard_imagemaster.area_id_id WHERE image_date <= '" + str(user_date[0]['to_date']) + "'" + query + " ORDER BY area_name "+ sort_order elif order_by == "subarea_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_subareamaster.name as subarea_name FROM dashboard_imagemaster LEFT JOIN dashboard_subareamaster on dashboard_subareamaster.id = dashboard_imagemaster.subarea_id_id WHERE image_date <= '" + str(user_date[0]['to_date']) + "'" + query + " ORDER BY subarea_name "+ sort_order elif order_by == "status": query = "SELECT dashboard_imagemaster.*, dashboard_statusmaster.name as status_name FROM dashboard_imagemaster LEFT JOIN dashboard_statusmaster on dashboard_statusmaster.id = dashboard_imagemaster.status_id WHERE image_date <= '" + str(user_date[0]['to_date']) + "'" + query + " ORDER BY status_name "+ sort_order else: query = "SELECT * FROM dashboard_imagemaster WHERE image_date <= '" + str(user_date[0]['to_date']) + "'" + query data_sort_field = order_by.replace("text-center", "").strip() order_by_clause = " ORDER BY " + data_sort_field + " " + sort_order # Append the ORDER BY clause to the base query query = query + order_by_clause else: if order_by == "district_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_districtmaster.name as district_name FROM dashboard_imagemaster LEFT JOIN dashboard_districtmaster on dashboard_districtmaster.id = dashboard_imagemaster.district_id_id WHERE YEAR(image_date) = " + str(current_year)+ query + " ORDER BY district_name "+ sort_order elif order_by == "subdistrict_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_subdistrictmaster.name as subdistrict_name FROM dashboard_imagemaster LEFT JOIN dashboard_subdistrictmaster on dashboard_subdistrictmaster.id = dashboard_imagemaster.subdistrict_id_id WHERE YEAR(image_date) = " + str(current_year)+ query + " ORDER BY subdistrict_name "+ sort_order elif order_by == "city_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_citymaster.name as city_name FROM dashboard_imagemaster LEFT JOIN dashboard_citymaster on dashboard_citymaster.id = dashboard_imagemaster.city_id_id WHERE YEAR(image_date) = " + str(current_year)+ query + " ORDER BY city_name "+ sort_order elif order_by == "area_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_areamaster.name as area_name FROM dashboard_imagemaster LEFT JOIN dashboard_areamaster on dashboard_areamaster.id = dashboard_imagemaster.area_id_id WHERE YEAR(image_date) = " + str(current_year)+ query + " ORDER BY area_name "+ sort_order elif order_by == "subarea_id_id": query = "SELECT dashboard_imagemaster.*, dashboard_subareamaster.name as subarea_name FROM dashboard_imagemaster LEFT JOIN dashboard_subareamaster on dashboard_subareamaster.id = dashboard_imagemaster.subarea_id_id WHERE YEAR(image_date) = " + str(current_year)+ query + " ORDER BY subarea_name "+ sort_order elif order_by == "status": query = "SELECT dashboard_imagemaster.*, dashboard_statusmaster.name as status_name FROM dashboard_imagemaster LEFT JOIN dashboard_statusmaster on dashboard_statusmaster.id = dashboard_imagemaster.status_id WHERE YEAR(image_date) = " + str(current_year)+ query + " ORDER BY status_name "+ sort_order else: query = "SELECT * FROM dashboard_imagemaster WHERE YEAR(image_date) = " + str(current_year)+ query data_sort_field = order_by.replace("text-center", "").strip() order_by_clause = " ORDER BY " + data_sort_field + " " + sort_order # Append the ORDER BY clause to the base query query = query + order_by_clause if query.strip(): # Check if query is not empty or only whitespace images = ImageMaster.objects.raw(query) else: print("Empty query") pages = request.GET.get('page_count',0) page_list = [10,20,50,100,500] if int(pages) in page_list: page_passed = pages else: page_passed = 10 paginator = Paginator(images, page_passed) page_number = request.GET.get('page', 1) page_obj = paginator.get_page(page_number) range_status = Setting.objects.filter(name = "update_within_range_status").values("value") if range_status[0]["value"] == '1': range = Setting.objects.filter(name = "update_within_range").values("value") setting_range = range[0]['value'] else: setting_range = 0 context = {"countries":countries, "states":states,"districts":districts,"subdistricts":subdistricts,"cities":cities,"areas":areas,"allsubareas":allsubareas,"subareas":subareas, "statuses":statuses,"img_list":img_list,"DRIVE_IMAGE_PATH":DRIVE_IMAGE_PATH, "title":"Location Listing","page_obj": page_obj,"paginator": paginator,"count": count,"search_for":search_for, "user_state":user_state,"company_data":get_company_data(),"range":setting_range,"order_by": order_by,"sort_order": sort_order} return render(request, 'menu/master/images/imagelist.html', context) def get_subarea_names(request): query = request.GET.get('term', '') subareas = SubAreaMaster.objects.filter(id = query ).values( 'name', 'id', 'area__name', 'area__id', 'area__name', 'area__city__id', 'area__city__name', 'area__city__subdistrict__id', 'area__city__subdistrict__name', 'area__city__subdistrict__district__id', 'area__city__subdistrict__district__name', 'area__city__subdistrict__district__state__id', 'area__city__subdistrict__district__state__name' ) subarea_list = [] for subarea in subareas: subarea_str = subarea['name'] + ' (' + subarea['area__name'] + ')' subarea_dict = { 'subarea': subarea_str, 'id': subarea['id'], 'area_id': subarea['area__id'], 'area_name': subarea['area__name'], 'city_id': subarea['area__city__id'], 'city_name': subarea['area__city__name'], 'subdistrict_id': subarea['area__city__subdistrict__id'], 'subdistrict_name': subarea['area__city__subdistrict__name'], 'district_id': subarea['area__city__subdistrict__district__id'], 'district_name': subarea['area__city__subdistrict__district__name'], 'state_id': subarea['area__city__subdistrict__district__state__id'], 'state_name': subarea['area__city__subdistrict__district__state__name'], } subarea_list.append(subarea_dict) return JsonResponse(subarea_list, safe=False) return JsonResponse(subarea_list, safe=False) def maps_view(request): location_data = [] locations = None # Initialize the variable with a default value current_year = datetime.datetime.now().year # group = user.groups.all() user_locations= UserLocation.objects.filter(user=request.user.id) # statusOptions = list(StatusMaster.objects.all().values('id','name')) 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') if current_grp[0]["groups__id"] == 2: status_ids = [1, 2, 4] statusOptions = list(StatusMaster.objects.filter(id__in=status_ids).values('id','name').order_by('order_by')) elif current_grp[0]["groups__id"] == 3: status_ids = [2,3,5] statusOptions = list(StatusMaster.objects.filter(id__in=status_ids).values('id','name').order_by('order_by')) else: statusOptions = list(StatusMaster.objects.all().values('id','name').order_by('order_by')) state_list = list(user_locations.values_list('state_id',flat=True).distinct()) district_list = list(user_locations.values_list('district_id',flat=True).distinct()) subdistrict_list = list(user_locations.values_list('subdistrict_id',flat=True).distinct()) city_list = list(user_locations.values_list('city_id',flat=True).distinct()) area_list = list(user_locations.values_list('area_id',flat=True).distinct()) subarea_list = list(user_locations.values_list('subarea_id',flat=True).distinct()) 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 subarea: # subarea = subarea.replace(")", "") # subarea = subarea.split(" (") # subareas = SubAreaMaster.objects.filter(name=subarea[0],area__name=subarea[1]).values('id','area__name','area__name','area__city__id','area__city__district__id','area__city__district__state__id') # subarea = subareas[0]["id"] # subarea = str(subarea) 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) country_list = [] state_list = [] district_list = [] city_list = [] area_list = [] subarea_list = [] query = "" extra_where = "" state_query = Q() district_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 round: if user_state: query += " AND round = " + round if status: query +=" AND status_id =" + status if image_date_from: image_date_from = datetime.datetime.strptime(str(image_date_from), '%Y-%m-%d').date() query += " AND image_date >= '" + str(image_date_from) + "'" if image_date_to: image_date_to = datetime.datetime.strptime(str(image_date_to), '%Y-%m-%d').date() query += " AND image_date <= '" + str(image_date_to) + "'" if img_id: query += " AND id = '" + img_id + "'" else: query += " AND round = " + round if status: query +=" AND status_id =" + status if image_date_from: image_date_from = datetime.datetime.strptime(str(image_date_from), '%Y-%m-%d').date() query += " AND image_date >= '" + str(image_date_from) + "'" if image_date_to: image_date_to = datetime.datetime.strptime(str(image_date_to), '%Y-%m-%d').date() query += " AND image_date <= '" + str(image_date_to) + "'" if img_id: query += " AND id = '" + img_id + "'" if status and not round: if user_state or subarea: query +=" AND status_id =" + status if img_id: query += " AND id = '" + img_id + "'" else: query += " AND status_id = " + status if image_date_from: image_date_from = datetime.datetime.strptime(str(image_date_from), '%Y-%m-%d').date() query += " AND image_date >= '" + str(image_date_from) + "'" if image_date_to: image_date_to = datetime.datetime.strptime(str(image_date_to), '%Y-%m-%d').date() query += " AND image_date <= '" + str(image_date_to) + "'" if img_id: query += " AND id = '" + img_id + "'" if image_date_from and not status and not round: if user_state or subarea: image_date_from = datetime.datetime.strptime(str(image_date_from), '%Y-%m-%d').date() query += " AND image_date >= '" + str(image_date_from) + "'" if image_date_to: image_date_to = datetime.datetime.strptime(str(image_date_to), '%Y-%m-%d').date() query += " AND image_date <= '" + str(image_date_to) + "'" if img_id: query += " AND id = '" + img_id + "'" else: image_date_from = datetime.datetime.strptime(str(image_date_from), '%Y-%m-%d').date() query += " AND (image_date >= '" + str(image_date_from) + "')" if image_date_to: image_date_to = datetime.datetime.strptime(str(image_date_to), '%Y-%m-%d').date() query += " AND (image_date <= '" + str(image_date_to) + "')" if img_id: query += " AND id = '" + img_id + "'" if image_date_to and not status and not round: if user_state or subarea: image_date_to = datetime.datetime.strptime(str(image_date_to), '%Y-%m-%d').date() query += " AND (image_date <= '" + str(image_date_to) + "')" if image_date_from: image_date_from = datetime.datetime.strptime(str(image_date_from), '%Y-%m-%d').date() query += " AND image_date >= '" + str(image_date_from) + "'" if img_id: query += " AND id = '" + img_id + "'" else: image_date_to = datetime.datetime.strptime(str(image_date_to), '%Y-%m-%d').date() query += " AND (image_date <= '" + str(image_date_to) + "')" if image_date_from: image_date_from = datetime.datetime.strptime(str(image_date_from), '%Y-%m-%d').date() query += " AND image_date >= '" + str(image_date_from) + "'" if img_id: query += " AND id = '" + img_id + "'" if img_id and not status and not round and not image_date_from and not image_date_to: if user_state: query +=" AND id =" + img_id else: query += " AND id = " + img_id 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 " extra_where += " ( " 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']: query = "SELECT * 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 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: query = "SELECT * FROM dashboard_imagemaster WHERE image_date >= '" + str(user_date[0]['from_date']) + "'"+ ' AND ' + extra_where + query else: query = "SELECT * FROM dashboard_imagemaster WHERE image_date <= '" + str(user_date[0]['to_date']) + "'"+ ' AND ' + extra_where + query else: # Prepare the query if query: query = "SELECT * FROM dashboard_imagemaster WHERE "+ extra_where + query else: query = "SELECT * FROM dashboard_imagemaster WHERE YEAR(image_date) = " + str(current_year)+" AND " +extra_where if query.strip(): # Check if query is not empty or only whitespace locations = ImageMaster.objects.raw(query) else: print("Empty query") else: 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']: query = "SELECT * FROM dashboard_imagemaster WHERE (image_date <= '" + str(user_date[0]['to_date']) + "') AND (image_date >= '" + str(user_date[0]['from_date']) + "')" + query 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: query = "SELECT * FROM dashboard_imagemaster WHERE image_date >= '" + str(user_date[0]['from_date']) + "'" + query else: query = "SELECT * FROM dashboard_imagemaster WHERE image_date <= '" + str(user_date[0]['to_date']) + "'" + query else: query = "SELECT * FROM dashboard_imagemaster WHERE YEAR(image_date) = " + str(current_year)+ query if query.strip(): # Check if query is not empty or only whitespace locations = ImageMaster.objects.raw(query) else: print("Empty query") if locations is None: print("Empty query") else: for location in locations: location_ = {} location_["id"] = location.id state = StateMaster.objects.filter(id=location.state_id_id).values('name') if state.exists(): location_["state"] = state[0]['name'] else: location_["state"] = "" district = DistrictMaster.objects.filter(id=location.district_id_id).values('name') if district.exists(): location_["district"] = district[0]['name'] else: location_["district"] = "" subdistrict = SubDistrictMaster.objects.filter(id=location.subdistrict_id_id).values('name') if subdistrict.exists(): location_["subdistrict"] = subdistrict[0]['name'] else: location_["subdistrict"] = "" city = CityMaster.objects.filter(id=location.city_id_id).values('name') if city.exists(): location_["city"] = city[0]['name'] else: location_["city"] = "" area = AreaMaster.objects.filter(id=location.area_id_id).values('name') if area.exists(): location_["area"] = area[0]['name'] else: location_["area"] = "" subarea = SubAreaMaster.objects.filter(id=location.subarea_id_id).values('name') if subarea.exists(): location_["subarea"] = subarea[0]['name'] else: location_["subarea"] = "" status = StatusMaster.objects.filter(id = location.status_id).values('name','id') location_["status"]= status[0]['name'] location_["status_id"]= status[0]['id'] location_["latitude"] = location.latitude location_["longitude"] = location.longitude location_["altitude"] = location.altitude location_["round"] = location.round location_["image_id"]=location.drive_image_id location_["updated_by"]=location.updated_by if location.updated_by is not None: user = User.objects.filter(id = location.updated_by).values('id','groups__id') location_["groups_id"]=user[0]['groups__id'] else: location_["groups_id"]=None location_data.append(location_) range_status = Setting.objects.filter(name = "update_within_range_status").values("value") if range_status[0]["value"] == '1': range = Setting.objects.filter(name = "update_within_range").values("value") setting_range = range[0]['value'] else: setting_range = 0 context = {'locations':json.dumps(location_data), 'status':statusOptions,"company_data":get_company_data(),'DRIVE_IMAGE_PATH':DRIVE_IMAGE_PATH,"range":setting_range,"MAP_API_KEY":MAP_API_KEY} return render(request, 'menu/master/images/map.html',context) class UpdateStatus(LoginRequiredMixin, View): def post(self, request): crt_superuser = request.user.is_superuser user = User.objects.filter(id = request.user.id).values('id') status = request.POST.get("selected_value") sel_id = request.POST.get("sel_id") messageseen = request.POST.get("messageseen") ImageMaster.objects.filter(id=sel_id).update(status=status,updated_by=user[0]['id'],updated_at=datetime.datetime.now().strftime('%Y-%m-%d')) user_id = ImageMaster.objects.filter(id=sel_id).values("updated_by") user_groups_id = User.objects.filter(id = user_id[0]['updated_by']).values('id','groups__id') # Return a JSON response with a success message data = {'success': True, 'message': 'Status updated successfully.','group_id':user_groups_id[0]['groups__id'],'sel_id':sel_id,'crt_superuser':crt_superuser,"statusname":status } if messageseen == None: messages.success(request, "status was successfully updated" ) return JsonResponse(data) class ChangeStatus(LoginRequiredMixin, View): def post(self, request): user = User.objects.filter(id = request.user.id).values('id') sel_id = request.POST.getlist("ids") status = request.POST.get("SValue") try: id_list = ast.literal_eval(sel_id[0]) # Evaluate the string as a Python literal except (SyntaxError, ValueError): id_list = [] # Handle the case if the value is not a valid list ids = [int(id) for id in id_list] # Check if sel_id is empty if not ids: data= {'success': False, 'message': 'Please Select Checkbox.'} # Handle the case when sel_id is empty (e.g., display an error message, return an appropriate response) messages.error(request, "Please Select Checkbox" ) # ... else: ImageMaster.objects.filter(id__in=ids).update(status=status, updated_by=user[0]['id']) # Return a JSON response with a success message data = {'success': True, 'message': 'Status updated successfully'} messages.success(request, "status was successfully updated" ) return JsonResponse(data) class DeleteSelected(LoginRequiredMixin, View): def post(self, request): if request.method == 'POST': try: # Get the user's ID sel_id = request.POST.getlist("ids") try: id_list = ast.literal_eval(sel_id[0]) # Evaluate the string as a Python literal except (SyntaxError, ValueError): id_list = [] # Handle the case if the value is not a valid list ids = [int(id) for id in id_list] # Check if sel_id is empty if not ids: data = {'success': False, 'message': 'Please select checkboxes.'} messages.error(request, "Please select checkboxes.") else: ImageMaster.objects.filter(id__in=ids).delete() data = {'success': True, 'message': 'Items deleted successfully'} messages.success(request, "Items deleted successfully") except Exception as e: data = {'success': False, 'message': 'An error occurred while deleting items.'} messages.error(request, "An error occurred while deleting items.") else: data = {'success': False, 'message': 'Invalid request method.'} messages.error(request, "Invalid request method.") return JsonResponse(data) def export_excel(request): current_year = datetime.datetime.now().year 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') if current_grp[0]["groups__id"] == 2: status_ids = [1, 2, 4] statuses = StatusMaster.objects.filter(id__in=status_ids).values('id','name').order_by('order_by') elif current_grp[0]["groups__id"] == 3: status_ids = [2,3,5] statuses = StatusMaster.objects.filter(id__in=status_ids).values('id','name').order_by('order_by') else: statuses = StatusMaster.objects.all().values('id','name').order_by('order_by') countries = CountryMaster.objects.all().values('name','id').order_by('name') states = StateMaster.objects.all().values('id','name').order_by('name') img_list = [] count = 0 search_for = None districts=[] 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: districts = 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) country_list = [] state_list = [] district_list = [] city_list = [] area_list = [] subarea_list = [] 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 round: if user_state: query += " AND round = " + round if status: query +=" AND status_id =" + status if image_date_from: image_date_from = datetime.datetime.strptime(str(image_date_from), '%Y-%m-%d').date() query += " AND image_date >= '" + str(image_date_from) + "'" if image_date_to: image_date_to = datetime.datetime.strptime(str(image_date_to), '%Y-%m-%d').date() query += " AND image_date <= '" + str(image_date_to) + "'" if img_id: query += " AND id = '" + img_id + "'" else: query += " AND round = " + round if status: query +=" AND status_id =" + status if image_date_from: image_date_from = datetime.datetime.strptime(str(image_date_from), '%Y-%m-%d').date() query += " AND image_date >= '" + str(image_date_from) + "'" if image_date_to: image_date_to = datetime.datetime.strptime(str(image_date_to), '%Y-%m-%d').date() query += " AND image_date <= '" + str(image_date_to) + "'" if img_id: query += " AND id = '" + img_id + "'" if status and not round: if user_state or subarea: query +=" AND status_id =" + status if img_id: query += " AND id = '" + img_id + "'" else: query += " AND status_id = " + status if image_date_from: image_date_from = datetime.datetime.strptime(str(image_date_from), '%Y-%m-%d').date() query += " AND image_date >= '" + str(image_date_from) + "'" if image_date_to: image_date_to = datetime.datetime.strptime(str(image_date_to), '%Y-%m-%d').date() query += " AND image_date <= '" + str(image_date_to) + "'" if img_id: query += " AND id = '" + img_id + "'" if image_date_from and not status and not round: if user_state or subarea: image_date_from = datetime.datetime.strptime(str(image_date_from), '%Y-%m-%d').date() query += " AND image_date >= '" + str(image_date_from) + "'" if image_date_to: image_date_to = datetime.datetime.strptime(str(image_date_to), '%Y-%m-%d').date() query += " AND image_date <= '" + str(image_date_to) + "'" if img_id: query += " AND id = '" + img_id + "'" else: image_date_from = datetime.datetime.strptime(str(image_date_from), '%Y-%m-%d').date() query += " AND (image_date >= '" + str(image_date_from) + "')" if image_date_to: image_date_to = datetime.datetime.strptime(str(image_date_to), '%Y-%m-%d').date() query += " AND (image_date <= '" + str(image_date_to) + "')" if img_id: query += " AND id = '" + img_id + "'" if image_date_to and not status and not round: if user_state or subarea: image_date_to = datetime.datetime.strptime(str(image_date_to), '%Y-%m-%d').date() query += " AND (image_date <= '" + str(image_date_to) + "')" if image_date_from: image_date_from = datetime.datetime.strptime(str(image_date_from), '%Y-%m-%d').date() query += " AND image_date >= '" + str(image_date_from) + "'" if img_id: query += " AND id = '" + img_id + "'" else: image_date_to = datetime.datetime.strptime(str(image_date_to), '%Y-%m-%d').date() query += " AND (image_date <= '" + str(image_date_to) + "')" if image_date_from: image_date_from = datetime.datetime.strptime(str(image_date_from), '%Y-%m-%d').date() query += " AND image_date >= '" + str(image_date_from) + "'" if img_id: query += " AND id = '" + img_id + "'" if img_id and not status and not round and not image_date_from and not image_date_to: if user_state: query +=" AND id =" + img_id else: query += " AND id = " + img_id is_upload_team_user = request.user.groups.values_list('name',flat=True).first() == 'Upload Team' crt_user = User.objects.filter(id=request.user.id).values('id','username') 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 " extra_where += " ( " 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 is_upload_team_user: query = "SELECT * FROM dashboard_imagemaster WHERE created_by="+str(crt_user[0]['id']) else: 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']: query = "SELECT * 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 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: query = "SELECT * FROM dashboard_imagemaster WHERE image_date >= '" + str(user_date[0]['from_date']) + "'"+ ' AND ' + extra_where + query else: query = "SELECT * FROM dashboard_imagemaster WHERE image_date <= '" + str(user_date[0]['to_date']) + "'"+ ' AND ' + extra_where + query else: # Prepare the query if query: query = "SELECT * FROM dashboard_imagemaster WHERE "+ extra_where + query else: query = "SELECT * FROM dashboard_imagemaster WHERE YEAR(image_date) = " + str(current_year)+" AND " +extra_where else: if is_upload_team_user: query = "SELECT * FROM dashboard_imagemaster WHERE created_by="+str(crt_user[0]['id']) if query.strip(): # Check if query is not empty or only whitespace images = ImageMaster.objects.raw(query) else: print("Empty query") else: 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']: query = "SELECT * FROM dashboard_imagemaster WHERE (image_date <= '" + str(user_date[0]['to_date']) + "') AND (image_date >= '" + str(user_date[0]['from_date']) + "')" + query 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: query = "SELECT * FROM dashboard_imagemaster WHERE image_date >= '" + str(user_date[0]['from_date']) + "'" + query else: query = "SELECT * FROM dashboard_imagemaster WHERE image_date <= '" + str(user_date[0]['to_date']) + "'" + query else: query = "SELECT * FROM dashboard_imagemaster WHERE YEAR(image_date) = " + str(current_year)+ query if query.strip(): # Check if query is not empty or only whitespace images = ImageMaster.objects.raw(query) else: print("Empty query") superuser = request.user.is_superuser # is_upload_team_user = request.user.groups.values_list('name',flat=True).first() == 'Upload Team' for img in images: image_data = {} image_data["Location ID"]=img.id state = StateMaster.objects.filter(id=img.state_id_id).values('name') if state: image_data["State"] = state[0]['name'] else: image_data["State"] = "" district = DistrictMaster.objects.filter(id=img.district_id_id).values('name') if district: image_data["District"] = district[0]['name'] else: image_data["District"] = "" subdistrict = SubDistrictMaster.objects.filter(id=img.subdistrict_id_id).values('name') if subdistrict: image_data["Sub District"] = subdistrict[0]['name'] else: image_data["Sub District"] = "" city = CityMaster.objects.filter(id=img.city_id_id).values('name') if city: image_data["City/Village"] = city[0]['name'] else: image_data["City/Village"] = "" area = AreaMaster.objects.filter(id=img.area_id_id).values('name') if area: image_data["Area"] = area[0]['name'] else: image_data["Area"] = "" subarea1 = SubAreaMaster.objects.filter(id=img.subarea_id_id).values('name') if subarea1: image_data["Sub Area"] = subarea1[0]['name'] else: image_data["Sub Area"] = "" status = StatusMaster.objects.filter(id = img.status_id).values('id','name') survey_date = datetime.datetime.strptime(str(img.image_date),'%Y-%m-%d').date() if is_upload_team_user: image_data["Upload Date"] = datetime.datetime.strptime(str(img.created_at.date()),'%Y-%m-%d').date() image_data["Drive Link"] = img.drive_url image_data["Image Link"] = DRIVE_IMAGE_PATH+img.drive_image_id image_data["Given Survey Date"] = survey_date user_full_name = request.user.get_full_name() image_data['User Name'] = user_full_name if not img.image_name: image_data["Image Name"]= "---" else: image_data['Image Name'] = img.image_name if not img.image_capture_date: image_data["Image Capture Date"]= "---" else: image_data['Image Capture Date'] = img.image_capture_date else: image_data["Latitude"] = img.latitude image_data["Longitude"] = img.longitude # print("longitude.....",img.longitude) image_data["Operation Round"] = img.round image_data["Survey Date"] = survey_date image_data["Status"]= status[0]['name'] if superuser: input_string = str(img.updated_at) datetime_obj = parse(input_string) date_only = datetime_obj.date() image_data["Updated At"] = date_only if not img.updated_by: image_data["Updated By"]= "---" else: full_name_u = User.objects.filter(id=img.updated_by).values("first_name", "last_name", "username") image_data['Updated By'] = full_name_u[0]["first_name"] +" " + full_name_u[0]["last_name"] img_list.append(image_data) # your code to generate the DataFrame here df = pd.DataFrame(img_list) if is_upload_team_user: file_response = excel_format_upload_team(df,crt_user) else: file_response = prepare_excel_format(superuser,crt_user,df,user_state,user_district,user_subdistrict,user_city,user_area,subarea) # return the response object return file_response def upload_export_excel(request): current_year = datetime.datetime.now().year 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') if current_grp[0]["groups__id"] == 2: status_ids = [1, 2, 4] statuses = StatusMaster.objects.filter(id__in=status_ids).values('id','name').order_by('order_by') elif current_grp[0]["groups__id"] == 3: status_ids = [2,3,5] statuses = StatusMaster.objects.filter(id__in=status_ids).values('id','name').order_by('order_by') else: statuses = StatusMaster.objects.all().values('id','name').order_by('order_by') countries = CountryMaster.objects.all().values('name','id').order_by('name') states = StateMaster.objects.all().values('id','name').order_by('name') img_list = [] count = 0 search_for = None districts=[] 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: districts = 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) country_list = [] state_list = [] district_list = [] city_list = [] area_list = [] subarea_list = [] 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 round: if user_state: query += " AND round = " + round if status: query +=" AND status_id =" + status if image_date_from: image_date_from = datetime.datetime.strptime(str(image_date_from), '%Y-%m-%d').date() query += " AND image_date >= '" + str(image_date_from) + "'" if image_date_to: image_date_to = datetime.datetime.strptime(str(image_date_to), '%Y-%m-%d').date() query += " AND image_date <= '" + str(image_date_to) + "'" if img_id: query += " AND id = '" + img_id + "'" else: query += " AND round = " + round if status: query +=" AND status_id =" + status if image_date_from: image_date_from = datetime.datetime.strptime(str(image_date_from), '%Y-%m-%d').date() query += " AND image_date >= '" + str(image_date_from) + "'" if image_date_to: image_date_to = datetime.datetime.strptime(str(image_date_to), '%Y-%m-%d').date() query += " AND image_date <= '" + str(image_date_to) + "'" if img_id: query += " AND id = '" + img_id + "'" if status and not round: if user_state or subarea: query +=" AND status_id =" + status if img_id: query += " AND id = '" + img_id + "'" else: query += " AND status_id = " + status if image_date_from: image_date_from = datetime.datetime.strptime(str(image_date_from), '%Y-%m-%d').date() query += " AND image_date >= '" + str(image_date_from) + "'" if image_date_to: image_date_to = datetime.datetime.strptime(str(image_date_to), '%Y-%m-%d').date() query += " AND image_date <= '" + str(image_date_to) + "'" if img_id: query += " AND id = '" + img_id + "'" if image_date_from and not status and not round: if user_state or subarea: image_date_from = datetime.datetime.strptime(str(image_date_from), '%Y-%m-%d').date() query += " AND image_date >= '" + str(image_date_from) + "'" if image_date_to: image_date_to = datetime.datetime.strptime(str(image_date_to), '%Y-%m-%d').date() query += " AND image_date <= '" + str(image_date_to) + "'" if img_id: query += " AND id = '" + img_id + "'" else: image_date_from = datetime.datetime.strptime(str(image_date_from), '%Y-%m-%d').date() query += " AND (image_date >= '" + str(image_date_from) + "')" if image_date_to: image_date_to = datetime.datetime.strptime(str(image_date_to), '%Y-%m-%d').date() query += " AND (image_date <= '" + str(image_date_to) + "')" if img_id: query += " AND id = '" + img_id + "'" if image_date_to and not status and not round: if user_state or subarea: image_date_to = datetime.datetime.strptime(str(image_date_to), '%Y-%m-%d').date() query += " AND (image_date <= '" + str(image_date_to) + "')" if image_date_from: image_date_from = datetime.datetime.strptime(str(image_date_from), '%Y-%m-%d').date() query += " AND image_date >= '" + str(image_date_from) + "'" if img_id: query += " AND id = '" + img_id + "'" else: image_date_to = datetime.datetime.strptime(str(image_date_to), '%Y-%m-%d').date() query += " AND (image_date <= '" + str(image_date_to) + "')" if image_date_from: image_date_from = datetime.datetime.strptime(str(image_date_from), '%Y-%m-%d').date() query += " AND image_date >= '" + str(image_date_from) + "'" if img_id: query += " AND id = '" + img_id + "'" if img_id and not status and not round and not image_date_from and not image_date_to: if user_state: query +=" AND id =" + img_id else: query += " AND id = " + img_id is_upload_team_user = request.user.groups.values_list('name',flat=True).first() == 'Upload Team' crt_user = User.objects.filter(id=request.user.id).values('id','username') # group_names = ['Upload Team', 'Admin'] # upload_team_users = User.objects.filter(groups__name__in=group_names) # user_ids = tuple(user.id for user in upload_team_users) # print("upload_team_users",upload_team_users) 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']: query = "SELECT * FROM dashboard_imagemaster WHERE (image_date <= '" + str(user_date[0]['to_date']) + "') AND (image_date >= '" + str(user_date[0]['from_date']) + "')" + query # + " AND " +"created_by IN %s" 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: query = "SELECT * FROM dashboard_imagemaster WHERE image_date >= '" + str(user_date[0]['from_date']) + "'" + query else: query = "SELECT * FROM dashboard_imagemaster WHERE image_date <= '" + str(user_date[0]['to_date']) + "'" + query else: query = "SELECT * FROM dashboard_imagemaster WHERE YEAR(image_date) = " + str(current_year)+ query if query.strip(): # Check if query is not empty or only whitespace images = ImageMaster.objects.raw(query) else: print("Empty query") superuser = request.user.is_superuser for img in images: image_data = {} image_data["Location ID"]=img.id state = StateMaster.objects.filter(id=img.state_id_id).values('name') if state: image_data["State"] = state[0]['name'] else: image_data["State"] = "" district = DistrictMaster.objects.filter(id=img.district_id_id).values('name') if district: image_data["District"] = district[0]['name'] else: image_data["District"] = "" subdistrict = SubDistrictMaster.objects.filter(id=img.subdistrict_id_id).values('name') if subdistrict: image_data["Sub District"] = subdistrict[0]['name'] else: image_data["Sub District"] = "" city = CityMaster.objects.filter(id=img.city_id_id).values('name') if city: image_data["City/Village"] = city[0]['name'] else: image_data["City/Village"] = "" area = AreaMaster.objects.filter(id=img.area_id_id).values('name') if area: image_data["Area"] = area[0]['name'] else: image_data["Area"] = "" subarea1 = SubAreaMaster.objects.filter(id=img.subarea_id_id).values('name') if subarea1: image_data["Sub Area"] = subarea1[0]['name'] else: image_data["Sub Area"] = "" status = StatusMaster.objects.filter(id = img.status_id).values('id','name') survey_date = datetime.datetime.strptime(str(img.image_date),'%Y-%m-%d').date() # if is_upload_team_user: image_data["Upload Date"] = datetime.datetime.strptime(str(img.created_at.date()),'%Y-%m-%d').date() image_data["Drive Link"] = img.drive_url image_data["Image Link"] = DRIVE_IMAGE_PATH+img.drive_image_id image_data["Given Survey Date"] = survey_date # user_full_name = request.user.get_full_name( ) full_name = User.objects.filter(id=img.created_by).values("first_name", "last_name", "username") image_data['User Name'] = full_name[0]["first_name"] +" " + full_name[0]["last_name"] if not img.updated_by: image_data["Updated By"]= "---" else: full_name_u = User.objects.filter(id=img.updated_by).values("first_name", "last_name", "username") image_data['Updated By'] = full_name_u[0]["first_name"] +" " + full_name_u[0]["last_name"] if not img.image_name: image_data["Image Name"]= "---" else: image_data['Image Name'] = img.image_name if not img.image_capture_date: image_data["Image Capture Date"]= "---" else: image_data['Image Capture Date'] = img.image_capture_date image_data["Operation Round"] = img.round image_data["Status"]= status[0]['name'] image_data["Latitude"] = img.latitude image_data["Longitude"] = img.longitude image_data["Operation Round"] = img.round input_string = str(img.updated_at) datetime_obj = parse(input_string) date_only = datetime_obj.date() image_data["Updated At"] = date_only img_list.append(image_data) # your code to generate the DataFrame here df = pd.DataFrame(img_list) file_response = excel_format_upload_team_admin(df,crt_user) # return the response object return file_response def prepare_excel_format(superuser,crt_user,df,user_state,user_district,user_subdistrict,user_city,user_area,subarea): if superuser: file_name = 'Admin_Site_'+datetime.datetime.now().strftime('%d%m%Y')+".xlsx" else: file_name = str(crt_user[0]['username'])+'_Site_'+datetime.datetime.now().strftime('%d%m%Y')+".xlsx" sheet_name = "Sheet1" # create the HttpResponse object with appropriate MIME type and headers response = HttpResponse(content_type='application/vnd.ms-excel') response['Content-Disposition'] = 'attachment; filename="{}"'.format(file_name) writer = pd.ExcelWriter(response, engine='xlsxwriter') empty_df = pd.DataFrame() empty_df.to_excel(writer, sheet_name=sheet_name, index = False) # df.to_excel(writer, sheet_name=sheet_name, startrow = 20, index = False) workbook = writer.book worksheet = writer.sheets[sheet_name] # ws = # set width of columns worksheet.set_column('A:J', 13) worksheet.set_column('K:K', 19) worksheet.set_column('L:L', 19) worksheet.set_column('M:N', 19) # worksheet.set_column(10, 10, width=40) # # Autofit all columns # for col in range(20): # worksheet.set_column(col, col, width=None) # worksheet.set_column('L:XFD', None, None, {'hidden': True}) # hide rows beyond row 16 # worksheet.set_row(16, None, None, {'hidden': True}) # company logo image_path = MEDIA_ROOT + get_company_data()["company_logo"] #adding img to cell B2 worksheet.insert_image('A1', image_path, {"x_scale": 0.45, "y_scale": 0.25}) # add title title = "Vector Control by Drone Technology\n(Survey and Operation Report)" # merge cells format = workbook.add_format({'text_wrap':1,'font_size':17,'font_name':'Arial','valign':'vcenter','align':'center'}) worksheet.merge_range('A1:K4', title, format) date_title = "Report Generation Date: "+datetime.datetime.now().strftime('%d/%m/%Y') worksheet.merge_range('H6:J6', date_title) row_format = workbook.add_format({'text_wrap':1,'border':1,'font_size':11,'font_name':'Calibri'}) header_format = workbook.add_format({'bold': 1,'text_wrap':1,'border':1,'font_size':11,'font_name':'Calibri'}) # first table worksheet.merge_range('A8:C8','State', header_format) worksheet.merge_range('A9:C9','District', header_format) worksheet.merge_range('A10:C10','Sub District', header_format) worksheet.merge_range('A11:C11','City/Village', header_format) worksheet.merge_range('A12:C12','Area', header_format) worksheet.merge_range('A13:C13','Sub Area', header_format) worksheet.merge_range('A14:C14','Total No. of Sites', header_format) worksheet.merge_range('A15:C15','Pending', header_format) worksheet.merge_range('A16:C16','No of Sites Completed by Ground Team:', header_format) worksheet.merge_range('A17:C17','Not Reachable By Ground Team:', header_format) worksheet.merge_range('A18:C18','No of Sites Completed by Drone Team:', header_format) worksheet.merge_range('A19:C19','Not Reachable By Drone Team:', header_format) if user_state: state = StateMaster.objects.filter(id=user_state).values_list('name',flat=True).first() worksheet.write(7, 6, state, row_format) else: worksheet.write(7, 6, user_state, row_format) if user_district: district = DistrictMaster.objects.filter(id=user_district).values_list('name',flat=True).first() worksheet.write(8, 6, district, row_format) else: worksheet.write(8, 6, user_district, row_format) if user_subdistrict: subdistrict = SubDistrictMaster.objects.filter(id=user_subdistrict).values_list('name',flat=True).first() worksheet.write(9, 6, subdistrict, row_format) else: worksheet.write(9, 6, user_subdistrict, row_format) if user_city: city = CityMaster.objects.filter(id=user_city).values_list('name',flat=True).first() worksheet.write(10, 6, city, row_format) else: worksheet.write(10, 6, user_city, row_format) if user_area: area = AreaMaster.objects.filter(id=user_area).values_list('name',flat=True).first() worksheet.write(11, 6, area, row_format) else: worksheet.write(11, 6, user_area, row_format) if subarea: subarea_value = SubAreaMaster.objects.filter(id=subarea).values_list('name',flat=True).first() worksheet.write(12, 6, subarea_value, row_format) else: worksheet.write(12, 6, subarea, row_format) total_sites = len(df) if total_sites != 0: total_pending_site = len(df[df['Status'] == "Pending"]) completed_by_gt = len(df[df['Status'] == "Completed by GT"]) not_reachable_by_gt = len(df[df['Status'] == "Not Reachable by GT"]) completed_by_dt = len(df[df['Status'] == "Completed by DT"]) not_reachable_by_dt = len(df[df['Status'] == "Not Reachable by DT"]) worksheet.write(13, 6, total_sites, row_format) worksheet.write(14, 6, total_pending_site, row_format) worksheet.write(15, 6, completed_by_gt, row_format) worksheet.write(16, 6, not_reachable_by_gt, row_format) worksheet.write(17, 6, completed_by_dt, row_format) worksheet.write(18, 6, not_reachable_by_dt, row_format) else: # Set 0 in all fields of the first table worksheet.write(13, 6, 0, row_format) worksheet.write(14, 6, 0, row_format) worksheet.write(15, 6, 0, row_format) worksheet.write(16, 6, 0, row_format) worksheet.write(17, 6, 0, row_format) worksheet.write(18, 6, 0, row_format) # second table # Write the column headers with the defined format. for col_num, value in enumerate(df.columns.values): worksheet.write(19, col_num, value,header_format) headers = ['Location ID', 'State', 'District','Sub District', 'City/Village', 'Area', 'Sub Area', 'Latitude', 'Longitude', 'Operation Round', 'Survey Date', 'Status'] if superuser: headers.append('Updated At') headers.append('Updated By') if df.empty: # Write column headers even when there are no records for col_idx, header in enumerate(headers): worksheet.write(19, col_idx, header, header_format) # Set "0" in all cells of the second table with header format num_cols = len(headers) for r in range(20, 20 + 1): # Only one row for headers for c in range(num_cols): worksheet.write(r, c, "N/A", row_format) else: date_format = workbook.add_format({'text_wrap': 1, 'border': 1, 'font_size': 11, 'font_name': 'Calibri', 'num_format': 'dd/mm/yy'}) df = df[headers] row_idx, col_idx = df.shape for r in range(row_idx): for c in range(col_idx): if c == 10 or c == 12: worksheet.write(r + 20, c, df.values[r, c], date_format) else: worksheet.write(r + 20, c, df.values[r, c], row_format) # close the writer object writer.close() # return the response object return response def excel_format_upload_team(df,crt_user): file_name = str(crt_user[0]['username'])+'_'+datetime.datetime.now().strftime('%d%m%Y')+".xlsx" sheet_name = "Sheet1" # create the HttpResponse object with appropriate MIME type and headers response = HttpResponse(content_type='application/vnd.ms-excel') response['Content-Disposition'] = 'attachment; filename="{}"'.format(file_name) writer = pd.ExcelWriter(response, engine='xlsxwriter') empty_df = pd.DataFrame() empty_df.to_excel(writer, sheet_name=sheet_name, index = False) workbook = writer.book worksheet = writer.sheets[sheet_name] # set width of columns worksheet.set_column('A:L', 12) worksheet.set_column('M:N', 36) # company logo image_path = MEDIA_ROOT + get_company_data()["company_logo"] #adding img to cell B2 worksheet.insert_image('A1', image_path, {"x_scale": 0.45, "y_scale": 0.25}) # add title title = "DATA UPLOAD REPORT" # merge cells format = workbook.add_format({'text_wrap':1,'border':1,'font_size':17,'font_name':'Arial','valign':'vcenter','align':'center'}) worksheet.merge_range('A1:L3', title, format) row_format = workbook.add_format({'text_wrap':1,'border':1,'font_size':11,'font_name':'Calibri'}) header_format = workbook.add_format({'bold': 1,'text_wrap':1,'border':1,'font_size':11,'font_name':'Calibri','valign':'vcenter','align':'center'}) worksheet.merge_range('A5:A7', 'Upload Date', header_format) worksheet.merge_range('B5:B7', 'User Name', header_format) worksheet.merge_range('C5:C7', 'Location ID', header_format) worksheet.merge_range('D5:D7', 'Image Name', header_format) worksheet.merge_range('E5:E7', 'Image Capture Date', header_format) worksheet.merge_range('F5:F7', 'Given Survey Date', header_format) worksheet.merge_range('G5:L5', 'Location', header_format) worksheet.merge_range('G6:G7', 'State', header_format) worksheet.merge_range('H6:H7', 'District', header_format) worksheet.merge_range('I6:I7', 'Sub District', header_format) worksheet.merge_range('J6:J7', 'City/Village', header_format) worksheet.merge_range('K6:K7', 'Area', header_format) worksheet.merge_range('L6:L7', 'Sub Area', header_format) worksheet.merge_range('M5:M7', 'Drive Link', header_format) worksheet.merge_range('N5:N7', 'Image Link', header_format) date_format = workbook.add_format({'text_wrap': 1, 'border': 1, 'font_size': 11, 'font_name': 'Calibri', 'num_format': 'dd/mm/yy'}) headers = ['Upload Date', 'User Name', 'Location ID', 'Image Name', 'Image Capture Date', 'Given Survey Date','State','District','Sub District', 'City/Village', 'Area', 'Sub Area', 'Drive Link','Image Link'] if df.empty: num_cols = len(headers) for r in range(7, 8): # Only one row for headers for c in range(num_cols): worksheet.write(r, c, "N/A", row_format) else: df = df[headers] row_idx, col_idx = df.shape for r in range(row_idx): for c in range(col_idx): if c in [0,4,5]: worksheet.write(r + 7, c, df.values[r, c], date_format) else: worksheet.write(r + 7, c, df.values[r, c], row_format) writer.close() return response def excel_format_upload_team_admin(df,crt_user): file_name = 'Admin_upload data_'+datetime.datetime.now().strftime('%d%m%Y')+".xlsx" sheet_name = "Sheet1" # create the HttpResponse object with appropriate MIME type and headers response = HttpResponse(content_type='application/vnd.ms-excel') response['Content-Disposition'] = 'attachment; filename="{}"'.format(file_name) writer = pd.ExcelWriter(response, engine='xlsxwriter') empty_df = pd.DataFrame() empty_df.to_excel(writer, sheet_name=sheet_name, index = False) workbook = writer.book worksheet = writer.sheets[sheet_name] # set width of columns worksheet.set_column('A:R', 12) worksheet.set_column('S:T', 36) # company logo image_path = MEDIA_ROOT + get_company_data()["company_logo"] #adding img to cell B2 worksheet.insert_image('A1', image_path, {"x_scale": 0.45, "y_scale": 0.25}) # add title title = "DATA UPLOAD REPORT" # merge cells format = workbook.add_format({'text_wrap':1,'border':1,'font_size':17,'font_name':'Arial','valign':'vcenter','align':'center'}) worksheet.merge_range('A1:S3', title, format) row_format = workbook.add_format({'text_wrap':1,'border':1,'font_size':11,'font_name':'Calibri'}) header_format = workbook.add_format({'bold': 1,'text_wrap':1,'border':1,'font_size':11,'font_name':'Calibri','valign':'vcenter','align':'center'}) worksheet.merge_range('A5:A7', 'Upload Date', header_format) worksheet.merge_range('B5:B7', 'User Name', header_format) worksheet.merge_range('C5:C7', 'Location ID', header_format) worksheet.merge_range('D5:D7', 'Image Name', header_format) worksheet.merge_range('E5:E7', 'Image Capture Date', header_format) worksheet.merge_range('F5:F7', 'Given Survey Date', header_format) worksheet.merge_range('G5:L5', 'Location', header_format) worksheet.merge_range('G6:G7', 'State', header_format) worksheet.merge_range('H6:H7', 'District', header_format) worksheet.merge_range('I6:I7', 'Sub District', header_format) worksheet.merge_range('J6:J7', 'City/Village', header_format) worksheet.merge_range('K6:K7', 'Area', header_format) worksheet.merge_range('L6:L7', 'Sub Area', header_format) worksheet.merge_range('M6:M7', 'Latitude', header_format) worksheet.merge_range('N6:N7', 'Longitude', header_format) worksheet.merge_range('O6:O7', 'Operation Round', header_format) worksheet.merge_range('P6:P7', 'Status', header_format) worksheet.merge_range('Q6:Q7', 'Updated At', header_format) worksheet.merge_range('R6:R7', 'Updated By', header_format) worksheet.merge_range('S5:S7', 'Drive Link', header_format) worksheet.merge_range('T5:T7', 'Image Link', header_format) date_format = workbook.add_format({'text_wrap': 1, 'border': 1, 'font_size': 11, 'font_name': 'Calibri', 'num_format': 'dd/mm/yy'}) headers = ['Upload Date', 'User Name', 'Location ID', 'Image Name', 'Image Capture Date', 'Given Survey Date','State','District','Sub District', 'City/Village', 'Area', 'Sub Area', 'Latitude', 'Longitude', 'Operation Round', 'Status','Updated At', 'Updated By', 'Drive Link','Image Link'] if df.empty: num_cols = len(headers) for r in range(7, 8): # Only one row for headers for c in range(num_cols): worksheet.write(r, c, "N/A", row_format) else: date_format = workbook.add_format({'text_wrap': 1, 'border': 1, 'font_size': 11, 'font_name': 'Calibri', 'num_format': 'dd/mm/yy'}) df = df[headers] row_idx, col_idx = df.shape for r in range(row_idx): for c in range(col_idx): if c in [0,4,5,14,16]: worksheet.write(r + 7, c, df.values[r, c], date_format) else: worksheet.write(r + 7, c, df.values[r, c], row_format) writer.close() return response