from apps.core.utils import check_user def sfr_upload(request): if request.method == 'POST': filename = str(request.FILES['sfr_file']).replace(' ','_') print(filename) path = "sfr/user_"+ request.session['id_user'] if not os.path.exists(path): os.makedirs(path) handle_uploaded_file(request.FILES['sfr_file'], path, filename) insert_sfr(path+'/'+filename) return render(request , "core/sfr_upload.html" ) def add_line_to_excel(file_path, new_data): import openpyxl """ Adds a new line at the beginning of an Excel file. Parameters: file_path (str): Path to the Excel file. new_data (list): Data to be inserted in the new first row. """ # Load the workbook and select the active worksheet workbook = openpyxl.load_workbook(file_path) worksheet = workbook.active # Insert a new row at the beginning worksheet.insert_rows(1) # Add new data to the first row for col, data in enumerate(new_data, start=1): worksheet.cell(row=1, column=col, value=data) # Save the workbook workbook.save(file_path) return "Line added successfully." def insert_sfr(file): # import re import pandas as pd from dateutil import parser from django.db import IntegrityError new_data = ['New', 'Row', 'Data', 'Here'] # Replace with your data add_line_to_excel(file, new_data) df = pd.read_excel(file) columns = ["cols_"+ str(i) for i in range(len(df.columns))] df.columns = columns info = df.head(7)[["cols_3","cols_4"]] dic = {} for index, row in info.iterrows(): if str(row["cols_3"]) in ["Date", "Chaîne(s)", "Cible(s)", "KPI"]: dic[str(row["cols_3"]).replace('(s)','').strip()] = row["cols_4"] dic['Chaîne'] = dic['Chaîne'].split(', ') for col in columns[4:]: test = df[13:][["cols_3",col]] name = test.iloc[0][col] for index, row in test.iterrows(): line = 1 if str(row["cols_3"]) != 'Heure': for channel in dic['Chaîne']: line += 1 # try: # Create SFR Analytics Object sfr = Sfr_analytics( sfr_channel_name = channel, cible = dic['Cible'], region = "France", indicateur = dic["KPI"] ) # date = datetime.datetime.strptime(dic['Date'], '%d/%m/%Y') # heure = row["cols_3"] # if isinstance(heure, datetime.time): heure_time = heure heure_time = datetime.datetime.strptime(heure_time.strftime('%H:%M:%S'), '%H:%M:%S').time() elif isinstance(heure, datetime.datetime): heure_time = heure.time() heure_time = datetime.datetime.strptime(heure_time.strftime('%H:%M:%S'), '%H:%M:%S').time() # midnight = datetime.datetime.combine(date.date(), datetime.time(0, 0, 0)) # am_3 = datetime.datetime.combine(date.date(), datetime.time(3, 0, 0)) # combined_heure = datetime.datetime.combine(date, heure_time) # sfr.day = date + timedelta(days=1) if midnight <= combined_heure < am_3 else date # sfr.minute = heure_time # remove percentage sign and convert to float sfr.purcent = float(re.sub('%', '', str(row[col]))) # sfr.save() except IntegrityError: print("This entry already exists in the database.") # def insert_sfr(file): # # # import re # import pandas as pd # from dateutil import parser # from django.db import IntegrityError # new_data = ['New', 'Row', 'Data', 'Here'] # Replace with your data # add_line_to_excel(file, new_data) # df = pd.read_excel(file) # columns = ["cols_"+ str(i) for i in range(len(df.columns))] # df.columns = columns # info = df.head(7)[["cols_3","cols_4"]] # dic = {} # for index, row in info.iterrows(): # if str(row["cols_3"]) in ["Date", "Chaîne(s)", "Cible(s)", "KPI"]: # dic[str(row["cols_3"]).replace('(s)','').strip()] = row["cols_4"] # dic['Chaîne'] = dic['Chaîne'].split(', ') # # day = dic['Date'] # # day = df.iloc[0, 4] # # # # # Save DataFrame to CSV file # # df.to_csv('file.csv', index=False) # # # # # Load the CSV file # # df_csv = pd.read_csv('file.csv') # # # # # Retrieve the value from cell E1 # # # Note that Python uses 0-indexing, so '4' here represents the 5th column (column E in Excel) # # day = df_csv.iat[0, 4] # last_minute = '' # for col in columns[4:] : # test = df[13:][["cols_3",col]] # name = test.iloc[0][col] # for index, row in test.iterrows(): # line = 1 # if str(row["cols_3"]) != 'Heure': # for channel in dic['Chaîne']: # line += 1 # # Create SFR Analytics Object # sfr = Sfr_analytics( # sfr_channel_name = channel, # cible = dic['Cible'], # region = "France", # indicateur = dic["KPI"] # ) # # # day = dic['Date'] # date = datetime.datetime.strptime(day, '%d/%m/%Y') # heureok = str(row["cols_3"]).split('.') # # if index == 879: # # tot = ada # if index>=1274: # date += datetime.timedelta(days=1) # heureok = str(row["cols_3"]).split('.') # # heureok = str(heure[1]).split('.') # # time = heureok[0] # if "03:00:00" in heureok: # # time = str(heureok)+".00" # break # else: # time = heureok[0][11:] # else: # heure = str(row["cols_3"]).split(':') # if heure[1] == last_minute: # if heure[1] == '59': # h2 = int(heure[0]) +1 # heure[0] = str(h2) # heure[1] = '00' # h2 = int(heure[1]) +1 # heure[1] = str(h2) # time = str(int(heure[0])%24)+':'+heure[1] # # if time == "17:25": # # tot = ada # # date += datetime.timedelta(days=1) # # tot +=2 # # heure = str(row["cols_3"]).split(':') # # if int(heure[0]) >= 24 : # # time = str(int(heure[0])%24)+':'+heure[1] # # date += datetime.timedelta(days=1) # # else : # # time = str(int(heure[0]))+':'+heure[1] # sfr.day = date # sfr.minute = time # print(sfr.minute) # last_minute = heure[1] # # remove percentage sign and convert to float # sfr.purcent = float(re.sub('%', '', str(row[col]))) # # sfr.save() # try: # sfr.save() # except IntegrityError: # print("This entry already exists in the database.") def insert_sfr_backup(file): import numpy as np import pandas as pd # df = pd.read_excel(file,encode="utf-8") df = pd.read_excel(file) columns = ["cols_"+ str(i) for i in range(len(df.columns))] df.columns = columns info = df.head(6)[["cols_1","cols_2"]] dic = {} t = list(info) for index, row in info.iterrows(): #print(row["cols_1"]) if str(row["cols_1"]) == "Date" or str(row["cols_1"]) == "Région" or str(row["cols_1"]) == "Cible" or str(row["cols_1"]) == "Indicateur : " : dic[str(row["cols_1"]).replace(':','').strip()]=row["cols_2"] for col in columns[2:] : print(col) test = df[10:][["cols_1",col]] name = test.iloc[0][col] for index, row in test.iterrows(): if str(row["cols_1"]) != 'nan': sfr = Sfr_analytics(sfr_channel_name = name ,cible=dic['Cible'],region= dic["Région"],indicateur=dic["Indicateur"] ) day = str(dic['Date']).split(' ')[1] from dateutil import parser # date = parser.parse(day) date = datetime.datetime.strptime(str(day), '%d/%m/%Y') heure = str(row["cols_1"]).split(':') if int(heure[0]) >= 24 : time = str(int(heure[0])%24)+':'+heure[1] date += datetime.timedelta(days=1) else : time = str(int(heure[0]))+':'+heure[1] sfr.day = date sfr.minute = time sfr.purcent= row[col] sfr.save() def predict_sfr(request): from django.db import connection if request.method == 'POST': channel = Channels.objects.get(pk = request.POST.get('channel_id')) zonename = request.POST.get('zonename') region = ChannelsZone.objects.get(zonename=zonename, id_channel = channel.id_channel) #day = request.POST.get('day') #day = datetime.datetime.strptime(day, '%Y-%m-%d') #dayformat = day.strftime('%Y-%m-%d') val = Sfr_analytics.objects.filter(sfr_channel_name='2M Maroc' , region = region.region).values_list("day","minute","purcent") purcent = Impressions.objects.get(pk='1') labels,predict_val = predict(val) nb = float(purcent.total_users) / float(purcent.market_share_purcent) data = [int(float(x)*nb) for x in predict_val ] channels = Channels.objects.filter(id_user=request.session['id_user']) return render(request, 'core/predict.html', {'labels': labels,'data': data, 'day': '2021-09=19', 'channels': channels}) else: channels = Channels.objects.filter(id_user=request.session['id_user']) return render(request, 'core/predict.html',{'channels': channels}) def upload_bouygues(request): if request.method == 'POST': filename = str(request.FILES['bouygues_file']).replace(' ','_') print(filename) path = "bouygues/user_"+ request.session['id_user'] if not os.path.exists(path): os.makedirs(path) handle_uploaded_file(request.FILES['bouygues_file'], path, filename) insert_bouygues(path+'/'+filename) return render(request , "core/bouygues_upload.html" ) def insert_bouygues(file): import pandas as pd from datetime import datetime ,timedelta df = pd.read_excel(file) date = df.columns[4] date = date.replace('au ','').replace('du ','').strip() date = date.split(' ') start_day = datetime.strptime(date[0],'%d/%m/%Y') end_day = datetime.strptime(date[1],'%d/%m/%Y') periode = pd.date_range(start=start_day,end=end_day) columns = ["cols_"+ str(i) for i in range(len(df.columns))] df.columns = columns cible = df['cols_4'][2] minute =list(df['cols_3'][13:]) for day in periode : for col in df.columns[4:] : channel = df[col][12] val = list(df[col][13:]) for i in range(0,len(minute)): if i < 1260 : bouygue = Bouygues_analytics(channel_name=channel,day=day,cible=cible,minute=minute[i],purcent=val[i]*2) bouygue.save() else : day_2 = day + timedelta(days=1) bouygue = Bouygues_analytics(channel_name=channel,day=day_2,cible=cible,minute=minute[i],purcent=val[i]*2) bouygue.save() def edit_impressions(request): imp = Impressions.objects.get(id='1') if request.method == 'POST': name = request.POST.get('name') users = request.POST.get('users') purcent = request.POST.get('purcent') region = request.POST.get('region') if name != "": imp.tool_name = name if users != "": imp.total_users = int(users) if purcent != "": imp.market_share_purcent = float(purcent) if region != "": imp.region = region imp.save() return render(request,'core/edit_impressions.html' , {'imp':imp}) def chart_test(request): from django.db import connection if request.method == 'POST': channel = Channels.objects.get(pk = request.POST.get('channel_id')) zonename = request.POST.get('zonename') region = ChannelsZone.objects.get(zonename=zonename, id_channel = channel.id_channel) day = request.POST.get('day') day = datetime.datetime.strptime(day, '%m/%d/%Y') dayformat = day.strftime('%Y-%m-%d') val = Sfr_analytics.objects.filter(sfr_channel_name="2M Maroc", region = region.region, day=str(dayformat)).values_list("minute","purcent") purcent = Impressions.objects.get(pk='1') nb = float(purcent.total_users) / float(purcent.market_share_purcent) labels = [x[0] for x in val ] data = [int(float(x[1])*nb) for x in val] channels = Channels.objects.filter(id_user=request.session['id_user']) return render(request, 'core/pie_chart.html', {'labels': labels,'data': data, 'day': dayformat, 'channels': channels}) else: channels = Channels.objects.filter(id_user=request.session['id_user']) return render(request, 'core/pie_chart.html',{'channels': channels}) def verifs(request): from datetime import datetime channels =Channels.objects.filter(id_user = request.session['id_user']).values_list('channel_name') channels = [x[0] for x in channels] # lines = Verifs.objects.filter(airStatuscode="0001") lines = Verifs.objects.all().order_by('-airTime')[:300] data = [] for line in lines: if line.networkname in channels : d = str(line.airTime).split(' ')[0].split('-') d = d[0]+'-'+ d[2] +'-'+ d[1] p ={ 'channel':line.networkname, 'name' : line.spotId, 'day' : d } region = ChannelsZone.objects.get(zonename =line.zonename) p['region'] = region.region min_ss = str(line.airTime).split(' ')[1] min = str(line.airTime).split(' ')[1].split(':') minute = min[0]+':'+min[1]+':00' p['minute'] = minute p['minute_ss'] = min_ss p['color'] = '#00800000' if str(line.airStatuscode) == '0001': p['status'] = 'Aired Successfully' p['color'] = '#2c2c8cb3' try : donnees = Sfr_analytics.objects.filter(day=p['day'],minute=p['minute']) except : donnees = Sfr_analytics.objects.filter(day=str(line.airTime).split(' ')[0],minute=p['minute']) print(donnees) for i in donnees : if p['channel'] in i.sfr_channel_name : purcent = Impressions.objects.get(pk='1') nb = float(i.purcent) * float(purcent.total_users) / float(purcent.market_share_purcent) p['nb_wach'] = nb elif str(line.airStatuscode) == '0008': p['status'] = 'Failed, Adspot cut' p['nb_wach'] = '-' elif str(line.airStatuscode) == '1005': p['status'] = 'Not aired yet' p['nb_wach'] = '-' p['color'] = '#c7c7c7b3' else : p['status'] = 'Failed, Other Reason.' p['nb_wach'] = '-' p['color'] = '#c7c7c7b3' data.append(p) return render(request,'core/verifs.html',{'data':data}) def sfr_channel(request): import datetime channel = request.GET.get('channel') region = request.GET.get("region") day = request.GET.get("day") print(region) channel = Channels.objects.get(id_channel=channel).sfr_channel_name region = ChannelsZone.objects.get(zonename= region).region val = Sfr_analytics.objects.filter(sfr_channel_name=channel, region =region, day=day).values_list("minute","purcent") purcent = Impressions.objects.get(pk='1') nb = float(purcent.total_users) / float(purcent.market_share_purcent) labels = [x[0] for x in val ] data = [int(float(x[1])*nb) for x in val] data = {'data':data , 'label':labels} return JsonResponse(data, safe=True)