import json
from math import exp
import os
import argparse
from django.core.exceptions import ValidationError
from numpy import nan

import logging
import xml.etree.ElementTree as xml
from django.http import HttpResponse, JsonResponse
from django.shortcuts import render
import ftplib
from ftplib import FTP_TLS
from django.db import models
from django.template.loader import render_to_string
import datetime
from datetime import datetime

from numpy.lib.stride_tricks import broadcast_arrays
from .models import *
from django.contrib.sessions.backends.db import SessionStore as DBStore
from django.contrib.sessions.base_session import AbstractBaseSession
from django.contrib.sessions.models import Session
from pprint import pprint
from slugify import slugify
import datetime
from django import template
import time
from lxml import etree
from io import StringIO, BytesIO
from django.http import HttpResponseRedirect
from django.db.models import Max
from django.shortcuts import redirect
import xml.dom.minidom as xml2
from django.db import IntegrityError
from ftplib import error_perm
from pathlib import Path
import threading
import schedule
from django.contrib import messages
from datetime import timedelta

logger = logging.getLogger(__name__)
colors = ['#9DBFF9','#FCCC75','#FF5C5C']

def index(request):
    from threading import Thread
    # pprint(request.session.user)
    #insert_verif = Thread(verifs_timer_insert,request)
    #insert_verif.start()
    request.session['id_user'] = '1' # make is dynamique
    channel = Channels.objects.get(pk = 1)
    day =  '2021-09-06' #make it dynamique than make it fix if data not found
    day = datetime.datetime.strptime(day, '%Y-%m-%d')
    dayformat = day.strftime('%Y-%m-%d')
    #user = 1 ==> make it dynamique
    channels_sfr = list(Channels.objects.filter(id_user='1').values_list("sfr_channel_name",flat=True))
    #region => make it dynamique
    val = Sfr_analytics.objects.filter(sfr_channel_name__in=channels_sfr, region='France' ,day=str(dayformat))
    result = []
    channel_sfr=[]
    purcent = Impressions.objects.get(pk='1') # change the name impressions to SFR_market_share
    for channel in channels_sfr :
        res= val.filter(sfr_channel_name=channel).values_list('minute','purcent')
        if len(res)>0:

            nb  =  float(purcent.total_users) / float(purcent.market_share_purcent)
            labels  = [x[0] for x in res ]
            data  = [int(float(x[1])*nb) for x in res]
            result.append( data)
            channel_sfr.append(channel)
    import random

    color = colors

    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",broadcastDate="20211024")
    # lines  = Verifs.objects.all()
    data2 = []

    for line in lines:
        if line.networkname in channels :
            p ={
                'channel':line.networkname,
                'name' : line.spotId,
                'day' : str(line.airTime).split(' ')[0],
            }

            region = ChannelsZone.objects.get(zonename =line.zonename)
            p['region'] = region.region
            min = str(line.airTime).split(' ')[1].split(':')
            minute = min[0]+':'+min[1]+':00'
            p['minute'] = minute
            p['color'] = '#00800000'
            if  str(line.airStatuscode) == '0001':
                p['status'] = 'Aired Successfully'
                p['color'] = '#2c2c8cb3'

                for i in Sfr_analytics.objects.filter(day=p['day'],minute=p['minute']):
                    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'] = int(nb)
            elif str(line.airStatuscode) == '0008':
                p['status'] = 'Failed, Adspot cut'
                p['nb_wach'] = '-'

            else :
                p['status'] = 'Failed, Other Reason'
                p['nb_wach'] = '-'

            data2.append(p)

    campaigns = len(Campaigns.objects.filter(id_user=request.session['id_user']))
    campaigns_active = len(Campaigns.objects.filter(id_user=request.session['id_user'],pacing=True))
    advertiser = most_advertisers(request)
    bookeds = booked_adbreaks(request)
    agences =active_agency(request)
    playlist = playlists(request)
    activites = Activity.objects.all().order_by('-id_activity')[0:5]
    channels = Channels.objects.filter(id_user=request.session['id_user'])
    #activites = []
    return render(request, "DAIManagementApp/index.html", {'labels': labels,'data': data, 'data2': data2, 'day': dayformat,
                                                            'nb_channels':len(channels) ,'campaigns':campaigns,
                                                            'campaigns_active':campaigns_active , 'advertisers':advertiser,
                                                            'bookeds':bookeds,'agences':agences,'playlists':playlist,
                                                            'activites':activites , "channels":channels , 'result':result ,
                                                            'channels_sfr':channel_sfr,'color':color})

# calculer le nombrer des playlists delivrer et annuler
def playlists(request):
    from datetime import datetime, timedelta
    result = {
        'delivered' :0,
        'cancelled' :0,
        'purcent' : 0
    }
    channels =Channels.objects.filter(id_user = request.session['id_user'])
    yesterday = datetime.now() - timedelta(days=1)
    date = yesterday.strftime('%Y-%m-%d')
    try :
        playlist = list(Playlists.objects.filter(id_channel__in = channels ,broadcastdate=date ))[-1]
        channel = playlist.id_channel.channel_name
    except:
        return result
    day = date.replace('-','')
    verifs = Verifs.objects.filter(networkname=channel,broadcastDate=date).values_list('airStatuscode')
    verifs = [i[0] for i in verifs ]
    if len(verifs) == 0:
        return result
    for i in verifs :
        if i =='0001':
            result['delivered'] +=1
        else:
            result['cancelled'] +=1
    purcent  = result['delivered']*100/len(verifs)
    result['purcent'] = round(purcent,2)
    return result

def booked_adbreaks(request):
        channels =Channels.objects.filter(id_user = request.session['id_user']).values_list('id_channel','channel_name')
        result = []
        for channel in channels :
            r = {}

            adspot = Adspots.objects.filter(id_channel = channel[0])
            booked = Campaigns.objects.filter(id_adpost__in=adspot , booked=True)
            avail = AdspotsInAvail.objects.filter(id_adspot__in = adspot)
            r['channel'] = channel[1]
            r['booked'] = len(booked)
            r['avail'] = len(avail)
            if len(booked) > 0 :
                result.append(r)
        return result
# les campaigns qui sont termine  ou en cours
def active_agency(request):
    agences = Agency.objects.filter(id_user=request.session['id_user']).values_list('id_agency','name','datetime')
    result = []
    for agence in agences:
        r={}
        r['name'] = agence[1]
        brands = Brands.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

