import os import uuid import pytz import requests import datetime from multiprocessing.spawn import import_main_path from django.views import View from django.conf import settings from django.db import connection from django.utils import timezone from django.contrib import messages from django.shortcuts import render, redirect from django.contrib.auth.decorators import login_required from django.db.models import F, ExpressionWrapper, FloatField from django.db.models.functions import Substr from apps.common.utils import get_client_ip from apps.channels.models import Channel from apps.activities.models import Activity from apps.campaigns.models import Campaigns, Adspots from apps.agencies.models import Agency, Brand, Advertiser, BrandCategory, BrandStatus from apps.core.utils import handle_uploaded_file # ===================================================================== # Agency views # ===================================================================== @login_required def view_agencies(request): # Get the user from the request user = request.user # Get all agencies agencies = Agency.objects.filter( owner=request.user, is_deleted=0 ) # Log user activity Activity.log_activity( user=user, action="view_agencies", description=f"User {user.email if user else 'System'} performed view_agencies", request=request, ip_address=get_client_ip(request), user_agent=request.META.get("HTTP_USER_AGENT", ""), metadata={ } ) return render(request , 'agencies/agency/list.html',{'agency':agencies}) @login_required def add_agency(request): # Get the user from the request user = request.user # check request method if request.method == 'POST': # Get Agency Info name_agency = request.POST.get('name_agency') description_agency = request.POST.get('description_agency') # Check if the channel name is empty if not name_agency: messages.error(request, 'Agency name is required.') return redirect("agencies:add_agency") # Create and save the new agency agency = Agency.objects.create( owner=user, name=name_agency, description=description_agency, datetime=timezone.now() ) # Log user activity Activity.log_activity( user=user, action="add_agency", description=f"User {user.email if user else 'System'} performed add_agency", request=request, ip_address=get_client_ip(request), user_agent=request.META.get("HTTP_USER_AGENT", ""), metadata={ "agency_id": str(agency.id), "agency_name": name_agency, "agency_desc": description_agency, } ) # Redirect to the agency list page return redirect("agencies:view_agencies") return render(request, "agencies/agency/add.html") @login_required def edit_agency(request, id_agency=None): # Get the user from the request user = request.user # check request method if request.method == 'POST': # check if the agency id is not None if id_agency is not None: agency = Agency.objects.get(pk=id_agency) # check if the agency owner is the user if agency.owner != user: messages.error(request, 'You are not the owner of this agency.') return redirect("agencies:edit_agency") data = {'agency':agency} # Log user activity Activity.log_activity( user=user, action="edit_agency", description=f"User {user.email if user else 'System'} performed view_agency with id: {id_agency}", request=request, ip_address=get_client_ip(request), user_agent=request.META.get("HTTP_USER_AGENT", ""), metadata={ "agency_id": str(agency.id), "agency_name": agency.name, "agency_desc": agency.description, } ) return render(request , 'agencies/agency/edit.html',{'data':data}) # Redirect to the agency list page return redirect("agencies:view_agencies") @login_required def update_agency(request, id_agency=None): user = request.user # check request method if request.method == 'POST': # check if the agency id is not None if id_agency is not None: # Get Agency Info name = request.POST.get('name_agency') description = request.POST.get('description_agency') # Check if the agency name is empty if not name : messages.error(request, 'Agency name is required.') return redirect("agencies:edit_agency") # Get Agency Info agency = Agency.objects.get(pk=id_agency) # Update Agency Info agency.name = name if name else agency.name agency.description = description if description else agency.description # Save Agency Info agency.save() # Log user activity Activity.log_activity( user=user, action="edit_agency", description=f"User {user.email if user else 'System'} performed edit_agency with id: {id_agency}", request=request, ip_address=get_client_ip(request), user_agent=request.META.get("HTTP_USER_AGENT", ""), metadata={ "agency_id": str(agency.id), "agency_name": agency.name, "agency_desc": agency.description, } ) # Redirect to the agency list page return redirect("agencies:view_agencies") @login_required def delete_agency(request,id_agency=None): # Get the user from the request user = request.user # check request method if request.method == 'POST': # check if the agency id is not None if id_agency is not None: # Get Agency Info agency = Agency.objects.get(pk=id_agency) # Update Agency Info agency.datetime = datetime.datetime.now() agency.is_deleted = 1 # Save Agency Info agency.save() # Log user activity Activity.log_activity( user=user, action="delete_agency", description=f"User {user.email if user else 'System'} performed delete_agency with id: {id_agency}", request=request, ip_address=get_client_ip(request), user_agent=request.META.get("HTTP_USER_AGENT", ""), metadata={ "agency_id": str(agency.id), "agency_name": agency.name, "agency_desc": agency.description, } ) # Redirect to the agency list page return redirect("agencies:view_agencies") @login_required def load_agency(request): # Get all agencies return render( request, "agencies/components/dropdown_agency_campaign.html", {"agencies": Agency.objects.filter(owner=request.user, is_deleted=0)} ) @login_required def send_agency(request): # Get the user from the request user = request.user name_agency = request.POST.get('name_agency') description_agency = request.POST.get('description_agency') agency = Agency( owner=user, name=name_agency, description=description_agency, datetime=timezone.now(), ) # Save the agency agency.save() return render(request,"agencies/components/dropdown_agency_campaign.html") @login_required def active_agency(request): agences = Agency.objects.filter( owner=request.user, is_deleted=0 ).values_list('id_agency','name','datetime') result = [] for agence in agences: r={} r['name'] = agence[1] brands = Brand.objects.filter(id_agency=agence[0]) campaigns = Campaigns.objects.filter(id_brand__in=brands,pacing=True) if len(campaigns)!=0: r['date'] = agence[2] r['campaigns'] = len(campaigns) result.append(r) return result # ===================================================================== # Advertisers views # ===================================================================== @login_required def views_advertiser(request, id_brand=None): # Get the user from the request user = request.user # if id_brand!="" and id_brand!=None: brand = Brand.objects.get(pk=id_brand) channels = Adspots.objects.filter(brand=brand).values_list('channel').distinct() channels = [ i[0] for i in list(channels)] channels = Channel.objects.filter(pk__in = channels) adspots = Adspots.objects.filter(brand=brand).order_by('channel') # Log user activity Activity.log_activity( user=user, action="view_advertiser", description=f"User {user.email if user else 'System'} performed view_advertiser with id {id_brand}", request=request, ip_address=get_client_ip(request), user_agent=request.META.get("HTTP_USER_AGENT", ""), metadata={ } ) return render( request, 'agencies/advertiser/views_adspots.html', {'data':{ 'adspots':adspots, 'brand': brand , 'channels' : list(channels) }} ) else : brands_tuples = Advertiser.objects.filter(id_user=user).values_list("brand") brands_id = [brand_tuple[0] for brand_tuple in brands_tuples] brands = Brand.objects.filter(pk__in=brands_id).order_by('-created_at') brand_dic = [] for brand in brands: # total_adspots = 0 # try: adspots = Adspots.objects.filter(brand=brand) total_adspots = len(adspots) except Adspots.DoesNotExist: adspot = None # brand_data = { "id_brand" : str(brand.id), "name_brand" : brand.name, "total_adspots" : total_adspots, "logo" : brand.logo_url } brand_dic.append(brand_data) # Log user activity Activity.log_activity( user=user, action="view_advertisers", description=f"User {user.email if user else 'System'} performed view_advertisers", request=request, ip_address=get_client_ip(request), user_agent=request.META.get("HTTP_USER_AGENT", ""), metadata={ } ) return render( request, "agencies/advertiser/views.html", {"brands":brand_dic } ) @login_required def add_advertiser(request): """ Handle brand/advertiser creation with proper validation and error handling """ user = request.user agencies = Agency.objects.filter(owner=request.user,is_deleted=0) if request.method == 'POST': try: # Extract form data with proper field names matching the HTML form name = request.POST.get('name', '').strip() category = request.POST.get('category', '').strip() description = request.POST.get('description', '').strip() status = request.POST.get('status', 'active').strip() is_featured = request.POST.get('is_featured') == 'on' # Checkbox handling id_agency = request.POST.get('agency') # Validation errors = [] # Required field validation if not name: errors.append("Brand name is required.") elif len(name) > 255: errors.append("Brand name cannot exceed 255 characters.") if not category: errors.append("Category is required.") elif category not in dict(BrandCategory.choices): errors.append("Invalid category selected.") if status not in dict(BrandStatus.choices): errors.append("Invalid status selected.") if description and len(description) > 1000: errors.append("Description cannot exceed 1000 characters.") # Handle logo upload logo_file = None if 'logo' in request.FILES: logo_file = request.FILES['logo'] # Validate file size (5MB limit) max_size = 5 * 1024 * 1024 # 5MB if logo_file.size > max_size: errors.append("Logo file size cannot exceed 5MB.") # Validate file format allowed_extensions = ['jpg', 'jpeg', 'png', 'svg', 'webp'] file_extension = logo_file.name.split('.')[-1].lower() if file_extension not in allowed_extensions: errors.append("Logo must be in JPG, JPEG, PNG, SVG, or WebP format.") # If there are validation errors, return to form with errors if errors: for error in errors: messages.error(request, error) return redirect("agencies:add_advertiser") # Get the user's agency (assuming there's a relationship) try: # agency = Agency.objects.filter(pk=id_agency) # if agency.count() <= 0: # Adjust this based on your user-agency relationship agency = Agency.objects.filter( owner=request.user, is_deleted=0 ) except AttributeError: messages.error(request, "User is not associated with any agency.") return redirect("agencies:views_advertiser") print(agency) print(agency.count()) agency = agency.first() # Check for duplicate brand name within the same agency brand_exist = Brand.objects.filter(name=name, agency=agency).exists() if not brand_exist: # Create the Brand instance brand = Brand( name=name, agency=agency, description=description, category=category, status=status, is_featured=is_featured ) # Handle logo file upload if provided if logo_file: # Generate unique filename to prevent conflicts file_extension = logo_file.name.split('.')[-1].lower() unique_filename = f"{uuid.uuid4().hex}_{name[:50]}.{file_extension}" # Use Django's file handling for better security and management brand.logo.save(unique_filename, logo_file, save=False) # Save the brand brand.save() # Log activity (if Activity model exists) try: # Log user activity Activity.log_activity( user=user, action="create_brand", description=f"User {user.email if user else 'System'} performed create_brand", request=request, ip_address=get_client_ip(request), user_agent=request.META.get("HTTP_USER_AGENT", ""), metadata={ "brand": str(brand.id) } ) except ImportError: pass # Activity model doesn't exist else: brand = Brand.objects.get(name=name, agency=agency) # Create Advertiser relationship (if Advertisers model exists) try: advertiser_user = Advertisers( id_user=user, brand=brand, status=status, ) advertiser_user.save() except ImportError: pass # Advertisers model doesn't exist # Log activity (if Activity model exists) try: # Log user activity Activity.log_activity( user=user, action="create_advertiser", description=f"User {user.email if user else 'System'} performed create_advertiser", request=request, ip_address=get_client_ip(request), user_agent=request.META.get("HTTP_USER_AGENT", ""), metadata={ "create_advertiser": str(advertiser_user.id) } ) except ImportError: pass # Activity model doesn't exist # Success message and redirect messages.success(request, f'Brand "{brand.name}" has been created successfully!') return redirect("agencies:views_advertiser") except Exception as e: # Handle unexpected errors messages.error(request, f"An error occurred while creating the brand: {str(e)}") return redirect("agencies:add_advertiser") else: # GET request - render the form context = { "agencies": agencies, "brand_categories": BrandCategory.choices, "brand_statuses": BrandStatus.choices, } return render(request, "agencies/advertiser/add.html", context) @login_required def edit_advertiser(request,id_brand=None): print(request.method) if request.method == 'POST': print(id_brand) brand = Brand.objects.get(pk=id_brand) return render(request , 'core/edit_advertiser_id.html', {'brand' : brand}) else: # id_brand = Advertisers.objects.filter(id_user=request.session['id_user']).select_related('id_brand').all() # brands = Brand.objects.filter(pk__in=brands_id) brands_id = Advertisers.objects.filter(id_user=request.user).values_list('id_brand') brands_id = [x[0] for x in brands_id] brands = Brand.objects.filter(pk__in=brands_id).order_by('-id_brand') return render(request,'agencies/advertiser/edit.html',{ 'brands' : brands}) @login_required def update_advertiser(request , id_brand=None): brand = Brand.objects.get(pk=id_brand) advertiser_name = request.POST.get('advertiser_name') category = request.POST.get('category') advertiser_desc = request.POST.get('advertiser_desc') active_0_1 = request.POST.get('active_0_1') now = datetime.datetime.now() try : path = "Advertisers_imgs" now_infile = str(now).replace(" ", "_") now_infile = now_infile.replace(":", "-") filename = advertiser_name+"__"+now_infile+".png" handle_uploaded_file(request.FILES['advertiser_logo'], 'static/'+path, filename) brand.logo = 'static/'+path+'/'+filename except : pass if advertiser_name != '' : brand.brand_name = advertiser_name if category != '': brand.category = category if advertiser_desc != '': brand.description = advertiser_desc if active_0_1 != '': brand.status = active_0_1 activite = 'Edit Advertirer' desc = 'Admin edit advertiser id: ' + str(brand.id_brand) activity = Activity(activity=activite , date=now ,description=desc ) activity.save() brand.save() brands = Advertisers.objects.filter(id_user=request.session['id_user']).filter(status="1").select_related('id_brand').all() return redirect("agencies:views_advertiser") @login_required def disable_advertiser(request , id_brand=None): if request.method == 'POST': now = datetime.datetime.now() activite = 'Disable Advertirer' desc = 'Admin disable advertiser id: ' + str(id_brand) activity = Activity(activity=activite , date=now ,description=desc ) activity.save() brand = Brand.objects.get(pk=id_brand) status = (1+ int(brand.status) ) % 2 brand.status = str(status) brand.save() # brands_id = Advertisers.objects.filter(id_user=request.session['id_user']).select_related('id_brand').all() # brands = Brand.objects.filter(pk__in=brands_id).order_by('-id_brand') brands_id = Advertisers.objects.filter(id_user=request.session['id_user']).values_list('id_brand') brands_id = [x[0] for x in brands_id] brands = Brand.objects.filter(pk__in=brands_id).order_by('-id_brand') return render(request,'core/advertiser/disable.html',{ 'brands' : brands}) @login_required def load_advertisers(request): agency_id = request.GET.get('agency') advertisers = Brand.objects.filter(id_agency=agency_id) return render(request,'core/dropdown_advertisers_campaign.html',{'brands': advertisers}) @login_required def send_advertisers(request): advertiser_name = request.POST.get('name') print(advertiser_name) category = request.POST.get('category') print(category) advertiser_desc = request.POST.get('description') active_0_1 = request.POST.get('status') now = datetime.datetime.now() path = "static/Advertisers_imgs" now_infile = str(now).replace(" ", "_") now_infile = now_infile.replace(":", "-") filename = advertiser_name+"__"+now_infile+".png" # handle_uploaded_file(request.FILES['logo'], path, filename) agency = request.POST.get('agency') agency = Agency.objects.get(id_agency=agency) Brand = Brand( id_agency=agency, brand_name=advertiser_name , description=advertiser_desc, category=category, status=active_0_1, logo=path+'/'+filename ) Brand.save() Advertiser_user = Advertisers( id_user_id=request.session['id_user'], id_brand_id=Brand.id_brand, status=active_0_1, datetime=now ) Advertiser_user.save() return render(request,'core/dropdown_advertisers_campaign.html') @login_required def most_advertisers(request) : brands_id = Advertisers.objects.filter(id_user=request.session['id_user']).values_list('id_brand') brands_id = [x[0] for x in brands_id] brands = Brand.objects.filter(pk__in=brands_id).order_by('-id_brand') totals = [] dic = [] result = [] for i in brands: adspots = Adspots.objects.filter(brand=i) toutal = len(adspots) totals.append(toutal) brand = {'id_brand' : i.id_brand, 'name_brand' : i.brand_name, 'total_adspots' : toutal, 'logo' : i.logo } print(brand) dic.append(brand) print(totals) for i in range(0,4): max_value = max(totals) max_index = totals.index(max_value) a=totals.pop(max_index) result.append(dic[max_index]) print(result) return result @login_required def report_advertiser_backup(request ,id_brand=""): from django.db.models import F, ExpressionWrapper, FloatField from django.db.models.functions import Substr from datetime import timedelta # def my_custom_sql(query, params): # from django.db import connection # with connection.cursor() as cursor: # cursor.execute(query, params) # columns = [col[0] for col in cursor.description] # return [ # dict(zip(columns, row)) # for row in cursor.fetchall() # ] def dictfetchall(cursor): "Return all rows from a cursor as a dict" columns = [col[0] for col in cursor.description] return [ dict(zip(columns, row)) for row in cursor.fetchall() ] def my_custom_sql(query, params): try: with connection.cursor() as cursor: cursor.execute(query, params) rows = cursor.fetchall() return rows except Exception as e: print(f"An error occurred: {e}") return [] if request.method =='POST': from datetime import datetime import pytz # Get the current date and time current_datetime = datetime.now() # Format the date in 'YYYY-MM-DD' format report_date = current_datetime.strftime('%Y-%m-%d') paris_tz = pytz.timezone('Europe/Paris') paris_time = datetime.now(paris_tz) utc_offset = paris_time.utcoffset().total_seconds() / 3600 print(f"Current UTC offset for France (Paris) is GMT+{int(utc_offset)}.") start_day = request.POST.get('start_day').replace("-", "") end_day = request.POST.get('end_day').replace("-", "") selected_brand = request.POST.get('selected_brand') # if utc_offset == 1: # # # ************************************* # # * for when France timezone is GMT+1 * # # ************************************* # # query = """ # SELECT networkname, spotId, airTime, airStatusCode, purcent AS sfr_percentage, purcent*1.25*4500000/17 AS total_volume, Epg.emission_name # FROM Verifs # LEFT JOIN SFR_analytics ON SUBSTRING(Verifs.airTime, 12, 5) = SUBSTRING(SFR_analytics.`minute`, 1, 5) # AND SUBSTRING(Verifs.airTime, 1, 10) = SUBSTRING(SFR_analytics.`day`, 1, 10) # AND SFR_analytics.sfr_channel_name = '2M Maroc' # LEFT JOIN Epg ON (Verifs.airTime < Epg.End_time AND Verifs.airTime > Epg.Start_time) # WHERE Verifs.spotId LIKE %s AND Verifs.airStatusCode = '0001' AND Verifs.broadcastDate > %s AND Verifs.broadcastDate < %s # GROUP BY networkname, spotId, airTime, airStatusCode, purcent, Epg.emission_name ORDER BY airTime # """ # if utc_offset == 2: if utc_offset == 1: # ************************************* # * for when France timezone is GMT+2 * # ************************************* query_old = """ SELECT networkname, spotId, DATE_SUB(Verifs.airTime, INTERVAL 1 HOUR) as airTime, airStatusCode, purcent AS sfr_percentage, purcent*1.25*4500000/17 AS total_volume, Epg.emission_name FROM Verifs LEFT JOIN SFR_analytics ON SUBSTRING(Verifs.airTime, 12, 5) = SUBSTRING(SFR_analytics.`minute`, 1, 5) AND SUBSTRING(Verifs.airTime, 1, 10) = SUBSTRING(SFR_analytics.`day`, 1, 10) AND SFR_analytics.sfr_channel_name = '2M Maroc' LEFT JOIN Epg ON (Verifs.airTime < DATE_ADD(Epg.End_time, INTERVAL 1 HOUR) AND Verifs.airTime > DATE_ADD(Epg.Start_time, INTERVAL 1 HOUR)) WHERE Verifs.spotId LIKE %s AND Verifs.airStatusCode = '0001' AND Verifs.broadcastDate > %s AND Verifs.broadcastDate < %s GROUP BY networkname, spotId, airTime, airStatusCode, purcent, Epg.emission_name ORDER BY airTime """ query_ol3 = """ SELECT networkname, spotId, DATE_SUB(Verifs.airTime, INTERVAL 1 HOUR) as airTime, airStatusCode, purcent AS sfr_percentage, CAST(purcent * 1.25 * 4500000 / 17 AS UNSIGNED) AS total_volume, Epg.emission_name FROM Verifs LEFT JOIN SFR_analytics ON SUBSTRING(Verifs.airTime, 12, 5) = SUBSTRING(SFR_analytics.`minute`, 1, 5) AND SUBSTRING(Verifs.airTime, 1, 10) = SUBSTRING(SFR_analytics.`day`, 1, 10) AND SFR_analytics.sfr_channel_name = '2M Maroc' LEFT JOIN Epg ON (Verifs.airTime < DATE_ADD(Epg.End_time, INTERVAL 1 HOUR) AND Verifs.airTime > DATE_ADD(Epg.Start_time, INTERVAL 1 HOUR)) WHERE Verifs.spotId LIKE %s AND Verifs.airStatusCode = '0001' AND Verifs.broadcastDate > %s AND Verifs.broadcastDate < %s GROUP BY networkname, spotId, airTime, airStatusCode, purcent, Epg.emission_name ORDER BY airTime """ query_old4 = """ SELECT networkname, spotId, DATE_SUB(Verifs.airTime, INTERVAL 1 HOUR) as airTime, airStatusCode, purcent AS sfr_percentage, CAST(purcent * 1.25 * 4500000 / 17 AS UNSIGNED) AS total_volume, Epg.emission_name FROM Verifs LEFT JOIN SFR_analytics ON SUBSTRING(Verifs.airTime, 12, 5) = SUBSTRING(SFR_analytics.`minute`, 1, 5) AND SUBSTRING(Verifs.airTime, 1, 10) = SUBSTRING(SFR_analytics.`day`, 1, 10) AND SFR_analytics.sfr_channel_name = '2M Maroc' Inner JOIN Epg ON (Verifs.airTime < DATE_ADD(Epg.End_time, INTERVAL 2 HOUR) AND Verifs.airTime > DATE_ADD(Epg.Start_time, INTERVAL 2 HOUR)) WHERE Verifs.spotId LIKE %s AND Verifs.airStatusCode = '0001' AND Verifs.broadcastDate > %s AND Verifs.broadcastDate < %s GROUP BY networkname, spotId, airTime, airStatusCode, purcent, Epg.emission_name ORDER BY airTime """ query = """ SELECT networkname, spotId, DATE_SUB(Verifs.airTime, INTERVAL 1 HOUR) as airTime, airStatusCode, purcent AS sfr_percentage, CAST(MAX(purcent) * 1.25 * 4500000 / 17 AS UNSIGNED) AS total_volume, Epg.emission_name FROM Verifs LEFT JOIN ( SELECT sfr_channel_name, `minute`, `day`, MAX(purcent) as purcent FROM SFR_analytics WHERE sfr_channel_name = '2M Maroc' GROUP BY sfr_channel_name, `minute`, `day` ) AS SFR_analytics ON SUBSTRING(Verifs.airTime, 12, 5) = SUBSTRING(SFR_analytics.`minute`, 1, 5) AND SUBSTRING(Verifs.airTime, 1, 10) = SUBSTRING(SFR_analytics.`day`, 1, 10) Inner JOIN Epg ON (Verifs.airTime < DATE_ADD(Epg.End_time, INTERVAL 2 HOUR) AND Verifs.airTime > DATE_ADD(Epg.Start_time, INTERVAL 2 HOUR)) WHERE Verifs.spotId LIKE %s AND Verifs.airStatusCode = '0001' AND Verifs.broadcastDate > %s AND Verifs.broadcastDate < %s GROUP BY networkname, spotId, airTime, airStatusCode, purcent, Epg.emission_name ORDER BY airTime """ # query = """ # SELECT networkname, spotId, airTime, airStatusCode, purcent AS sfr_percentage, purcent*1.25*4500000/17 AS total_volume, Epg.emission_name # FROM Verifs # LEFT JOIN SFR_analytics ON SUBSTRING(Verifs.airTime, 12, 5) = SUBSTRING(SFR_analytics.`minute`, 1, 5) # AND SUBSTRING(Verifs.airTime, 1, 10) = SUBSTRING(SFR_analytics.`day`, 1, 10) # AND SFR_analytics.sfr_channel_name = '2M Maroc' # LEFT JOIN Epg ON (Epg.End_time > DATE_SUB(Verifs.airTime, INTERVAL 1 HOUR) AND Epg.End_time < DATE_SUB(Verifs.airTime, INTERVAL 1 HOUR)) # WHERE Verifs.spotId LIKE %s AND Verifs.airStatusCode = '0001' AND Verifs.broadcastDate > %s AND Verifs.broadcastDate < %s # GROUP BY networkname, spotId, airTime, airStatusCode, purcent, Epg.emission_name ORDER BY airTime # """ # The % signs are part of the parameter, not the SQL string # params = [f"%{selected_brand}%", '0001%', start_day, end_day] from django.db import connection cursor = connection.cursor() data_tuple=(f"%{selected_brand}%", start_day, end_day) cursor.execute(query,data_tuple) # data = cursor.fetchall() total = 0 data = dictfetchall(cursor) processed_data = [] last_date = None for row in data: # Convert airTime string to datetime object to extract the date part only row_date = datetime.strptime(row['airTime'], '%Y-%m-%d %H:%M:%S').date() if last_date and row_date != last_date: # Insert an empty or placeholder row processed_data.append({'networkname': '--', 'spotId': '--', 'airTime': '--', 'total_volume': '--', 'emission_name': '--'}) processed_data.append(row) last_date = row_date for row in data: if row['total_volume'] == None: print(row) else: total += int(row['total_volume']) return render(request,'core/view_report.html', {'data': processed_data, 'total':total, 'selected_brand':selected_brand, 'report_date':report_date }) # result = Verifs.objects.filter(spotId__contains='kia', airStatuscode__startswith='0001', broadcastDate__gt=start_date)\ # .annotate(sfr_percentage=F('purcent'), total_volume=ExpressionWrapper(F('purcent') * 1.25 * 4500000 / 17, output_field=DateTimeField()))\ # .values('networkname', 'spotId', 'airTime', 'airStatusCode', 'sfr_percentage', 'total_volume', 'epg__emission_name')\ # .prefetch_related('sfranalytics_set', 'epg') # # # Apply the JOIN conditions # result = result.filter(sfranalytics__minute__startswith=Substr(F('airTime'), 12, 5), # sfranalytics__day=Substr(F('airTime'), 1, 10), # sfranalytics__sfr_channel_name='2M Maroc', # epg__start_time__lt=ExpressionWrapper(F('airTime') + timedelta(hours=1), output_field=DateTimeField()), # epg__end_time__gt=ExpressionWrapper(F('airTime') + timedelta(hours=1), output_field=DateTimeField())) # # Retrieve the required fields # result = result.values('networkname', 'spotId', 'airTime', 'airStatusCode', 'sfr_percentage', # 'total_volume', 'epg__emission_name') # # # Apply the ordering # result = result.order_by('airTime') # ptint(result) # return render(request,'core/views_advertiser_adspots.html', {'data':{'adspots':adspots, 'brand' : brand , 'channels' : list(channels)}}) else : brands_id = Advertiser.objects.filter(id_user=request.session['id_user']).values_list('id_brand') brands_id = [x[0] for x in brands_id] brands = Brand.objects.filter(pk__in=brands_id, status=1).order_by('-id_brand') dic = [] for i in brands: adspots = Adspots.objects.filter(brand=i) toutal = len(adspots) brand = {'id_brand' : i.id_brand, 'name_brand' : i.brand_name, 'description' : i.description, 'total_adspots' : toutal, 'logo' : i.logo } print(brand) dic.append(brand) return render(request , 'core/advertiser/report.html' , {'brands':dic } ) @login_required def report_advertiser(request, id_brand=""): def dictfetchall(cursor): "Return all rows from a cursor as a dict" columns = [col[0] for col in cursor.description] return [ dict(zip(columns, row)) for row in cursor.fetchall() ] def my_custom_sql(query, params): try: with connection.cursor() as cursor: cursor.execute(query, params) rows = cursor.fetchall() return rows except Exception as e: print(f"An error occurred: {e}") return [] if request.method == 'POST': current_datetime = datetime.now() report_date = current_datetime.strftime('%Y-%m-%d') paris_tz = pytz.timezone('Europe/Paris') paris_time = datetime.now(paris_tz) utc_offset = paris_time.utcoffset().total_seconds() / 3600 print(f"Current UTC offset for France (Paris) is GMT+{int(utc_offset)}.") start_day = request.POST.get('start_day').replace("-", "") end_day = request.POST.get('end_day').replace("-", "") selected_brand = request.POST.get('selected_brand') if utc_offset == 2: # normal one hardcoded for 2M: query_old = """ SELECT networkname, spotId, DATE_SUB(Verifs.airTime, INTERVAL 1 HOUR) as airTime, airStatusCode, purcent AS sfr_percentage, CAST(MAX(purcent) * 1.25 * 4500000 / 17 AS UNSIGNED) AS total_volume, Epg.emission_name FROM Verifs LEFT JOIN ( SELECT sfr_channel_name, `minute`, `day`, MAX(purcent) as purcent FROM SFR_analytics WHERE sfr_channel_name = '2M Maroc' GROUP BY sfr_channel_name, `minute`, `day` ) AS SFR_analytics ON SUBSTRING(Verifs.airTime, 12, 5) = SUBSTRING(SFR_analytics.`minute`, 1, 5) AND SUBSTRING(Verifs.airTime, 1, 10) = SUBSTRING(SFR_analytics.`day`, 1, 10) Inner JOIN Epg ON (Verifs.airTime < DATE_ADD(Epg.End_time, INTERVAL 2 HOUR) AND Verifs.airTime > DATE_ADD(Epg.Start_time, INTERVAL 2 HOUR)) WHERE Verifs.spotId LIKE %s AND Verifs.airStatusCode = '0001' AND Verifs.broadcastDate > %s AND Verifs.broadcastDate < %s GROUP BY networkname, spotId, airTime, airStatusCode, purcent, Epg.emission_name ORDER BY airTime """ query_11 = """ SELECT networkname, spotId, DATE_SUB(Verifs.airTime, INTERVAL 1 HOUR) as airTime, airStatusCode, purcent AS sfr_percentage, CAST(MAX(purcent) * 1.25 * 4500000 / CASE WHEN DATE(Verifs.airTime) < '2024-03-18' THEN 17 ELSE 14 END AS UNSIGNED) AS total_volume, Epg.emission_name FROM Verifs LEFT JOIN ( SELECT sfr_channel_name, `minute`, `day`, MAX(purcent) as purcent FROM SFR_analytics WHERE sfr_channel_name = '2M Maroc' GROUP BY sfr_channel_name, `minute`, `day` ) AS SFR_analytics ON SUBSTRING(Verifs.airTime, 12, 5) = SUBSTRING(SFR_analytics.`minute`, 1, 5) AND SUBSTRING(Verifs.airTime, 1, 10) = SUBSTRING(SFR_analytics.`day`, 1, 10) INNER JOIN Epg ON (Verifs.airTime < DATE_ADD(Epg.End_time, INTERVAL 2 HOUR) AND Verifs.airTime > DATE_ADD(Epg.Start_time, INTERVAL 2 HOUR) AND Epg.verif_channel_id = Verifs.zonename) WHERE Verifs.spotId LIKE %s AND Verifs.airStatusCode = '0001' AND Verifs.broadcastDate > %s AND Verifs.broadcastDate < %s GROUP BY networkname, spotId, airTime, airStatusCode, purcent, Epg.emission_name ORDER BY airTime """ query11 = """ Select MIN(networkname) AS networkname, MIN(networkname) AS networkname, MIN(spotId) AS spotId, MIN(Epg.emission_name) AS emission_name, SUBSTRING(DATE_SUB(Verifs.airTime, INTERVAL 2 HOUR), 1, 19) as airTime, CAST(MAX(purcent) * 1.25 * 4500000 / CASE WHEN DATE(Verifs.broadcastDate) < '20240317' THEN 17 ELSE 14 END AS UNSIGNED) AS total_volume FROM Verifs INNER JOIN SFR_analytics ON SUBSTRING(Verifs.airTime, 12, 5) = SUBSTRING(SFR_analytics.`minute`, 1, 5) AND SUBSTRING(Verifs.airTime, 1, 10) = SUBSTRING(SFR_analytics.`day`, 1, 10) AND SFR_analytics.sfr_channel_name = '2M Maroc' INNER JOIN Epg ON (Verifs.airTime < DATE_ADD(Epg.End_time, INTERVAL 1 HOUR) AND Verifs.airTime > DATE_ADD(Epg.Start_time, INTERVAL 1 HOUR)) WHERE Verifs.spotId LIKE %s AND Verifs.airStatusCode = '0001' AND Verifs.broadcastDate > %s AND Verifs.broadcastDate < %s GROUP BY SUBSTRING(Verifs.airTime, 1, 19), Verifs.broadcastDate ORDER BY airTime; """ #made for Ramadan 2024 query = """ SELECT MIN(Verifs.networkname) AS networkname, MIN(spotId) AS spotId, CASE WHEN DATE(Verifs.airTime) > '2024-03-30' THEN SUBSTRING(Verifs.airTime, 1, 19) ELSE SUBSTRING(DATE_SUB(Verifs.airTime, INTERVAL 1 HOUR), 1, 19) END AS airTime, MIN(Epg.emission_name) AS emission_name, CAST(MAX(purcent) * 1.25 * 4500000 / CASE WHEN DATE(Verifs.broadcastDate) < '2024-03-17' THEN Channels_zone.market_share ELSE Channels_zone.market_share END AS UNSIGNED) AS total_volume FROM Verifs INNER JOIN Channels_zone ON Channels_zone.id_zone_channel = Verifs.zonename Inner JOIN ( SELECT sfr_channel_name, `minute`, `day`, MAX(purcent) AS purcent FROM SFR_analytics INNER JOIN Channels_zone ON Channels_zone.sfr_name = SFR_analytics.sfr_channel_name WHERE sfr_channel_name = Channels_zone.sfr_name GROUP BY sfr_channel_name, `minute`, `day` ) AS SFR_analytics ON ( SUBSTRING(Verifs.airTime, 12, 5) = SUBSTRING(SFR_analytics.`minute`, 1, 5) OR SUBSTRING(Verifs.airTime, 12, 5) = SUBSTRING(ADDTIME(SFR_analytics.`minute`, '00:01:00'), 1, 5) ) AND SUBSTRING(Verifs.airTime, 1, 10) = SUBSTRING(SFR_analytics.`day`, 1, 10) AND Channels_zone.sfr_name = SFR_analytics.sfr_channel_name INNER JOIN Epg ON ( Epg.verif_channel_id = Verifs.networkname AND Verifs.airTime < DATE_ADD(Epg.End_time, INTERVAL 2 HOUR) AND Verifs.airTime > DATE_ADD(Epg.Start_time, INTERVAL 2 HOUR) ) WHERE Verifs.spotId LIKE %s AND Verifs.airStatusCode = '0001' AND Verifs.broadcastDate > %s AND Verifs.broadcastDate < %s GROUP BY CASE WHEN DATE(Verifs.airTime) > '2024-03-30' THEN SUBSTRING(Verifs.airTime, 1, 19) ELSE SUBSTRING(DATE_SUB(Verifs.airTime, INTERVAL 1 HOUR), 1, 19) END, Verifs.broadcastDate, Channels_zone.market_share, SFR_analytics.sfr_channel_name ORDER BY airTime; """ cursor = connection.cursor() selected_brand = selected_brand.capitalize() data_tuple = (f"%{selected_brand}%", start_day, end_day) cursor.execute(query, data_tuple) data = dictfetchall(cursor) processed_data = [] last_date = None total = 0 for row in data: row_date = datetime.strptime(row['airTime'], '%Y-%m-%d %H:%M:%S').date() if last_date and row_date != last_date: processed_data.append({'networkname': '--', 'spotId': '--', 'airTime': '--', 'total_volume': '--', 'emission_name': '--'}) processed_data.append(row) last_date = row_date if row['total_volume'] is not None: total += int(row['total_volume']) return render(request, 'core/view_report.html', {'data': processed_data, 'total': total, 'selected_brand': selected_brand, 'report_date': report_date}) else: brands_id = Advertisers.objects.filter(id_user=request.session['id_user']).values_list('id_brand', flat=True) brands = Brands.objects.filter(pk__in=brands_id, status=1).order_by('-id_brand') dic = [] for brand in brands: adspots = Adspots.objects.filter(brand=brand) total_adspots = len(adspots) brand_info = { 'id_brand': brand.id_brand, 'name_brand': brand.brand_name, 'description': brand.description, 'total_adspots': total_adspots, 'logo': brand.logo } dic.append(brand_info) return render(request, 'core/report_advertiser.html', {'brands': dic}) # ===================================================================== # Brands views # ===================================================================== @login_required def statsbrands(request): from django.db.models import Count ch = Channel.objects.filter(id_user=1) ads = Campaigns.objects.filter(id_user=request.session['id_user']).values('id_brand').annotate(dcount=Count("id_brand")).order_by('-dcount') label = [] data = [] import random chars = '0123456789ABCDEF' color = ['#'+''.join(random.sample(chars,6)) for i in range(len(ads))] for ad in ads : label.append(Brands.objects.get(id_brand=ad['id_brand']).brand_name) data.append(ad['dcount']) brands={ 'label' : label , 'data' : data , 'color' : color } return render(request ,'core/stats_brands.html', { 'brands' : brands} ) def brand_adspot(): from django.db.models import Count ch = Channel.objects.filter(id_user=1) verifs = Verifs.objects.filter(networkname__in = ch , airStatuscode='0001' ).values_list('spotId') verifs = [verif[0] for verif in verifs ] ads = Adspots.objects.filter(name__in=v, channel__in=ch ).values('brand').annotate(dcount=Count("brand")).order_by('-dcount') import random chars = '0123456789ABCDEF' color = ['#'+''.join(random.sample(chars,6)) for i in range(len(ads))] label = [] data = [] for ad in ads : label.append(Brands.objects.get(id_brand=ad['id_brand']).brand_name) data.append(ad['dcount']) # ===================================================================== # New class-based views for Agencies (modeled after Channels CBVs) # ===================================================================== from django.contrib.auth.mixins import LoginRequiredMixin from django.core.paginator import Paginator, PageNotAnInteger, EmptyPage from django.http import HttpRequest, HttpResponse, JsonResponse, Http404 from django.shortcuts import get_object_or_404 from django.urls import reverse class AgencyListView(LoginRequiredMixin, View): """ Agency List View Provides a paginated, filterable list of agencies owned by the current user (or all if the user is a superuser). Mirrors features from channels list view in a lightweight way. Template: agencies/agency/list.html """ http_method_names = ["get"] template_name = "agencies/agency/list.html" def get(self, request: HttpRequest) -> HttpResponse: search = request.GET.get('search', '').strip() status = request.GET.get('status', 'active') # active|deleted|all sort_by = request.GET.get('sort_by', 'name') per_page = int(request.GET.get('per_page', 20)) page = request.GET.get('page', 1) qs = Agency.objects.all() # Scope to owner unless superuser if not request.user.is_superuser: qs = qs.filter(owner=request.user) # Soft-delete status filter if status == 'active': qs = qs.filter(is_deleted=0) elif status == 'deleted': qs = qs.filter(is_deleted=1) if search: qs = qs.filter(name__icontains=search) # Sorting whitelist valid_sorts = ['name', '-name', 'created_at', '-created_at'] qs = qs.order_by(sort_by if sort_by in valid_sorts else 'name') paginator = Paginator(qs, max(10, min(per_page, 100))) try: page_obj = paginator.page(page) except (PageNotAnInteger, EmptyPage): page_obj = paginator.page(1) # Log activity Activity.log_activity( user=request.user, action="views_agencies_cbv", description=f"User {request.user.email if request.user else 'System'} viewed agencies list (CBV)", request=request, ip_address=get_client_ip(request), user_agent=request.META.get("HTTP_USER_AGENT", ""), metadata={ 'search': search, 'status': status, 'sort_by': sort_by, 'page': page, 'per_page': per_page, 'results': paginator.count, } ) # Basic stats to mimic channels list header cards stats = { 'total_agencies': qs.count(), 'active_agencies': qs.filter(is_deleted=0).count(), 'deleted_agencies': qs.filter(is_deleted=1).count(), } context = { 'agencies': page_obj.object_list, 'page_obj': page_obj, 'paginator': paginator, 'is_paginated': paginator.num_pages > 1, 'filter_params': { 'search': search, 'status': status, 'sort_by': sort_by, 'per_page': per_page, }, 'title': 'Agencies', 'active_menu': 'agencies', } # Optional AJAX JSON if request.headers.get('X-Requested-With') == 'XMLHttpRequest': agencies_data = [ { 'id': str(a.id), 'name': a.name, 'description': a.description or '', } for a in page_obj.object_list ] return JsonResponse({ 'success': True, 'agencies': agencies_data, 'total_results': paginator.count, 'current_page': page_obj.number, 'total_pages': paginator.num_pages, }) return render(request, self.template_name, context) class AgencyDetailView(LoginRequiredMixin, View): """Agency Detail View with related brands summary. Template: agencies/agency/detail.html """ http_method_names = ["get"] template_name = "agencies/agency/detail.html" def get(self, request: HttpRequest, pk: str) -> HttpResponse: agency = get_object_or_404(Agency, pk=pk) if not request.user.is_superuser and agency.owner != request.user: raise Http404("Agency not found") brands = Brand.objects.filter(agency=agency).select_related('agency').order_by('name') Activity.log_activity( user=request.user, action="view_agency_detail_cbv", description=f"Viewed agency detail (CBV): {agency.name}", request=request, ip_address=get_client_ip(request), user_agent=request.META.get("HTTP_USER_AGENT", ""), metadata={'agency_id': str(agency.id)} ) context = { 'agency': agency, 'brands': brands, 'title': f'{agency.name} - Details', 'active_menu': 'agencies', } return render(request, self.template_name, context) class AgencyCreateView(LoginRequiredMixin, View): """Create a new Agency. Template: agencies/agency/form.html """ def get(self, request: HttpRequest) -> HttpResponse: return render(request, 'agencies/agency/form.html', { 'form_title': 'Create New Agency', 'submit_text': 'Create Agency', 'is_create': True, }) def post(self, request: HttpRequest) -> HttpResponse: name = request.POST.get('name', '').strip() or request.POST.get('name_agency', '').strip() description = request.POST.get('description', '').strip() or request.POST.get('description_agency', '').strip() if not name: messages.error(request, 'Agency name is required.') return self.get(request) agency = Agency.objects.create( owner=request.user, name=name, description=description, datetime=timezone.now(), ) Activity.log_activity( user=request.user, action="create_agency_cbv", description=f"Created agency (CBV): {agency.name}", request=request, ip_address=get_client_ip(request), user_agent=request.META.get("HTTP_USER_AGENT", ""), metadata={'agency_id': str(agency.id)} ) messages.success(request, f'Agency "{agency.name}" created successfully.') return redirect('agencies:agency_detail', pk=agency.pk) class AgencyUpdateView(LoginRequiredMixin, View): """Update an existing Agency. Template: agencies/agency/form.html """ def get(self, request: HttpRequest, pk: str) -> HttpResponse: agency = get_object_or_404(Agency, pk=pk) if not request.user.is_superuser and agency.owner != request.user: raise Http404("Agency not found") return render(request, 'agencies/agency/form.html', { 'agency': agency, 'form_title': f'Update Agency: {agency.name}', 'submit_text': 'Update Agency', 'is_create': False, }) def post(self, request: HttpRequest, pk: str) -> HttpResponse: agency = get_object_or_404(Agency, pk=pk) if not request.user.is_superuser and agency.owner != request.user: raise Http404("Agency not found") name = (request.POST.get('name') or request.POST.get('name_agency') or '').strip() description = (request.POST.get('description') or request.POST.get('description_agency') or '').strip() if name: agency.name = name if description: agency.description = description agency.save() Activity.log_activity( user=request.user, action="update_agency_cbv", description=f"Updated agency (CBV): {agency.name}", request=request, ip_address=get_client_ip(request), user_agent=request.META.get("HTTP_USER_AGENT", ""), metadata={'agency_id': str(agency.id)} ) messages.success(request, f'Agency "{agency.name}" updated successfully.') return redirect('agencies:agency_detail', pk=agency.pk) class AgencyDeleteView(LoginRequiredMixin, View): """Soft-delete an Agency. Template: agencies/agency/confirm_delete.html """ def get(self, request: HttpRequest, pk: str) -> HttpResponse: agency = get_object_or_404(Agency, pk=pk) if not request.user.is_superuser and agency.owner != request.user: raise Http404("Agency not found") # Provide impact info (brands count) brands_count = Brand.objects.filter(agency=agency).count() return render(request, 'agencies/agency/confirm_delete.html', { 'agency': agency, 'brands_count': brands_count, }) def post(self, request: HttpRequest, pk: str) -> HttpResponse: agency = get_object_or_404(Agency, pk=pk) if not request.user.is_superuser and agency.owner != request.user: raise Http404("Agency not found") # Soft delete semantics consistent with existing FBV agency.datetime = timezone.now() agency.is_deleted = 1 agency.save() Activity.log_activity( user=request.user, action="delete_agency_cbv", description=f"Deleted (soft) agency (CBV): {agency.name}", request=request, ip_address=get_client_ip(request), user_agent=request.META.get("HTTP_USER_AGENT", ""), metadata={'agency_id': str(agency.id)} ) messages.success(request, f'Agency "{agency.name}" deleted successfully.') return redirect('agencies:agency_list')