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,DRIVE_POST_IMAGE_FOLDER_ID 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 from .forms import NewImageDetailForm import os from googleapiclient.http import MediaFileUpload from io import BytesIO import tempfile from django.db import IntegrityError from users.models import * from django.shortcuts import get_object_or_404 # 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') allsubareas = SubAreaMaster.objects.all().values('id','name').order_by('name') allareas = AreaMaster.objects.all().values('id','name').order_by('name') allcities = CityMaster.objects.all().values('id','name').order_by('name') allsubdistricts = SubDistrictMaster.objects.all().values('id','name').order_by('name') alldistricts = DistrictMaster.objects.all().values('id','name').order_by('name') districts = [] subdistricts = [] cities = [] areas = [] subares = [] 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) statuses = StatusMaster.objects.all().values('id','name').order_by('order_by') context = {"user_state":user_state,"user_area":user_area,"user_city":user_city, "user_subdistrict":user_subdistrict,"user_district":user_district, "countries":countries, "states":states,"districts": districts,"subdistricts": subdistricts, "cities":cities,"areas":areas, "subareas":subareas,"statuses":statuses, "title":"Upload Data", "allsubareas":allsubareas,"allareas":allareas,"allcities":allcities, "allsubdistricts":allsubdistricts,"alldistricts":alldistricts,"subareas":subareas,"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') assign_data = request.POST.get('assign_data') # 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', []) Img_count = 0 if file_list: 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'] if assign_data == 'DT': 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=2,created_by = user[0]['id']) else: 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) def delete_post_image_item(request): if request.method == 'POST': item_id = request.POST.get('id') try: item = NewImageDetail.objects.get(images=item_id) print(item) item.delete() credentials_path = settings.DRIVE_API creds = service_account.Credentials.from_service_account_file(credentials_path) service = build('drive', 'v3', credentials=creds) try: # Delete the file service.files().delete(fileId=item_id,supportsAllDrives=True).execute() print('Image file deleted successfully') except Exception as e: print(f'An error occurred: {e}') return JsonResponse({'message': 'Item deleted successfully.'}) except NewImageDetail.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 = User.objects.get(id=request.user.id) 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','groups__name') order_by = request.GET.get('order_by', 'id') # Default sort field if not specified try: existing_entry = NewImageListOptions.objects.get(group_name=current_grp[0]['groups__name']) listing_options_list = existing_entry.listing_options.split(',') except NewImageListOptions.DoesNotExist: listing_options_list = [] 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') allareas = AreaMaster.objects.all().values('id','name').order_by('name') allcities = CityMaster.objects.all().values('id','name').order_by('name') allsubdistricts = SubDistrictMaster.objects.all().values('id','name').order_by('name') alldistricts = DistrictMaster.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 image_date_from or image_date_to: current_year = "2000,2001,2002,2003,2004,2005,2006,2007,2008,2009,2010,2011,2012,2013,2014,2015,2016,2017,2018,2019,2020,2021,2022,2023,2024,2025,2026,2027,2028,2029,2030,2031,2032,2033,2034,2035,2036,2037,2038,2039,2040,2041,2042,2043,2044,2045,2046,2047,2048,2049,2050" 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 = [] rep_context = {} if subarea: subarea_rep_data = get_object_or_404(SubAreaMaster, id=subarea) rep_context = { 'ground_rep_name': subarea_rep_data.ground_rep_name, 'ground_rep_number': subarea_rep_data.ground_rep_number, 'drone_rep_name': subarea_rep_data.drone_rep_name, 'drone_rep_number': subarea_rep_data.drone_rep_number, } 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 YEAR(image_date) IN (" + str(current_year) + ")"+" 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 YEAR(image_date) IN (" + str(current_year) + ")"+" 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 YEAR(image_date) IN (" + str(current_year) + ")"+" 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 YEAR(image_date) IN (" + str(current_year) + ")"+" 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 YEAR(image_date) IN (" + str(current_year) + ")"+" 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 YEAR(image_date) IN (" + str(current_year) + ")"+" AND "+ extra_where + query + " ORDER BY status_name "+ sort_order else: query = "SELECT * FROM dashboard_imagemaster WHERE YEAR(image_date) IN (" + str(current_year) + ")"+" 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: 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) IN (" + 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) IN (" + 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) IN (" + 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) IN (" + 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) IN (" + 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) IN (" + str(current_year) + ")"+" AND " +extra_where + " ORDER BY status_name "+ sort_order else: query = "SELECT * FROM dashboard_imagemaster WHERE YEAR(image_date) IN (" + 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) IN (" + 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) IN (" + 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) IN (" + 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) IN (" + 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) IN (" + 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) IN (" + str(current_year) + ")"+ query + " ORDER BY status_name "+ sort_order else: query = "SELECT * FROM dashboard_imagemaster WHERE YEAR(image_date) IN (" + 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 = {"listing_options_list":listing_options_list,"user_area":user_area,"user_city":user_city, "user_subdistrict":user_subdistrict,"user_district":user_district,"countries":countries, "states":states,"districts":districts,"subdistricts":subdistricts, "cities":cities,"areas":areas,"allsubareas":allsubareas,"allareas":allareas,"allcities":allcities, "allsubdistricts":allsubdistricts,"alldistricts":alldistricts,"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, "rep_context": len(rep_context),**rep_context} 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) def get_area_names(request): query = request.GET.get('term', '') areas = AreaMaster.objects.filter(id = query ).values( 'name', 'id', 'city__id', 'city__name', 'city__subdistrict__id', 'city__subdistrict__name', 'city__subdistrict__district__id', 'city__subdistrict__district__name', 'city__subdistrict__district__state__id', 'city__subdistrict__district__state__name' ) area_list = [] for area in areas: area_str = area['name'] + ' (' + area['city__name'] + ')' area_dict = { 'area': area_str, 'id': area['id'], 'area_id': area['id'], 'area_name': area['name'], 'city_id': area['city__id'], 'city_name': area['city__name'], 'subdistrict_id': area['city__subdistrict__id'], 'subdistrict_name': area['city__subdistrict__name'], 'district_id': area['city__subdistrict__district__id'], 'district_name': area['city__subdistrict__district__name'], 'state_id': area['city__subdistrict__district__state__id'], 'state_name': area['city__subdistrict__district__state__name'], } area_list.append(area_dict) return JsonResponse(area_list, safe=False) def get_city_names(request): query = request.GET.get('term', '') cities = CityMaster.objects.filter(id = query ).values( 'name', 'id', 'subdistrict__id', 'subdistrict__name', 'subdistrict__district__id', 'subdistrict__district__name', 'subdistrict__district__state__id', 'subdistrict__district__state__name' ) city_list = [] for city in cities: city_str = city['name'] + ' (' + city['name'] + ')' city_dict = { 'city': city_str, 'id': city['id'], 'city_id': city['id'], 'city_name': city['name'], 'subdistrict_id': city['subdistrict__id'], 'subdistrict_name': city['subdistrict__name'], 'district_id': city['subdistrict__district__id'], 'district_name': city['subdistrict__district__name'], 'state_id': city['subdistrict__district__state__id'], 'state_name': city['subdistrict__district__state__name'], } city_list.append(city_dict) return JsonResponse(city_list, safe=False) def get_subdistrict_names(request): query = request.GET.get('term', '') subdistricts = SubDistrictMaster.objects.filter(id = query ).values( 'name', 'id', 'district__id', 'district__name', 'district__state__id', 'district__state__name' ) subdistrict_list = [] for subdistrict in subdistricts: subdistrict_str = subdistrict['name'] + ' (' + subdistrict['name'] + ')' subdistrict_dict = { 'subdistrict': subdistrict_str, 'id': subdistrict['id'], 'subdistrict_id': subdistrict['id'], 'subdistrict_name': subdistrict['name'], 'district_id': subdistrict['district__id'], 'district_name': subdistrict['district__name'], 'state_id': subdistrict['district__state__id'], 'state_name': subdistrict['district__state__name'], } subdistrict_list.append(subdistrict_dict) return JsonResponse(subdistrict_list, safe=False) def get_district_names(request): query = request.GET.get('term', '') districts = DistrictMaster.objects.filter(id = query ).values( 'name', 'id', 'state__id', 'state__name' ) district_list = [] for district in districts: district_str = district['name'] + ' (' + district['name'] + ')' district_dict = { 'district': district_str, 'id': district['id'], 'district_id': district['id'], 'district_name': district['name'], 'state_id': district['state__id'], 'state_name': district['state__name'], } district_list.append(district_dict) return JsonResponse(district_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')) 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') allareas = AreaMaster.objects.all().values('id','name').order_by('name') allcities = CityMaster.objects.all().values('id','name').order_by('name') allsubdistricts = SubDistrictMaster.objects.all().values('id','name').order_by('name') alldistricts = DistrictMaster.objects.all().values('id','name').order_by('name') 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 image_date_from or image_date_to: current_year = "2000,2001,2002,2003,2004,2005,2006,2007,2008,2009,2010,2011,2012,2013,2014,2015,2016,2017,2018,2019,2020,2021,2022,2023,2024,2025,2026,2027,2028,2029,2030,2031,2032,2033,2034,2035,2036,2037,2038,2039,2040,2041,2042,2043,2044,2045,2046,2047,2048,2049,2050" # 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 YEAR(image_date) IN (" + str(current_year) + ") AND "+ extra_where + query else: query = "SELECT * FROM dashboard_imagemaster WHERE YEAR(image_date) IN (" + 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) IN (" + 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), "countries":countries, "states":states,"allsubareas":allsubareas,"allareas":allareas,"allcities":allcities, "allsubdistricts":allsubdistricts,"alldistricts":alldistricts, '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) def get_post_image(request): img_id = request.GET.get("image_id") try: images = NewImageDetail.objects.filter(image_master=img_id, state='post').values('images') post_count = NewImageDetail.objects.filter(image_master=img_id, state='post').count() remark = ImageMaster.objects.filter(id=img_id).values('remark').first() if remark and remark['remark'] != "": remark = remark['remark'] else: remark = '' combined_list = [(item['images']) for item in images] return JsonResponse({'success': True, 'combined_list': combined_list , 'post_image_count':post_count,'remark':remark}) except NewImageDetail.DoesNotExist: return JsonResponse({'success': False, 'message': 'ImageMaster object does not exist'}) class UpdateStatus(LoginRequiredMixin, View): data={} def get_folder_id(self, service, folder_name, parent_id): query = f"name='{folder_name}' and '{parent_id}' in parents and mimeType='application/vnd.google-apps.folder' and trashed=false" results = service.files().list(q=query, fields="files(id, name)").execute() items = results.get('files', []) if items: return items[0]['id'] return None def post(self, request): status = request.POST.get("selected_value") crt_superuser = request.user.is_superuser crt_isoperational_head = (request.user.groups.all()[0].name == 'Operational Head') user_id = request.user.id sel_id = request.POST.get("sel_id") img_state = request.POST.get("state") messageseen = request.POST.get("messageseen") images_list = request.FILES.getlist('images') remark = request.POST.get("remark") image_id = sel_id post_count = NewImageDetail.objects.filter(image_master=image_id, state='post').count() credentials_path = settings.DRIVE_API creds = service_account.Credentials.from_service_account_file(credentials_path) service = build('drive', 'v3', credentials=creds) if post_count != 5: try: image_master = ImageMaster.objects.get(id=image_id) user_groups_id = User.objects.filter(id=user_id).values('id', 'groups__id') except ImageMaster.DoesNotExist: return JsonResponse({'success': False, 'message': 'ImageMaster object does not exist'}) if post_count < 5: folder_name = image_id # Folder name based on image_id folder_id = self.get_folder_id(service, folder_name, DRIVE_POST_IMAGE_FOLDER_ID) if not folder_id: folder_metadata = { 'name': folder_name, 'parents': [DRIVE_POST_IMAGE_FOLDER_ID], 'mimeType': 'application/vnd.google-apps.folder' } folder = service.files().create(body=folder_metadata, fields='id').execute() folder_id = folder.get('id') for img in images_list: with tempfile.NamedTemporaryFile(delete=False) as tmp_file: tmp_file.write(img.read()) file_metadata = {'name': img.name, 'parents': [folder_id]} media = MediaFileUpload(tmp_file.name, mimetype='image/jpeg') file = service.files().create(body=file_metadata, media_body=media, fields='id').execute() file_id = file.get('id') img_folder_path = f"https://drive.google.com/drive/folders/{folder_id}" NewImageDetail.objects.create(image_master=image_master, state=img_state, image_folder_url=img_folder_path, images=file_id, created_by=user_id) # close the file tmp_file.close() # os.unlink(tmp_file.name) ImageMaster.objects.filter(id=sel_id).update(status=status, updated_by=user_id, remark=remark, updated_at=datetime.datetime.now().strftime('%Y-%m-%d')) user_groups_id = User.objects.filter(id=user_id).values('id', 'groups__id') data = {'success': True, 'message': 'Status and Images updated successfully.', 'group_id': user_groups_id[0]['groups__id'], 'sel_id': sel_id, 'crt_superuser': crt_superuser, 'crt_isoperational_head':crt_isoperational_head, "statusname": status} if messageseen is None: messages.success(request, "Status and Images updated successfully") return JsonResponse(data) else: data = {'success': False, 'message': 'Images upload limit exceeded.'} return JsonResponse(data) else: ImageMaster.objects.filter(id=sel_id).update(status=status, updated_by=user_id, remark=remark,updated_at=datetime.datetime.now().strftime('%Y-%m-%d')) user_groups_id = User.objects.filter(id=user_id).values('id', 'groups__id') data = {'success': True, 'message': 'Status updated successfully.', 'group_id': user_groups_id[0]['groups__id'], 'sel_id': sel_id, 'crt_superuser': crt_superuser, 'crt_isoperational_head': crt_isoperational_head, "statusname": status} if messageseen is 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 image_date_from or image_date_to: current_year = "2000,2001,2002,2003,2004,2005,2006,2007,2008,2009,2010,2011,2012,2013,2014,2015,2016,2017,2018,2019,2020,2021,2022,2023,2024,2025,2026,2027,2028,2029,2030,2031,2032,2033,2034,2035,2036,2037,2038,2039,2040,2041,2042,2043,2044,2045,2046,2047,2048,2049,2050" 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 YEAR(image_date) IN (" + str(current_year) + ")"+" AND "+ extra_where + query else: query = "SELECT * FROM dashboard_imagemaster WHERE YEAR(image_date) IN (" + 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']) else : 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']: 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) IN (" + 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 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) 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) IN (" + 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 def get_subarea_info(request): subarea_id = request.GET.get('subarea_id') subarea = get_object_or_404(SubAreaMaster, id=subarea_id) data = { 'ground_rep_name': subarea.ground_rep_name, 'ground_rep_number': subarea.ground_rep_number, 'drone_rep_name': subarea.drone_rep_name, 'drone_rep_number': subarea.drone_rep_number, } return JsonResponse(data)