def most_advertisers(request) :

    brands_id = UsersAdvertisers.objects.filter(id_user=request.session['id_user']).values_list('id_brand')
    brands_id = [x[0] for x in brands_id]
    brands = Brands.objects.filter(pk__in=brands_id).order_by('-id_brand')
    totals = []
    dic = []
    result = []
    for i in brands:
        adspots = Adspots.objects.filter(id_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


def login(request):
    return render(request, "DAIManagementApp/login.html")

def test_options(request):
    if request.method =='POST':
        options = request.POST.get('optionsname')
        print(options)
    return render(request, "DAIManagementApp/test_options.html")

def logout(request):
    #The request.session['islogged'] or['id_user'] raise KeyError if the given key isn�t already in the session.
    try:
        del request.session['islogged']
        del request.session['id_user']
    except KeyError:
        pass
    return render(request, "DAIManagementApp/login.html")

def dologin(request):
    username = request.POST.get('username')
    password = request.POST.get('password')
    try:
        data=Users.objects.filter(userlogin=username).get(userpass=password)
    except Users.DoesNotExist:
        messages.error(request, 'Someting Wrong.')
        return render(request,'DAIManagementApp/login.html')
    if data:
        request.session['islogged'] = 'yes'
        request.session['id_user'] = str(data.id_user)
        return redirect('/DAIManagement/')
    else:
        messages.error(request, 'Error detected.')


def handle_uploaded_file(file, path, filename):
    with open(path+"/"+filename, 'wb+') as destination:
        for chunk in file.chunks():
            destination.write(chunk)


# Generating XML file from the Database
def GenerateXMLfromDatabase(day, channel_id, id_zonename, version,draft_version='0'):
    #day is 2021-10-25
    # getting the channel selected in form
    channel = Channels.objects.get(id_channel=channel_id)
    channel_zone = ChannelsZone.objects.get(id_zone_channel=id_zonename)

    # day format will be YYYY-MM-DD, so to get the other format YYYYMMDD we'll transform it
    datetimeobject = datetime.datetime.strptime(day, '%Y-%m-%d')
    dayformat = datetimeobject.strftime('%Y%m%d')
    dayformat_string = str(dayformat)

    # finding the playlist based on Channel and Daytime and Version
    # max_version_draft = Playlists.objects.filter(broadcastdate=str(day)).aggregate(Max('draft_version')).get('draft_version__max')
    # new_version_draft = max_version_draft
    playlist = Playlists.objects.get(id_channel_id=int(channel_id), version=version, broadcastdate=str(day), id_zone_channel=channel_zone.id_zone_channel,is_draft='0',draft_version=draft_version)

    # insertion of Schedule tag data in the xml file
    root = xml.Element('Schedule')
    root.set('xmlns', 'http://www.scte.org/schemas/118-3/201X')
    root.set('broadcastDate', dayformat_string)
    root.set('begDateTime', day + 'T00:01:00+00:00')
    root.set('endDateTime', day + 'T23:59:59+00:00')
    root.set('networkName', channel.networkname)
    root.set('zoneName', channel_zone.zonename)
    root.set('revision', version)
    root.set('level', '0')
    root.set('schemaVersion', 'http://www.w3.org/2001/XMLSchema')

    # finding windows linked to this playlist
    windows = Windows.objects.filter(id_playlist=playlist.id_playlist)

    windows_array = {}
    win_i = 0
    for window in windows:
        # transforming window start from datetime to string
        windowStartxml = str(window.window_start).replace(' ', 'T')
        windowStartxml = windowStartxml + '+00:00'
        # using windows_array to insert as many windows as possible without having issues with the names of windows to append to xml
        windows_array[win_i] = xml.Element('Window')  # we can have more than one window
        windows_array[win_i].set('windowStart', windowStartxml)
        windows_array[win_i].set('windowDuration', window.window_duration)
        root.append(windows_array[win_i])
        # finding avails linked to this window
        avails = Avails.objects.filter(id_window=window.id_window)
        avails_array = {}
        av_i = 0
        for avail in avails:
            # transforming window start from datetime to string
            availStartxml = str(avail.avail_start).replace(' ', 'T')
            availStartxml =  availStartxml + '+00:00'
            # using avails_array to insert as many avails as possible without having issues with the names of avails to append to xml
            avails_array[av_i] = xml.SubElement(windows_array[win_i], 'Avail')
            avails_array[av_i].set('availStart', availStartxml)
            avails_array[av_i].set('availInWindow', avail.availinwindow)
            avails_array[av_i].set('availNum', '0')
            # finding adspots linked to this avail
            adspotsinAV = AdspotsInAvail.objects.filter(id_avail=avail.id_avail).select_related('id_adspot')
            adspots_array = {}
            ads_i = 0
            for adspot in adspotsinAV:

                adspot_duration = time.strftime('%H%M%S00', time.gmtime(int(adspot.id_adspot.duration)))
                adspots_array[ads_i] = xml.SubElement(avails_array[av_i], 'Spot')
                adspots_array[ads_i].set('eventType', 'LOI')
                adspots_array[ads_i].set('trafficId', str(adspot.trafficid))
                adspots_array[ads_i].set('positionInAvail', str(adspot.positioninavail))
                # we will add a function that will convert seconds to hhmmssmm there are many ones on stackoverflow, let's now suppose that the duration of the spot is less than 60 seconds just to test
                adspots_array[ads_i].set('length', adspot_duration)
                adspots_array[ads_i].set('spotId', adspot.id_adspot.filename)
                adspots_array[ads_i].set('adId', adspot.id_adspot.duration + 'sec')
                adspots_array[ads_i].set('schedSource', 'Local')
            ads_i += 1
        av_i += 1
    win_i += 1
    tree = xml.ElementTree(root)
    print(dayformat_string)
    print(channel_zone.zonename)
    print(channel.channel_name)
    print(playlist.version)
    fileName = dayformat_string + "-" + channel_zone.zonename + "-" + channel.channel_name + "-" + playlist.version + ".sch"
    print(fileName)
    path = "files/DAI-Management"
    if not os.path.exists(path):
        os.makedirs(path)
    with open(fileName, "wb") as files:
        tree.write(files, encoding='utf-8', xml_declaration=True)
    return fileName


def testGenerateFromDatabase(request):
    return HttpResponse(GenerateXMLfromDatabase("2021-08-16", "2M", "1"))


def getfile(filename , mode ):
    for path, subdirs, files in os.walk('/'):
        for name in files:
            if filename == name :
                file = os.path.join(path, name)
                f = open(file , mode)
                return f


def uploadFTP2(host, user, password, filename, path_inftp):

    ftp = ftplib.FTP(host, user, password)
    ftp.encoding = "utf-8"
    ftp.cwd(path_inftp)

    for path, subdirs, files in os.walk(os.getcwd()):
        for name in files:
            if filename == name :
                file = os.path.join(path, name)
                print(file)
                with open(file, "rb") as f:
                    ftp.storbinary(f"STOR {filename}", f)
    ftp.quit()

def downloadFTP(host, user, password, filepath_inftp, file_inftp,  localpath):

    ftp = ftplib.FTP(host, user, password)
    ftp.encoding = "utf-8"
    ftp.cwd(filepath_inftp)
    filename = file_inftp
    # localfile = localpath+'/'+filename
    localfile = localpath+'/'+filename
    print(localfile)

    try:
        with open(localfile, "wb") as file:
            ftp.retrbinary(f"RETR {filename}", file.write)
    except error_perm:
        print('ERR', localfile)
        os.unlink(localfile)

    #
    # with open(localfile, "wb") as file:
    #     ftp.retrbinary(f"RETR {filename}", file.write)

    ftp.quit()



def test_download(request):
    return HttpResponse(downloadFTP("uk06.tmd.cloud", "testftp@epgsano.com", "I?#=s3FfnSu_", "/2M/schedules/",  "test.txt" , "/var/www/html/DAI-Management/DAIManagement/FTP_files/"))

def uploadFTP(host, port, user, password, filename, path_inftp):

    ftp = ftplib.FTP(host, user, password)
    # ftp.encoding = "utf-8"
    # Enter File Name with Extension
    ftp.cwd(path_inftp)
    module_dir = os.path.dirname(__file__)  # get current directory
    path = "files/DAI-Management"


    # Read file in binary mode
    with open(filename, "rb") as file:
        # Command for Uploading the file "STOR filename"
        ftp.storbinary(f"STOR {filename}", file)
    ftp.quit()


def saveXML2db(request):
    path = "files/results/20210906-2005-00001.xml"
    doc = xml2.parse(path)
    networkname = doc.firstChild.getAttribute("networkName")
    zonename = doc.firstChild.getAttribute("zoneName")
    broadcastdate = doc.firstChild.getAttribute("broadcastDate")
    verComplete = doc.firstChild.getAttribute("verComplete")


    Spots = doc.getElementsByTagName("Spot")
    results = []
    for spot in Spots:
        trafficId = spot.getAttribute("trafficId")
        spotId  = spot.getAttribute("spotId")

        airTime = spot.getAttribute("airTime")
        newAirTime = airTime.replace("T", " ")
        newAirTime2 = newAirTime.replace("+02:00", "")

        airLength = spot.getAttribute("airLength")
        airStatusCode = spot.getAttribute("airStatusCode")
        version = spot.getAttribute("revision")
        try:
            Verifs.objects.update_or_create(networkname=networkname, zonename=zonename, broadcastDate=broadcastdate, trafficId=trafficId, spotId=spotId, airTime=newAirTime2, airLength=airLength, airStatuscode=airStatusCode, revision=version,  vercomplete = verComplete)
        except IntegrityError as e:
            insertion = Verifs.objects.filter(networkname=networkname, zonename=zonename, broadcastDate=broadcastdate, trafficId=trafficId, spotId=spotId, airTime=newAirTime2, airLength=airLength, airStatuscode=airStatusCode, revision=version).update(vercomplete = verComplete)

        # Verifs.objects.update_or_create(networkname=networkname, zonename=zonename, broadcastDate=broadcastdate, trafficId=trafficId, spotId=spotId, airTime=newAirTime2, airLength=airLength, airStatuscode=airStatusCode, revision=version,  vercomplete = verComplete)
        #verifs.save()
    return HttpResponse("data has been inserted")





def test_upload(request):
    return HttpResponse(uploadFTP2("uk06.tmd.cloud", "testftp@epgsano.com", "I?#=s3FfnSu_", "/files/DAI-Management/20210815-2005-2M-1.sch" , "2M/schedules/France"))

def views_advertiser(request ,id_brand=""):
    if id_brand != "":
        brand =  Brands.objects.get(pk=id_brand)
        channels = Adspots.objects.filter(id_brand=id_brand).values_list('id_channel').distinct()
        channels =  [ i[0]  for i in list(channels)]
        channels = Channels.objects.filter(pk__in = channels)
        adspots = Adspots.objects.filter(id_brand=id_brand).order_by('id_channel')

        return render(request,'DAIManagementApp/views_advertiser_adspots.html', {'data':{'adspots':adspots, 'brand' : brand , 'channels' : list(channels)}})
    else :

        brands_id = UsersAdvertisers.objects.filter(id_user=request.session['id_user']).values_list('id_brand')
        brands_id = [x[0] for x in brands_id]
        brands = Brands.objects.filter(pk__in=brands_id).order_by('-id_brand')

        dic = []
        for i in brands:

            adspots = Adspots.objects.filter(id_brand=i)
            toutal = len(adspots)
            brand = {'id_brand' : i.id_brand,
                    'name_brand' : i.brand_name,
                    'total_adspots' : toutal,
                    'logo' : i.logo }
            print(brand)
            dic.append(brand)


        return render(request , 'DAIManagementApp/views_advertiser.html' , {'brands':dic } )

def add_advertiser(request):
    if request.method =='POST':
        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()
        path = "static/Advertisers_imgs"
        now_infile = str(now).replace(" ", "_")
        now_infile = now_infile.replace(":", "-")
        filename = advertiser_name+"__"+now_infile+".png"
        try :
            handle_uploaded_file(request.FILES['advertiser_logo'], path, filename)
        except:
            import shutil
            shutil.move('logo.png',path+'/'+filename)
        Brand = Brands(brand_name=advertiser_name ,description=advertiser_desc, category=category, status=active_0_1, logo=path+'/'+filename)
        activite ='Add Advertirer'
        desc = 'Admin Add advertiser  id: ' + str(Brand.id_brand)
        activity = Activity(activity=activite , date=now ,description=desc )
        activity.save()
        Brand.save()
        Advertiser_user = UsersAdvertisers(id_user_id=request.session['id_user'], id_brand_id=Brand.id_brand, status=active_0_1, datetime=now)
        Advertiser_user.save()
        return redirect('/DAIManagement/views_advertiser/')

    else:
        return render(request, "DAIManagementApp/add_advertiser.html")

def edit_advertiser(request,id_brands="1"):
    print(request.method)
    if request.method == 'POST':
        print(id_brands)
        brand = Brands.objects.get(pk=id_brands)
        return render(request , 'DAIManagementApp/edit_advertiser_id.html', {'brand' : brand})
    else:
        # brands_id = UsersAdvertisers.objects.filter(id_user=request.session['id_user']).select_related('id_brand').all()
        # brands = Brands.objects.filter(pk__in=brands_id)
        brands_id = UsersAdvertisers.objects.filter(id_user=request.session['id_user']).values_list('id_brand')
        brands_id = [x[0] for x in brands_id]
        brands = Brands.objects.filter(pk__in=brands_id).order_by('-id_brand')
        return render(request,'DAIManagementApp/edit_advertiser.html',{ 'brands' : brands})

def update_advertiser(request , id_brand =""):
    brand = Brands.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 = UsersAdvertisers.objects.filter(id_user=request.session['id_user']).filter(status="1").select_related('id_brand').all()
    return redirect('/DAIManagement/views_advertiser/')


def disable(request , id_brand=""):

    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 = Brands.objects.get(pk=id_brand)
        status = (1+ int(brand.status) ) % 2
        brand.status = str(status)
        brand.save()

    # brands_id = UsersAdvertisers.objects.filter(id_user=request.session['id_user']).select_related('id_brand').all()
    # brands = Brands.objects.filter(pk__in=brands_id).order_by('-id_brand')

    brands_id = UsersAdvertisers.objects.filter(id_user=request.session['id_user']).values_list('id_brand')
    brands_id = [x[0] for x in brands_id]
    brands = Brands.objects.filter(pk__in=brands_id).order_by('-id_brand')

    return render(request,'DAIManagementApp/disable_advertiser.html',{ 'brands' : brands})



def add_creative(request):
    if request.method =='POST':
        # channel_id = request.POST.get('channel_id')



        advertiser_id = request.POST.get('advertiser_id')
        adspot_name = request.POST.get('adspot_name')
        adspot_duration = request.POST.get('adspot_duration')
        active_0_1 = request.POST.get('active_0_1')
        now = datetime.datetime.now()
        now_infile = str(now).replace(" ", "_")
        now_infile = now_infile.replace(":", "-")


        channel_list = request.POST.getlist('channel_id')
        if channel_list != None:
            for channel in channel_list:
                path = "adspots/user_"+str(request.session['id_user'])+"/ch_"+str(channel)
                if not os.path.exists("static/"+path):
                    os.makedirs("static/"+path)

                new_now_infile = now_infile.replace(".","-")
                filename = request.FILES['adspot_file'].name
                # filename = "ch_"+str(channel)+"__"+slugify(adspot_name)+"__"+str(adspot_duration)+"sec__"+new_now_infile+".ts"
                handle_uploaded_file(request.FILES['adspot_file'], "static/"+path, filename)
                creative = Adspots(id_channel_id=channel ,id_brand_id=advertiser_id, adspot_name=adspot_name, duration=adspot_duration, status=active_0_1, original_filepath=path+'/'+filename ,  datetime=now,filename=filename)
                creative.save()

                channel_name = Channels.objects.get(pk=channel)
                DST_FOLDER = channel_name.channel_name +'/'+'spots'
                SRC_FILEPATH = "static/"+path+'/'+ filename
                send_adspot(DST_FOLDER,SRC_FILEPATH)

        activite = 'Add Creative'
        desc = 'Admin Add creative  id: ' + str(creative.id_brand)
        activity = Activity(activity=activite , date=now ,description=desc )
        activity.save()

        return redirect("/DAIManagement/edit_creative/")
    else:
        channels=Channels.objects.filter(id_user=request.session['id_user'])
        advertisers = UsersAdvertisers.objects.filter(id_user=request.session['id_user']).filter(status="1").select_related('id_brand').all()
        return render(request, "DAIManagementApp/add_creative.html" ,{'data':{'channels':channels, 'advertisers':advertisers}})


def edit_creative(request , id_adpost="1"):
    if request.method =='POST':
        adpost = Adspots.objects.get(pk=id_adpost)

        channels=Channels.objects.filter(id_user=request.session['id_user']).order_by('-id_channel')
        advertisers = UsersAdvertisers.objects.filter(id_user=request.session['id_user']).filter(status="1").select_related('id_brand').all().order_by('-id_user_advertiser')

        adspost = {'adspost' : adpost,
                    'channels': channels,
                    'advertisers': advertisers}

        return render(request, "DAIManagementApp/edit_creative_id.html", adspost)
    else :
        print(request.method)
        id_session = str(request.session['id_user'])
        channels = Channels.objects.filter(id_user=id_session).order_by('-id_channel').values_list('id_channel',flat=True)
        brands = UsersAdvertisers.objects.filter(id_user=id_session).order_by('-id_user_advertiser').values_list('id_brand',flat=True)
        adspots = Adspots.objects.filter(id_channel__in=channels).filter(id_brand__in=brands).order_by('-id_adpost')
        data = {'adspots': adspots}
        return render(request , "DAIManagementApp/edit_creative.html", data)

def update(request , id_adpost  ):
    if request.method == "POST":
        creative = Adspots.objects.get(pk=id_adpost)

        channel_id = request.POST.get('channel_id')
        advertiser_id = request.POST.get('advertiser_id')
        adspot_name = request.POST.get('adspot_name')
        adspot_duration = request.POST.get('adspot_duration')
        active_0_1 = request.POST.get('active_0_1')
        now = datetime.datetime.now()

        now_infile = str(now).replace(" ", "_")
        now_infile = now_infile.replace(":", "-")
        path = "adspots/user_"+str(request.session['id_user'])+"/ch_"+str(channel_id)
        filename = "ch_"+str(channel_id)+"__"+slugify(adspot_name)+"__"+str(adspot_duration)+"sec__"+now_infile+".ts"
        filename_m3u8 = "ch_"+str(channel_id)+"__"+adspot_name+"__"+str(adspot_duration)+"sec__"+now_infile+".m3u8"
        if not os.path.exists("static/"+path):
            print("path not  exist")
            os.makedirs("static/"+path)
        else :
            print("path exist ")
        print(str(request))
        print("static/"+str(creative.original_filepath) + "  test file " )


        try :
            handle_uploaded_file(request.FILES['adspot_file'], "static/"+path, filename)
            f = open("static/"+path+"/"+filename_m3u8, "a")
            f.write("#EXTM3U \n")
            f.write("#EXT-X-VERSION:3 \n")
            f.write("#EXT-X-MEDIA-SEQUENCE:0 \n")
            f.write("#EXTINF:"+adspot_duration+", \n")
            f.write(filename)
            f.close()
        except :
            import shutil
            if os.path.exists("static/"+ creative.original_filepath):
                shutil.move("static/"+ creative.original_filepath, "static/"+path+"/"+filename)

        creative.original_filepath = path+"/"+filename

        channel_name = Channels.objects.get(pk=channel_id)
        DST_FOLDER = channel_name.channel_name +'/'+'spots'
        SRC_FILEPATH = "static/"+path+'/'+ filename
        send_adspot(DST_FOLDER,SRC_FILEPATH)

        # if creative.id_channel.id_channel != channel_id:
        #     creative.id_channel = Channels.objects.get(pk=channel_id)
        # elif creative.id_brand.id_brand != int(advertiser_id):
        #     creative.id_brand == Brands.objects.get(pk=advertiser_id)
        # elif creative.adspot_name!=adspot_name:
        #     print("inside if")
        #     creative.adspot_namea=adspot_name
        #
        # elif bool(adspot_duration) and creative.duration!=adspot_duration :
        #     creative.duration=adspot_duration
        # elif creative.status!=active_0_1:
        #     creative.status=active_0_1

        creative.id_channel = Channels.objects.get(pk=channel_id)
        creative.id_brand == Brands.objects.get(pk=advertiser_id)
        creative.adspot_name=adspot_name
        creative.duration=adspot_duration
        creative.status=active_0_1
        creative.datetime=now

        activite = 'Edit Creative'
        desc = 'Admin Edit creative  id: ' + str(creative.id_brand)
        activity = Activity(activity=activite , date=now ,description=desc )
        activity.save()

        creative.save()
        return redirect("/DAIManagement/edit_creative/")

def views_creative(request):
    adspots = Adspots.objects.all().order_by('-id_post')
    return render(request , 'DAIManagementApp/views_creative.html',{'adspots':adspots})

# def new_playlist(request):
#     if request.method == 'POST':
#         useraccess = Useraccess.objects.get(id_user=request.session['id_user'])
#         channel_id = request.POST.get('channel_id')
#         channeldata = Channels.objects.get(id_channel=channel_id)
#         zonename = request.POST.get('zonename')
#         daydate = request.POST.get('day')
#         number_of_windows = request.POST.get('numofwin')
#         channel_zone = ChannelsZone.objects.get(id_channel=channel_id, zonename=zonename)
#
#         daydate = datetime.datetime.strptime(str(daydate), '%m/%d/%Y')
#         daydate = daydate.strftime('%Y-%m-%d')
#         start_date = str(daydate) + "T00:01:00+00:00"
#         end_date = str(daydate) + "T23:59:00+00:00"
#         now = datetime.datetime.now()
#         Playlist = Playlists(id_channel_id=channel_id ,version="1", broadcastdate=str(daydate), start_date=start_date, end_date=end_date, creation_datetime=now, id_zone_channel_id=channel_zone.id_zone_channel)
#         Playlist.save()
#         traffic = 0
#         record_inserted = 0
#         for i in range(int(number_of_windows)):
#             if request.POST.get('numofavails['+str(i)+']'):
#                 numofavails = request.POST.get('numofavails['+str(i)+']')
#                 window_start = request.POST.get('windowstart['+str(i)+']')
#                 window_start = daydate+' '+window_start+':00'
#                 window_end = request.POST.get('windowend['+str(i)+']')
#                 window_end = daydate+' '+window_end+':00'
#                 FMT = '%Y-%m-%d %H:%M:%S'
#                 window_duration = datetime.datetime.strptime(window_end, FMT) - datetime.datetime.strptime(window_start, FMT)
#                 window_duration = datetime.datetime.strptime(str(window_duration), '%H:%M:%S')
#                 window_duration = window_duration.strftime('%H%M%S00')
#                 Window = Windows(id_playlist_id=Playlist.id_playlist, window_start=window_start, window_end=window_end, window_duration=window_duration )
#                 Window.save()
#
#                 for j in range(int(numofavails)):
#                     if request.POST.get('availstart['+str(i)+']['+str(j)+']'):
#                         av_start = request.POST.get('availstart['+str(i)+']['+str(j)+']')
#                         av_start = daydate+' '+av_start+':00'
#                         number_of_ads = request.POST.get('numofads['+str(i)+']['+str(j)+']')
#                         Avail = Avails(id_window_id=Window.id_window, avail_start=av_start, availinwindow=str(j+1), datetime=now )
#                         Avail.save()
#
#                         for k in range(int(number_of_ads)):
#                             if request.POST.get('ad['+str(i)+']['+str(j)+']['+str(k)+']'):
#                                 adspot = request.POST.get('ad['+str(i)+']['+str(j)+']['+str(k)+']')
#                                 traffic +=1
#                                 AdspotsInAv = AdspotsInAvail(id_avail_id=Avail.id_avail, id_adspot_id=adspot, positioninavail=str(k+1), trafficid=traffic)
#                                 AdspotsInAv.save()
#                                 record_inserted = 1
#
#         xmlfilename = GenerateXMLfromDatabase(daydate, channel_id, channel_zone.id_zone_channel, '1' )
#         path_inftp = channeldata.ftp_channel_name+'/schedules/'+channel_zone.region
#         uploadFTP(useraccess.ftp_server, useraccess.ftp_port, useraccess.ftp_user, useraccess.ftp_password, xmlfilename , path_inftp)
#         channels = Channels.objects.filter(id_user=request.session['id_user'])
#         return render(request, "DAIManagementApp/new_playlist.html",{'data': {'channels': channels},'record_inserted': record_inserted})
#     else:
#         channels = Channels.objects.filter(id_user=request.session['id_user'])
#         return render(request, "DAIManagementApp/new_playlist.html",{'data': {'channels': channels}})


def new_playlist_old(request):
    if request.method == 'POST':

        useraccess = Useraccess.objects.get(id_user=request.session['id_user'])
        channel_id = request.POST.get('channel_id')
        channeldata = Channels.objects.get(id_channel=channel_id)
        zonename = request.POST.get('zonename')
        daydate = request.POST.get('day')
        number_of_windows = request.POST.get('numofwin')
        channel_zone = ChannelsZone.objects.get(id_channel=channel_id, zonename=zonename)

        daydate = datetime.datetime.strptime(str(daydate), '%m/%d/%Y')
        daydate = daydate.strftime('%Y-%m-%d')
        start_date = str(daydate) + "T00:01:00+00:00"
        end_date = str(daydate) + "T23:59:00+00:00"
        now = datetime.datetime.now()
        Playlist = Playlists(id_channel_id=channel_id ,version="1", broadcastdate=str(daydate), start_date=start_date, end_date=end_date, creation_datetime=now, id_zone_channel_id=channel_zone.id_zone_channel)
        Playlist.save()
        traffic = 0
        # starts =[]
        for i in range(int(number_of_windows)):
            number_of_ads = request.POST.get('numofads['+str(i)+']')
            if request.POST.get('numofads['+str(i)+']'):
                number_of_ads = request.POST.get('numofads['+str(i)+']')
                window_start = request.POST.get('windowstart['+str(i)+']')
                window_start = daydate+' '+window_start+':00'
                window_end = request.POST.get('windowend['+str(i)+']')
                window_end = daydate+' '+window_end+':00'
                FMT = '%Y-%m-%d %H:%M:%S'
                window_duration = datetime.datetime.strptime(window_end, FMT) - datetime.datetime.strptime(window_start, FMT)
                window_duration = datetime.datetime.strptime(str(window_duration), '%H:%M:%S')
                window_duration = window_duration.strftime('%H%M%S00')

                Window = Windows(id_playlist_id=Playlist.id_playlist, window_start=window_start, window_end=window_end, window_duration=window_duration )
                Window.save()
                Avail = Avails(id_window_id=Window.id_window, avail_start=window_start, availinwindow='1', datetime=now )
                Avail.save()
                for j in range(int(number_of_ads)):
                    if request.POST.get('ad['+str(i)+']['+str(j)+']'):
                        adspot = request.POST.get('ad['+str(i)+']['+str(j)+']')
                        traffic +=1
                        AdspotsInAv = AdspotsInAvail(id_avail_id=Avail.id_avail, id_adspot_id=adspot, positioninavail=str(j+1), trafficid=traffic)
                        AdspotsInAv.save()
        xmlfilename = GenerateXMLfromDatabase(daydate, channel_id, channel_zone.id_zone_channel, '1' )
        path_inftp = channeldata.ftp_channel_name+'/schedules/'+channel_zone.region
        uploadFTP(useraccess.ftp_server, useraccess.ftp_port, useraccess.ftp_user, useraccess.ftp_password, xmlfilename , path_inftp)
        # channels = Channels.objects.filter(id_user=request.session['id_user'])
        return render(request, "DAIManagementApp/new_playlist.html")
    else:
        channels = Channels.objects.filter(id_user=request.session['id_user'])
        return render(request, "DAIManagementApp/new_playlist.html",{'data': {'channels': channels}})


def load_adspots(request):
    channel_id = request.GET.get('channel')
    adspots = Adspots.objects.filter(id_channel=channel_id).select_related('id_brand').all()

    return render(request, 'DAIManagementApp/dropdown_adspots.html', {'adspots': adspots})

def load_regions(request):
    channel_id = request.GET.get('channel')
    regions = ChannelsZone.objects.filter(id_channel=channel_id)
    return render(request,'DAIManagementApp/dropdown_region.html',{'regions': regions})

def load_playlistdays(request):
    channel_id = request.GET.get('channel')
    region_id = request.GET.get('region_id')

    # playlist = Playlists.objects.filter(id_channel=channel_id)
    return render(request,'DAIManagementApp/dropdown_region.html',{'regions': regions})



def edit_playlist_old(request,id_playlist="1"):
    if request.method == 'POST':
        playlist = Playlists.objects.get(pk=id_playlist)
        channels = Channels.objects.filter(id_user=request.session['id_user'])
        region = ChannelsZone.objects.get(id_zone_channel=playlist.id_zone_channel.id_zone_channel)
        d = playlist.broadcastdate

        windows = Windows.objects.filter(id_playlist=playlist.id_playlist)

        win = Windows.objects.filter(id_playlist=playlist.id_playlist).values_list('id_window', flat=True)
        avails = Avails.objects.filter(id_window__in=win).values_list('id_avail', flat=True)
        av = Avails.objects.filter(id_window__in=win);
        adspots = AdspotsInAvail.objects.filter(id_avail__in=avails)

        data = {'playlist': playlist, 'channels': channels,'region':region,'d':d,'windows':windows,'adspots':adspots, 'av':av }
        return render(request, "DAIManagementApp/edit_playlist_id.html", data)
    else:
        id_session = str(request.session['id_user'])
        channels = Channels.objects.filter(id_user=id_session).values_list('id_channel', flat=True)
        playlists = Playlists.objects.filter(id_channel__in=channels).order_by('-id_playlist')
        data = {'playlists': playlists}
        return render(request, "DAIManagementApp/edit_playlist.html",data)



def new_playlist(request):
    if request.method == 'POST':
        apply_btn = request.POST.get("apply_btn")
        draft_btn = request.POST.get("draft_btn")
        if(apply_btn):
            useraccess = Useraccess.objects.get(id_user=request.session['id_user'])
            channel_id = request.POST.get('channel_id')
            channeldata = Channels.objects.get(id_channel=channel_id)
            zonename = request.POST.get('zonename')
            daydate = request.POST.get('day')
            number_of_windows = request.POST.get('numofwin')
            channel_zone = ChannelsZone.objects.get(id_channel=channel_id, zonename=zonename)
            draft_version = request.POST.get('draft_version')

            daydate = datetime.datetime.strptime(str(daydate), '%m/%d/%Y')
            daydate = daydate.strftime('%Y-%m-%d')
            start_date = str(daydate) + "T00:01:00+00:00"
            end_date = str(daydate) + "T23:59:00+00:00"
            now = datetime.datetime.now()
            Playlist = Playlists(id_channel_id=channel_id ,version="1", broadcastdate=str(daydate), start_date=start_date, end_date=end_date, creation_datetime=now, id_zone_channel_id=channel_zone.id_zone_channel,is_draft='0',draft_version='0')
            Playlist.save()
            traffic = 0
            record_inserted = 0
            for i in range(int(number_of_windows)):
                if request.POST.get('numofavails['+str(i)+']'):
                    numofavails = request.POST.get('numofavails['+str(i)+']')
                    window_start = request.POST.get('windowstart['+str(i)+']')
                    window_start = daydate+' '+window_start+':00'
                    window_end = request.POST.get('windowend['+str(i)+']')
                    window_end = daydate+' '+window_end+':00'
                    FMT = '%Y-%m-%d %H:%M:%S'
                    window_duration = datetime.datetime.strptime(window_end, FMT) - datetime.datetime.strptime(window_start, FMT)
                    window_duration = datetime.datetime.strptime(str(window_duration), '%H:%M:%S')
                    window_duration = window_duration.strftime('%H%M%S00')
                    Window = Windows(id_playlist_id=Playlist.id_playlist, window_start=window_start, window_end=window_end, window_duration=window_duration )
                    Window.save()

                    for j in range(int(numofavails)):
                        if request.POST.get('availstart['+str(i)+']['+str(j)+']'):
                            av_start = request.POST.get('availstart['+str(i)+']['+str(j)+']')
                            av_start = daydate+' '+av_start+':00'
                            number_of_ads = request.POST.get('numofads['+str(i)+']['+str(j)+']')
                            Avail = Avails(id_window_id=Window.id_window, avail_start=av_start, availinwindow=str(j+1), datetime=now )
                            Avail.save()

                            for k in range(int(number_of_ads)):
                                if request.POST.get('ad['+str(i)+']['+str(j)+']['+str(k)+']'):
                                    adspot = request.POST.get('ad['+str(i)+']['+str(j)+']['+str(k)+']')
                                    traffic +=1
                                    AdspotsInAv = AdspotsInAvail(id_avail_id=Avail.id_avail, id_adspot_id=adspot, positioninavail=str(k+1), trafficid=traffic)
                                    AdspotsInAv.save()

            print(channel_zone.id_zone_channel)
            xmlfilename = GenerateXMLfromDatabase(daydate, channel_id, channel_zone.id_zone_channel, '1',Playlist.draft_version)
            path_inftp = channeldata.ftp_channel_name+'/schedules/'+channel_zone.region
            uploadFTP(useraccess.ftp_server, useraccess.ftp_port, useraccess.ftp_user, useraccess.ftp_password, xmlfilename , path_inftp)
            last_id_playlist = Playlist.id_playlist
            now = datetime.datetime.now()
            activite = 'Add PlayList'
            desc = 'Admin Add PlayList  id: ' + str(Playlist.id_playlist)
            activity = Activity(activity=activite , date=now ,description=desc )
            activity.save()

            ############ Display data on view_playlist_id page ##################
            return redirect('view_playlist', id_playlist=last_id_playlist)
        else:
            useraccess = Useraccess.objects.get(id_user=request.session['id_user'])
            channel_id = request.POST.get('channel_id')
            channeldata = Channels.objects.get(id_channel=channel_id)
            zonename = request.POST.get('zonename')
            daydate = request.POST.get('day')
            number_of_windows = request.POST.get('numofwin')
            channel_zone = ChannelsZone.objects.get(id_channel=channel_id, zonename=zonename)

            daydate = datetime.datetime.strptime(str(daydate), '%m/%d/%Y')
            daydate = daydate.strftime('%Y-%m-%d')
            start_date = str(daydate) + "T00:01:00+00:00"
            end_date = str(daydate) + "T23:59:00+00:00"
            now = datetime.datetime.now()
            Playlist = Playlists(id_channel_id=channel_id ,version="1", broadcastdate=str(daydate), start_date=start_date, end_date=end_date, creation_datetime=now, id_zone_channel_id=channel_zone.id_zone_channel,is_draft='1',draft_version='1')
            Playlist.save()
            traffic = 0
            record_inserted = 0
            for i in range(int(number_of_windows)):
                if request.POST.get('numofavails['+str(i)+']'):
                    numofavails = request.POST.get('numofavails['+str(i)+']')
                    window_start = request.POST.get('windowstart['+str(i)+']')
                    window_start = daydate+' '+window_start+':00'
                    window_end = request.POST.get('windowend['+str(i)+']')
                    window_end = daydate+' '+window_end+':00'
                    FMT = '%Y-%m-%d %H:%M:%S'
                    window_duration = datetime.datetime.strptime(window_end, FMT) - datetime.datetime.strptime(window_start, FMT)
                    window_duration = datetime.datetime.strptime(str(window_duration), '%H:%M:%S')
                    window_duration = window_duration.strftime('%H%M%S00')
                    Window = Windows(id_playlist_id=Playlist.id_playlist, window_start=window_start, window_end=window_end, window_duration=window_duration )
                    Window.save()

                    for j in range(int(numofavails)):
                        if request.POST.get('availstart['+str(i)+']['+str(j)+']'):
                            av_start = request.POST.get('availstart['+str(i)+']['+str(j)+']')
                            av_start = daydate+' '+av_start+':00'
                            number_of_ads = request.POST.get('numofads['+str(i)+']['+str(j)+']')
                            Avail = Avails(id_window_id=Window.id_window, avail_start=av_start, availinwindow=str(j+1), datetime=now )
                            Avail.save()

                            for k in range(int(number_of_ads)):
                                if request.POST.get('ad['+str(i)+']['+str(j)+']['+str(k)+']'):
                                    adspot = request.POST.get('ad['+str(i)+']['+str(j)+']['+str(k)+']')
                                    traffic +=1
                                    AdspotsInAv = AdspotsInAvail(id_avail_id=Avail.id_avail, id_adspot_id=adspot, positioninavail=str(k+1), trafficid=traffic)
                                    AdspotsInAv.save()
            # xmlfilename = GenerateXMLfromDatabase(daydate, channel_id, channel_zone.id_zone_channel, '1' )
            # path_inftp = channeldata.ftp_channel_name+'/schedules/'+channel_zone.region
            # uploadFTP(useraccess.ftp_server, useraccess.ftp_port, useraccess.ftp_user, useraccess.ftp_password, xmlfilename , path_inftp)
            last_id_playlist = Playlist.id_playlist

            ############ Display data on view_playlist_id page ##################
            return redirect('draft_playlist')
    else:
        channels = Channels.objects.filter(id_user=request.session['id_user'])
        return render(request, "DAIManagementApp/new_playlist.html",{'data': {'channels': channels}})



def edit_playlist(request,id_playlist="1"):
    if request.method == 'POST':
        edit_btn = request.POST.get("edit_btn")
        view_btn = request.POST.get("view_btn")
        insertion_results_btn = request.POST.get("insertion_results_btn")
        duplicate_btn = request.POST.get("duplicate_btn")
        draft_btn = request.POST.get("draft_btn")
        if(edit_btn):
            playlist = Playlists.objects.get(pk=id_playlist)
            channels = Channels.objects.filter(id_user=request.session['id_user'])
            region = ChannelsZone.objects.get(id_zone_channel=playlist.id_zone_channel.id_zone_channel)
            d = playlist.broadcastdate

            win = Windows.objects.filter(id_playlist=playlist.id_playlist).values_list('id_window', flat=True)

            data = {}
            data["windows"] = []
            data["number_of_wins"] = 0
            i = 0

            windows = Windows.objects.filter(id_playlist=playlist.id_playlist).order_by('window_start')
            for window in windows:
                window_dic = {}
                window_dic['i'] = i
                i = i+1
                window_dic['id_window'] = window.id_window
                window_start_formated = datetime.datetime.strptime(window.window_start, '%Y-%m-%d %H:%M:%S')
                window_start_formated_2 = window_start_formated.strftime("%H:%M")
                window_end_formated = datetime.datetime.strptime(window.window_end, '%Y-%m-%d %H:%M:%S')
                window_end_formated_2 = window_end_formated.strftime("%H:%M")
                window_dic['window_start'] = window_start_formated_2
                window_dic['window_end'] = window_end_formated_2
                window_dic['avails_in_win'] = []
                window_dic['num_of_avails'] = 0
                data["number_of_wins"] = i
                avails = Avails.objects.filter(id_window=window.id_window)
                j = 0
                for avail in avails:
                    avail_dic = {}
                    avail_dic["j"] = j
                    j = j+1
                    avail_dic["id_avail"] = avail.id_avail
                    avail_start_formated = datetime.datetime.strptime(avail.avail_start, '%Y-%m-%d %H:%M:%S')
                    avail_start_formated2 = avail_start_formated.strftime("%H:%M")
                    avail_dic["avail_start"] = avail_start_formated2
                    avail_dic["adspots_in_avail"] = []
                    window_dic["avails_in_win"].append(avail_dic)
                    adspots = AdspotsInAvail.objects.filter(id_avail=avail.id_avail)
                    window_dic['num_of_avails'] = j
                    k = 0
                    avail_dic["num_of_adspots"] = 0
                    for adspot in adspots:
                        adspot_dic = {}
                        adspot_dic["k"] = k
                        k = k+1
                        avail_dic["num_of_adspots"] = k
                        adspot_dic["id_adsinavail"] = adspot.id_adsinavail
                        adspot_dic["id_adspot"] = adspot.id_adspot
                        avail_dic["adspots_in_avail"].append(adspot_dic)
                data["windows"].append(window_dic)


            now = datetime.datetime.now()
            activite = 'Edit PlayList'
            desc = 'Admin Edit PlayList  id: ' + str(playlist.id_playlist)
            activity = Activity(activity=activite , date=now ,description=desc )
            activity.save()

            data_playlist = {'data':data["windows"],'playlist': playlist, 'channels': channels,'region':region,'d':d,'number_of_wins':data["number_of_wins"]}
            return render(request, "DAIManagementApp/edit_playlist_id.html", data_playlist)
        elif(duplicate_btn):
            playlist = Playlists.objects.get(pk=id_playlist)
            channels = Channels.objects.filter(id_user=request.session['id_user'])
            region = ChannelsZone.objects.get(id_zone_channel=playlist.id_zone_channel.id_zone_channel)
            d = playlist.broadcastdate

            win = Windows.objects.filter(id_playlist=playlist.id_playlist).values_list('id_window', flat=True)

            data = {}
            data["windows"] = []
            data["number_of_wins"] = 0
            i = 0

            windows = Windows.objects.filter(id_playlist=playlist.id_playlist)
            for window in windows:
                window_dic = {}
                window_dic['i'] = i
                i = i+1
                window_dic['id_window'] = window.id_window
                window_start_formated = datetime.datetime.strptime(window.window_start, '%Y-%m-%d %H:%M:%S')
                window_start_formated_2 = window_start_formated.strftime("%H:%M")
                window_end_formated = datetime.datetime.strptime(window.window_end, '%Y-%m-%d %H:%M:%S')
                window_end_formated_2 = window_end_formated.strftime("%H:%M")
                window_dic['window_start'] = window_start_formated_2
                window_dic['window_end'] = window_end_formated_2
                window_dic['avails_in_win'] = []
                window_dic['num_of_avails'] = 0
                data["number_of_wins"] = i
                avails = Avails.objects.filter(id_window=window.id_window)
                j = 0
                for avail in avails:
                    avail_dic = {}
                    avail_dic["j"] = j
                    j = j+1
                    avail_dic["id_avail"] = avail.id_avail
                    avail_start_formated = datetime.datetime.strptime(avail.avail_start, '%Y-%m-%d %H:%M:%S')
                    avail_start_formated2 = avail_start_formated.strftime("%H:%M")
                    avail_dic["avail_start"] = avail_start_formated2
                    avail_dic["adspots_in_avail"] = []
                    window_dic["avails_in_win"].append(avail_dic)
                    adspots = AdspotsInAvail.objects.filter(id_avail=avail.id_avail)
                    window_dic['num_of_avails'] = j
                    k = 0
                    avail_dic["num_of_adspots"] = 0
                    for adspot in adspots:
                        adspot_dic = {}
                        adspot_dic["k"] = k
                        k = k+1
                        avail_dic["num_of_adspots"] = k
                        adspot_dic["id_adsinavail"] = adspot.id_adsinavail
                        adspot_dic["id_adspot"] = adspot.id_adspot
                        avail_dic["adspots_in_avail"].append(adspot_dic)
                data["windows"].append(window_dic)


            data_playlist = {'data':data["windows"],'playlist': playlist, 'channels': channels,'region':region,'d':d,'number_of_wins':data["number_of_wins"]}
            return render(request, "DAIManagementApp/duplicate_playlist_id.html", data_playlist)
        elif(view_btn):
            return redirect('view_playlist', id_playlist=id_playlist)
        else:
            return redirect('insertion_results', id_playlist=id_playlist)
    else:
        now = datetime.datetime.now() - timedelta(days=30)
        id_session = str(request.session['id_user'])
        channels = Channels.objects.filter(id_user=id_session).values_list('id_channel', flat=True)
        playlists = Playlists.objects.filter(id_channel__in=channels,is_draft='0',start_date__gte=now).order_by('-broadcastdate')
        windows= Windows.objects.filter(id_playlist__in=playlists)
        details = []
        for window in windows :
            detail = {}
            detail["id_playlist"]=window.id_playlist.id_playlist
            broadcastDate_verif = str(window.window_start).split(' ')[0]
            broadcastDate_verif = broadcastDate_verif.replace('-','')
            detail["window_start"] = str(window.window_start).split(' ')[1]
            detail["window_end"] = str(window.window_end).split(' ')[1]
            avails = Avails.objects.filter(id_window=window)
            detail["avails"] = []
            for avail in avails:
                av = {}
                av['start']= str(avail.avail_start).split(' ')[1]
                AdspotsInAv = AdspotsInAvail.objects.filter(id_avail=avail)
                for adspot in AdspotsInAv:
                    av["adspot"] = AdspotsInAvail.objects.filter(id_avail=avail)
                    try:
                        result = Verifs.objects.filter(broadcastDate = broadcastDate_verif, trafficId = adspot.trafficid).latest('id_verif')
                        av["adspotname"] = adspot.id_adspot.adspot_name
                        av["airStatusCode"] = result.airStatuscode
                        av["airTime"] = result.airTime
                    except Verifs.DoesNotExist:
                        print('oups')
                    else:
                        print("file not ready yet")
                detail["avails"].append(av)
            details.append(detail)


        data = {'playlists': playlists , 'details':details}
        return render(request, "DAIManagementApp/edit_playlist.html",data)



def insertion_results(request,id_playlist):
    playlist = Playlists.objects.get(pk=id_playlist)
    channels = Channels.objects.get(id_channel=playlist.id_channel.id_channel)
    region = ChannelsZone.objects.get(id_zone_channel=playlist.id_zone_channel.id_zone_channel)
    ftp_channel_name = channels.ftp_channel_name
    networkname = channels.networkname
    zonename = region.zonename
    broadcastdate = playlist.broadcastdate.replace("-","")
    result = Verifs.objects.filter(networkname=networkname,zonename=zonename,broadcastDate=broadcastdate).last()
    d = playlist.broadcastdate
    broadcastDate_verif = str(playlist.broadcastdate).replace("-","")
    win = Windows.objects.filter(id_playlist=playlist.id_playlist).values_list('id_window', flat=True)
    data = {}
    data["windows"] = []
    data["number_of_wins"] = 0
    i = 0
    record_inserted = 0
    if(result):
        verComplete = result.vercomplete
    else:
        verComplete = "false"
    if verComplete == "false":
    # if true:
        print("Wait I'm in ftp")
        filename_in_ftp = broadcastdate+"-"+zonename+"-00001.ver"
        path_in_ftp = "/"+ftp_channel_name+"/verifs/"
        path_in_app = "files/results/"+ftp_channel_name+"/verifs"
        if not os.path.exists(path_in_app):
            os.makedirs(path_in_app)
        # downloadFTP("uk06.tmd.cloud", "testftp@epgsano.com", "I?#=s3FfnSu_", "/2M/schedules/",  "test.txt" , "/var/www/html/DAI-Management/DAIManagement/FTP_files/")
        useraccess = Useraccess.objects.get(id_user=request.session['id_user'])
        print(useraccess)
        downloadFTP(useraccess.ftp_server, useraccess.ftp_user, useraccess.ftp_password, path_in_ftp , filename_in_ftp, path_in_app)
        # def downloadFTP(host, user, password, filepath_inftp, file_inftp,  localpath):
        if Path(path_in_app+'/'+filename_in_ftp).exists():
            doc = xml2.parse(path_in_app+'/'+filename_in_ftp)
            Spots = doc.getElementsByTagName("Spot")
            verComplete = doc.firstChild.getAttribute("verComplete")
            results = []
            for spot in Spots:
                trafficId = spot.getAttribute("trafficId")
                spotId  = spot.getAttribute("spotId")
                airTime = spot.getAttribute("airTime")
                newAirTime = airTime.replace("T", " ")
                newAirTime2 = newAirTime.replace("+02:00", "")
                airLength = spot.getAttribute("airLength")
                airStatusCode = spot.getAttribute("airStatusCode")
                version = spot.getAttribute("revision")
                try:
                    verif_to_update = Verifs.objects.get(networkname=networkname, zonename=zonename, broadcastDate=broadcastdate, trafficId=trafficId, spotId=spotId)
                    if verif_to_update:
                        verif_to_update.airTime = newAirTime2
                        verif_to_update.airLength = airLength
                        verif_to_update.airStatuscode = airStatusCode
                        verif_to_update.revision = version
                        verif_to_update.vercomplete = verComplete
                        verif_to_update.save()
                    else:
                        new_ad_verif = Verifs(networkname=networkname, zonename=zonename, broadcastDate=broadcastdate, trafficId=trafficId, spotId=spotId, airTime=newAirTime2, airLength=airLength, airStatuscode=airStatusCode, revision=version,  vercomplete = verComplete)
                        new_ad_verif.save()
                except Verifs.DoesNotExist:
                    print('oups')
                    new_ad_verif = Verifs(networkname=networkname, zonename=zonename, broadcastDate=broadcastdate, trafficId=trafficId, spotId=spotId, airTime=newAirTime2, airLength=airLength, airStatuscode=airStatusCode, revision=version,  vercomplete = verComplete)
                    new_ad_verif.save()
    windows = Windows.objects.filter(id_playlist=playlist.id_playlist)
    for window in windows:
        window_dic = {}
        window_dic['i'] = i
        i = i+1
        window_dic['id_window'] = window.id_window
        window_start_formated = datetime.datetime.strptime(window.window_start, '%Y-%m-%d %H:%M:%S')
        window_start_formated_2 = window_start_formated.strftime("%H:%M")
        window_end_formated = datetime.datetime.strptime(window.window_end, '%Y-%m-%d %H:%M:%S')
        window_end_formated_2 = window_end_formated.strftime("%H:%M")
        window_dic['window_start'] = window_start_formated_2
        window_dic['window_end'] = window_end_formated_2
        window_dic['avails_in_win'] = []
        window_dic['num_of_avails'] = 0
        data["number_of_wins"] = i
        avails = Avails.objects.filter(id_window=window.id_window)
        j = 0
        for avail in avails:
            avail_dic = {}
            avail_dic["j"] = j
            j = j+1
            avail_dic["id_avail"] = avail.id_avail
            avail_start_formated = datetime.datetime.strptime(avail.avail_start, '%Y-%m-%d %H:%M:%S')
            avail_start_formated2 = avail_start_formated.strftime("%H:%M")
            avail_dic["avail_start"] = avail_start_formated2
            avail_dic["adspots_in_avail"] = []
            window_dic["avails_in_win"].append(avail_dic)
            adspots = AdspotsInAvail.objects.filter(id_avail=avail.id_avail)
            window_dic['num_of_avails'] = j
            k = 0
            avail_dic["num_of_adspots"] = 0
            for adspot in adspots:
                adspot_dic = {}
                adspot_dic["k"] = k
                k = k+1
                avail_dic["num_of_adspots"] = k
                adspot_dic["id_adsinavail"] = adspot.id_adsinavail
                adspot_dic["id_adspot"] = adspot.id_adspot
                print("Wait I'm in database")
                try:
                    result = Verifs.objects.filter(broadcastDate = broadcastDate_verif, trafficId = adspot.trafficid, revision__lte = int(playlist.version)).latest('id_verif')
                    adspot_dic["airStatusCode"] = result.airStatuscode
                    adspot_dic["airTime"] = result.airTime
                except Verifs.DoesNotExist:
                    print('oups')
                else:
                    print("file not ready yet")
                avail_dic["adspots_in_avail"].append(adspot_dic)
        data["windows"].append(window_dic)
        record_inserted = 0
    data_playlist = {'data':data["windows"],'playlist': playlist, 'channels': channels,'region':region,'d':d,'number_of_wins':data["number_of_wins"],'record_inserted':record_inserted}
    return render(request, "DAIManagementApp/insertion_results.html", data_playlist)



def update_playlist(request, id_playlist):
    if request.method == 'POST':
        apply_btn = request.POST.get("apply_btn")
        draft_btn = request.POST.get("draft_btn")
        if(apply_btn):
            id_user = request.session['id_user']
            useraccess = Useraccess.objects.get(id_user=id_user)
            channel_id = request.POST.get('channel_id')
            channeldata = Channels.objects.get(id_channel=channel_id)
            zonename = request.POST.get('zonename')
            daydate = request.POST.get('day')
            number_of_windows = request.POST.get('numofwin')
            #return HttpResponse(number_of_windows)
            channel_zone = ChannelsZone.objects.get(id_channel=channel_id, zonename=zonename)
            version = request.POST.get('version')
            draft_version_old = request.POST.get('draft_version')

            daydate = datetime.datetime.strptime(str(daydate), '%Y-%m-%d')
            daydate = daydate.strftime('%Y-%m-%d')
            start_date = str(daydate) + "T00:01:00+00:00"
            end_date = str(daydate) + "T23:59:00+00:00"
            now = datetime.datetime.now()

            draft_version = request.POST.get('draft_version')

            playlist = Playlists.objects.get(pk=id_playlist)

            version_p = playlist.version
            broadcastdate_p = playlist.broadcastdate
            draft_version_p = playlist.draft_version
            start_date_p = playlist.start_date
            end_date_p = playlist.end_date

            max_version = Playlists.objects.filter(broadcastdate=str(daydate)).aggregate(Max('version')).get('version__max')
            new_version = int(max_version)+1
            Playlist = Playlists(id_channel_id=channel_id ,version=int(max_version)+1, broadcastdate=str(daydate), start_date=start_date, end_date=end_date, creation_datetime=now, id_zone_channel_id=channel_zone.id_zone_channel,is_draft='0',draft_version=draft_version_old)
            Playlist.save()
            traffic = 0
            record_inserted = 0
            var_test = 0
            for i in range(int(number_of_windows)):
                if request.POST.get('numofavails['+str(i)+']'):
                    numofavails = request.POST.get('numofavails['+str(i)+']')
                    window_start = request.POST.get('windowstart['+str(i)+']')
                    window_start = daydate+' '+window_start+':00'
                    window_end = request.POST.get('windowend['+str(i)+']')
                    window_end = daydate+' '+window_end+':00'
                    FMT = '%Y-%m-%d %H:%M:%S'
                    window_duration = datetime.datetime.strptime(window_end, FMT) - datetime.datetime.strptime(window_start, FMT)
                    window_duration = datetime.datetime.strptime(str(window_duration), '%H:%M:%S')
                    window_duration = window_duration.strftime('%H%M%S00')
                    Window = Windows(id_playlist_id=Playlist.id_playlist, window_start=window_start, window_end=window_end, window_duration=window_duration )
                    Window.save()

                    for j in range(int(numofavails)):
                        if request.POST.get('availstart['+str(i)+']['+str(j)+']'):
                            av_start = request.POST.get('availstart['+str(i)+']['+str(j)+']')
                            av_start = daydate+' '+av_start+':00'
                            number_of_ads = request.POST.get('numofads['+str(i)+']['+str(j)+']')
                            Avail = Avails(id_window_id=Window.id_window, avail_start=av_start, availinwindow=str(j+1), datetime=now )
                            Avail.save()

                            for k in range(int(number_of_ads)):
                                if request.POST.get('ad['+str(i)+']['+str(j)+']['+str(k)+']'):
                                    adspot = request.POST.get('ad['+str(i)+']['+str(j)+']['+str(k)+']')
                                    traffic +=1
                                    AdspotsInAv = AdspotsInAvail(id_avail_id=Avail.id_avail, id_adspot_id=adspot, positioninavail=str(k+1), trafficid=traffic)
                                    AdspotsInAv.save()
                                    record_inserted = 1
            last_id_playlist = Playlist.id_playlist
            xmlfilename = GenerateXMLfromDatabase(daydate, channel_id, channel_zone.id_zone_channel, str(new_version),draft_version_old)
            path_inftp = channeldata.ftp_channel_name+'/schedules/'+channel_zone.region
            uploadFTP(useraccess.ftp_server, useraccess.ftp_port, useraccess.ftp_user, useraccess.ftp_password, xmlfilename , path_inftp)

            ############ Display data on view_playlist_id page ##################
            return redirect('view_playlist', id_playlist=last_id_playlist)
        else:
                        useraccess = Useraccess.objects.get(id_user=request.session['id_user'])
                        channel_id = request.POST.get('channel_id')
                        channeldata = Channels.objects.get(id_channel=channel_id)
                        zonename = request.POST.get('zonename')
                        daydate = request.POST.get('day')
                        number_of_windows = request.POST.get('numofwin')
                        channel_zone = ChannelsZone.objects.get(id_channel=channel_id, zonename=zonename)
                        playlist = Playlists.objects.get(pk=id_playlist)

                        version_p = playlist.version
                        broadcastdate_p = playlist.broadcastdate
                        draft_version_p = playlist.draft_version
                        start_date_p = playlist.start_date
                        end_date_p = playlist.end_date

                        max_version_draft = Playlists.objects.filter(broadcastdate=broadcastdate_p).aggregate(Max('draft_version')).get('draft_version__max')
                        new_version_draft = max_version_draft

                        #daydate = datetime.datetime.strptime(str(daydate), '%m/%d/%Y')
                        #daydate = daydate.strftime('%Y-%m-%d')
                        start_date = str(daydate) + "T00:01:00+00:00"
                        end_date = str(daydate) + "T23:59:00+00:00"
                        now = datetime.datetime.now()
                        Playlist = Playlists(id_channel_id=channel_id ,version=version_p, broadcastdate=broadcastdate_p, start_date=start_date_p, end_date=end_date_p, creation_datetime=now, id_zone_channel_id=channel_zone.id_zone_channel,is_draft='1',draft_version=new_version_draft+1)
                        Playlist.save()
                        traffic = 0
                        record_inserted = 0
                        for i in range(int(number_of_windows)):
                            if request.POST.get('numofavails['+str(i)+']'):
                                numofavails = request.POST.get('numofavails['+str(i)+']')
                                window_start = request.POST.get('windowstart['+str(i)+']')
                                window_start = daydate+' '+window_start+':00'
                                window_end = request.POST.get('windowend['+str(i)+']')
                                window_end = daydate+' '+window_end+':00'
                                FMT = '%Y-%m-%d %H:%M:%S'
                                window_duration = datetime.datetime.strptime(window_end, FMT) - datetime.datetime.strptime(window_start, FMT)
                                window_duration = datetime.datetime.strptime(str(window_duration), '%H:%M:%S')
                                window_duration = window_duration.strftime('%H%M%S00')
                                Window = Windows(id_playlist_id=Playlist.id_playlist, window_start=window_start, window_end=window_end, window_duration=window_duration )
                                Window.save()

                                for j in range(int(numofavails)):
                                    if request.POST.get('availstart['+str(i)+']['+str(j)+']'):
                                        av_start = request.POST.get('availstart['+str(i)+']['+str(j)+']')
                                        av_start = daydate+' '+av_start+':00'
                                        number_of_ads = request.POST.get('numofads['+str(i)+']['+str(j)+']')
                                        Avail = Avails(id_window_id=Window.id_window, avail_start=av_start, availinwindow=str(j+1), datetime=now )
                                        Avail.save()

                                        for k in range(int(number_of_ads)):
                                            if request.POST.get('ad['+str(i)+']['+str(j)+']['+str(k)+']'):
                                                adspot = request.POST.get('ad['+str(i)+']['+str(j)+']['+str(k)+']')
                                                traffic +=1
                                                AdspotsInAv = AdspotsInAvail(id_avail_id=Avail.id_avail, id_adspot_id=adspot, positioninavail=str(k+1), trafficid=traffic)
                                                AdspotsInAv.save()
                        # xmlfilename = GenerateXMLfromDatabase(daydate, channel_id, channel_zone.id_zone_channel, '1' )
                        # path_inftp = channeldata.ftp_channel_name+'/schedules/'+channel_zone.region
                        # uploadFTP(useraccess.ftp_server, useraccess.ftp_port, useraccess.ftp_user, useraccess.ftp_password, xmlfilename , path_inftp)
                        last_id_playlist = Playlist.id_playlist

                        ############ Display data on view_playlist_id page ##################
                        return redirect('draft_playlist')




def view_playlist(request, id_playlist):
    playlist = Playlists.objects.get(pk=id_playlist)
    channels = Channels.objects.filter(id_user=request.session['id_user'])
    region = ChannelsZone.objects.get(id_zone_channel=playlist.id_zone_channel.id_zone_channel)
    d = playlist.broadcastdate

    win = Windows.objects.filter(id_playlist=playlist.id_playlist).values_list('id_window', flat=True)

    data = {}
    data["windows"] = []
    data["number_of_wins"] = 0
    i = 0
    record_inserted = 0
    windows = Windows.objects.filter(id_playlist=playlist.id_playlist)
    for window in windows:
        window_dic = {}
        window_dic['i'] = i
        i = i+1
        window_dic['id_window'] = window.id_window
        window_start_formated = datetime.datetime.strptime(window.window_start, '%Y-%m-%d %H:%M:%S')
        window_start_formated_2 = window_start_formated.strftime("%H:%M")
        window_end_formated = datetime.datetime.strptime(window.window_end, '%Y-%m-%d %H:%M:%S')
        window_end_formated_2 = window_end_formated.strftime("%H:%M")
        window_dic['window_start'] = window_start_formated_2
        window_dic['window_end'] = window_end_formated_2
        window_dic['avails_in_win'] = []
        window_dic['num_of_avails'] = 0
        data["number_of_wins"] = i
        avails = Avails.objects.filter(id_window=window.id_window)
        j = 0
        for avail in avails:
            avail_dic = {}
            avail_dic["j"] = j
            j = j+1
            avail_dic["id_avail"] = avail.id_avail

            avail_start_formated = datetime.datetime.strptime(avail.avail_start, '%Y-%m-%d %H:%M:%S')
            avail_start_formated2 = avail_start_formated.strftime("%H:%M")

            avail_dic["avail_start"] = avail_start_formated2
            avail_dic["adspots_in_avail"] = []
            window_dic["avails_in_win"].append(avail_dic)
            adspots = AdspotsInAvail.objects.filter(id_avail=avail.id_avail)
            window_dic['num_of_avails'] = j
            k = 0
            avail_dic["num_of_adspots"] = 0
            for adspot in adspots:
                adspot_dic = {}
                adspot_dic["k"] = k
                k = k+1
                avail_dic["num_of_adspots"] = k
                adspot_dic["id_adsinavail"] = adspot.id_adsinavail
                adspot_dic["id_adspot"] = adspot.id_adspot
                avail_dic["adspots_in_avail"].append(adspot_dic)
        data["windows"].append(window_dic)
        record_inserted = 0


    data_playlist = {'data':data["windows"],'playlist': playlist, 'channels': channels,'region':region,'d':d,'number_of_wins':data["number_of_wins"],'record_inserted':record_inserted}
    return render(request, "DAIManagementApp/view_playlist_id.html", data_playlist)


def duplicate_playlist(request,id_playlist="1"):
    if request.method == 'POST':
        playlist = Playlists.objects.get(pk=id_playlist)
        channels = Channels.objects.filter(id_user=request.session['id_user'])
        region = ChannelsZone.objects.get(id_zone_channel=playlist.id_zone_channel.id_zone_channel)
        d = playlist.broadcastdate

        win = Windows.objects.filter(id_playlist=playlist.id_playlist).values_list('id_window', flat=True)

        data = {}
        data["windows"] = []
        data["number_of_wins"] = 0
        i = 0

        windows = Windows.objects.filter(id_playlist=playlist.id_playlist)
        for window in windows:
            window_dic = {}
            window_dic['i'] = i
            i = i+1
            window_dic['id_window'] = window.id_window
            window_start_formated = datetime.datetime.strptime(window.window_start, '%Y-%m-%d %H:%M:%S')
            window_start_formated_2 = window_start_formated.strftime("%H:%M")
            window_end_formated = datetime.datetime.strptime(window.window_end, '%Y-%m-%d %H:%M:%S')
            window_end_formated_2 = window_end_formated.strftime("%H:%M")
            window_dic['window_start'] = window_start_formated_2
            window_dic['window_end'] = window_end_formated_2
            window_dic['avails_in_win'] = []
            window_dic['num_of_avails'] = 0
            data["number_of_wins"] = i
            avails = Avails.objects.filter(id_window=window.id_window)
            j = 0
            for avail in avails:
                avail_dic = {}
                avail_dic["j"] = j
                j = j+1
                avail_dic["id_avail"] = avail.id_avail
                avail_start_formated = datetime.datetime.strptime(avail.avail_start, '%Y-%m-%d %H:%M:%S')
                avail_start_formated2 = avail_start_formated.strftime("%H:%M")
                avail_dic["avail_start"] = avail_start_formated2
                avail_dic["adspots_in_avail"] = []
                window_dic["avails_in_win"].append(avail_dic)
                adspots = AdspotsInAvail.objects.filter(id_avail=avail.id_avail)
                window_dic['num_of_avails'] = j
                k = 0
                avail_dic["num_of_adspots"] = 0
                for adspot in adspots:
                    adspot_dic = {}
                    adspot_dic["k"] = k
                    k = k+1
                    avail_dic["num_of_adspots"] = k
                    adspot_dic["id_adsinavail"] = adspot.id_adsinavail
                    adspot_dic["id_adspot"] = adspot.id_adspot
                    avail_dic["adspots_in_avail"].append(adspot_dic)
            data["windows"].append(window_dic)


        data_playlist = {'data':data["windows"],'playlist': playlist, 'channels': channels,'region':region,'d':d,'number_of_wins':data["number_of_wins"]}
        return render(request, "DAIManagementApp/duplicate_playlist_id.html", data_playlist)
    else:
        id_session = str(request.session['id_user'])
        channels = Channels.objects.filter(id_user=id_session).values_list('id_channel', flat=True)
        playlists = Playlists.objects.filter(id_channel__in=channels).order_by('-id_playlist')
        data = {'playlists': playlists}
        return render(request, "DAIManagementApp/duplicate_playlist.html",data)


def duplicate_playlist_update(request, id_playlist):
    if request.method == 'POST':
        id_user = request.session['id_user']
        useraccess = Useraccess.objects.get(id_user_id=id_user)
        channel_id = request.POST.get('channel_id')
        channeldata = Channels.objects.get(id_channel=channel_id)
        zonename = request.POST.get('zonename')
        daydate = request.POST.get('day')
        number_of_windows = request.POST.get('numofwin')
        #return HttpResponse(number_of_windows)
        channel_zone = ChannelsZone.objects.get(id_channel=channel_id, zonename=zonename)
        version = request.POST.get('version')

        daydate = datetime.datetime.strptime(str(daydate), '%m/%d/%Y')
        daydate = daydate.strftime('%Y-%m-%d')
        start_date = str(daydate) + "T00:01:00+00:00"
        end_date = str(daydate) + "T23:59:00+00:00"
        now = datetime.datetime.now()

        max_version = Playlists.objects.filter(broadcastdate=str(daydate)).aggregate(Max('version')).get('version__max')

        Playlist = Playlists(id_channel_id=channel_id ,version=1, broadcastdate=str(daydate), start_date=start_date, end_date=end_date, creation_datetime=now, id_zone_channel_id=channel_zone.id_zone_channel,is_draft='0',draft_version='0')
        Playlist.save()
        traffic = 0
        record_inserted = 0
        var_test = 0
        for i in range(int(number_of_windows)):
            if request.POST.get('numofavails['+str(i)+']'):
                numofavails = request.POST.get('numofavails['+str(i)+']')
                window_start = request.POST.get('windowstart['+str(i)+']')
                window_start = daydate+' '+window_start+':00'
                window_end = request.POST.get('windowend['+str(i)+']')
                window_end = daydate+' '+window_end+':00'
                FMT = '%Y-%m-%d %H:%M:%S'
                window_duration = datetime.datetime.strptime(window_end, FMT) - datetime.datetime.strptime(window_start, FMT)
                window_duration = datetime.datetime.strptime(str(window_duration), '%H:%M:%S')
                window_duration = window_duration.strftime('%H%M%S00')
                Window = Windows(id_playlist_id=Playlist.id_playlist, window_start=window_start, window_end=window_end, window_duration=window_duration )
                Window.save()

                for j in range(int(numofavails)):
                    if request.POST.get('availstart['+str(i)+']['+str(j)+']'):
                        av_start = request.POST.get('availstart['+str(i)+']['+str(j)+']')
                        av_start = daydate+' '+av_start+':00'
                        number_of_ads = request.POST.get('numofads['+str(i)+']['+str(j)+']')
                        Avail = Avails(id_window_id=Window.id_window, avail_start=av_start, availinwindow=str(j+1), datetime=now )
                        Avail.save()

                        for k in range(int(number_of_ads)):
                            if request.POST.get('ad['+str(i)+']['+str(j)+']['+str(k)+']'):
                                adspot = request.POST.get('ad['+str(i)+']['+str(j)+']['+str(k)+']')
                                traffic +=1
                                AdspotsInAv = AdspotsInAvail(id_avail_id=Avail.id_avail, id_adspot_id=adspot, positioninavail=str(k+1), trafficid=traffic)
                                AdspotsInAv.save()
                                record_inserted = 1
        last_id_playlist = Playlist.id_playlist
        xmlfilename = GenerateXMLfromDatabase(daydate, channel_id, channel_zone.id_zone_channel, '1')
        path_inftp = channeldata.ftp_channel_name+'/schedules/'+channel_zone.region
        uploadFTP(useraccess.ftp_server, useraccess.ftp_port, useraccess.ftp_user, useraccess.ftp_password, xmlfilename , path_inftp)

        ############ Display data on view_playlist_id page ##################
        return redirect('view_playlist', id_playlist=last_id_playlist)



# fonction manipulations les fichiers logs
def insert_logs(file_path):

    file = open(file_path, 'r')
    lines = file.read().splitlines()
    count = 0
    for line in lines :
        l = line.split('|')
        #print(l)

        if l[1].strip() == 'info':
            msg = l[2].strip().split(' ')
            if "Detected" == msg[0] :
                #print(l[2].strip())
                date = l[0].strip().replace(',' , '')
                day = date.split(' ')[0]
                day =date_time(day.replace('/','-'))
                time = date.split(' ')[1]
                if time.split(':')[0]== '24':
                    time = '00' + time[2:]
                if count == 0:
                    first_day = day

                datetime = str(day + str(' ') + time)
                #datetime = datetime.datetime.strptime(datetime, '%Y-%m-%d %H:%M:%S')

            # print(date)
                info = l[2].split(',')
                #print(info[0])
                channel  = info[0].split(' ')[-1]
                ch = channel.split('(')[0]
                id = channel.split(":")[1].replace(')','')
                duration = info[2].split(' ')[3]


                #print("Date : "+ datetime + " | Channel : "+ ch + " | ID : "+ id   +" | Duration = " + duration)
                count +=1

                print(str(count) + " Date : "+ datetime)
                channel = Channels.objects.get(pk=id)
                log = Adbreak_history.objects.update_or_create(id_channel=channel , datetime=datetime , day=day, time=time,channel_name=ch,duration=duration)
                #log.save()

    file.close()
    print(count)
    return first_day

def logs(request):
    print(request.method)
    if request.method == "POST":
        print(str(request.FILES['logs_file']))
        #preparer le chemin stocker fichier logs

        path = "logs/user_"+str(request.session['id_user'])
        filename = str(request.FILES['logs_file'])
        if not os.path.exists(path):
                print("path not  exist")
                os.makedirs(path)
        else :
                print("path exist ")
        handle_uploaded_file(request.FILES['logs_file'],  path ,filename)
        # traiter ficher
        day = insert_logs(path+'/'+filename)
        # Predir day prochain
        predict_adbreak(day)

    yesterday = datetime.now() - timedelta(days=15)
    logs = AdbreakHistory.objects.filter(datetime__gte= yesterday )
    return render(request,'DAIManagementApp/add_logs.html' , {'logs':logs})

def date_time(datetime):
    date = datetime.split('-')
    date=date[2]+'-'+date[0]+'-'+date[1]
    return date

def get_logs(request):
    method = 'get'
    chanels = Channels.objects.all()
    if request.method == 'POST':
        ch = request.POST.get('channel_id')
        day = request.POST.get('day')
        print( " ID : " + ch + " DAY : " + day)

        channel = Channels.objects.get(pk=ch)
        logs = Adbreak_history.objects.filter(id_channel=channel,day=day)
        method = 'post'
        data = {
                'logs':logs,
                'channels':chanels,
                'method' : method
            }
    else :
            data = {

                'channels':chanels,
                'method' : method
            }
    return render(request,"DAIManagementApp/get_logs.html",{'data':data})


def add_campaign(request):
    if request.method == 'POST':
        # Part 1
        name = request.POST.get('campaign_name')
        id_brand = request.POST.get('id_brand')
        id_adpost = request.POST.get('id_adpost')
        pacing = request.POST.get('pacing')

        # Part data
        start_date = request.POST.get('start_day')
        end_date = request.POST.get('end_day')

        # part cpm
        pacing  = request.POST.get('pacing')
        volume  = request.POST.get('volume')

        delivery  = request.POST.get('delivery')
        if delivery == None:
            delivery = 0
        cpm     = request.POST.get('cpm')

        # Part placement
        placement = []
        time = DayTime.objects.all()
        for i in time:
            placement.append(request.POST.get(i.name))


        #Insert
        brand = Brands.objects.get(pk=id_brand)
        adpost = Adspots.objects.get(pk=id_adpost)

        campaign = Campaigns(name=name,id_advertiser=adpost,id_brand=brand,pacing=pacing,start_day=start_date,end_day=end_date,
                            volume=int(volume) , delivery=int(delivery) , cpm=float(cpm))
        campaign.save()
        now - datetime.datetime.now()
        activite = 'Add Campaign'
        desc = 'Admin Add Campaign  id: ' + str(campaign.id_campaign)
        activity = Activity(activity=activite , date=now ,description=desc )
        activity.save()
        print(campaign.id_campaign)
        campaign = Campaigns.objects.get(pk=str(campaign.id_campaign))
        for i in placement :
            if i != None:
                time = DayTime.objects.get(pk=i)

                p = Placement(id_time=time ,id_campaign=campaign)
                p.save()
        campaign = Campaigns.objects.all()
        return render(request,'DAIManagementApp/viwes_campaigns.html',{'campaigns': campaign})





    #brands = Brands.objects.all()
    brands = UsersAdvertisers.objects.filter(id_user=request.session['id_user']).filter(status="1")
    channels= Channels.objects.filter(id_user=request.session['id_user'])
    adspots = Adspots.objects.filter(id_channel__in=channels)
    print(len(adspots))
    datetime = DayTime.objects.all()
    data = {
        'brands'  : brands,
        'adspots' : adspots,
        'datetime': datetime
    }
    return render(request,'DAIManagementApp/add_campaign.html', {'data':data})



def add_campaign_new(request):
    from datetime import datetime
    if request.method == 'POST':
        name = request.POST.get('campaign_name')
        id_brand = request.POST.get('id_brand')
        id_adpost = request.POST.get('id_adpost')

        # pacing = request.POST.get('pacing')

        # Part data
        start_date = request.POST.get('start_day')
        end_date = request.POST.get('end_day')

        # part cpm
        pacing  = request.POST.get('pacing')
        volume  = request.POST.get('volume')




        cpm     = request.POST.get('cpm')

        rotation = request.POST.get('general_rotation')


        start_date1 = datetime.strptime(str(start_date), '%Y-%m-%d')
        end_date1 = datetime.strptime(str(end_date), '%Y-%m-%d')
        print(str((end_date1-start_date1).days))

        delivery  = ""
        if pacing == "0":
            delivery = 0
        else:
            delivery = int(volume)/int(str((end_date1-start_date1).days))


        brand = Brands.objects.get(pk=id_brand)
        adpost = Adspots.objects.get(pk=id_adpost)

        campaign = Campaigns(name=name,id_adpost=adpost,id_brand=brand,pacing=pacing,start_day=start_date,end_day=end_date,
                    volume=int(volume) , delivery=float(delivery) , cpm=float(cpm),general_rotation=int(rotation),id_user_id=request.session['id_user'],booked="0")
        campaign.save()

        channels= Channels.objects.filter(id_user=request.session['id_user'])
        if(rotation=='0'):
            for channel in channels:
                channel_placements = request.POST.getlist('check_placement_'+str(channel.id_channel))
                if channel_placements != None:
                    for placement_of_channel in channel_placements:
                        time = DayTime.objects.get(pk=int(placement_of_channel))
                        placement = Placement(id_time=time ,id_campaign=campaign , id_channel=channel)
                        placement.save()

            # agency = Agency.objects.filter(id_user=request.session['id_user'])
            # advertiser = Brands.objects.filter(id_agency__in=agency)
            # channels= Channels.objects.filter(id_user=request.session['id_user'])
            # adspots = Adspots.objects.filter(id_channel__in=channels)
            # datetime = DayTime.objects.all()
            # # brands = UsersAdvertisers.objects.filter(id_user=request.session['id_user']).filter(status="1").filter(id_brand__in=advertiser)
            # data = {
            #     'brands'  : advertiser,
            #     'agency'  : agency,
            #     'adspots' : adspots,
            #     'channels': channels,
            #     'datetime': datetime
            # }
            # return render(request,'DAIManagementApp/add_campaign_new.html', {'data':data})
            activite = 'Add Campaign'
            desc = 'Admin Add Campaign  id: ' + str(campaign.id_campaign)
            now = datetime.now()
            activity = Activity(activity=activite , date=now ,description=desc )
            activity.save()

            campaigns = Campaigns.objects.filter(id_user_id=request.session['id_user'])
            return render(request , 'DAIManagementApp/edit_campaign.html',{'campaigns':campaigns})
        else:
            channels_checked = request.POST.getlist('channels_checked')
            if channels_checked != None:
                for channel in channels_checked:
                    time = DayTime.objects.get(id_time=13)
                    placement = Placement(id_time=time,id_campaign=campaign , id_channel_id=channel)
                    placement.save()
            campaigns = Campaigns.objects.filter(id_user_id=request.session['id_user'])
            return render(request , 'DAIManagementApp/edit_campaign.html',{'campaigns':campaigns})

    agency = Agency.objects.filter(id_user=request.session['id_user'])
    advertiser = Brands.objects.filter(id_agency__in=agency)
    channels= Channels.objects.filter(id_user=request.session['id_user'])
    advertisers_for_creative = UsersAdvertisers.objects.filter(id_user=request.session['id_user']).filter(status="1").select_related('id_brand').all()
    adspots = Adspots.objects.filter(id_channel__in=channels)
    # datetime = DayTime.objects.all()
    datetime = DayTime.objects.exclude(id_time = 13)
    # brands = UsersAdvertisers.objects.filter(id_user=request.session['id_user']).filter(status="1").filter(id_brand__in=advertiser)
    data = {
        'brands'  : advertiser,
        'agency'  : agency,
        'adspots' : adspots,
        'channels': channels,
        'datetime': datetime,
        'advertisers_for_creative':advertisers_for_creative
    }
    return render(request,'DAIManagementApp/add_campaign_new.html', {'data':data})


def load_creatives(request):
    advertiser_id = request.GET.get('advertiser')
    adspots = Adspots.objects.filter(id_brand=advertiser_id)
    return render(request,'DAIManagementApp/dropdown_adspots_campaign.html',{'adspots': adspots})

def load_advertisers(request):
    agency_id = request.GET.get('agency')
    advertisers = Brands.objects.filter(id_agency=agency_id)
    return render(request,'DAIManagementApp/dropdown_advertisers_campaign.html',{'brands': advertisers})

def load_agency(request):
    agency = Agency.objects.filter(id_user_id=request.session['id_user'])
    return render(request,'DAIManagementApp/dropdown_agency_campaign.html',{'agency': agency})

def load_creative(request):
    channels= Channels.objects.filter(id_user=request.session['id_user'])
    adspots = Adspots.objects.filter(id_channel__in=channels)
    return render(request,'DAIManagementApp/dropdown_creative_campaign.html',{'adspots': adspots})


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 = Brands(id_agency=agency,brand_name=advertiser_name ,description=advertiser_desc, category=category, status=active_0_1, logo=path+'/'+filename)
    Brand.save()
    Advertiser_user = UsersAdvertisers(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,'DAIManagementApp/dropdown_advertisers_campaign.html')

def send_agency(request):
    name_agency = request.POST.get('name_agency')
    print(name_agency)
    description_agency = request.POST.get('description_agency')
    print(description_agency)
    now = datetime.datetime.now()
    agency = Agency(id_user_id=request.session['id_user'],name=name_agency,description=description_agency,datetime=now)
    agency.save()
    return render(request,'DAIManagementApp/dropdown_agency_campaign.html')


def send_creative(request):
    channel_id = request.POST.get('channel_id')
    advertiser_id = request.POST.get('advertiser_id')
    adspot_name = request.POST.get('adspot_name')
    adspot_duration = request.POST.get('adspot_duration')
    active_0_1 = request.POST.get('active_0_1')
    now = datetime.datetime.now()
    now_infile = str(now).replace(" ", "_")
    now_infile = now_infile.replace(":", "-")
    path = "adspots/user_"+str(request.session['id_user'])+"/ch_"+str(channel_id)
    if not os.path.exists("static/"+path):
        os.makedirs("static/"+path)

    new_now_infile = now_infile.replace(".","-")
    filename = "ch_"+str(channel_id)+"__"+adspot_name+"__"+str(adspot_duration)+"sec__"+new_now_infile+".ts"
    print(filename)
    handle_uploaded_file(request.FILES['adspot_file'], "static/"+path, filename)
    creative = Adspots(id_channel_id=channel_id ,id_brand_id=advertiser_id, adspot_name=adspot_name, duration=adspot_duration, status=active_0_1, original_filepath=path+'/'+filename ,  datetime=now,filename=filename)
    creative.save()
    return render(request,'DAIManagementApp/dropdown_agency_campaign.html')



def disable_campaign(request,id_campaign=""):

    if request.method == 'POST' :
        campaign = Campaigns.objects.get(pk=id_campaign)
        campaign.booked = campaign.booked == False
        campaign.save()
        now = datetime.datetime.now()
        activite = 'Disable Campaign'
        desc = 'Admin Disable Campaign  id: ' + str(id_campaign)
        activity = Activity(activity=activite , date=now ,description=desc )
        activity.save()
    campaigns = Campaigns.objects.filter(id_user_id=request.session['id_user']).order_by('-id_campaign')
    return render(request,'DAIManagementApp/disable_campaign.html',{'campaigns':campaigns})

def edit_campaign(request , id_campaign=""):
    if request.method == 'POST':
        edit_btn = request.POST.get("edit_btn")
        view_btn = request.POST.get("view_btn")
        if (edit_btn):
            campaign = Campaigns.objects.get(pk=id_campaign)
            brands_for_agency = Brands.objects.get(id_brand=campaign.id_brand.id_brand)
            agency_by_brand = Agency.objects.get(id_agency=brands_for_agency.id_agency.id_agency)

            channels= Channels.objects.filter(id_user=request.session['id_user'])
            adspots = Adspots.objects.filter(id_channel__in=channels)
            datetime = DayTime.objects.exclude(id_time = 13)
            placement_channel = Placement.objects.filter(id_campaign=campaign.id_campaign).values_list('id_channel')
            placement_channel = [x[0] for x in list(placement_channel)]

            channels_and_placement = {} # or []
            tab_channels_and_placement = []
            ch_n = 0
            for channel in channels:
                # we will get the placement of 1 channel here:
                placement = Placement.objects.filter(id_campaign=campaign.id_campaign, id_channel=channel).values_list('id_time')
                placement = [x[0] for x in list(placement)]
                # here will append the array channels_and_placement with the value of the channel and the placement to use it in the html
                channels_and_placement[ch_n] = {"channel_name":channel.channel_name, "channel_id": channel.id_channel, "placement": placement}
                tab_channels_and_placement.append(channels_and_placement[ch_n])
                ch_n +=1

            placement = Placement.objects.filter(id_campaign=campaign.id_campaign).values_list('id_time')
            placement = [x[0] for x in list(placement)]

            print(channels_and_placement)
            now = datetime.datetime.now()
            activite = 'Edit Campaign'
            desc = 'Admin Edit Campaign  id: ' + str(id_campaign)
            activity = Activity(activity=activite , date=now ,description=desc )
            activity.save()
            data = {
                'campaign':campaign,
                'agency_by_brand':agency_by_brand,
                'channels':channels,
                'datetime':datetime,
                'placement_channel':placement_channel,
                'channels_and_placement':tab_channels_and_placement,
                'placement':placement
            }
            return render(request , 'DAIManagementApp/edit_campaign_id.html',{'data':data})
        elif(view_btn):
            return redirect('view_campaign', id_campaign=id_campaign)
        else:
            return redirect('load_excel', id_campaign=id_campaign)

    from datetime import datetime
    campaigns = Campaigns.objects.filter(id_user_id=request.session['id_user']).order_by('-id_campaign')
    print(campaigns)
    now =datetime.now()

    start = []

    for i in campaigns:
                try :
                    date = datetime.strptime(i.start_day , '%Y-%m-%d')
                    if now > date:
                        start.append(i.id_campaign)
                except :
                    pass
    return render(request , 'DAIManagementApp/edit_campaign.html',{'campaigns':campaigns , 'start' : start})



def view_campaign(request, id_campaign='15'):
    campaign = Campaigns.objects.get(pk=id_campaign)
    brands_for_agency = Brands.objects.get(id_brand=campaign.id_brand.id_brand)
    agency_by_brand = Agency.objects.get(id_agency=brands_for_agency.id_agency.id_agency)

    channels= Channels.objects.filter(id_user=request.session['id_user'])
    adspots = Adspots.objects.filter(id_channel__in=channels)
    datetime = DayTime.objects.exclude(id_time = 13)
    placement_channel = Placement.objects.filter(id_campaign=campaign.id_campaign).values_list('id_channel')
    placement_channel = [x[0] for x in list(placement_channel)]

    channels_and_placement = {} # or []
    tab_channels_and_placement = []
    ch_n = 0
    for channel in channels:
        # we will get the placement of 1 channel here:
        placement = Placement.objects.filter(id_campaign=campaign.id_campaign, id_channel=channel).values_list('id_time')
        placement = [x[0] for x in list(placement)]
        # here will append the array channels_and_placement with the value of the channel and the placement to use it in the html
        channels_and_placement[ch_n] = {"channel_name":channel.channel_name, "channel_id": channel.id_channel, "placement": placement}
        tab_channels_and_placement.append(channels_and_placement[ch_n])
        ch_n +=1

    placement = Placement.objects.filter(id_campaign=campaign.id_campaign).values_list('id_time')
    placement = [x[0] for x in list(placement)]

    print(channels_and_placement)

    data = {
        'campaign':campaign,
        'agency_by_brand':agency_by_brand,
        'channels':channels,
        'datetime':datetime,
        'placement_channel':placement_channel,
        'channels_and_placement':tab_channels_and_placement,
        'placement':placement
    }
    return render(request , 'DAIManagementApp/view_campaign_id.html',{'data':data})

def views_campaigns(request):
    campaign =Campaigns.objects.filter(id_user=request.session['id_user']).order_by('-id_campaign')
    return render(request,'DAIManagementApp/views_campaigns.html',{'campaigns':campaign})

def update_campaign(request,id_campaign=''):
    if request.method == 'POST':
        campaign = Campaigns.objects.get(pk=id_campaign)
        # Part 1
        name = request.POST.get('campaign_name')
        id_agency = request.POST.get("id_agency")
        id_brand = request.POST.get('id_brand')
        id_adpost = request.POST.get('id_adpost')

        # pacing = request.POST.get('pacing')

        # Part data
        start_date = request.POST.get('start_day')
        end_date = request.POST.get('end_day')

        pacing  = request.POST.get('pacing')
        volume  = request.POST.get('volume')
        delivery  = request.POST.get('delivery')
        cpm     = request.POST.get('cpm')


        rotation = request.POST.get('general_rotation')


        # Part placement




        #Insert


        brand = Brands.objects.get(pk=id_brand)
        adpost = Adspots.objects.get(pk=id_adpost)
        agency =  Agency.objects.get(pk=id_agency)

        campaign.name=name
        campaign.id_advertiser=adpost
        campaign.id_brand=brand
        campaign.pacing=pacing
        campaign.start_day=start_date
        campaign.end_day=end_date
        campaign.volume=int(volume)
        campaign.general_rotation = rotation

        try :
            campaign.delivery=int(delivery)
        except:
            campaign.delivery=0



        campaign.cpm=float(cpm)
        campaign.save()
        now - datetime.datetime.now()
        activite = 'Edite Campaign'
        desc = 'Admin Edit Campaign  id: ' + str(id_campaign)
        activity = Activity(activity=activite , date=now ,description=desc )
        activity.save()


        channels = Channels.objects.filter(id_user=request.session['id_user'])
        if(rotation=='0'):
            p = Placement.objects.filter(id_campaign=campaign)
            p.delete()
            for channel in channels:
                channel_placements = request.POST.getlist('check_placement_'+str(channel.id_channel))
                if channel_placements != None:
                    for placement_of_channel in channel_placements:
                        time = DayTime.objects.get(pk=int(placement_of_channel))
                        placement = Placement(id_time=time ,id_campaign=campaign , id_channel=channel)
                        placement.save()
        else:
            channels_checked = request.POST.getlist('channels_checked')
            print(channels_checked)
            p = Placement.objects.filter(id_campaign=campaign)
            p.delete()
            if channels_checked != None:

                for channel in channels_checked:
                    time = DayTime.objects.get(id_time=13)

                    # p = [x[0] for x in list(p)]

                    # if channel not in p:
                    placement = Placement(id_time=time ,id_campaign=campaign,id_channel_id=channel)
                    placement.save()
                    # else:
                    #         placement = Placement.objects.get(id_time=time,id_campaign=campaign,id_channel_id=channel)
                    #         placement.id_time = time
                    #         placement.id_channel_id = channel
                    #         placement.save()

        # campaign = Campaigns.objects.filter(id_user_id=request.session['id_user'])
        return redirect('view_campaign', id_campaign=id_campaign)

def load_region_dates(request, channel_id=1, region_id=1 ):
    channel_id = request.GET.get('channel')
    region_id = request.GET.get('region')
    zone = ChannelsZone.objects.get(zonename=region_id, id_channel=channel_id)
    playlists = Playlists.objects.filter(id_channel=channel_id, id_zone_channel=zone.id_zone_channel)
    dates =[]
    for playlist in playlists:
        datetimeobject = datetime.datetime.strptime(playlist.broadcastdate, '%Y-%m-%d')
        dayformat = datetimeobject.strftime('%d.%m.%Y')
        dates.append(dayformat)
    data = {'dates':dates}
    return JsonResponse(data, safe=False)


def add_channels(request):
    if request.method == 'POST':
        name = request.POST.get('channel_name')
        lang = request.POST.get('language')
        genre = request.POST.get('genre')
        ftp = request.POST.get('ftp')
        sfr_channel_name = request.POST.get('sfr_channel_name')
        creation = modif = datetime.datetime.now()
        user = Users.objects.get(pk=request.session['id_user'])
        channel_decs = request.POST.get("channel_desc")
        ch = Channels(id_user = user , channel_name=name,channel_desc=channel_decs ,channel_lang=lang , channel_genre= genre , ftp_channel_name= ftp , sfr_channel_name=sfr_channel_name ,creation_datetime=creation , modif_datetime=modif)

        ch.save()
        now = datetime.datetime.now()
        activite = 'Add Channels'
        desc = 'Admin Add Channels  id: ' + str(ch.id_channel)
        activity = Activity(activity=activite , date=now ,description=desc )
        activity.save()
        return  redirect('/DAIManagement/edit_channels/')

    return render(request,'DAIManagementApp/add_channels.html')

def edit_channels(request,id_channel=""):
    if request.method == 'POST':
        print(id_channel)
        channel = Channels.objects.get(pk=id_channel)
        return render(request, 'DAIManagementApp/edit_channels_id.html',{'channel':channel})
    user = Users.objects.get(pk=request.session['id_user'])
    channels = Channels.objects.filter(id_user=user).order_by('-id_channel')
    return render(request,'DAIManagementApp/edit_channels.html',{'channels' : channels} )

def update_channels(request , id_channel=""):
    if request.method == 'POST':
        name = request.POST.get('channel_name')
        lang = request.POST.get('language')
        genre = request.POST.get('genre')
        ftp = request.POST.get('ftp')
        sfr = request.POST.get('sfr')
        zone_name = request.POST.get('zone_name')
        network = request.POST.get("networkname")
        modif = datetime.datetime.now()
        channel_decs = request.POST.get("channel_desc")

        user = Users.objects.get(pk=request.session['id_user'])
        channel = Channels.objects.filter(id_channel=id_channel,id_user=user)[0]
        channel.channel_name = name
        channel.channel_lang =lang
        channel.channel_genre = genre
        channel.ftp_channel_name = ftp
        channel.sfr_channel_name = sfr
        channel.zonename = zone_name
        channel.network = network
        channel.channel_desc = channel_decs
        channel.modif_datetime = modif
        now = datetime.datetime.now()
        activite = 'Edit Channel'
        desc = 'Admin Edit Channel  id: ' + str(id_channel)
        activity = Activity(activity=activite , date=now ,description=desc )
        activity.save()

        channel.save()
        return redirect('/DAIManagement/edit_channels/')

def delete_channel(request ,id_channel=""):
    if request.method == 'POST':
        ch = Channels.objects.get(pk=id_channel).delete()
        now - datetime.datetime.now()
        activite = 'Delete Channel'
        desc = 'Admin delete Channel  id: ' + str(id_channel)
        activity = Activity(activity=activite , date=now ,description=desc )
        activity.save()
        #ch.save()
    user = Users.objects.get(pk=request.session['id_user'])
    channels = Channels.objects.filter(id_user=user).order_by('-id_channel')
    return render(request,'DAIManagementApp/delete_channel.html', {'channels' : channels})

def add_jingle(request):
    if request.method == 'POST' :
        name = request.POST.get('jingle_name')
        channel = request.POST.get("channel_id")
        status = request.POST.get('active_0_1')

        path = "jingles/user_"+str(request.session['id_user'] )+"/channel_"+channel + "/"+name

        if not os.path.exists("static/"+path):
            os.makedirs("static/"+path)

        filename_video_ext = '.'+request.FILES['video_file'].name.split(".")[1].lower()
        filename_video = name+filename_video_ext
        # filename_video = FILES['video_file'].name
        filename_md5 = name+".md5"
        handle_uploaded_file(request.FILES['video_file'], "static/"+path, filename_video)
        handle_uploaded_file(request.FILES['md5_file'], "static/"+path, filename_md5)
        now = modif = datetime.datetime.now()
        ch = Channels.objects.get(pk=channel)
        jingle = Jingles(id_channel=ch,jingle_name=name,video_jingle=path+"/"+filename_video,md5_file=path+"/"+filename_md5,creation_datetime=now,modif_datetime=modif,status=status, is_delete='0' )
        now - datetime.datetime.now()
        activite = 'Add Jingle'
        desc = 'Admin Add Jingle  id: ' + str(jingle.id_jingle)
        activity = Activity(activity=activite , date=now ,description=desc )
        activity.save()
        jingle.save()
        return redirect("/DAIManagement/edit_jingle/")
    user = Users.objects.get(pk=request.session['id_user'])
    channels = Channels.objects.filter(id_user=user)

    return render(request,'DAIManagementApp/add_jingles.html',{'channels':channels})

def edit_jingle(request , id_jingle=""):
    if request.method == 'POST':
        jingle = Jingles.objects.get(pk=id_jingle)
        user = Users.objects.get(pk=request.session['id_user'])
        channels = Channels.objects.filter(id_user=user)
        data =  {
            'jingle' : jingle,
            'channels' : channels
        }
        return render(request , 'DAIManagementApp/edit_jingles_id.html', {'data' : data})
    user = Users.objects.get(pk=request.session['id_user'])
    channels = Channels.objects.filter(id_user=user)
    # .values_list('id_channel')
    # channels = [x[0] for x in channels]

    jingles = Jingles.objects.filter(id_channel__in=channels,is_delete=0).order_by('-id_jingle')
    return render(request,'DAIManagementApp/edit_jingles.html' , {'jingles':jingles})

def update_jingle(request,id_jingle=""):
    if request.method == 'POST' :
        jingle = Jingles.objects.get(pk=id_jingle)
        name = request.POST.get('jingle_name')
        channel = request.POST.get("channel_id")
        print(channel)
        status = request.POST.get('active_0_1')
        path = "jingles/user_"+str(request.session['id_user'] )+"/channel_"+str(channel) + "/"+name
        try:
            filename_video_ext = request.FILES['video_file'].name.split(".")[1].lower()
            filename_video = name+filename_video_ext
        except:
            from pathlib import Path
            filename_video = Path(jingle.video_jingle).name


        filename_md5 = name+".md5"
        if not os.path.exists("static/"+path):
                os.makedirs("static/"+path)
        try :
            handle_uploaded_file(request.FILES['video_file'], "static/"+path, filename_video)
            os.remove("static/"+ jingle.video_jingle)
        except :
            import shutil
            if os.path.exists("static/"+ jingle.video_jingle):
                    shutil.move("static/"+ jingle.video_jingle, "static/"+path+"/"+filename_video)

        try :

            handle_uploaded_file(request.FILES['md5_file'], "static/"+path, filename_md5)
            os.remove("static/"+ jingle.md5_file)


        except :
            import shutil
            if os.path.exists("static/"+ jingle.md5_file):
                    shutil.move("static/"+ jingle.md5_file, "static/"+path+"/"+filename_md5)

        jingle.jingle_name= name
        ch = Channels.objects.get(pk=channel)
        jingle.id_channel = ch
        jingle.status = status == '1'
        jingle.video_jingle=path+"/"+filename_video
        jingle.md5_file=path+"/"+filename_md5
        jingle.modif_datetime = datetime.datetime.now()
        jingle.save()
        now = datetime.datetime.now()
        activite = 'Edit Jingle'
        desc = 'Admin Edit Jingle  id: ' + str(id_jingle)
        activity = Activity(activity=activite , date=now ,description=desc )
        activity.save()


        return redirect("/DAIManagement/edit_jingle/")

def delete_jingle(request,id_jingle=''):
    if request.method == 'POST':
        jingle = Jingles.objects.get(pk=id_jingle)
        jingle.modif_datetime = datetime.datetime.now()
        jingle.is_delete = 1
        jingle.save()
        now = datetime.datetime.now()
        activite = 'Delete Jingle'
        desc = 'Admin Delete Jingle  id: ' + str(id_jingle)
        activity = Activity(activity=activite , date=now ,description=desc )
        activity.save()

    user = Users.objects.get(pk=request.session['id_user'])
    channels = Channels.objects.filter(id_user=user)


    jingles = Jingles.objects.filter(id_channel__in=channels,is_delete=0).order_by('-id_jingle')

    return render(request,'DAIManagementApp/delete_jingles.html', {'jingles': jingles})

def views_jingles(request,id_channel=''):
    if  id_channel != '':
        jingles = Jingles.objects.filter(id_channel = id_channel)
        return render(request,'DAIManagementApp/views_channel_jingles.html',{'jingles':jingles})

    user = Users.objects.get(pk=request.session['id_user'])
    channels = Channels.objects.filter(id_user=user)
    ch = []
    for i in channels :
        jingles = Jingles.objects.filter(id_channel=i,is_delete=0)
        rest = {
            'channel' : i,
            'total'   : len(jingles)
        }
        ch.append(rest)

    return render(request,'DAIManagementApp/views_jingles.html',{'data': ch})

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 , "DAIManagementApp/sfr_upload.html" )

def  insert_sfr(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"]) == "Region" 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 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,'DAIManagementApp/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, 'DAIManagementApp/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, 'DAIManagementApp/pie_chart.html',{'channels': channels})


def verifs(request):
    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()
    data = []

    for line in lines:
        if line.networkname in channels :
            p ={
                'channel':line.networkname,
                'name' : line.spotId,
                'day' : str(line.airTime).split(' ')[0],
            }

            region = ChannelsZone.objects.get(zonename =line.zonename)
            p['region'] = region.region
            min = str(line.airTime).split(' ')[1].split(':')
            minute = min[0]+':'+min[1]+':00'
            p['minute'] = minute
            p['color'] = '#00800000'
            if  str(line.airStatuscode) == '0001':
                p['status'] = 'Aired Successfully'
                p['color'] = '#2c2c8cb3'

                for i in Sfr_analytics.objects.filter(day=p['day'],minute=p['minute']):
                    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'] = int(nb)
            elif str(line.airStatuscode) == '0008':
                p['status'] = 'Failed, Adspot cut'
                p['nb_wach'] = '-'

            else :
                p['status'] = 'Failed, Other Reason'
                p['nb_wach'] = '-'

            data.append(p)
    return render(request,'DAIManagementApp/verifs.html',{'data':data})


def playlists_results(request,id_playlist="1"):
    if request.method == 'POST':
        edit_btn = request.POST.get("edit_btn")
        view_btn = request.POST.get("view_btn")
        insertion_results_btn = request.POST.get("insertion_results")
        duplicate_btn = request.POST.get("duplicate_btn")
        if(edit_btn):
            playlist = Playlists.objects.get(pk=id_playlist)
            channels = Channels.objects.filter(id_user=request.session['id_user'])
            region = ChannelsZone.objects.get(id_zone_channel=playlist.id_zone_channel.id_zone_channel)
            d = playlist.broadcastdate

            win = Windows.objects.filter(id_playlist=playlist.id_playlist).values_list('id_window', flat=True)

            data = {}
            data["windows"] = []
            data["number_of_wins"] = 0
            i = 0

            windows = Windows.objects.filter(id_playlist=playlist.id_playlist)
            for window in windows:
                window_dic = {}
                window_dic['i'] = i
                i = i+1
                window_dic['id_window'] = window.id_window
                window_start_formated = datetime.datetime.strptime(window.window_start, '%Y-%m-%d %H:%M:%S')
                window_start_formated_2 = window_start_formated.strftime("%H:%M")
                window_end_formated = datetime.datetime.strptime(window.window_end, '%Y-%m-%d %H:%M:%S')
                window_end_formated_2 = window_end_formated.strftime("%H:%M")
                window_dic['window_start'] = window_start_formated_2
                window_dic['window_end'] = window_end_formated_2
                window_dic['avails_in_win'] = []
                window_dic['num_of_avails'] = 0
                data["number_of_wins"] = i
                avails = Avails.objects.filter(id_window=window.id_window)
                j = 0
                for avail in avails:
                    avail_dic = {}
                    avail_dic["j"] = j
                    j = j+1
                    avail_dic["id_avail"] = avail.id_avail
                    avail_start_formated = datetime.datetime.strptime(avail.avail_start, '%Y-%m-%d %H:%M:%S')
                    avail_start_formated2 = avail_start_formated.strftime("%H:%M")
                    avail_dic["avail_start"] = avail_start_formated2
                    avail_dic["adspots_in_avail"] = []
                    window_dic["avails_in_win"].append(avail_dic)
                    adspots = AdspotsInAvail.objects.filter(id_avail=avail.id_avail)
                    window_dic['num_of_avails'] = j
                    k = 0
                    avail_dic["num_of_adspots"] = 0
                    for adspot in adspots:
                        adspot_dic = {}
                        adspot_dic["k"] = k
                        k = k+1
                        avail_dic["num_of_adspots"] = k
                        adspot_dic["id_adsinavail"] = adspot.id_adsinavail
                        adspot_dic["id_adspot"] = adspot.id_adspot
                        avail_dic["adspots_in_avail"].append(adspot_dic)
                data["windows"].append(window_dic)


            data_playlist = {'data':data["windows"],'playlist': playlist, 'channels': channels,'region':region,'d':d,'number_of_wins':data["number_of_wins"]}
            return render(request, "DAIManagementApp/edit_playlist_id.html", data_playlist)
        elif(duplicate_btn):
            playlist = Playlists.objects.get(pk=id_playlist)
            channels = Channels.objects.filter(id_user=request.session['id_user'])
            region = ChannelsZone.objects.get(id_zone_channel=playlist.id_zone_channel.id_zone_channel)
            d = playlist.broadcastdate

            win = Windows.objects.filter(id_playlist=playlist.id_playlist).values_list('id_window', flat=True)

            data = {}
            data["windows"] = []
            data["number_of_wins"] = 0
            i = 0

            windows = Windows.objects.filter(id_playlist=playlist.id_playlist)
            for window in windows:
                window_dic = {}
                window_dic['i'] = i
                i = i+1
                window_dic['id_window'] = window.id_window
                window_start_formated = datetime.datetime.strptime(window.window_start, '%Y-%m-%d %H:%M:%S')
                window_start_formated_2 = window_start_formated.strftime("%H:%M")
                window_end_formated = datetime.datetime.strptime(window.window_end, '%Y-%m-%d %H:%M:%S')
                window_end_formated_2 = window_end_formated.strftime("%H:%M")
                window_dic['window_start'] = window_start_formated_2
                window_dic['window_end'] = window_end_formated_2
                window_dic['avails_in_win'] = []
                window_dic['num_of_avails'] = 0
                data["number_of_wins"] = i
                avails = Avails.objects.filter(id_window=window.id_window)
                j = 0
                for avail in avails:
                    avail_dic = {}
                    avail_dic["j"] = j
                    j = j+1
                    avail_dic["id_avail"] = avail.id_avail
                    avail_start_formated = datetime.datetime.strptime(avail.avail_start, '%Y-%m-%d %H:%M:%S')
                    avail_start_formated2 = avail_start_formated.strftime("%H:%M")
                    avail_dic["avail_start"] = avail_start_formated2
                    avail_dic["adspots_in_avail"] = []
                    window_dic["avails_in_win"].append(avail_dic)
                    adspots = AdspotsInAvail.objects.filter(id_avail=avail.id_avail)
                    window_dic['num_of_avails'] = j
                    k = 0
                    avail_dic["num_of_adspots"] = 0
                    for adspot in adspots:
                        adspot_dic = {}
                        adspot_dic["k"] = k
                        k = k+1
                        avail_dic["num_of_adspots"] = k
                        adspot_dic["id_adsinavail"] = adspot.id_adsinavail
                        adspot_dic["id_adspot"] = adspot.id_adspot
                        avail_dic["adspots_in_avail"].append(adspot_dic)
                data["windows"].append(window_dic)


            data_playlist = {'data':data["windows"],'playlist': playlist, 'channels': channels,'region':region,'d':d,'number_of_wins':data["number_of_wins"]}
            return render(request, "DAIManagementApp/duplicate_playlist_id.html", data_playlist)
        elif(view_btn):
            return redirect('view_playlist', id_playlist=id_playlist)
        else:
            return redirect('insertion_results', id_playlist=id_playlist)
    else:
        id_session = str(request.session['id_user'])
        channels = Channels.objects.filter(id_user=id_session).values_list('id_channel', flat=True)
        playlists = Playlists.objects.filter(id_channel__in=channels).order_by('-id_playlist')
        data = {'playlists': playlists}
        return render(request, "DAIManagementApp/playlists_results.html",data)



def draft_playlist(request,id_playlist="1"):
    if request.method == 'POST':
        playlist = Playlists.objects.get(pk=id_playlist)
        channels = Channels.objects.filter(id_user=request.session['id_user'])
        region = ChannelsZone.objects.get(id_zone_channel=playlist.id_zone_channel.id_zone_channel)
        d = playlist.broadcastdate

        win = Windows.objects.filter(id_playlist=playlist.id_playlist).values_list('id_window', flat=True)

        data = {}
        data["windows"] = []
        data["number_of_wins"] = 0
        i = 0

        windows = Windows.objects.filter(id_playlist=playlist.id_playlist)
        for window in windows:
            window_dic = {}
            window_dic['i'] = i
            i = i+1
            window_dic['id_window'] = window.id_window
            window_start_formated = datetime.datetime.strptime(window.window_start, '%Y-%m-%d %H:%M:%S')
            window_start_formated_2 = window_start_formated.strftime("%H:%M")
            window_end_formated = datetime.datetime.strptime(window.window_end, '%Y-%m-%d %H:%M:%S')
            window_end_formated_2 = window_end_formated.strftime("%H:%M")
            window_dic['window_start'] = window_start_formated_2
            window_dic['window_end'] = window_end_formated_2
            window_dic['avails_in_win'] = []
            window_dic['num_of_avails'] = 0
            data["number_of_wins"] = i
            avails = Avails.objects.filter(id_window=window.id_window)
            j = 0
            for avail in avails:
                avail_dic = {}
                avail_dic["j"] = j
                j = j+1
                avail_dic["id_avail"] = avail.id_avail
                avail_start_formated = datetime.datetime.strptime(avail.avail_start, '%Y-%m-%d %H:%M:%S')
                avail_start_formated2 = avail_start_formated.strftime("%H:%M")
                avail_dic["avail_start"] = avail_start_formated2
                avail_dic["adspots_in_avail"] = []
                window_dic["avails_in_win"].append(avail_dic)
                adspots = AdspotsInAvail.objects.filter(id_avail=avail.id_avail)
                window_dic['num_of_avails'] = j
                k = 0
                avail_dic["num_of_adspots"] = 0
                for adspot in adspots:
                    adspot_dic = {}
                    adspot_dic["k"] = k
                    k = k+1
                    avail_dic["num_of_adspots"] = k
                    adspot_dic["id_adsinavail"] = adspot.id_adsinavail
                    adspot_dic["id_adspot"] = adspot.id_adspot
                    avail_dic["adspots_in_avail"].append(adspot_dic)
            data["windows"].append(window_dic)

        data_playlist = {'data':data["windows"],'playlist': playlist, 'channels': channels,'region':region,'d':d,'number_of_wins':data["number_of_wins"]}
        return render(request, "DAIManagementApp/edit_draft_playlist_id.html", data_playlist)
    else:
        id_session = str(request.session['id_user'])
        channels = Channels.objects.filter(id_user=id_session).values_list('id_channel', flat=True)
        playlists = Playlists.objects.filter(id_channel__in=channels,is_draft='1').order_by('-id_playlist')
        data = {'playlists': playlists}
        return render(request, "DAIManagementApp/draft_playlist.html",data)


def update_draft_playlist(request, id_playlist):
    if request.method == 'POST':
        apply_btn = request.POST.get("apply_btn")
        draft_btn = request.POST.get("draft_btn")
        if(apply_btn):
            id_user = request.session['id_user']
            useraccess = Useraccess.objects.get(id_user=id_user)
            channel_id = request.POST.get('channel_id')
            channeldata = Channels.objects.get(id_channel=channel_id)
            zonename = request.POST.get('zonename')
            daydate = request.POST.get('day')
            number_of_windows = request.POST.get('numofwin')
            #return HttpResponse(number_of_windows)
            channel_zone = ChannelsZone.objects.get(id_channel=channel_id, zonename=zonename)
            version = request.POST.get('version')

            daydate = datetime.datetime.strptime(str(daydate), '%Y-%m-%d')
            daydate = daydate.strftime('%Y-%m-%d')
            start_date = str(daydate) + "T00:01:00+00:00"
            end_date = str(daydate) + "T23:59:00+00:00"
            now = datetime.datetime.now()

            max_version = Playlists.objects.filter(broadcastdate=str(daydate)).aggregate(Max('version')).get('version__max')
            new_version = int(max_version)
            max_version_draft = Playlists.objects.filter(broadcastdate=str(daydate)).aggregate(Max('draft_version')).get('draft_version__max')
            new_version_draft = int(max_version_draft)
            Playlist = Playlists(id_channel_id=channel_id ,version=int(max_version), broadcastdate=str(daydate), start_date=start_date, end_date=end_date, creation_datetime=now, id_zone_channel_id=channel_zone.id_zone_channel,is_draft='0',draft_version=int(new_version_draft))
            Playlist.save()
            traffic = 0
            record_inserted = 0
            var_test = 0
            for i in range(int(number_of_windows)):
                if request.POST.get('numofavails['+str(i)+']'):
                    numofavails = request.POST.get('numofavails['+str(i)+']')
                    window_start = request.POST.get('windowstart['+str(i)+']')
                    window_start = daydate+' '+window_start+':00'
                    window_end = request.POST.get('windowend['+str(i)+']')
                    window_end = daydate+' '+window_end+':00'
                    FMT = '%Y-%m-%d %H:%M:%S'
                    window_duration = datetime.datetime.strptime(window_end, FMT) - datetime.datetime.strptime(window_start, FMT)
                    window_duration = datetime.datetime.strptime(str(window_duration), '%H:%M:%S')
                    window_duration = window_duration.strftime('%H%M%S00')
                    Window = Windows(id_playlist_id=Playlist.id_playlist, window_start=window_start, window_end=window_end, window_duration=window_duration )
                    Window.save()

                    for j in range(int(numofavails)):
                        if request.POST.get('availstart['+str(i)+']['+str(j)+']'):
                            av_start = request.POST.get('availstart['+str(i)+']['+str(j)+']')
                            av_start = daydate+' '+av_start+':00'
                            number_of_ads = request.POST.get('numofads['+str(i)+']['+str(j)+']')
                            Avail = Avails(id_window_id=Window.id_window, avail_start=av_start, availinwindow=str(j+1), datetime=now )
                            Avail.save()

                            for k in range(int(number_of_ads)):
                                if request.POST.get('ad['+str(i)+']['+str(j)+']['+str(k)+']'):
                                    adspot = request.POST.get('ad['+str(i)+']['+str(j)+']['+str(k)+']')
                                    traffic +=1
                                    AdspotsInAv = AdspotsInAvail(id_avail_id=Avail.id_avail, id_adspot_id=adspot, positioninavail=str(k+1), trafficid=traffic)
                                    AdspotsInAv.save()
                                    record_inserted = 1
            last_id_playlist = Playlist.id_playlist
            xmlfilename = GenerateXMLfromDatabase(daydate, channel_id, channel_zone.id_zone_channel, str(new_version), str(max_version_draft))
            path_inftp = channeldata.ftp_channel_name+'/schedules/'+channel_zone.region
            uploadFTP(useraccess.ftp_server, useraccess.ftp_port, useraccess.ftp_user, useraccess.ftp_password, xmlfilename , path_inftp)

            ############ Display data on view_playlist_id page ##################
            return redirect('view_playlist', id_playlist=last_id_playlist)
        else:
            id_user = request.session['id_user']
            useraccess = Useraccess.objects.get(id_user=id_user)
            channel_id = request.POST.get('channel_id')
            channeldata = Channels.objects.get(id_channel=channel_id)
            zonename = request.POST.get('zonename')
            daydate = request.POST.get('day')
            number_of_windows = request.POST.get('numofwin')
            #return HttpResponse(number_of_windows)
            channel_zone = ChannelsZone.objects.get(id_channel=channel_id, zonename=zonename)
            version = request.POST.get('version')

            daydate = datetime.datetime.strptime(str(daydate), '%Y-%m-%d')
            daydate = daydate.strftime('%Y-%m-%d')
            start_date = str(daydate) + "T00:01:00+00:00"
            end_date = str(daydate) + "T23:59:00+00:00"
            now = datetime.datetime.now()

            max_version = Playlists.objects.filter(broadcastdate=str(daydate)).aggregate(Max('version')).get('version__max')
            new_version = int(max_version)

            max_version_draft = Playlists.objects.filter(broadcastdate=str(daydate)).aggregate(Max('draft_version')).get('draft_version__max')
            new_version_draft = int(max_version_draft)
            Playlist = Playlists(id_channel_id=channel_id ,version=int(max_version), broadcastdate=str(daydate), start_date=start_date, end_date=end_date, creation_datetime=now, id_zone_channel_id=channel_zone.id_zone_channel,is_draft='1',draft_version=int(max_version_draft)+1)
            Playlist.save()
            traffic = 0
            record_inserted = 0
            var_test = 0
            for i in range(int(number_of_windows)):
                if request.POST.get('numofavails['+str(i)+']'):
                    numofavails = request.POST.get('numofavails['+str(i)+']')
                    window_start = request.POST.get('windowstart['+str(i)+']')
                    window_start = daydate+' '+window_start+':00'
                    window_end = request.POST.get('windowend['+str(i)+']')
                    window_end = daydate+' '+window_end+':00'
                    FMT = '%Y-%m-%d %H:%M:%S'
                    window_duration = datetime.datetime.strptime(window_end, FMT) - datetime.datetime.strptime(window_start, FMT)
                    window_duration = datetime.datetime.strptime(str(window_duration), '%H:%M:%S')
                    window_duration = window_duration.strftime('%H%M%S00')
                    Window = Windows(id_playlist_id=Playlist.id_playlist, window_start=window_start, window_end=window_end, window_duration=window_duration )
                    Window.save()

                    for j in range(int(numofavails)):
                        if request.POST.get('availstart['+str(i)+']['+str(j)+']'):
                            av_start = request.POST.get('availstart['+str(i)+']['+str(j)+']')
                            av_start = daydate+' '+av_start+':00'
                            number_of_ads = request.POST.get('numofads['+str(i)+']['+str(j)+']')
                            Avail = Avails(id_window_id=Window.id_window, avail_start=av_start, availinwindow=str(j+1), datetime=now )
                            Avail.save()

                            for k in range(int(number_of_ads)):
                                if request.POST.get('ad['+str(i)+']['+str(j)+']['+str(k)+']'):
                                    adspot = request.POST.get('ad['+str(i)+']['+str(j)+']['+str(k)+']')
                                    traffic +=1
                                    AdspotsInAv = AdspotsInAvail(id_avail_id=Avail.id_avail, id_adspot_id=adspot, positioninavail=str(k+1), trafficid=traffic)
                                    AdspotsInAv.save()
                                    record_inserted = 1
            last_id_playlist = Playlist.id_playlist
            # xmlfilename = GenerateXMLfromDatabase(daydate, channel_id, channel_zone.id_zone_channel, str(new_version) )
            # path_inftp = channeldata.ftp_channel_name+'/schedules/'+channel_zone.region
            # uploadFTP(useraccess.ftp_server, useraccess.ftp_port, useraccess.ftp_user, useraccess.ftp_password, xmlfilename , path_inftp)

            ############ Display data on view_playlist_id page ##################
            return redirect('draft_playlist')


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, 'DAIManagementApp/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, 'DAIManagementApp/predict.html',{'channels': channels})

def predict(val ):
    import datetime
    import pandas as pd
    import numpy as np
    import calendar
    from statsmodels.tsa.arima_model import ARIMA
    df = pd.DataFrame(val , columns = ['Day','Minute','Purcent'])
    #df['Datetime'] = df['Day'] + ' ' + df['Minute']
    week = []
    for i in df['Day']:
        week.append(calendar.day_name[datetime.datetime.strptime(str(i), '%Y-%m-%d').weekday()])
    day = datetime.datetime.now()
    day_week = calendar.day_name[day.weekday()-1]
    df['Day_Week'] = week

    df_final  = df.loc[ df['Day_Week']==day_week  ]
    df_final.index = range(0,len(df_final['Minute']))
    from sklearn.preprocessing import PolynomialFeatures
    from sklearn.pipeline import make_pipeline
    from sklearn.linear_model import LinearRegression
    degree=8
    polyreg=make_pipeline(PolynomialFeatures(degree),LinearRegression())
    X= np.array(df_final.index).reshape(-1,1)
    y= np.array(df_final['Purcent']).reshape(-1,1)
    polyreg.fit(X,y)
    predict = polyreg.predict(X)
    df_final.drop_duplicates(subset=['Minute'])

    day += datetime.timedelta(days=6)
    label = df['Minute']
    for i in range(0,len(predict)) :
        line = Sfr_predict( sfr_channel_name = '2M Maroc',day = day , minute=label[i] , purcent=abs(predict[i]))
        line.save()

    return (label , predict)


def predict_adbreak(request):
    import pandas as pd
    import calendar
    import datetime
    channels= AdbreakHistory.objects.all().values_list('id_channel').distinct()
    channels = [i[0] for i in channels ]
    for channel in channels:
        adbreak = AdbreakHistory.objects.filter(id_channel=channel).values_list('channel_name','day','time','duration')
        df = pd.DataFrame(adbreak , columns=['Channel','Day','Time','Duration'])
        week = []
        for i in df['Day']:
                week.append(calendar.day_name[datetime.datetime.strptime(str(i), '%Y-%m-%d').weekday()])
        df['week'] = week
        dur =[float(i)for i in df['Duration']]
        df['Duration']= dur
        time = [x[:-3] for x in df['Time']]
        t=[]
        for i in time :
            m = int(i[3:]) - int(i[3:])%30
            if len(str(m)) ==1:
                m =str(m)+'0'
            t.append(i[:3]+str(m))
        df['Time'] = t
        #print(type(day))
        day = datetime.datetime.strptime('2021-10-17','%Y-%m-%d')
        day_week = calendar.day_name[day.weekday()]
        df2 = df.loc[df['week'] == day_week]

        all_time = list(df2['Time'])
        time_ad = set(all_time)
        count = [ { 'time' :i , 'count' : all_time.count(i) }  for i in time_ad]
        fact = [ { 'time' :i['time'] , 'fact' : i['count'] / 3 }  for i in count]
        #day = datetime.datetime.now()
        day += datetime.timedelta(days=7)

        for i in fact :
                if i['fact'] >= 0.6 :
                    df3 = df2.loc[df2['Time'] == i['time']]
                    mean = sum(df3['Duration'])/len(df3['Duration'])
                    mean = round(mean,2)
                    intervale = int(30 // round(i['fact']))
                    for j in range(round(i['fact'])) :
                        minute =i['time'][3:]
                        minute = str(j*intervale +int(minute) )
                        if len(minute)==1:
                            minute ='0' + minute

                        time_  = i['time'].split(':')[0]+':' + minute
                        channel_id = Channels.objects.get(pk=channel)
                        adbreak_predict = AdbreaPredict(id_channel=1 ,channel_name =channel_id.channel_name , datetime=day,day=str(day).split(' ')[0] ,time =time_,duration=mean)
                        adbreak_predict.save()
        return render(request,'DAIManagementApp/index.html')








def add_agency(request):
    if request.method == 'POST':
        name_agency = request.POST.get('name_agency')
        description_agency = request.POST.get('description_agency')
        now = datetime.datetime.now()
        agency = Agency(id_user_id=request.session['id_user'],name=name_agency,description=description_agency,datetime=now,is_deleted=0)
        agency.save()
        now = datetime.datetime.now()
        activite = 'Add  Agency'
        desc = 'Admin Add  Agency  id: ' + str(agency.id_agency)
        activity = Activity(activity=activite , date=now ,description=desc )
        activity.save()
    return render(request, "DAIManagementApp/add_agency.html")


def edit_agency(request , id_agency=""):
    if request.method == 'POST':
        agency = Agency.objects.get(pk=id_agency)

        data = {
            'agency':agency
        }
        return render(request , 'DAIManagementApp/edit_agency_id.html',{'data':data})
    agency = Agency.objects.filter(id_user=request.session['id_user'],is_deleted=0)
    return render(request , 'DAIManagementApp/edit_agency.html',{'agency':agency})


def update_agency(request,id_agency=''):
    if request.method == 'POST':
        agency = Agency.objects.get(pk=id_agency)
        # Part 1
        name = request.POST.get('name_agency')
        description = request.POST.get('description_agency')


        agency.name=name
        agency.description=description

        agency.save()
        activite = 'Edit  Agency'
        desc = 'Admin Edit  Agency  id: ' + str(agency.id_agency)
        activity = Activity(activity=activite , date=now ,description=desc )
        activity.save()

        agency = Agency.objects.filter(id_user=request.session['id_user'])
        return render(request,'DAIManagementApp/edit_agency.html',{'agency': agency})


def delete_agency(request,id_agency=''):
    if request.method == 'POST':
        agency = Agency.objects.get(pk=id_agency)
        print(agency.is_deleted)
        agency.datetime = datetime.datetime.now()
        agency.is_deleted = 1
        agency.save()
        activite = 'Delete   Agency'
        desc = 'Admin Delete  Agency  id: ' + str(agency.id_agency)
        activity = Activity(activity=activite , date=now ,description=desc )
        activity.save()

    # jingles = Jingles.objects.filter(id_channel__in=channels,is_delete=0)
    agency = Agency.objects.filter(id_user=request.session['id_user'],is_deleted=0)

    return render(request,'DAIManagementApp/delete_agency.html', {'agency': agency})



def theme_mode(request):
    current_mode = request.GET.get('current_mode')
    if current_mode == 'light':
        body_class_theme = ''
    else:
        body_class_theme = 'dark-mode'

    request.session['theme_mode'] = body_class_theme
    return HttpResponse('')

def func_predict():
    schedule.every().day.at('00:00').do(predict_adbreak)
    # schedule.run_pending()
    while True:
        schedule.run_pending()
        time.sleep(1)

def genExcel(request,id_campaign):
    import xlsxwriter
    import pandas as pd
    import datetime
    info = Campaigns.objects.get(pk=id_campaign)
    now = datetime.datetime.now()

    path = 'excel/user_'+request.session['id_user']
    if not os.path.exists(path):
        os.makedirs(path)
    path+= '/'+info.name+now.strftime('%d_%m_%Y')+'.xlsx'

    response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = "attachment; filename="+info.name+now.strftime('%d_%m_%Y')+'.xlsx'
    workbook = xlsxwriter.Workbook(response, {'in_memory': True})
    worksheet = workbook.add_worksheet()

    cell_format = workbook.add_format()

    cell_format.set_pattern(1)  # This is optional when using a solid fill.
    cell_format.set_bg_color('#acbbfe')

    info = Campaigns.objects.get(pk=id_campaign)

    worksheet.write(1, 1,'Adtlas Reporting | ' +info.name+ ' cash [ ' +info.start_day + ' to ' + info.end_day + ' ] ' )

    ###################################################
    #############           INFO           ############
    ###################################################

    worksheet.write(7, 1, 'INFO' , cell_format)

    worksheet.write(8, 1, 'Campaign Name')
    worksheet.write(8, 2, info.name)

    worksheet.write(9, 1, 'Agency')
    worksheet.write(9, 2, info.id_brand.id_agency.name)

    worksheet.write(10, 1, 'Advertiser')
    worksheet.write(10, 2, info.id_brand.brand_name)

    worksheet.write(11, 1, 'Creative')
    worksheet.write(11, 2, info.id_adpost.adspot_name)

    #####################################################
    #############           PERIOD           ############
    #####################################################
    worksheet.write(13, 1, 'PERIOD' ,cell_format)

    worksheet.write(14, 1, 'Start date')
    worksheet.write(14, 2, info.start_day)

    worksheet.write(15, 1, 'End date')
    worksheet.write(15, 2, info.end_day)

    #####################################################
    #############           BOOKED           ############
    #####################################################

    worksheet.write(17, 1, 'BOOKED' ,cell_format)

    worksheet.write(18, 1, 'Volume')
    worksheet.write(18, 2, info.volume)

    worksheet.write(19, 1, 'Pacing')
    worksheet.write(19, 2, info.pacing == True)

    worksheet.write(20, 1, 'CPM')
    worksheet.write(20, 2, info.cpm)

    ########################################################
    #############           PLACEMENT           ############
    ########################################################

    worksheet.write(22, 1, 'PLACEMENT',cell_format)

    worksheet.write(23, 1, 'General Retation')
    worksheet.write(23, 2, info.general_rotation == 1)

    placement_channel = Placement.objects.filter(id_campaign=id_campaign).values_list('id_channel')
    placement_channel = [x[0] for x in list(placement_channel)]
    channels = Channels.objects.filter(id_channel__in =placement_channel ).values_list('channel_name', 'sfr_channel_name')
    channels_name = [i[0] for i in channels]
    channels_sfr = [i[1] for i in channels]
    worksheet.write(24, 1, 'Channels')
    worksheet.write(24, 2, str(channels_name).replace('\'' , ''))

    ########################################################
    #############           Tables              ############
    ########################################################
    worksheet.write(7, 6, 'CHANNEL' ,cell_format)
    worksheet.write(7, 7, 'REGION',cell_format)
    worksheet.write(7, 8, 'DAY',cell_format)
    worksheet.write(7, 9, 'MINUTE',cell_format)
    worksheet.write(7, 10, 'IMPRESSIONS',cell_format)

    name = info.id_adpost.adspot_name
    verifs = Verifs.objects.filter(spotId=name ,airStatuscode='0001').values_list('networkname','zonename','airTime')
    df= pd.DataFrame(verifs,columns=['Channel','Region','Time'])
    region = [ ChannelsZone.objects.get(zonename=i).region for i in df['Region']]
    channels = [Channels.objects.get(channel_name=i[0]).sfr_channel_name for i in df[['Channel','Region']].values ]
    df['Channel'] = channels
    df['Region'] = region

    day = [i.split(' ')[0] for i in df['Time']]
    minute = [i.split(' ')[1] for i in df['Time']]

    df['Day']=day
    df['Minute']=minute
    df.drop('Time' , axis='columns',inplace=True)
    df['Minute'] = [i[:-2]+'00' for i in df['Minute']]

    purcent=[]

    for i in df.values:
        purcent.append(SfrAnalytics.objects.get(sfr_channel_name=i[0],region=i[1],day=i[2],minute=i[3]).purcent)



    purcent = [float(i) for i in purcent]

    df['Purcent'] = purcent



    imp = Impressions.objects.get(pk='1')

    purcent = [float(i) * float(imp.total_users) / float(imp.market_share_purcent) for i in df['Purcent']]

    df['Purcent'] = purcent


    j=0
    for i in df.values:
        worksheet.write(8+j, 6, i[0])
        worksheet.write(8+j, 7, i[1])
        worksheet.write(8+j, 8, i[2])
        worksheet.write(8+j, 9, i[3])
        worksheet.write(8+j, 10, i[4])
        j+=1

    worksheet.write(7, 13, 'TOTAL IMPRESSIONS',cell_format)
    worksheet.write(8, 13, int(sum(purcent)))

    workbook.close()
    return response



def test_rest(request , channel="", start_at="", duration=""):
    print("channel:"+channel)
    print("start_at:"+start_at)
    print("duration:"+str(duration))
    alldata = "channel:"+channel+"start_at:"+start_at+"duration:"+str(duration)


    return HttpResponse(alldata)

# def realtime_filter(id_channel,start_at,duration):#start_at
#     # realtime=RealTimeAdbreak.objects.filter(id_channel=id_channel,start_at=start_at,duration=duration)
#     if int(duration)<30:
#         window=Windows.objects.filter(window_start=start_at)
#         window_start_day=window.window_start.split()[0]
#         window_start_time=window.window_start.split()[1]
#         window_end_time=window.window_end.split()[1]
#         # print('Window end: ',window_end_time)
#         # print("Windos start: ",window_start_day)
#         realtime_start_day=start_at.split()[0]
#         realtime_start_time=start_at.split()[1]
#         # print('type: ',type(window_start_day))
#         # print(type(realtime_start_day))
#         if realtime_start_day ==window_start_day:
#             if window_start_time < realtime_start_time and window_end_time > realtime_start_time:
#                 print("Yes the adbreal is indsid the window")
#                 # Now we can edite the Playlist
#             else:
#                 print("No the adbreak is out of window")
#         else:
#             print(f"There No window Aailable in {realtime_start_day}")
#             # return JsonResponse({'Error message':f"There No window Aailable in {realtime_start_day}"})
#
#         # 'window start':window.window_start,'start_abreak':realtime.start_at})


def test_rest(request , channel="", start_at="", duration=""):
    print("channel:"+channel)
    print("start_at:"+start_at)
    print("duration:"+str(duration))
    alldata = "channel:"+channel+"start_at:"+start_at+"duration:"+str(duration)


# def realtime_filter(id_channel,start_at,duration):#start_at
#     # realtime=RealTimeAdbreak.objects.filter(id_channel=id_channel,start_at=start_at,duration=duration)
#     if int(duration)<30:
#         window=Windows.objects.filter(window_start=start_at)
#         window_start_day=window.window_start.split()[0]
#         window_start_time=window.window_start.split()[1]
#         window_end_time=window.window_end.split()[1]
#         # print('Window end: ',window_end_time)
#         # print("Windos start: ",window_start_day)
#         realtime_start_day=start_at.split()[0]
#         realtime_start_time=start_at.split()[1]
#         # print('type: ',type(window_start_day))
#         # print(type(realtime_start_day))
#         playlist=Playlists.objects.get(id_channel=id_channel).last()
#         window=Windows.objects.filter(id_playlist__in=playlist).get(window_start__lte=start_at, window_end__gte=start_at)
#         if window:
#
#             print("Yes the adbreal is indsid the window")
#             # Now we can edite the Playlist
#             preview_window_end = window.window_end
#
#             window.window_end = start_at
#             window_duration = datetime.datetime.strptime(window.window_end, FMT) - datetime.datetime.strptime(window.window_start, FMT)
#             window_duration = datetime.datetime.strptime(str(window_duration), '%H:%M:%S')
#             window_duration = window_duration.strftime('%H%M%S00')
#             window.window_duration = window_duration
#
#             from datetime import timedelta
#             start_at += timedelta(days=0, hours=0, minutes=2)
#
#             new_window_duration = datetime.datetime.strptime(start_at, FMT) - datetime.datetime.strptime(preview_window_end, FMT)
#             new_window_duration = datetime.datetime.strptime(str(new_window_duration), '%H:%M:%S')
#             new_window_duration = new_window_duration.strftime('%H%M%S00')
#             new_window = Windows(id_playlist_id=playlist.id_playlist, window_start=start_at, window_end=preview_window_end, window_duration=new_window_duration )
#             new_window.save()
#
#             channels = Channels.objects.get(id_channel=playlist.id_channel.id_channel)
#             region = ChannelsZone.objects.get(id_zone_channel=playlist.id_zone_channel.id_zone_channel)
#             zonename = region.zonename
#             networkname = channels.networkname
#             broadcastdate = playlist.broadcastdate.replace("-","")
#
#             verifs_result = Verifs.objects.filter(networkname=networkname,zonename=zonename,broadcastDate=broadcastdate).last()
#
#         else:
#             print(f"There No window Aailable in {realtime_start_day}")
#             # return JsonResponse({'Error message':f"There No window Aailable in {realtime_start_day}"})
#
#         # 'window start':window.window_start,'start_abreak':realtime.start_at})



# def realtime_adbreak(request,id_channel,start_at,duration):
#     from django.http import JsonResponse
#     start=str(start_at.replace('_',' '))
#
#
#     realtime=RealTimeAdbreak(id_channel=id_channel,start_at=start,duration=duration)
#     realtime.save()
#
#     realtime_filter(id_channel=realtime.id_channel,start_at=realtime.start_at,duration=realtime.duration)
#     return JsonResponse({
#         'Message':'Successfully Created',
#         'channel':id_channel,
#         'start_at':start,
#         'duration':duration})

def realtime_filter(id_channel,start_at,duration):#start_at
    FMT = '%Y-%m-%d %H:%M:%S'
    if int(duration)<30:
        playlist=Playlists.objects.filter(id_channel=id_channel)
        window=Windows.objects.filter(window_start__lte=start_at, window_end__gte=start_at).filter(id_playlist__in=playlist).last()
        my_channel = Channels.objects.get(id_channel=id_channel)
        send_msg_telegram(my_channel.channel_name,start_at,duration)
        if (window):
            print("I'm heeeere")
            print(window.id_playlist.id_playlist)
            preview_window_end = window.window_end
            from datetime import timedelta
            print(start_at)
            #start_at += timedelta(days=0, hours=0, minutes=-2)
            start_at = datetime.datetime.strptime(start_at, "%Y-%m-%d %H:%M:%S") - datetime.timedelta(minutes=2)

            window.window_end = start_at
            window_duration = datetime.datetime.strptime(str(window.window_end), FMT) - datetime.datetime.strptime(str(window.window_start), FMT)
            window_duration = datetime.datetime.strptime(str(window_duration), '%H:%M:%S')
            window_duration = window_duration.strftime('%H%M%S00')
            window.window_duration = window_duration
            window.save()
            print(window)
            start_at =  start_at + datetime.timedelta(minutes=4)
            # start_at = datetime.datetime.strptime(start_at, '%Y-%m-%d %H:%M:%S') + datetime.timedelta(minutes=2)

            new_window_duration = datetime.datetime.strptime(str(preview_window_end), FMT) - datetime.datetime.strptime(str(start_at), FMT)
            print("start_at"+str(start_at))
            print("preview_window_end"+str(preview_window_end))
            print("new window duration "+str(new_window_duration))
            new_window_duration = datetime.datetime.strptime(str(new_window_duration), '%H:%M:%S')
            new_window_duration = new_window_duration.strftime('%H%M%S00')
            new_window = Windows(id_playlist_id=window.id_playlist.id_playlist, window_start=start_at, window_end=preview_window_end, window_duration=new_window_duration )
            new_window.save()
            print(new_window)

            # Get the id of the adbreaks that are not Aired yet
            from django.db import connection
            cursor = connection.cursor()

            broadcastDateForQuery = str(window.id_playlist.broadcastdate).replace("-","")
            id_windowForQuery = window.id_window

            queries = """
                              SELECT Adspots_in_avail.id_avail FROM Avails
                              left join Adspots_in_avail on Adspots_in_avail.id_avail = Avails.id_avail
                              left join Verifs on ( Verifs.broadcastDate = %s and Verifs.trafficId = Adspots_in_avail.trafficId )
                              where ( Adspots_in_avail.positionInAvail = 1 and Verifs.airStatusCode <> 0001 ) and  Avails.id_window= %s
                          """

            data_tuple=(broadcastDateForQuery,id_windowForQuery)
            cursor.execute(queries,data_tuple)
            row = cursor.fetchall()
            if new_window:
                for r in row:
                    print(r[0])
                    avail = Avails.objects.get(id_avail=r[0])
                    pprint(avail)
                    avail.id_window = new_window
                    avail.avail_start = new_window.window_start
                    print("the new avail winodws : "+str(avail.id_window))
                    avail.save()



def realtime_adbreak(request,id_channel,start_at,duration):
    from django.http import JsonResponse
    start=str(start_at.replace('_',' '))
    realtime=RealTimeAdbreak(id_channel=id_channel,start_at=start,duration=duration)
    realtime.save()

    realtime_filter(id_channel, start, duration)
    return JsonResponse({'channel':id_channel,
        'start_at':start,
        'duration':duration})






def send_adspot(DST_FOLDER ,SRC_FILEPATH , ) :
    import os
    import ftplib
    FTP_ADDR = "uk06.tmd.cloud"
    USERNAME = "testftp@epgsano.com"
    PASSWORD = "I?#=s3FfnSu_"

    """Transfer file to FTP."""

    # Connect
    print("Connecting to FTP...")
    session = ftplib.FTP(FTP_ADDR, USERNAME, PASSWORD)


    # Change to target dir
    chdir(session, dirpath=DST_FOLDER)

    # Transfer file
    print("Transferring %s and storing as %s..." % (os.path.basename(SRC_FILEPATH), SRC_FILEPATH))
    with open(SRC_FILEPATH, "rb") as file:
        session.storbinary('STOR %s' % os.path.basename(SRC_FILEPATH), file)

    print("Closing session.")
    # Close session
    session.quit()


def chdir(session, dirpath):
    """Change to directory."""
    if directory_exists(session, dirpath) is False: # (or negate, whatever you prefer for readability)
        print("Creating folder %s..." % dirpath)
        folders = dirpath.split('/')

        session.mkd(folders[0])
        session.mkd(folders[0]+'/'+folders[1])

    print("Changing to directory %s..." % dirpath)
    session.cwd(dirpath)


def directory_exists(session, dirpath):
    """Check if remote directory exists."""
    filelist = []
    session.retrlines('LIST',filelist.append)
    for f in filelist:
        if f.split()[-1] == dirpath and f.upper().startswith('D'):
            return True
    return False



import telegram # this is from python-telegram-bot package

from django.conf import settings
from django.template.loader import render_to_string
import requests
def send_msg_telegram(channel_name,start_at,duration):
    message = """
    ====================
    New Adbreak Detected:
    - channel: """+channel_name+"""
    - Time: ["""+start_at+"""]
    - Duration : ["""+duration+"""]
    ====================
    """
    telegram_settings = settings.TELEGRAM
    bot = telegram.Bot(token=telegram_settings['bot_token'])
    bot.send_message(chat_id="@%s" % telegram_settings['channel_name'],
        text=message, parse_mode=telegram.ParseMode.HTML)



def directory_exists(session, dirpath):
    """Check if remote directory exists."""
    filelist = []
    session.retrlines('LIST',filelist.append)
    for f in filelist:
        if f.split()[-1] == dirpath and f.upper().startswith('D'):
            return True
    return False

def statscreative(request ):
    print('jjjjjjjjjjjjjjjjjjjjjjjjjj')
    from django.db.models import Count
    channel = Channels.objects.filter(id_user=request.session['id_user']).values_list('channel_name')
    channel = [ch[0] for ch in channel]
    adspot = Verifs.objects.filter(networkname__in = channel ,    airStatuscode='0001' ).values('spotId').annotate(dcount=Count("spotId")).order_by('-dcount')
    import random
    chars = '0123456789ABCDEF'
    color = ['#'+''.join(random.sample(chars,6)) for i in range(len(adspot))]
    label =[]
    data = []

    for ad in adspot :
        label.append(ad['spotId'])
        data.append(ad['dcount'])
    print(label)
    print(data)
    verifs = {  'lebel' : label ,
                'data'  : data ,
                'color' : color
            }
    brands = ads_brand(request.session['id_user'] )
    return render(request,'DAIManagementApp/stats_creative.html',{'verifs':verifs  , 'brands':brands})

def ads_brand(session):
    from django.db.models import Count
    import random
    channel = Channels.objects.filter(id_user=session)
    ads = Adspots.objects.filter(id_channel__in = channel).values('id_brand').annotate(count = Count('id_brand')).order_by('-count')
    chars = '0123456789ABCDEF'
    color = ['#'+''.join(random.sample(chars,6)) for i in range(len(ads))]
    label = []
    data = []

    for ad in list(ads ):
        label.append(Brands.objects.get(pk=ad['id_brand']).brand_name)
        data.append(round(ad['count'] * 100 / 23,2 ))

    brand  =     verifs = {  'label' : label ,
                'data'  : data ,
                'color' : color
            }

    return brand

def statscampaign(request):
    campaign = Campaigns.objects.filter(id_user=request.session['id_user'] ).values_list('name','volume').order_by('-volume')
    pacing = campaign_pacing(request)
    brands = campign_brand(request)
    active = campaign_avtive(request)
    cpms = Campaigns.objects.filter(id_user=1).order_by('-cpm')[:4]
    return render(request ,'DAIManagementApp/stats_campaign.html',{'pacing' : pacing , 'active':active  , 'brands':brands , 'cpms' : cpms })

def campaign_pacing(request):
    pacing = Campaigns.objects.filter(id_user=1).values_list('pacing')
    pacing = [p[0] for p in pacing ]

    result = { 'true'  : pacing.count(True) ,
               'false' : pacing.count(False) ,
                'purcent' : pacing.count(True) * 100 / len(pacing)
            }
    return result

def campaign_avtive(request):
    from datetime import datetime
    import random
    etat = Campaigns.objects.filter(id_user = request.session['id_user'] ).exclude(start_day__exact='' ).exclude(end_day__exact='').values_list('start_day','end_day' )

    etat = [(datetime.strptime(date[0], '%Y-%m-%d'),datetime.strptime(date[1], '%Y-%m-%d')) for date in etat ]
    now = datetime.now()
    finished =0
    not_start = 0
    start = 0
    for date in etat :
        if now < date[0]:
            not_start += 1
        elif now < date[0] :
            start += 1
        else :
            finished += 1
    data = [finished , start , not_start ]
    chars = '0123456789ABCDEF'
    color = ['#'+''.join(random.sample(chars,6)) for i in range(3)]
    label = ['Finished' , 'In progress' , 'Not started']

    result = {
        'label' : label ,
        'data'  : data ,
        'color' : color
    }

    return result

def campign_brand(request):
    from django.db.models import Count
    import random

    brand = Campaigns.objects.filter(id_user=request.session['id_user']).values('id_brand').annotate(dcount=Count("id_brand")).order_by('-dcount')
    data = []
    label = []
    chars = '0123456789ABCDEF'
    color = ['#'+''.join(random.sample(chars,6)) for i in range(len(brand))]


    for i in brand :
        label.append(Brands.objects.get(pk=i['id_brand']).brand_name)
        data.append(i['dcount'])

    result = {
        'label' : label ,
        'data'  : data ,
        'color' : color
    }

    return result


def statsbrands(request):

    from django.db.models import Count
    ch = Channels.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 ,'DAIManagementApp/stats_brands.html', { 'brands' : brands}   )

    def brand_adspot():
        from django.db.models import Count
        ch = Channels.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(adspot_name__in = v,id_channel__in = ch ).values('id_brand').annotate(dcount=Count("id_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'])

def statschannels(request):
    from django.db.models import Count
    import random
    channels = Channels.objects.filter(id_user=request.session['id_user'])
    zone = ChannelsZone.objects.filter(id_channel__in =channels).values('region').annotate(dcount=Count("id_channel")).order_by('-dcount')

    label = [region['region'] for region in zone]
    data = [data['dcount'] for data in zone]

    chars = '0123456789ABCDEF'
    color = ['#'+''.join(random.sample(chars,6)) for i in range(len(zone))]
    channels={
        'label':label,
        'data' : data,
        'color': color
    }
    ads=channels_ads(request)
    adbreak = channels_adbreak(request)
    sfr = channels_sfr(request)
    return render(request,'DAIManagementApp/stats_channels.html',{'channels':channels , 'ads':ads , 'adbreak':adbreak , 'sfr':sfr})

def channels_ads(request):
    from django.db.models import Count
    import random
    channels = Channels.objects.filter(id_user=request.session['id_user'])
    ads = Adspots.objects.filter(id_channel__in = channels).values('id_channel').annotate(dcount=Count('id_channel')).order_by('-dcount')
    chars = '0123456789ABCDEF'
    color = ['#'+''.join(random.sample(chars,6)) for i in range(len(ads))]
    label= []
    data=[]
    for ad in ads :
        label.append(Channels.objects.get(id_channel=ad['id_channel']).channel_name)
        data.append(ad['dcount'])

    result={
        'label' : label ,
        'data'  : data ,
        'color' : color
    }

    return result

def channels_adbreak(request):
    import random
    import pandas as pd
    from django.db.models import Count
    channels = Channels.objects.filter(id_user=request.session['id_user']).values_list('channel_name')
    channels = [channel[0] for channel in channels]
    data = AdbreakHistory.objects.filter(channel_name__in=channels).values_list('channel_name','day','time')
    df = pd.DataFrame(data,columns=['Channel' , 'Day' ,'Time'])

    label = []
    data = []
    channels = list(set(df['Channel']))
    chars = '0123456789ABCDEF'
    color = ['#'+''.join(random.sample(chars,6)) for i in range(len(channels))]
    for channel in channels :
        df2 = df.loc[df['Channel']==channel].groupby(by=['Channel','Day']).count()
        data.append(min(df2['Time']))
        label.append(channel)
    result={
        'label' : label ,
        'data'  : data ,
        'color' : color
    }

    return result
def channels_sfr(request):
    import random
    import pandas as pd

    channels = channels = Channels.objects.filter(id_user=request.session['id_user']).values_list('sfr_channel_name')
    channels = [channel[0] for channel in channels]

    sfr = SfrAnalytics.objects.filter(sfr_channel_name__in = channels).values_list('sfr_channel_name' , 'purcent')

    df = pd.DataFrame(sfr,columns=['Channel','Purcent'])
    purcent  = [float(i) for i in df['Purcent']]
    df['Purcent'] = purcent
    total_users = int(Impressions.objects.get(pk=2).total_users)
    market_share_purcent = float(Impressions.objects.get(pk=2).market_share_purcent)

    df2 =  df.groupby(by=['Channel']).mean()
    label = list(df2.index)
    data = [i[0]*total_users*market_share_purcent for i in df2.values ]

    chars = '0123456789ABCDEF'
    color = ['#'+''.join(random.sample(chars,6)) for i in range(len(data))]
    result={
        'label' : label ,
        'data'  : data ,
        'color' : color
    }

    return result

def generate_verifs(request):

    if request.method == "POST":
        import pandas as pd
        from django.db import connection
        cursor = connection.cursor()
        channels = request.POST.get('channels')
        adspots = request.POST.get('adspots')
        start_day = request.POST.get('start_day')
        end_day = request.POST.get('end_day')
        print(start_day)
        print(end_day)
        start_day = str(start_day).replace('-','')
        end_day = str(end_day).replace('-','')
        q = """
            select * from Verifs
            LEFT JOIN SFR_analytics on SUBSTRING(Verifs.airTime, 12, 5) = SUBSTRING(SFR_analytics.`minute`, 1, 5) and SFR_analytics.sfr_channel_name = '{}'
            where Verifs.spotId LIKE '%{}%' and Verifs.airStatusCode = '0001' and Verifs.broadcastDate > '{}' and Verifs.broadcastDate < '{}'
        """
        try :
            cursor.execute(q.format(channels,adspots,start_day,end_day))
            row = cursor.fetchall()
            print(row)
            df = pd.DataFrame(row)
            df2 = df[[0,12,14,3,4,5,6,7,8,15,16,17,18]]
            df2.columns = ['id_verifs','Channel','Region','broadcastdate','trafficId','spotId','airTime','airLenght','airStatus','cible','indicateur','minute','purcent']

            with BytesIO() as b:
                # Use the StringIO object as the filehandle.
                writer = pd.ExcelWriter(b, engine='xlsxwriter')
                df2.to_excel(writer,index = False)
                writer.save()
                # Set up the Http response.
                filename = start_day+'_'+adspots+'.xlsx'
                response = HttpResponse(
                    b.getvalue(),
                    content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
                )
                response['Content-Disposition'] = 'attachment; filename=%s' % filename
                return response

        except :
            print("===================================================")
            print("===================================================")
            print("===================================================")

    channels = Channels.objects.filter(id_user = request.session['id_user'])

    adspots = Adspots.objects.filter(id_channel__in = channels).values_list('adspot_name')
    adspots = [ads[0] for ads in adspots]
    channels = [ch.sfr_channel_name for ch in channels ]

    print(channels)
    return render(request,'DAIManagementApp/generate_verifs.html',{'adspots':adspots , '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 , "DAIManagementApp/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 charts(request):
    pass
def charts_test(request):
    channels = Channels.objects.filter(id_user=request.session['id_user'])
    return render(request,'DAIManagementApp/charts.html',{'channels':channels})

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)
def adspost_avail():
    from datetime import datetime
    now = datetime.now()
    campaigns = Placement.objects.filter(id_channel=1).values_list('id_campaign')

    campaigns = [i[0] for i in campaigns ]
    adspot = Campaigns.objects.filter(id_campaign__in = campaigns , end_day__lt=now).values_list('id_adpost').distinct()
    adspot = [i[0] for i in adspot]

    avail = AdspotsInAvail.objects.filter(id_adspot__in=adspot)
    avail = [av[0] for av in avail]

def channels_campaigns(request):
    from datetime import datetime
    now = datetime.now()
    channel = request.GET.get('channel')
    campaigns = Placement.objects.filter(id_channel=channel).values_list('id_campaign')
    channel = Channels.objects.get(id_channel=request.GET.get('channel'))
    campaigns = [i[0] for i in campaigns ]
    campaigns = Campaigns.objects.filter(id_campaign__in = campaigns , end_day__lt=now)
    data = {"campaigns":campaigns}
    return render(request,'DAIManagementApp/dropdown_compaigns.html',{"campaigns":campaigns})


def delete_avail(request):
    from datetime import datetime ,timedelta
    from iteration_utilities import deepflatten

    channel2 = request.GET.get('channel')
    playlist = request.GET.get('playlist')
    region =  request.GET.get('region')
    result = []
    channel = Channels.objects.get(id_channel=channel2)
    print(channel)
    channel_id = int(channel2)
    channel_networkname = channel.networkname
    now = datetime.now()

    if playlist != None:
        now = datetime.strptime(playlist,   '%Y-%m-%d')

    broadcast_playlist = now.strftime('%Y-%m-%d')
    broadcast_verif = now.strftime('%Y%m%d')
    n = now.strftime('%Y-%m-%d')


    print(channel)
    print(type(channel))
    print(region)
    data = Playlists.objects.last()
    print(data)
    playlists=[]
    # if region != None :
    #     region =ChannelsZone.objects.get(zonename=region)
    #
    #     playlists = Playlists.objects.filter(id_zone_channel=region)
    # if channel != None :
    #     channel = Channels.objects.get(id_channel=channel)
    #     if len(playlists)==0:
    #         playlists = Playlists.objects.filter(id_channel=channel)
    #     else :
    #         playlists = playlists.filter(id_channel=channel)
    # if playlist != None:
    #     now = datetime.strptime(playlist,   '%Y-%m-%d')
    #     n= now.strftime('%Y-%m-%d')
    #     t = datetime.strptime(n,'%Y-%m-%d') +timedelta(hours=4)
    #     #data = data.filter(start_date__gte=playlist, end_date__lt=playlist1 )
    # else :
    #     now = datetime.now()
    #     n= now.strftime('%Y-%m-%d')
    #     t = datetime.strptime(n,'%Y-%m-%d') +timedelta(hours=4)
    print(playlists)

    from django.db import connection
    cursor = connection.cursor()


    queries = """
                    SELECT Avails.id_avail FROM Windows
                    left join Playlists on Playlists.id_playlist = Windows.id_playlist
                    LEFT JOIN Avails on Avails.id_window = Windows.id_window
                    LEFT JOIN Adspots_in_avail on Adspots_in_avail.id_avail = Avails.id_avail
                    LEFT JOIN Verifs on (Verifs.trafficId = Adspots_in_avail.trafficId and Verifs.broadcastDate =  %s and Verifs.networkname = %s)
                    where Verifs.airStatusCode = "1005" and Playlists.id_channel = %s and Playlists.id_playlist in (Select max(id_playlist) from Playlists where Playlists.broadcastdate = %s group by id_zone_channel order by Playlists.version desc) group by Avails.id_avail limit 3
                  """
    print(queries)
    data_tuple=(broadcast_verif, channel_networkname, channel_id, broadcast_playlist )
    cursor.execute(queries,data_tuple)
    row = cursor.fetchall()
    print("row")
    print(row)


    # windows = Windows.objects.filter(window_start__gte=now,window_end__lt=t).select_related('id_playlist').filter(broadcastDate = n).last().order_by('window_start')[:3]
    # windows = Windows.objects.filter(window_start__gte=now,window_end__lt=t,id_playlist__in=playlists).order_by('window_start')[:3]

    # if len(playlists) > 0 :
    #     windows = Windows.objects.filter(window_start__gte=now,window_end__lt=t,id_playlist__in=playlists).order_by('window_start')[:3]
    # else :
    #     windows = Windows.objects.filter(id_playlist=data).order_by('window_start')[:3]

    for id_avs in row:
        for id_av in id_avs:
            avail = Avails.objects.get(pk=id_av)
            av={}
            av['id_avail'] = avail.id_avail
            av['start'] = avail.avail_start
            av['adspot'] = ""
            adspots = AdspotsInAvail.objects.filter(id_avail=avail)
            av['somme'] = 0
            av['duration' ]=""
            for adspot in adspots :
                av['adspot'] += " / " + adspot.id_adspot.adspot_name
                av['duration'] +=  " / " + str(adspot.id_adspot.duration)
                av['somme'] += int(adspot.id_adspot.duration)

                result.append(av)


    return render(request,'DAIManagementApp/avails_details.html' ,{'avails':result, 'queries':row})



def load_charts(request):

    channel_id = request.GET.get('channel')
    region = request.GET.get('region')
    daydate = request.GET.get('daydate')

    # ====================================
    # ====================================
    #code from index to clean
    if (daydate != None):
        day = daydate
    else:
        day =  '2021-09-06'

    if (region != None):
        zonename = ChannelsZone.objects.get(zonename=region).region
        print(zonename)
    else:
        zonename = "France"

    day = datetime.datetime.strptime(day, '%Y-%m-%d')
    dayformat = day.strftime('%Y-%m-%d')
    #user = 1 ==> make it dynamique
    if (channel_id != None):
        channels_sfr = list(Channels.objects.filter(id_user=request.session['id_user'], id_channel = channel_id).values_list("sfr_channel_name",flat=True))
    else:
        channels_sfr = list(Channels.objects.filter(id_user=request.session['id_user']).values_list("sfr_channel_name",flat=True))

    val = Sfr_analytics.objects.filter(sfr_channel_name__in=channels_sfr, region=zonename ,day=str(dayformat))
    result = []
    channel_sfr=[]
    purcent = Impressions.objects.get(pk='1') # change the name impressions to SFR_market_share
    for channel in channels_sfr :
        res= val.filter(sfr_channel_name=channel).values_list('minute','purcent')
        if len(res)>0:

            nb  =  float(purcent.total_users) / float(purcent.market_share_purcent)
            labels  = [x[0] for x in res ]
            data  = [int(float(x[1])*nb) for x in res]
            result.append( data)
            channel_sfr.append(channel)
    import random

    color = colors

    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",broadcastDate="20211024")
    # lines  = Verifs.objects.all()
    data2 = []

    for line in lines:
        if line.networkname in channels :
            p ={
                'channel':line.networkname,
                'name' : line.spotId,
                'day' : str(line.airTime).split(' ')[0],
            }

            region = ChannelsZone.objects.get(zonename =line.zonename)
            p['region'] = region.region
            min = str(line.airTime).split(' ')[1].split(':')
            minute = min[0]+':'+min[1]+':00'
            p['minute'] = minute
            p['color'] = '#00800000'
            if  str(line.airStatuscode) == '0001':
                p['status'] = 'Aired Successfully'
                p['color'] = '#2c2c8cb3'

                for i in Sfr_analytics.objects.filter(day=p['day'],minute=p['minute']):
                    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'] = int(nb)
            elif str(line.airStatuscode) == '0008':
                p['status'] = 'Failed, Adspot cut'
                p['nb_wach'] = '-'

            else :
                p['status'] = 'Failed, Other Reason'
                p['nb_wach'] = '-'

            data2.append(p)

    campaigns = len(Campaigns.objects.filter(id_user=request.session['id_user']))
    campaigns_active = len(Campaigns.objects.filter(id_user=request.session['id_user'],pacing=True))
    advertiser = most_advertisers(request)
    bookeds = booked_adbreaks(request)
    agences =active_agency(request)
    playlist = playlists(request)
    activites = Activity.objects.all().order_by('-id_activity')[0:5]
    channels = Channels.objects.filter(id_user=request.session['id_user'])
    #activites = []
    print("end of loadchart")
    return JsonResponse({'labels': labels, 'result':result, 'channels_sfr':channel_sfr,'color':color})
    # return render(request, "DAIManagementApp/load_charts.html", {'labels': labels, 'result':result ,
    #                                                         'channels_sfr':channel_sfr,'color':color})


def insertion_verifs(request):
    #playlist = Playlists.objects.get(pk=id_playlist)
    channels = Channels.objects.filter(id_user=request.session['id_user'])
    #region = ChannelsZone.objects.get(id_zone_channel=playlist.id_zone_channel.id_zone_channel)
    region ='2005'
    ftp_channel_name = channels.ftp_channel_name
    networkname = channels.networkname
    zonename = region
    broadcastdate = datetime.strftime(datetime.now() , '%Y%m%d')
    result = Verifs.objects.filter(networkname=networkname,zonename=zonename,broadcastDate=broadcastdate).last()
    #d = playlist.broadcastdate
    broadcastDate_verif = broadcastdate
    #win = Windows.objects.filter(id_playlist=playlist.id_playlist).values_list('id_window', flat=True)


    print("Wait I'm in ftp")
    filename_in_ftp = broadcastdate+"-"+zonename+"-00001.ver"
    path_in_ftp = "/"+ftp_channel_name+"/verifs/"
    path_in_app = "files/results/"+ftp_channel_name+"/verifs"
    if not os.path.exists(path_in_app):
        os.makedirs(path_in_app)
        # downloadFTP("uk06.tmd.cloud", "testftp@epgsano.com", "I?#=s3FfnSu_", "/2M/schedules/",  "test.txt" , "/var/www/html/DAI-Management/DAIManagement/FTP_files/")
    useraccess = Useraccess.objects.get(id_user=request.session['id_user'])
    print(useraccess)
    downloadFTP(useraccess.ftp_server, useraccess.ftp_user, useraccess.ftp_password, path_in_ftp , filename_in_ftp, path_in_app)
        # def downloadFTP(host, user, password, filepath_inftp, file_inftp,  localpath):
    if Path(path_in_app+'/'+filename_in_ftp).exists():
            doc = xml2.parse(path_in_app+'/'+filename_in_ftp)
            Spots = doc.getElementsByTagName("Spot")
            verComplete = doc.firstChild.getAttribute("verComplete")
            results = []
            for spot in Spots:
                trafficId = spot.getAttribute("trafficId")
                spotId  = spot.getAttribute("spotId")
                airTime = spot.getAttribute("airTime")
                newAirTime = airTime.replace("T", " ")
                newAirTime2 = newAirTime.replace("+02:00", "")
                airLength = spot.getAttribute("airLength")
                airStatusCode = spot.getAttribute("airStatusCode")
                version = spot.getAttribute("revision")
                try:
                    verif_to_update = Verifs.objects.get(networkname=networkname, zonename=zonename, broadcastDate=broadcastdate, trafficId=trafficId, spotId=spotId)
                    if verif_to_update:
                        verif_to_update.airTime = newAirTime2
                        verif_to_update.airLength = airLength
                        verif_to_update.airStatuscode = airStatusCode
                        verif_to_update.revision = version
                        verif_to_update.vercomplete = verComplete
                        verif_to_update.save()
                    else:
                        new_ad_verif = Verifs(networkname=networkname, zonename=zonename, broadcastDate=broadcastdate, trafficId=trafficId, spotId=spotId, airTime=newAirTime2, airLength=airLength, airStatuscode=airStatusCode, revision=version,  vercomplete = verComplete)
                        new_ad_verif.save()
                except Verifs.DoesNotExist:
                    print('oups')
                    new_ad_verif = Verifs(networkname=networkname, zonename=zonename, broadcastDate=broadcastdate, trafficId=trafficId, spotId=spotId, airTime=newAirTime2, airLength=airLength, airStatuscode=airStatusCode, revision=version,  vercomplete = verComplete)
                    new_ad_verif.save()

def verifs_timer_insert(request):
    import sched, time
    s = sched.scheduler(time.time, time.sleep)
    s.enter(300, 1, insertion_verifs, (request,))
    s.run()