o
    eh                    @   s  d dl Z d dlZd dlZd dlZd dlZd dlZd dlZd dlZd dlZd dl	Z	d dl
mZ d dlmZ d dlmZ d dlmZ d dlmZ d dlmZmZ d dlmZmZ zd d	lmZ W n eym   d d	lmZ Y nw d d
lmZ d dlm  mZ d dlm  m Z! d dl"Z"d dl"m#Z#m$Z$ d dl%m&Z& d dl	m'Z' d dl(m)Z) d dl*m+Z+ d dl,m-Z- d dl.m/Z/ d dl0m1Z1m2Z2 d dl3m4Z4m5Z5 d dl6m7Z7 d dl8m9Z9 d dl:m;Z; d dl<m=Z= d dl>m?Z@ d dlAmBZBmCZCmDZD d dl0mEZE ddl1T ddlFmGZGmHZHmIZImJZJmKZK eLeMZNg dZOdd  ZPd!d" ZQd#d$ ZReGd%d& ZSeGd'd( ZTeGd)d* ZUeGd+d, ZVeGd-d. ZWeGd/d0 ZXd1d2 ZYdd4d5ZZd6d7 Z[d8d9 Z\d:d; Z]d<d= Z^d>d? Z_d@dA Z`dBdC ZadDdE ZbeGdFdG ZceGdHdI ZdeGddKdLZedMdN ZfeGddOdPZgeGddQdRZheGddSdTZieGdUdV ZjeGddXdYZkeGddZd[ZleGdd\d]ZmeGd^d_ ZneGdd`daZoeGdbdc ZpeGdddc ZpeGdedf ZqeGdgdh ZreGdidj ZseGdkdl ZteGddmdnZueGdodp ZveGddqdrZweGddsdtZxeGdudv ZyeGdwdx ZzeGdydz Z{eGd{d| Z|eGdd}d~Z}eGdd Z~dd ZeGdd Zdd ZeGdd ZeGdd ZeGdd ZeGdd ZeGdd ZeGdd ZeGdd ZeGdd ZeGdd ZeGdd ZeGdddZeGdddZeGdddZeGdd ZeGdddZeGdddZeGdd ZeGdddZeGdddZeGdddZeGdd ZeGdddZeGdddZeGdddZeGdddZeGdd Zdd Zdd Zdd ZeGddÄ ZeGddń ZeGddǄ ZeGdddɄZeGddd˄ZeGdd̈́ ZeGddτ Zddф ZeGddӄ ZeGddՄ Zddׄ ZeGddل ZeGdddۄZeGddd݄ZeGddd߄ZeGdd Zdd ZeGdd ZeGdddZeGdddZeGdd Zdd Zdd Zdd Zdd ZeGdd ZeGdd ZeGdd ZeGdd Zdd Zdd Zdd  Zd dlZd dlmZ d dl:m;Z; d dlZdd Zdd ZÐdd ZĐdd	 ZŐd
d  ZeGdd ZƐdd ZeGdd ZeGdd ZeGdd ZeGdd ZeGdd ZeGdd ZeGdd ZeGdd ZeGdd  ZeGd!d" ZeGd#d$ ZҐd%d& ZeGd'd( ZeGd)d* ZՐd+d, ZeGd-d. ZeGd/d0 ZeGd1d2 ZeGd3d4 ZeGd5d6 ZeGd7d8 ZeGd9d: ZeGd;d< ZeGd=d> Zߐd?d@ ZeGdAdB ZdCdD ZdEdF ZdGdH ZdIdJ ZdKdL ZdMdN ZdOdP ZG dQdR dRe&Zd dlZd dSlmZ d dTl,mZ d dUlmZ d dVlmZ ddWlFmZmZ ddXl1mZmZmZmZmZmZmZmZ G dYdZ dZeZG d[d\ d\eZG d]d^ d^eZG d_d` d`eZdadb Z dcdd Zdedf ZG dgdh dhe&Zdidj Zdkdl Zdmdn Zdodp Zdqdr Zdsdt Z	G dudv dve&Z
G dwdx dxe&ZG dydz dze&ZG d{d| d|e&Zd}d~ ZdS (      N)exp)nanPath)pprint)slugify)StringIOBytesIOdatetime	timedelta)broadcast_arrays)etree)FTP_TLS
error_perm)APIViewThreadThreadPoolExecutor)template)Max)messages)modelsIntegrityError)renderredirect)Session)ValidationError)render_to_string)AbstractBaseSession)SessionStore)HttpResponseJsonResponseHttpResponseRedirect)transaction   )*)
check_user	FTPClientfetch_xml_from_urlparse_xml_contentextract_data_by_attributes)z#C152B6z#EA792Dz#499E59z#23A8E2z#974BEFc                 C   sL   d| j v r!| j d r!| j d d kr!tjj| j d dd kr!tdS t| dS )Nid_userr-    DAIManagementApp/login.html)sessionUsersobjectsfilterr   r   request r7   8/var/www/html/DAI27/Adtlas_DAI/DAIManagementApp/views.pylogin:   s   :
r9   c                 C   s   | j d}| j d}ztjj|dj|d}W n tjy.   t| d t| d Y S w |rBd| j	d< t
|j| j	d	< td
S t| d d S )Nusernamepassword)	userlogin)userpasszSometing Wrong.r0   yesisloggedr-   z/DAIManagement/zError detected.)POSTgetr2   r3   r4   DoesNotExistr   errorr   r1   strr-   r   )r6   r:   r;   datar7   r7   r8   dologin?   s   
rF   c                 C   s4   z| j d= | j d= W tdS  ty   Y tdS w )Nr?   r-   r9   )r1   KeyErrorr   r5   r7   r7   r8   logoutN   s   
rH   c                    s  t jjdd}tj dd j}tt jj| j	d dj
ddd	}tjj|d
t|d}g }g }tjjdd}|D ]=}|j|d
dd}tdt| t|dkrxt|jt|j dd |D }	fdd|D }
||
 || q;td|	 t}t jj| j	d d
d}dd |D }tjjdddd d }ttjj| j	d d}ttjj| j	d dd}t| }t| }t| }t| }tj ddd }t jj| j	d d}d}d}d }dd!lm} dd"lm m  |! }|"|  fd#d|# D }W d    n	1 sw   Y  |! }|"| fd$d|# D }W d    n	1 s:w   Y  t$| d%i d&|	d'|d(t|d)|d*|d+|d,|d-|d.|d/|d0|d1|d2|d3|d4tjj| j	d dd5|d6|S )7Nr&   pkz-id_impressionr   r-   r.   sfr_channel_nameTflatFrancesfr_channel_name__inregionday1rK   minutepurcentzres === c                 S      g | ]}|d  qS r   r7   .0xr7   r7   r8   
<listcomp>I      zindex.<locals>.<listcomp>c                    s   g | ]
}t |d    qS r&   floatrY   nbr7   r8   r\   J      zlabels === channel_namec                 S   rW   rX   r7   rY   r7   r7   r8   r\   Y  r]   0001)airStatuscodez	-id_verifP   r-   pacing-id_activity   M  
        SELECT
            Campaigns.id_campaign,
            Count(Verifs.airStatusCode) as total_ads,
            SUM(SFR_analytics.purcent*1.25*4500000/17) as Total_impressions
        FROM
            Campaigns
        LEFT JOIN Adspots ON Campaigns.id_campaign = Adspots.id_campaign
        LEFT JOIN Verifs ON Verifs.spotId = Adspots.filename
        LEFT JOIN SFR_analytics ON SUBSTRING(Verifs.airTime, 12, 5) = SUBSTRING(SFR_analytics.`minute`, 1, 5)
        AND SUBSTRING(Verifs.airTime, 1, 10) = SUBSTRING(SFR_analytics.`day`, 1, 10)
        AND SFR_analytics.sfr_channel_name = '2M Maroc'
        WHERE Adspots.id_campaign is not null AND Verifs.airStatusCode= 0001
        AND SUBSTRING(Verifs.airTime, 1, 10) > Campaigns.start_day AND SUBSTRING(Verifs.airTime, 1, 10) < Campaigns.end_day
        GROUP BY Campaigns.id_campaign
    at  
        SELECT
            Campaigns.id_campaign,
            Count(Verifs.airStatusCode) as total_ads,
            SUM(SFR_analytics.purcent*1.25*4500000/17) as Total_impressions
        FROM
            Campaigns
        INNER JOIN Adspots ON Campaigns.id_campaign = Adspots.id_campaign
        INNER JOIN Verifs ON Verifs.spotId = Adspots.filename
        INNER JOIN SFR_analytics ON SUBSTRING(Verifs.airTime, 12, 5) = SUBSTRING(SFR_analytics.`minute`, 1, 5)
        AND SUBSTRING(Verifs.airTime, 1, 10) = SUBSTRING(SFR_analytics.`day`, 1, 10)
        AND SFR_analytics.sfr_channel_name = '2M Maroc'
        WHERE Adspots.id_campaign is not null AND Verifs.airStatusCode= 0001
        AND SUBSTRING(Verifs.airTime, 1, 10) > Campaigns.start_day AND SUBSTRING(Verifs.airTime, 1, 10) < Campaigns.end_day
				AND Adspots.creative_id is null
        GROUP BY Campaigns.id_campaign
    j  
        SELECT
            Campaigns.id_campaign,
            COALESCE(Ads.total_ads, 0) as total_ads,
            COALESCE(Impressions.total_impression, 0) as total_impression
        FROM
            Campaigns
        LEFT JOIN (
            SELECT
                Campaigns.id_campaign,
                COUNT(Verifs.airStatusCode) as total_ads
            FROM
                Campaigns
            LEFT JOIN
                Adspots ON Campaigns.id_campaign = Adspots.id_campaign
            LEFT JOIN
                Verifs ON Verifs.spotId = Adspots.filename
            WHERE
                Adspots.id_campaign is not null and Verifs.airStatusCode= 0001
                AND SUBSTRING(Verifs.airTime, 1, 10) > Campaigns.start_day
                AND SUBSTRING(Verifs.airTime, 1, 10) < Campaigns.end_day
            GROUP BY
                Campaigns.id_campaign
        ) Ads ON Campaigns.id_campaign = Ads.id_campaign
        LEFT JOIN (
            SELECT
                Campaigns.id_campaign,
                COUNT(VAST_response.AD_id) as total_impression
            FROM
                Campaigns
            LEFT JOIN
                VAST_response ON Campaigns.vast_data = VAST_response.vast_url
                AND SUBSTRING(VAST_response.datetime_timestamp, 1, 10) > Campaigns.start_day
                AND SUBSTRING(VAST_response.datetime_timestamp, 1, 10) < Campaigns.end_day
            WHERE
                Campaigns.vast_data is not null
            GROUP BY
                Campaigns.id_campaign
        ) Impressions ON Campaigns.id_campaign = Impressions.id_campaign
        WHERE Campaigns.vast_data is not null
    
connectionNoVastResult
VastResultc                       g | ]} | qS r7   r7   rZ   rowrq   r7   r8   r\     r]   c                    rs   r7   r7   rt   rr   r7   r8   r\     r]   DAIManagementApp/index.htmllabelsrR   Znb_channels	campaignscampaigns_activeadvertisersbookedsagences	playlists	activiteschannelsresultchannels_sfrcolorcampaigns_datavast_resultsno_vast_results)%Channelsr3   rA   SfrAnalyticsallorder_byrR   listr4   r1   values_listSfr_analyticsrD   Impressionsprintlenr`   total_usersmarket_share_purcentappendcolorsVerifs	Campaignsmost_advertisersbooked_adbreaksactive_agencyr   Activity	django.dbro   utilsrq   rr   cursorexecutefetchallr   )r6   channel	dayformatr   valr   channel_sfrrV   resry   rE   r   r   linesrz   r{   
advertiserr}   r~   playlistr   no_vast_query_oldno_vast_query
vast_queryro   r   r   r   r7   )rq   rr   rb   r8   index2  s   "


.*



	
r   c                 C   s  ddl m }m} dddd}tjj| jd d}| |dd }|d}ztt	jj||d	d
 }|j
j}W n   | Y S |dd}	tjj||dd}
dd |
D }
t|
dkr`|S |
D ]}|dkrq|d  d7  < qb|d  d7  < qb|d d t|
 }t|d|d< |S )Nr   r
   )	delivered	cancelledrV   r-   r.   r&   days%Y-%m-%d)id_channel__inbroadcastdate-r/   )networknamebroadcastDaterf   c                 S   rW   rX   r7   rZ   ir7   r7   r8   r\      r]   zplaylists.<locals>.<listcomp>re   r   r   d      rV   )r   r   r   r3   r4   r1   nowstrftimer   	Playlists
id_channelrd   replacer   r   r   round)r6   r   r   r   r   	yesterdaydater   r   rR   verifsr   rV   r7   r7   r8   r     s2   
r   c                 C   s   t jj| jd ddd}g }|D ]9}i }tjj|d d}tjj|dd}tjj|d	}|d
 |d< t||d< t||d< t|dkrK|	| q|S )Nr-   r.   r   rd   r   r   T)Zid_adpost__inbookedZid_adspot__inr&   r   r   avail)
r   r3   r4   r1   r   Adspotsr   AdspotsInAvailr   r   )r6   r   r   r   radspotr   r   r7   r7   r8   r   ,  s   
r   c                 C   s   t jj| jd ddddd}g }|D ]2}i }|d |d< tjj|d d}tjj|d	d
}t|dkrF|d |d< t||d< || q|S )Nr-   r   r-   
is_deleted	id_agencynamer   r&   r   T)Zid_brand__inri   r   r   rz   )	Agencyr3   r4   r1   r   Brandsr   r   r   )r6   r~   r   Zagencer   brandsrz   r7   r7   r8   r   >  s    
r   c                 C   s   t jj| jd dd}dd |D }tjj|dd}g }g }g }|D ]%}tjj|d}t|}|	| |j
|j||jd	}	t|	 |	|	 q&t| td
dD ]}t|}
||
}||}|	||  qUt| |S )Nr-   r.   id_brandc                 S   rW   rX   r7   rY   r7   r7   r8   r\   P  r]   z$most_advertisers.<locals>.<listcomp>pk__in	-id_brandr   r   
name_brandtotal_adspotslogor      )UsersAdvertisersr3   r4   r1   r   r   r   r   r   r   r   
brand_namer   r   rangemaxr   pop)r6   	brands_idr   Ztotalsdicr   r   adspotstoutalbrand	max_valueZ	max_indexar7   r7   r8   r   M  s2   


r   c                 C   s(   | j dkr| jd}t| t| dS )Nr@   Zoptionsnamez"DAIManagementApp/test_options.html)methodr@   rA   r   r   )r6   optionsr7   r7   r8   test_optionsh  s   

r   c                 C   sP   t |d | d}|  D ]}|| qW d    d S 1 s!w   Y  d S )N/zwb+)openchunkswrite)filepathfilenamedestinationchunkr7   r7   r8   handle_uploaded_fileo  s
   "r   0c               
   C   s  t jj|d}tjj|d}tj| d}|d}t|}	tjjt	||t| |j
d|d}
td}|dd	 |d
|	 |d| d  |d| d  |d|j |d|j |d| |dd |dd tjj|
jdd}i }d}d}|D ]}t|jdd}|d }td||< || d| || d|j tjj|jd}t|dkr|||  i }d}|D ]}t|jdd}|d }t|| d||< || d | || d!|j || d"d tjj|jd# d$}i }d}|D ]c}|d%7 }t!d&t!"t	|j#j$}t|| d'||< || d(d) || d*t| || d+t|j% || d,| || d-|j#j& || d.t|j#j$d/  || d0d1 q|d%7 }q|d%7 }q|d%7 }t'|}t(|	 t(|j t(|j) t(|
j* |	d2 |j d2 |j) d2 |
j* d3 }t(| d4}t+j,-|st+.| t/|d5}|j0|d6d7d8 W d    |S 1 sw   Y  |S )9Nr   id_zone_channelr   %Y%m%dr   )id_channel_idversionr   r   is_draftdraft_versionSchedulexmlns&http://www.scte.org/schemas/118-3/201Xr   begDateTimeT00:01:00+00:00endDateTimeT23:59:59+00:00networkNamezoneNamerevisionlevelschemaVersion http://www.w3.org/2001/XMLSchemaid_playlistwindow_startr    T+00:00WindowwindowStartwindowDuration	id_windowAvail
availStartavailInWindowavailNumid_avail	id_adspotr&   %H%M%S00Spot	eventTypeLOI	trafficIdpositionInAvaillengthspotIdadIdsecschedSourceLocalr   .schfiles/DAI-Managementwbutf-8Tencodingxml_declaration)1r   r3   rA   ChannelsZoner   strptimer   rD   r   intr   xmlElementsetr   zonenameWindowsr4   r  r   r  r   window_durationAvailsr  r   r   avail_start
SubElementavailinwindowr   r  select_relatedtimegmtimer  durationpositioninavailr   ElementTreer   rd   r   osr   existsmakedirsr   r   ) rR   
channel_idZid_zonenamer   r   r   channel_zonedatetimeobjectr   Zdayformat_stringr   rootwindowsZwindows_arrayZwin_i	trafficidwindowZwindowStartxmlavailsZavails_arrayZav_ir   ZavailStartxmlZadspotsinAVZadspots_arrayZads_ir   adspot_durationtreefileNamer   filesr7   r7   r8   GenerateXMLfromDatabaseu  s   
"






&

rT  c                 C   s   t tdddS )Nz
2021-08-162MrS   )r"   rT  r5   r7   r7   r8   testGenerateFromDatabase  s   rV  c                 C   sP   t dD ] \}}}|D ]}| |kr$t j||}t||}|    S qqd S )Nr   )rE  walkr   joinr   )r   moder   subdirsrS  r   r   fr7   r7   r8   getfile  s   
r\  c              
   C   s   t | ||}d|_|| tt D ]7\}}}|D ]/}	||	krLtj||	}
t	|
 t
|
d}|d| | W d    n1 sGw   Y  qq|  d S )Nr.  rbSTOR )ftplibFTPr0  cwdrE  rW  getcwdr   rX  r   r   
storbinaryquit)hostuserr;   r   
path_inftpftpr   rZ  rS  r   r   r[  r7   r7   r8   
uploadFTP2  s   
ri  c           
      C   s   t | ||}d|_|| |}|d | }t| z!t|d}	|d| |	j W d    n1 s6w   Y  W n tyO   td| t	
| Y nw |  d S )Nr.  r   r-  zRETR ZERR)r_  r`  r0  ra  r   r   Z
retrbinaryr   r   rE  unlinkrd  )
re  rf  r;   Zfilepath_inftpZ
file_inftpZ	localpathrh  r   	localfiler   r7   r7   r8   downloadFTP  s    

rl  c                 C   s   t tddddddS )Nuk06.tmd.cloudtestftp@epgsano.comI?#=s3FfnSu_z/2M/schedules/ztest.txtz5/var/www/html/DAI-Management/DAIManagement/FTP_files/)r"   rl  r5   r7   r7   r8   test_download  s   rp  c           
      C   s   |r
t | |||nt | ||}|| tjt}d}t|d}	|d| |	 W d    n1 s7w   Y  |	  d S )Nr,  r]  r^  )
r_  r`  ra  rE  r   dirname__file__r   rc  rd  )
re  portrf  r;   r   rg  rh  
module_dirr   r   r7   r7   r8   	uploadFTP  s   "
ru  c           	   
   C   s   ddl m} zH|| 9}||| t|d}|d| | W d    n1 s+w   Y  td| d| d W d    W d S 1 sGw   Y  W d S  tyh } ztd|  W Y d }~d S d }~ww )	Nr   )r`  r]  r^  zFile 'z' uploaded to 'z' on the FTP server.zError: )r_  r`  r9   r   rc  r   	Exception)	re  r:   r;   local_file_pathremote_file_pathr`  rh  r   er7   r7   r8   AlmaFtp  s   
&rz  c                 C   sj  z9t | ||}|| tjt}d}t|d}	|d| |	 W d    n1 s.w   Y  |	  W dS  t
y }
 zotdt|
  td z>t | ||}|| tjt}d}t|d}	|d| |	 W d    n1 s{w   Y  |	  W W Y d }
~
dS  t
y }
 ztdt|
  td W Y d }
~
W Y d }
~
dS d }
~
ww d }
~
ww )	Nr,  r]  r^  Tz%suF   ⚠️ Connectivity issue with Enensys server, trying again ... ⚠️u=   ⛔️ update was not pushed, please check OpenVPN or EnensysF)r_  r`  ra  rE  r   rq  rr  r   rc  rd  rv  r   typesend_msg_telegram2)re  rs  rf  r;   r   rg  rh  rt  r   r   ry  r7   r7   r8   
uploadFTP42  s>   

r}  c                 C   s   d}t |}|jd}|jd}|jd}|jd}|d}g }|D ]c}	|	d}
|	d}|	d	}|d
d}|dd}|	d}|	d}|	d}ztjj||||
||||||d
 W q( t	y } ztjj
||||
|||||d	j|d}W Y d }~q(d }~ww tdS )Nz%files/results/20210906-2005-00001.xmlr  r  r   verCompleter   r#  r&  airTimer  r  +02:00r/   	airLengthairStatusCoder	  
r   r8  r   r#  r&  r  r  rf   r	  vercomplete)	r   r8  r   r#  r&  r  r  rf   r	  )r  zdata has been inserted)xml2parse
firstChildgetAttributegetElementsByTagNamer   r   r3   update_or_creater   r4   updater"   )r6   r   docr   r8  r   r~  Spotsresultsspotr#  r&  r  
newAirTimenewAirTime2r  r  r   ry  Z	insertionr7   r7   r8   
saveXML2dbm  s0   







$2r  c                 C   s   t tdddddS )Nrm  rn  ro  z,/files/DAI-Management/20210815-2005-2M-1.schz2M/schedules/France)r"   ri  r5   r7   r7   r8   test_upload  s   r  r/   c                 C   s   t d}tj|}|  d }ddkrd}ddkrd}dd }|d	krztjj|d
}ddl	m
} | }	d|jj d|jdd	|jdd	f}
|	||
 d}||	}|D ]}|d d krht| q[|t|d 7 }q[t| d||dS tdS )NEurope/Paris  r   r&   a  
        SELECT networkname, spotId, airTime, airStatusCode, purcent AS sfr_percentage, purcent*1.25*4500000/17 AS total_volume, Epg.emission_name
        FROM Verifs
        inner JOIN SFR_analytics ON SUBSTRING(Verifs.airTime, 12, 5) = SUBSTRING(SFR_analytics.`minute`, 1, 5)
        AND SUBSTRING(Verifs.airTime, 1, 10) = SUBSTRING(SFR_analytics.`day`, 1, 10)
        AND SFR_analytics.sfr_channel_name = '2M Maroc'
        inner JOIN Epg ON (Verifs.airTime < Epg.End_time AND Verifs.airTime > Epg.Start_time)
        WHERE Verifs.spotId LIKE %s AND Verifs.airStatusCode = '0001' AND Verifs.broadcastDate > %s AND Verifs.broadcastDate < %s
        GROUP BY networkname, spotId, airTime, airStatusCode, purcent, Epg.emission_name ORDER BY airTime
        a+  
        SELECT networkname, spotId, airTime, airStatusCode, purcent AS sfr_percentage, purcent*1.25*4500000/17 AS total_volume, Epg.emission_name
        FROM Verifs
        inner JOIN SFR_analytics ON SUBSTRING(Verifs.airTime, 12, 5) = SUBSTRING(SFR_analytics.`minute`, 1, 5)
        AND SUBSTRING(Verifs.airTime, 1, 10) = SUBSTRING(SFR_analytics.`day`, 1, 10)
        AND SFR_analytics.sfr_channel_name = '2M Maroc'
        inner JOIN Epg ON (Verifs.airTime < DATE_ADD(Epg.End_time, INTERVAL 1 HOUR) AND Verifs.airTime > DATE_ADD(Epg.Start_time, INTERVAL 1 HOUR))
        WHERE Verifs.spotId LIKE %s AND Verifs.airStatusCode = '0001' AND Verifs.broadcastDate > %s AND Verifs.broadcastDate < %s
        GROUP BY networkname, spotId, airTime, airStatusCode, purcent, Epg.emission_name ORDER BY airTime
        c                    &   dd | j D   fdd|  D S )'Return all rows from a cursor as a dictc                 S   rW   rX   r7   rZ   colr7   r7   r8   r\     r]   z9campaign_report.<locals>.dictfetchall.<locals>.<listcomp>c                       g | ]	}t t |qS r7   dictziprt   columnsr7   r8   r\         descriptionr   r   r7   r  r8   dictfetchall     
z%campaign_report.<locals>.dictfetchallr/   rI   rn   %r   total_volumez%DAIManagementApp/report_campaign.html)rE   totaledit_campaign)pytztimezoner   r   	utcoffsettotal_secondsr   r3   rA   r   ro   r   r   r   	start_dayr   end_dayr   r   r4  r   r   )r6   rE   paris_tz
paris_time
utc_offsetqueryr  campaignro   r   
data_tupler  ru   r7   r7   r8   campaign_report  s>   
	
	r  c                    r  )r  c                 S   rW   rX   r7   r  r7   r7   r8   r\     r]   z dictfetchall.<locals>.<listcomp>c                    r  r7   r  rt   r  r7   r8   r\         r  r  r7   r  r8   r    s   r  c           &   	      sl  ddl m}m}m} ddlm} ddlm} dd } fdd}| jd	krdd
lm}	 dd l	}
|	
 }|d}|
d}|	
|}|  d }tdt| d | jddd}| jddd}| jd}|dkrzd}d}d}d}ddlm    }d| d||f}||| d}||}g }d }|D ]$}|	|d d }|r||kr|dddddd || |}q|D ]}|d  d krt| q|t|d  7 }qt| d!||||d"S tjj| jd# d$ d%}d&d' |D }t!jj|dd("d)} g }!| D ]#}"t#jj|"d*}#t$|#}$|"j%|"j&|"j'|$|"j(d+}%t|% |!|% q
t| d,d-|!iS ).Nr   FExpressionWrapper
FloatFieldSubstrr   c                    r  )r  c                 S   rW   rX   r7   r  r7   r7   r8   r\     r]   zBreport_advertiser_backup.<locals>.dictfetchall.<locals>.<listcomp>c                    r  r7   r  rt   r  r7   r8   r\     r  r  r  r7   r  r8   r    r  z.report_advertiser_backup.<locals>.dictfetchallc              
         z$   }|| | | }|W  d    W S 1 sw   Y  W d S  ty? } ztd|  g W  Y d }~S d }~ww NzAn error occurred: r   r   r   rv  r   r  paramsr   rowsry  rn   r7   r8   my_custom_sql  s   
(z/report_advertiser_backup.<locals>.my_custom_sqlr@   r   r   r  r  -Current UTC offset for France (Paris) is GMT+.r  r   r/   r  selected_brandr&   az  
            SELECT networkname, spotId, DATE_SUB(Verifs.airTime, INTERVAL 1 HOUR) as airTime, airStatusCode, purcent AS sfr_percentage, purcent*1.25*4500000/17 AS total_volume, Epg.emission_name
            FROM Verifs
            LEFT JOIN SFR_analytics ON SUBSTRING(Verifs.airTime, 12, 5) = SUBSTRING(SFR_analytics.`minute`, 1, 5)
            AND SUBSTRING(Verifs.airTime, 1, 10) = SUBSTRING(SFR_analytics.`day`, 1, 10)
            AND SFR_analytics.sfr_channel_name = '2M Maroc'
            LEFT JOIN Epg ON (Verifs.airTime < DATE_ADD(Epg.End_time, INTERVAL 1 HOUR) AND Verifs.airTime > DATE_ADD(Epg.Start_time, INTERVAL 1 HOUR))
            WHERE Verifs.spotId LIKE %s AND Verifs.airStatusCode = '0001' AND Verifs.broadcastDate > %s AND Verifs.broadcastDate < %s
            GROUP BY networkname, spotId, airTime, airStatusCode, purcent, Epg.emission_name ORDER BY airTime
            a  
            SELECT networkname, spotId, DATE_SUB(Verifs.airTime, INTERVAL 1 HOUR) as airTime, airStatusCode, purcent AS sfr_percentage, CAST(purcent * 1.25 * 4500000 / 17 AS UNSIGNED) AS total_volume, Epg.emission_name
            FROM Verifs
            LEFT JOIN SFR_analytics ON SUBSTRING(Verifs.airTime, 12, 5) = SUBSTRING(SFR_analytics.`minute`, 1, 5)
            AND SUBSTRING(Verifs.airTime, 1, 10) = SUBSTRING(SFR_analytics.`day`, 1, 10)
            AND SFR_analytics.sfr_channel_name = '2M Maroc'
            LEFT JOIN Epg ON (Verifs.airTime < DATE_ADD(Epg.End_time, INTERVAL 1 HOUR) AND Verifs.airTime > DATE_ADD(Epg.Start_time, INTERVAL 1 HOUR))
            WHERE Verifs.spotId LIKE %s AND Verifs.airStatusCode = '0001' AND Verifs.broadcastDate > %s AND Verifs.broadcastDate < %s
            GROUP BY networkname, spotId, airTime, airStatusCode, purcent, Epg.emission_name
            ORDER BY airTime
            a  
            SELECT networkname, spotId, DATE_SUB(Verifs.airTime, INTERVAL 1 HOUR) as airTime, airStatusCode, purcent AS sfr_percentage, CAST(purcent * 1.25 * 4500000 / 17 AS UNSIGNED) AS total_volume, Epg.emission_name
            FROM Verifs
            LEFT JOIN SFR_analytics ON SUBSTRING(Verifs.airTime, 12, 5) = SUBSTRING(SFR_analytics.`minute`, 1, 5)
            AND SUBSTRING(Verifs.airTime, 1, 10) = SUBSTRING(SFR_analytics.`day`, 1, 10)
            AND SFR_analytics.sfr_channel_name = '2M Maroc'
            Inner JOIN Epg ON (Verifs.airTime < DATE_ADD(Epg.End_time, INTERVAL 2 HOUR) AND Verifs.airTime > DATE_ADD(Epg.Start_time, INTERVAL 2 HOUR))
            WHERE Verifs.spotId LIKE %s AND Verifs.airStatusCode = '0001' AND Verifs.broadcastDate > %s AND Verifs.broadcastDate < %s
            GROUP BY networkname, spotId, airTime, airStatusCode, purcent, Epg.emission_name
            ORDER BY airTime
              
            SELECT networkname,
            spotId,
            DATE_SUB(Verifs.airTime, INTERVAL 1 HOUR) as airTime,
            airStatusCode,
            purcent AS sfr_percentage,
            CAST(MAX(purcent) * 1.25 * 4500000 / 17 AS UNSIGNED) AS total_volume,
            Epg.emission_name
            FROM Verifs
            LEFT JOIN (
            SELECT sfr_channel_name,
            `minute`,
            `day`,
            MAX(purcent) as purcent
            FROM SFR_analytics
            WHERE sfr_channel_name = '2M Maroc'
            GROUP BY sfr_channel_name, `minute`, `day`
            ) AS SFR_analytics ON SUBSTRING(Verifs.airTime, 12, 5) = SUBSTRING(SFR_analytics.`minute`, 1, 5)
            AND SUBSTRING(Verifs.airTime, 1, 10) = SUBSTRING(SFR_analytics.`day`, 1, 10)
            Inner JOIN Epg ON (Verifs.airTime < DATE_ADD(Epg.End_time, INTERVAL 2 HOUR) AND Verifs.airTime > DATE_ADD(Epg.Start_time, INTERVAL 2 HOUR))
            WHERE Verifs.spotId LIKE %s
            AND Verifs.airStatusCode = '0001'
            AND Verifs.broadcastDate > %s
            AND Verifs.broadcastDate < %s
            GROUP BY networkname, spotId, airTime, airStatusCode, purcent, Epg.emission_name
            ORDER BY airTime
            rn   r  r  %Y-%m-%d %H:%M:%S--r   r&  r  r  emission_namer  !DAIManagementApp/view_report.htmlrE   r  r  report_dater-   r.   r   c                 S   rW   rX   r7   rY   r7   r7   r8   r\     r]   z,report_advertiser_backup.<locals>.<listcomp>r   statusr   r   r   r   r  r   r   'DAIManagementApp/report_advertiser.htmlr   ))django.db.modelsr  r  r  django.db.models.functionsr  r   r   r   r  r   r   r  r  r  r   r4  r@   rA   r   r   ro   r   r   r3  r   r   r   r   r3   r4   r1   r   r   r   r   r   r   r   r  r   )&r6   r   r  r  r  r  r   r  r  r   r  current_datetimer  r  r  r  r  r  r  	query_oldZ	query_ol3Z
query_old4r  r   r  r  rE   processed_data	last_dateru   row_dater   r   r   r   r   r   r   r7   rn   r8   report_advertiser_backup  sr   



,

r  c           &   	      sD  ddl m}m}m} ddlm} ddlm}m} dd l}ddl	m
  dd }	 fdd	}
| jd
kr| }|d}|d}||}|  d }tdt| d | jddd}| jddd}| jd}|dkr|d}d}d}d}  }| }d| d||f}||| |	|}g }d }d}|D ]2}||d d }|r||kr|dddddd || |}|d d ur|t|d 7 }qt| d ||||d!S tjj| j d" d#j!d$d%d&}t"jj|d'd(#d)} g }!| D ]}"t$jj|"d*}#t%|#}$|"j&|"j'|"j(|$|"j)d+}%|!|% qt| d,d-|!iS ).Nr   r  r  )r   r   rn   c                    r  )r  c                 S   rW   rX   r7   r  r7   r7   r8   r\     r]   z;report_advertiser.<locals>.dictfetchall.<locals>.<listcomp>c                    r  r7   r  rt   r  r7   r8   r\     r  r  r  r7   r  r8   r    r  z'report_advertiser.<locals>.dictfetchallc              
      r  r  r  r  rn   r7   r8   r    s   
(z(report_advertiser.<locals>.my_custom_sqlr@   r   r  r  r  r  r  r   r/   r  r  r   r  ay  
            SELECT networkname,
            spotId,
            DATE_SUB(Verifs.airTime, INTERVAL 1 HOUR) as airTime,
            airStatusCode,
            purcent AS sfr_percentage,
            CAST(MAX(purcent) * 1.25 * 4500000 /
            CASE
            WHEN DATE(Verifs.airTime) < '2024-03-18' THEN 17
            ELSE 14
            END AS UNSIGNED) AS total_volume,
            Epg.emission_name
            FROM Verifs
            LEFT JOIN (
            SELECT sfr_channel_name,
            `minute`,
            `day`,
            MAX(purcent) as purcent
            FROM SFR_analytics
            WHERE sfr_channel_name = '2M Maroc'
            GROUP BY sfr_channel_name, `minute`, `day`
            ) AS SFR_analytics ON SUBSTRING(Verifs.airTime, 12, 5) = SUBSTRING(SFR_analytics.`minute`, 1, 5)
            AND SUBSTRING(Verifs.airTime, 1, 10) = SUBSTRING(SFR_analytics.`day`, 1, 10)
            INNER JOIN Epg ON (Verifs.airTime < DATE_ADD(Epg.End_time, INTERVAL 2 HOUR) AND Verifs.airTime > DATE_ADD(Epg.Start_time, INTERVAL 2 HOUR) AND Epg.verif_channel_id = Verifs.zonename)
            WHERE Verifs.spotId LIKE %s
            AND Verifs.airStatusCode = '0001'
            AND Verifs.broadcastDate > %s
            AND Verifs.broadcastDate < %s
            GROUP BY networkname, spotId, airTime, airStatusCode, purcent, Epg.emission_name
            ORDER BY airTime
            a  
            Select

            MIN(networkname) AS networkname,
            MIN(networkname) AS networkname,
            MIN(spotId) AS spotId,

            MIN(Epg.emission_name) AS emission_name,
            SUBSTRING(DATE_SUB(Verifs.airTime, INTERVAL 2 HOUR), 1, 19) as airTime,

            CAST(MAX(purcent) * 1.25 * 4500000 /
            CASE
                WHEN DATE(Verifs.broadcastDate) < '20240317' THEN 17
            ELSE 14 END AS UNSIGNED) AS total_volume

            FROM Verifs
            INNER JOIN SFR_analytics ON SUBSTRING(Verifs.airTime, 12, 5) = SUBSTRING(SFR_analytics.`minute`, 1, 5)
                         AND SUBSTRING(Verifs.airTime, 1, 10) = SUBSTRING(SFR_analytics.`day`, 1, 10)
                         AND SFR_analytics.sfr_channel_name = '2M Maroc'
            INNER JOIN Epg ON (Verifs.airTime < DATE_ADD(Epg.End_time, INTERVAL 1 HOUR) AND Verifs.airTime > DATE_ADD(Epg.Start_time, INTERVAL 1 HOUR))
            WHERE Verifs.spotId LIKE %s
            AND Verifs.airStatusCode = '0001'
            AND Verifs.broadcastDate > %s
            AND Verifs.broadcastDate < %s
            GROUP BY SUBSTRING(Verifs.airTime, 1, 19), Verifs.broadcastDate
            ORDER BY airTime;
            a  

            SELECT
            MIN(Verifs.networkname) AS networkname,
            MIN(spotId) AS spotId,
            CASE
            WHEN DATE(Verifs.airTime) > '2024-03-30' THEN SUBSTRING(Verifs.airTime, 1, 19)
            ELSE SUBSTRING(DATE_SUB(Verifs.airTime, INTERVAL 1 HOUR), 1, 19)
            END AS airTime,
            MIN(Epg.emission_name) AS emission_name,
            CAST(MAX(purcent) * 1.25 * 4500000 /
            CASE
            WHEN DATE(Verifs.broadcastDate) < '2024-03-17' THEN Channels_zone.market_share
            ELSE Channels_zone.market_share
            END AS UNSIGNED) AS total_volume
            FROM Verifs
            INNER JOIN Channels_zone ON Channels_zone.id_zone_channel = Verifs.zonename
            Inner JOIN (
            SELECT sfr_channel_name, `minute`, `day`, MAX(purcent) AS purcent
            FROM SFR_analytics
            INNER JOIN Channels_zone ON Channels_zone.sfr_name = SFR_analytics.sfr_channel_name
            WHERE sfr_channel_name = Channels_zone.sfr_name
            GROUP BY sfr_channel_name, `minute`, `day`
            ) AS SFR_analytics ON (
            SUBSTRING(Verifs.airTime, 12, 5) = SUBSTRING(SFR_analytics.`minute`, 1, 5)
            OR SUBSTRING(Verifs.airTime, 12, 5) = SUBSTRING(ADDTIME(SFR_analytics.`minute`, '00:01:00'), 1, 5)
            )
            AND SUBSTRING(Verifs.airTime, 1, 10) = SUBSTRING(SFR_analytics.`day`, 1, 10)
            AND Channels_zone.sfr_name = SFR_analytics.sfr_channel_name
            INNER JOIN Epg ON (
            Epg.verif_channel_id = Verifs.networkname
            AND Verifs.airTime < DATE_ADD(Epg.End_time, INTERVAL 2 HOUR)
            AND Verifs.airTime > DATE_ADD(Epg.Start_time, INTERVAL 2 HOUR)
            )
            WHERE Verifs.spotId LIKE %s
            AND Verifs.airStatusCode = '0001'
            AND Verifs.broadcastDate > %s
            AND Verifs.broadcastDate < %s
            GROUP BY
            CASE
            WHEN DATE(Verifs.airTime) > '2024-03-30' THEN SUBSTRING(Verifs.airTime, 1, 19)
            ELSE SUBSTRING(DATE_SUB(Verifs.airTime, INTERVAL 1 HOUR), 1, 19)
            END,
            Verifs.broadcastDate,
            Channels_zone.market_share,
            SFR_analytics.sfr_channel_name
            ORDER BY airTime;



            r  r  r  r  r  r  r  r  r-   r.   r   TrL   r&   r  r   r   r  r  r   )*r  r  r  r  r  r  r   r   r  r   ro   r   r   r   r  r  r  r   r4  r@   rA   r   r   
capitalizer   r3  r   r   r   r   r3   r4   r1   r   r   r   r   r   r   r   r  r   )&r6   r   r  r  r  r  r   r   r  r  r  r  r  r  r  r  r  r  r  r  Zquery_11Zquery11r  r   r  rE   r  r  r  ru   r  r   r   r   r   r   r   Z
brand_infor7   rn   r8   report_advertiser  sl   




"4
r  c           
      C   s  |dkr?t jj|d}tjj|dd }dd t|D }tjj|d}tjj|d	d}t
| dd	||t|d
iS tjj| jd dd}dd |D }t jj|d	d}g }|D ] }tjj|d}t|}	|j|j|	|jd}t| || qat
| dd|iS )Nr/   rI   r   r   c                 S   rW   rX   r7   r   r7   r7   r8   r\     r]   z$views_advertiser.<locals>.<listcomp>r   z.DAIManagementApp/views_advertiser_adspots.htmlrE   )r   r   r   r-   r.   r   c                 S   rW   rX   r7   rY   r7   r7   r8   r\     r]   r   r   z&DAIManagementApp/views_advertiser.htmlr   )r   r3   rA   r   r4   r   distinctr   r   r   r   r   r1   r   r   r   r   r   r   )
r6   r   r   r   r   r   r   r   r   r   r7   r7   r8   views_advertiser  s,   r  c                 C   s,  | j dkr| jd}| jd}| jd}| jd}tj }d}t|dd}|d	d
}|d | d }zt| jd || W n   dd l	}	|	
d|d |  Y t|||||d | d}
d}dt|
j }t|||d}|  |
  t| jd |
j||d}|  tdS t| dS )Nr@   advertiser_namecategoryadvertiser_desc
active_0_1static/Advertisers_imgsr  _:r   __.pngadvertiser_logor   zlogo.pngr   )r   r  r  r  r   zAdd AdvertirerzAdmin Add advertiser  id: activityr   r  r-   
id_user_idid_brand_idr  r    /DAIManagement/views_advertiser/z$DAIManagementApp/add_advertiser.html)r   r@   rA   r   r   rD   r   r   FILESshutilmover   r   r   saver   r1   r   r   )r6   r  r  r  r  r   r   
now_infiler   r  Brandactivitedescr  Advertiser_userr7   r7   r8   add_advertiser  s2   


r  rS   c                 C   s   t | j | jdkrt | tjj|d}t| dd|iS tjj| jd d	d}dd	 |D }tjj|d

d}t| dd|iS )Nr@   rI   z(DAIManagementApp/edit_advertiser_id.htmlr   r-   r.   r   c                 S   rW   rX   r7   rY   r7   r7   r8   r\     r]   z#edit_advertiser.<locals>.<listcomp>r   r   z%DAIManagementApp/edit_advertiser.htmlr   )r   r   r   r3   rA   r   r   r4   r1   r   r   )r6   Z	id_brandsr   r   r   r7   r7   r8   edit_advertiser  s   

r  c                 C   sD  t jj|d}| jd}| jd}| jd}| jd}tj }z.d}t|dd}	|	d	d
}	|d |	 d }
t| j	d d| |
 d| d |
 |_
W n   Y |dkr^||_|dkre||_|dkrl||_|dkrs||_d}dt|j }t|||d}|  |  tjj| jd djddd }tdS )NrI   r  r  r  r  ZAdvertisers_imgsr  r  r  r   r  r  r  static/r   r/   zEdit AdvertirerzAdmin edit advertiser  id: r  r-   r.   rS   r  r   r  )r   r3   rA   r@   r   r   rD   r   r   r  r   r   r  r  r  r   r   r  r   r4   r1   r?  r   r   )r6   r   r   r  r  r  r  r   r   r  r   r  r  r  r   r7   r7   r8   update_advertiser  s<   
&r  c           
      C   s   | j dkr6tj }d}dt| }t|||d}|  tjj|d}dt	|j
 d }t||_
|  tjj| jd d	d
}dd |D }tjj|dd}	t| dd|	iS )Nr@   zDisable AdvertirerzAdmin disable advertiser  id: r  rI   r&   r   r-   r.   r   c                 S   rW   rX   r7   rY   r7   r7   r8   r\   L  r]   zdisable.<locals>.<listcomp>r   r   z(DAIManagementApp/disable_advertiser.htmlr   )r   r   r   rD   r   r  r   r3   rA   r4  r  r   r4   r1   r   r   r   )
r6   r   r   r  r  r  r   r  r   r   r7   r7   r8   disable:  s   


r   c              
   C   sh  | j dkr| jd}| jd}| jd}| jd}| jd}tj }t|ddd	d
dd
}|d krdt| jd  d t| }tj	d| dd d| j
v rk| j
d j}	t| j
d d| |	 nd}	tjj|d}
t|
||||d |	 ||	|d}|  d}dt|j }t|||d}|  tdS tjj| jd d}t| ddd|iiS )Nr@   campaign_idadspot_namerP  r  creative_idr  r  r  r   r  adspots/user_r-   
/campaign_r  Texist_okadspot_filer/   rI   r   )id_campaignr  rB  r  original_filepathr   r   r  zAdd CreativezAdmin Add creative  id: r  /DAIManagement/edit_creative/r.   z"DAIManagementApp/add_creative.htmlrE   rz   )r   r@   rA   r   r   rD   r   r1   rE  rG  r  r   r   r   r3   r   r  	id_adpostr   r   r4   r   )r6   r  r  rP  r  	vast_infor   r  r   r   r  creativer  r  r  rz   r7   r7   r8   add_creativeR  sD   

 


r  c                 C   s   | j dkrtjj|d}tjj| jd d}t| d||dS t| jd }tjj| jd dj	ddd	}tjj|d

d}t| dd|iS )Nr@   rI   r-   r.   z&DAIManagementApp/edit_creative_id.html)Zadspostrz   r	  TrL   )id_campaign__inz
-id_adpostz#DAIManagementApp/edit_creative.htmlr   )r   r   r3   rA   r   r4   r1   r   rD   r   r   )r6   r  adpostrz   
id_sessionZ
adspots_nnr7   r7   r8   edit_creative  s$   
	r  c                 C   s,  | j dkrtjj|d}| jd}| jd}| jd}| jd}| jd}| jd}| jd	}	| jd}| jd}| jd}tjj|d}
tj }t|	d
d	dd}dt| j
d  d t| }dt| d t| d t| d | d }dt| d | d t| d | d }tjd| dd d| jv r	z9t| jd d| | td| d | d}|d |d |d |d| d   || |  W n$   d!d l}tjd|j r|d|j d| d |  Y nd"}|d | |_d S d S )#Nr@   rI   r  r  rP  r  r  rH  advertiser_idr  r  r  r   r  r-   r  	campaign_r  sec__.ts.m3u8r  Tr  r  r   r   	#EXTM3U 
#EXT-X-VERSION:3 
#EXT-X-MEDIA-SEQUENCE:0 
#EXTINF:, 
r   r/   )r   r   r3   rA   r@   r   r   r   rD   r   r1   r   rE  rG  r  r   r   r   closer  r   rF  r
  r  )r6   r  r  r  r  rP  r  r  rH  r  r  r   r  r   r   filename_m3u8r[  r  r7   r7   r8   r    sJ   
0,



r  c                 C   s  | j dkr?tjj|d}| jd}| jd}| jd}| jd}| jd}tjj|d}tj }	t|		dd		d
d}
dt| j
d  d t| }dt| d t| d t| d |
 d }dt| d | d t| d |
 d }tjd| dd d| jv rz9t| jd d| | td| d | d}|d |d |d |d| d  || |  W n$   dd l}tjd|j r|d|j d| d |  Y n|j}|d | |_|jj|kr||_|j|kr||_|j|kr||_|j|kr||_|j|kr ||_|	|_d }d!t|j }t||	|d"}|   |   t!d#S d S )$Nr@   rI   r  r  rP  r  r  r  r  r  r   r  r-   r  r  r  r  r  r  r  Tr  r  r   r   r  r  r  r  r  r   zEdit CreativezAdmin Edit creative  id: r  r  )"r   r   r3   rA   r@   r   r   r   rD   r   r1   r   rE  rG  r  r   r   r   r  r  r   rF  r
  r  r   r	  r  rB  r  r  r   r   r  r   )r6   r  r  r  r  rP  r  r  r  r   r  r   r   r  r[  r  r  r  r  r7   r7   r8   r    sb   	
0,




c                 C   s    t j d}t| dd|iS )Nz-id_postz$DAIManagementApp/views_creative.htmlr   )r   r3   r   r   r   )r6   r   r7   r7   r8   views_creative4  s   r   c              	   C   s  | j dkr]tjj| jd d}| jd}tjj|d}| jd}| jd}| jd}tjj||d	}td
t	| td| t
j
t|d}|d}t|d }t|d }	t
j
 }
t|dt|||	|
|jd}|  d}tt|D ]}| jdt| d }| jdt| d rG| jdt| d }| jdt| d }|d | d }| jdt| d }|d | d }d}t
j
||t
j
|| }t
j
t|d}|d}t|j|||d}|  t|j|d|
d}|  tt|D ]=}| jdt| d t| d rE| jdt| d t| d }|d 7 }t|j|t|d  |d!}|  q	q|t|||jd}|jd" |j }t| d#S tjj| jd d}t| d#d$d%|iiS )&Nr@   r-   r.   rH  r   r8  rR   numofwinr   r8  ZTYYYYYYYYYYYYYYYYYYYYYPErI  %m/%d/%Yr   r  T23:59:00+00:00rS   )r   r   r   
start_dateend_datecreation_datetimeid_zone_channel_idr   	numofads[]windowstart[r  :00
windowend[r  %H:%M:%Sr  id_playlist_idr  
window_endr:  id_window_idr<  r>  r   ad[][r&   id_avail_idid_adspot_idrC  rM  /schedules/"DAIManagementApp/new_playlist.htmlrE   r   )r   
Useraccessr3   rA   r1   r@   r   r2  r   r{  r   r3  rD   r   r   r   r   r  r   r4  r9  r  r;  r  r   r  rT  ftp_channel_namerQ   r   r4   )r6   
useraccessrH  channeldatar8  daydatenumber_of_windowsrI  r%  r&  r   Playlisttrafficr   number_of_adsr  r1  FMTr:  r  r  jr   AdspotsInAvxmlfilenamerg  r   r7   r7   r8   new_playlist_old:  s\   



&$
rH  c                 C   s6   | j d}tjj|ddd }t| dd|iS )Nr   rS   )r   r  r   z&DAIManagementApp/dropdown_adspots.htmlr   )GETrA   r   r3   r4   r?  r   r   )r6   rH  r   r7   r7   r8   load_adspotsp  s   rJ  c                 C   *   | j d}tjj|d}t| dd|iS )Nr   r   z%DAIManagementApp/dropdown_region.htmlregions)rI  rA   r2  r3   r4   r   )r6   rH  rL  r7   r7   r8   load_regionsx     rM  c                 C   s  | j dkratjj|d}tjj| jd d}tjj|jjd}|j	}t
jj|jd}t
jj|jdjddd	}tjj|d
jddd	}tjj|d
}	tjj|d}
||||||
|	d}t| d|S t| jd }tjj|djddd	}tjj|dd}d|i}t| d|S )Nr@   rI   r-   r.   r   r  r  TrL   )Zid_window__inr  )Zid_avail__in)r   r   rQ   drL  r   av&DAIManagementApp/edit_playlist_id.htmlr   r   -id_playlistr   #DAIManagementApp/edit_playlist.html)r   r   r3   rA   r   r4   r1   r2  r   r   r9  r  r   r;  r   r   rD   r   )r6   r  r   r   rQ   rO  rL  winrO  rP  r   rE   r  r   r7   r7   r8   edit_playlist_old~  s"   
rV  c           -      C   sl  | j dkr| jd}| jd}|rtjj| jd d}| jd}tjj|d}| jdd	 }|d
\}}}	t	jj||||	d}
| jd}| jd}| jd}t
j
t|d}|d}t|d| | d| dt
j
 |
jddd	}|  d}d}|rt|dkrtt|D ]}| jdt| d r| jdt| d }| jdt| d }|d | d }| jdt| d }|d | d }d}t
j
||t
j
|| }t
j
t|d}|d}t|j|||d }|  tt|D ]}| jd!t| d" t| d r| jd!t| d" t| d }|d | d }| jd#t| d" t| d }t|j|t|d$ t
j
 d%}|  tt|D ]I}| jd&t| d" t| d" t| d r| jd&t| d" t| d" t| d }|d$7 }t|j|t|d$ |d'}|  qgqqt|}|d( rd)} d*}!d+}"d+}#|d, }$d-}%|jjd.krd-}%n	|jjd/krd0}%t| |!|"|#}&|&|$|% n	td1|d2   td3t
j
 d4|j d5}'|'  t d6|jd7S t d8S tjj| jd d}| jd}tjj|d}| jd}| jd}| jd}t	jj||d9}
t
j
t|d}|d}t|d }(t|d })t
j
 }*t|dt||(|)|*|
jddd	}|  d}d}tt|D ]}| jdt| d r| jdt| d }| jdt| d }|d | d }| jdt| d }|d | d }d}t
j
||t
j
|| }t
j
t|d}|d}t|j|||d }|  tt|D ]}| jd!t| d" t| d r| jd!t| d" t| d }|d | d }| jd#t| d" t| d }t|j|t|d$ |*d%}|  tt|D ]I}| jd&t| d" t| d" t| d r| jd&t| d" t| d" t| d }|d$7 }t|j|t|d$ |d'}|  qMqq|j}+t d:S tjj!| jd d},t"| d;d<d=|,iiS )>Nr@   	apply_btn	draft_btnr-   r.   rH  r   r8  r/   r   )r   rQ   r8  r   r!  r   rR   r#  r   rS   r  r$  r   	r   r   r   r%  r&  r'  r(  r   r   r   numofavails[r*  r+  r  r,  r-  r  r.  r  r/  availstart[r5  r)  r&   r2  r4  r6  r  172.16.205.1`  
SanoaMediar   2M/schedules/REG1_NLREGNLrU  2M/schedules/REG1_FR)Failed to generate playlist file. Error: messagezAdd PlayListzAdmin Add PlayList  id: r  view_playlistr  new_playlistr"  draft_playlistr:  rE   r   )#r   r@   rA   r;  r3   r1   r   stripsplitr2  r   r3  rD   r   r   r   r   r  r4  r   r9  r  r;  r  r   r  generateScheduler   FTPConnectorupload_filer   r   r   r4   r   )-r6   rW  rX  r=  rH  r>  r8  rQ   zonenetworkrI  r@  r   r?  rA  rB  record_insertedr   numofavailsr  r1  rD  r:  r  rE  av_startrC  r  kr   rF  xml_playlist_resftp_hostftp_portftp_usernameftp_passwordrw  rx  rh  r  r%  r&  r   last_id_playlistr   r7   r7   r8   re    s&  

&$$
20





 
&$$20re  c           )      C   s  | j dkr~| jd}| jd}| jd}| jd}| jd}|rVtjj|d}tjj| jd d	}tjj|j	j	|j	j
|j	j|j	jd
}	|j}
tjj|jdjddd}i }g |d< d|d< d}tjj|jdd}|D ]}i }||d< |d }|j|d< tj|jd}|d}tj|jd}|d}||d< ||d< g |d< d|d< ||d< tjj|jd}d}|D ]c}i }||d< |d }|j|d< tj|jd}|d}||d< g |d< |d | tjj|jd}||d< d}d|d < |D ]!}i }||d!< |d }||d < |j|d"< |j|d#< |d | qq|d | qstj }d$} d%t |j }!t!| ||!d&}"|""  |d |||	|
|d d'}#t#| d(|#S |rotjj|d}tjj| jd d	}tjj|j	j	|j	j
|j	j|j	jd
}	|j}
tjj|jdjddd}i }g |d< d|d< d}tjj|jd}|D ]}i }||d< |d }|j|d< tj|jd}|d}tj|jd}|d}||d< ||d< g |d< d|d< ||d< tjj|jd}d}|D ]e}i }||d< |d }|j|d< tj|jd}|d}||d< g |d< |d | tjj|jd}||d< d}d|d < |D ]"}i }||d!< |d }||d < |j|d"< |j|d#< |d | q.q|d | q|d |||	|
|d d'}#t#| d)|#S |rxt$d*|dS t$d+|dS tj t%d,d- }t | jd }$tjj|$d	jd.dd}tjj|d/|d0d1}%tjj|%d2}g }&|D ]>}i }'|jj|'d3< t |j&d4d }(|('d5d6}(t |j&d4d |'d< t |j&d4d |'d< tjj|d}g |'d7< q|%|&d8}t#| d9|S ):Nr@   edit_btnview_btninsertion_results_btnduplicate_btnrX  rI   r-   r.   )r   rQ   r8  r   r  r  TrL   rL  r   number_of_winsr  r   r&   r  %H:%Mr1  avails_in_winnum_of_availsr  rE  r  r<  adspots_in_availr  num_of_adspotsrq  id_adsinavailr  Edit PlayListAdmin Edit PlayList  id: r  rE   r   r   rQ   rO  r|  rQ  +DAIManagementApp/duplicate_playlist_id.htmlrd  insertion_results   r   r   r   )r   r   Zstart_date__gte-broadcastdateZid_playlist__inr  r  r   r/   rO  )r   detailsrT  )(r   r@   rA   r   r3   r   r4   r1   r2  r   rQ   r8  r   r   r9  r  r   r   r  r   r3  r  r   r1  r;  r  r<  r   r   r  r  r   rD   r   r  r   r   r   rh  r   ))r6   r  rx  ry  rz  r{  rX  r   r   rQ   rO  rU  rE   r   rL  rN  
window_dicwindow_start_formatedwindow_start_formated_2window_end_formatedwindow_end_formated_2rO  rE  r   	avail_dicavail_start_formatedavail_start_formated2r   rq  r   
adspot_dicr   r  r  r  data_playlistr  r   r  detailbroadcastDate_verifr7   r7   r8   edit_playlist^  s&  















r  c           &      C   s>  | j dkrl| jd}| jd}| jd}| jd}| jd}|rMtjj|d}tjj| jd d	}tjj|j	j	d
}	|j
}
tjj|jdjddd}i }g |d< d|d< d}tjj|jdd}|D ]}i }||d< |d }|j|d< tj|jd}|d}tj|jd}|d}||d< ||d< g |d< d|d< ||d< tjj|jd}d}|D ]c}i }||d< |d }|j|d< tj|jd}|d}||d< g |d< |d | tjj|jd}||d< d}d|d < |D ]!}i }||d!< |d }||d < |j|d"< |j|d#< |d | qq|d | qjtj }d$} d%t|j }!t| ||!d&}"|"  |d |||	|
|d d'}#t | d(|#S |r]tjj|d}tjj| jd d	}tjj|j	j	d
}	|j
}
tjj|jdjddd}i }g |d< d|d< d}tjj|jd}|D ]}i }||d< |d }|j|d< tj|jd}|d}tj|jd}|d}||d< ||d< g |d< d|d< ||d< tjj|jd}d}|D ]e}i }||d< |d }|j|d< tj|jd}|d}||d< g |d< |d | tjj|jd}||d< d}d|d < |D ]"}i }||d!< |d }||d < |j|d"< |j|d#< |d | qq|d | q|d |||	|
|d d'}#t | d)|#S |rft!d*|dS t!d+|dS tj t"d,d- }t| jd }$tjj|$d	jd.dd}tjj|d/|d0d1}%d2|%i}t | d3|S )4Nr@   rx  ry  rz  r{  rX  rI   r-   r.   r   r  r  TrL   rL  r   r|  r  r   r&   r  r}  r1  r~  r  r  rE  r  r<  r  r  r  rq  r  r  r  r  r  r  rQ  r  rd  r  r  r   r   r   )r   r   Zstart_date__lter  r   z)DAIManagementApp/edit_older_playlist.html)#r   r@   rA   r   r3   r   r4   r1   r2  r   r   r9  r  r   r   r  r   r3  r  r   r1  r;  r  r<  r   r   r  r  r   rD   r   r  r   r   r   )&r6   r  rx  ry  rz  r{  rX  r   r   rQ   rO  rU  rE   r   rL  rN  r  r  r  r  r  rO  rE  r   r  r  r  r   rq  r   r  r   r  r  r  r  r  r   r7   r7   r8   edit_older_playlist	  s   














r  c           ;      C   s  t jj|d}tjj|jjd}tjj|jjd}|j}|j}|j	
dd}|jdkr.|jnd}|j}	tjj||j|d }
|j	}t|j	
dd}tjj|jd	jd
dd}i }g |d< d|d< d}d}|
rk|
jnd}|d | d |	 d }|dkrRtd d| d}tj|dd d}d}d}d}d| }| d| }t||||}||| t| rRt|}|d}|j d}g }|D ]}| d}| d} | d}!|!
d d!}"|"
d"d}#| d#}$| d$}%| d%}&z8tjj||j||| d&}'|'r|#|'_!|$|'_"|%|'_#|&|'_$||'_|'%  nt||j||| |#|$|%|&|d'
}(|(%  W q tj&yQ   td( t||j||| |#|$|%|&|d'
}(|(%  Y qw tjj|jd	'd)})|)D ]}*i }+||+d*< |d }|*j(|+d
< t)j)*|*j+d+},|,,d,}-t)j)*|*j-d+}.|.,d,}/|-|+d)< |/|+d-< g |+d.< d|+d/< ||d< t.jj|*j(d0}0d}1|0D ]}2i }3|1|3d1< |1d }1|2j/|3d2< t)j)*|2j0d+}4|4,d,}5|5|3d3< g |3d4< |+d. 1|3 t2jj|2j/d5}6|1|+d/< d}7d|3d6< |6D ]V}8i }9|7|9d7< |7d }7|7|3d6< |8j3|9d8< |8j4|9d9< td: ztjj||8j5t6|j7d;8d<}
|
j#|9d$< |
j!|9d< W n tj&y4   td( Y nw td= |3d4 1|9 qq|d 1|+ d}q_|d |||||d |d>}:t9| d?|:S )@NrI   r   r   r   r/   r&   2005r   r8  r   r  r  TrL   rL  r   r|  false.verWait I'm in ftpfiles/results//verifsr  r\  r]  r^  z
2M/verifs/r   r   r~  r#  r&  r  r  r  r  r  r  r	  r   r8  r   r#  r&  r  oupsr  r   r  r}  r1  r~  r  r  rE  r  r<  r  r  r  rq  r  r  Wait I'm in databaser   r#  Zrevision__lteid_veriffile not ready yetrE   r   r   rQ   rO  r|  rn  z'DAIManagementApp/insertion_results.html):r   r3   rA   r   r   r2  r   r<  r   r   r   r8  verifs_numberr   r4   lastrD   r9  r  r   r  r   rE  rG  rj  Zdownload_filer   rF  r  r  r  r  r  r  r  rf   r	  r  rB   r   r  r   r3  r  r   r1  r;  r  r<  r   r   r  r  rM  r4  r   latestr   );r6   r  r   r   rQ   r<  r   r   r8  r  r   rO  r  rU  rE   r   rn  r~  filename_in_ftppath_in_apprs  rt  ru  rv  Zremote_pathZ
local_pathrh  r  r  r  r  r#  r&  r  r  r  r  r  r   verif_to_updatenew_ad_verifrL  rN  r  r  r  r  r  rO  rE  r   r  r  r  r   rq  r   r  r  r7   r7   r8   r  J
  s@  

















 
	r  c           4      C   s  t jj| d}tjj|jjd}tjj|jjd}|j}|j}|j	}|j
dd}tjj|||d }|j
}	t|j
dd}
tjj|jdjdd	d
}i }g |d< d|d< d}d}|ra|j}nd}|dkrItd |d | d }d| d }d| d }tj|st| tjjdd}t| t|j|j|j||| t|d |  rIt |d | }|!d}|j"#d}g }|D ]}|#d}|#d}|#d}|dd}|dd}|#d}|#d }|#d!}z6tjj|||||d"} | r|| _$|| _%|| _&|| _'|| _| (  nt||||||||||d#
}!|!(  W q tj)yH   td$ t||||||||||d#
}!|!(  Y qw tjj|jd*d%}"|"D ]}#i }$||$d&< |d }|#j+|$d< t,j,-|#j.d'}%|%/d(}&t,j,-|#j0d'}'|'/d(}(|&|$d%< |(|$d)< g |$d*< d|$d+< ||d< t1jj|#j+d,})d}*|)D ]}+i },|*|,d-< |*d }*|+j2|,d.< t,j,-|+j3d'}-|-/d(}.|.|,d/< g |,d0< |$d* 4|, t5jj|+j2d1}/|*|$d+< d}0d|,d2< |/D ]V}1i }2|0|2d3< |0d }0|0|,d2< |1j6|2d4< |1j7|2d5< td6 ztjj|
|1j8t9|j:d7;d8}|j&|2d < |j$|2d< W n tj)y+   td$ Y nw td9 |,d0 4|2 qq|d 4|$ d}qV|d ||||	|d |d:}3d S );NrI   r   r   r   r/   r  r  r  TrL   rL  r   r|  r  r  
-00001.verr   /verifs/r  r  r&   r.   r   r~  r#  r&  r  r  r  r  r  r  r	  r  r  r  r  r   r  r}  r1  r~  r  r  rE  r  r<  r  r  r  rq  r  r  r  r  r  r  r  )<r   r3   rA   r   r   r2  r   r<  r   r8  r   r   r   r4   r  rD   r9  r  r   r  r   rE  r   rF  rG  r;  rl  
ftp_serverftp_userrv  r   r  r  r  r  r  r  r  rf   r	  r  rB   r   r  r   r3  r  r   r1  r;  r  r<  r   r   r  r  rM  r4  r   r  )4r  r   r   rQ   r<  r   r8  r   r   rO  r  rU  rE   r   rn  r~  r  path_in_ftpr  r=  r  r  r  r  r#  r&  r  r  r  r  r  r   r  r  rL  rN  r  r  r  r  r  rO  rE  r   r  r  r  r   rq  r   r  r  r7   r7   r8   insertion_results_getter
  s   
















 
 r  c           8      C   s  | j dkr| jd}| jd}|r6| jd }tjj|d}| jd}tjj|d}| jd}| jd	}	| jd
}
| jd}tjt	|d}|
d}t	|d }t	|d }tj }| jd}tjj|d}|j}|j}|j}|j}|j}tjj| |jjd }t|d }t|t	|t	|||||jjd|d	}|  d}d}d}tt|	D ]}| jdt	| d r| jdt	| d }| jdt	| d }|d | d }| jdt	| d }|d | d }d} tj|| tj||  }!tjt	|!d}!|!
d}!t|j|||!d}"|"  tt|D ]}#| jdt	| d  t	|# d r| jdt	| d  t	|# d }$|d |$ d }$| jd!t	| d  t	|# d }%t|"j|$t	|#d |d"}&|&  tt|%D ]K}'| jd#t	| d  t	|# d  t	|' d r| jd#t	| d  t	|# d  t	|' d }(|d7 }t|&j|(t	|'d |d$})|)  d}qq,q|j}*t|}+t|+ |+d% rd&},d'}-d(}.d(}/|+d) }0d }1|jj d*krd+}1n	|jj d,krd-}1|1rt!|,|-|.|/}2|2"|0|1 n	td.|+d/   t#d0tj d1|j d2}3|3  t$d3|*d4S tjj| jd d}| jd}tjj|d}| jd5}4| jd}| jd	}	t%jj||4d6}5tjj|d}|j}|j}|j}|j}|j}tjj|d7&t'dd8}6|6}7t	|d }t	|d }tj }t|||||||5jd9|7d d	}|  d}d}tt|	D ]}| jdt	| d r| jdt	| d }| jdt	| d }|d | d }| jdt	| d }|d | d }d} tj|| tj||  }!tjt	|!d}!|!
d}!t|j|||!d}"|"  tt|D ]}#| jdt	| d  t	|# d r| jdt	| d  t	|# d }$|d |$ d }$| jd!t	| d  t	|# d }%t|"j|$t	|#d |d"}&|&  tt|%D ]I}'| jd#t	| d  t	|# d  t	|' d r| jd#t	| d  t	|# d  t	|' d }(|d7 }t|&j|(t	|'d |d$})|)  qq2q|j}*t$d:S d S );Nr@   rW  rX  r-   r.   rH  r   rR   r!  r   r   r   r  r$  rI   )r   r   r&   r   rY  r   rZ  r*  r+  r  r,  zwinend_[r  r.  r  r/  r[  r5  r)  r2  r4  r6  r  r\  r]  r^  r   r`  r_  rU  ra  rb  rc  zUpdate PlayListzAdmin Update PlayList id: r  rd  r  r8  r"  r   draft_version__maxrS   rf  )(r   r@   rA   r1   r;  r3   r   r   r3  rD   r   r   r   r   r   r   r%  r&  r4   r   countr4  r  r   r9  r  r;  r  r   r  ri  r   r   rj  rk  r   r   r2  	aggregater   )8r6   r  rW  rX  r-   r=  rH  r>  r?  r@  r   Zdraft_version_oldr%  r&  r   r   r   Z	version_pZbroadcastdate_pZdraft_version_pZstart_date_pZ
end_date_pmax_versionnew_versionrA  rB  rn  var_testr   ro  r  r1  rD  r:  r  rE  rp  rC  r  rq  r   rF  rw  rr  rs  rt  ru  rv  rw  rx  rh  r  r8  rI  max_version_draftnew_version_draftr7   r7   r8   update_playlist  sP  



&$$
20




 
&$$20 r  c                 C   s  t jj|d}tjj| jd d}tjj|jjd}|j}t	jj|j
djddd}i }g |d	< d
|d< d
}d
}	t	jj|j
d}
|
D ]}i }||d< |d }|j|d< tj|jd}|d}tj|jd}|d}||d< ||d< g |d< d
|d< ||d< tjj|jd}d
}|D ]c}i }||d< |d }|j|d< tj|jd}|d}||d< g |d< |d | tjj|jd}||d< d
}d
|d< |D ]!}i }||d< |d }||d< |j|d< |j|d< |d | qq|d	 | d
}	qB|d	 |||||d |	d}t| d|S ) NrI   r-   r.   r   r  r  TrL   rL  r   r|  r   r&   r  r}  r  r1  r~  r  r  rE  r  r<  r  r  r  rq  r  r  r  z&DAIManagementApp/view_playlist_id.html)r   r3   rA   r   r4   r1   r2  r   r   r9  r  r   r  r   r3  r  r   r1  r;  r  r<  r   r   r  r  r   )r6   r  r   r   rQ   rO  rU  rE   r   rn  rL  rN  r  r  r  r  r  rO  rE  r   r  r  r  r   rq  r   r  r  r7   r7   r8   rd  R  sl   






rd  c                 C   sn  | j dkrtjj|d}tjj| jd d}tjj|jjd}|j	}t
jj|jdjddd	}i }g |d
< d|d< d}t
jj|jd}	|	D ]}
i }||d< |d }|
j|d< tj|
jd}|d}tj|
jd}|d}||d< ||d< g |d< d|d< ||d< tjj|
jd}d}|D ]c}i }||d< |d }|j|d< tj|jd}|d}||d< g |d< |d | tjj|jd}||d< d}d|d< |D ]!}i }||d< |d }||d< |j|d< |j|d< |d | qq|d
 | qF|d
 |||||d d}t| d |S t| jd }tjj|djd!dd	}tjj|d"d#}d$|i}t| d%|S )&Nr@   rI   r-   r.   r   r  r  TrL   rL  r   r|  r   r&   r  r}  r  r1  r~  r  r  rE  r  r<  r  r  r  rq  r  r  r  r  r   rR  rS  r   z(DAIManagementApp/duplicate_playlist.htmlr   r   r3   rA   r   r4   r1   r2  r   r   r9  r  r   r  r   r3  r  r   r1  r;  r  r<  r   r   r  r  r   rD   r   r6   r  r   r   rQ   rO  rU  rE   r   rL  rN  r  r  r  r  r  rO  rE  r   r  r  r  r   rq  r   r  r  r  r   r7   r7   r8   duplicate_playlist  st   






r  c           &      C   s  | j dkr| jd }tjj|d}| jd}tjj|d}| jd}| jd}| jd}tjj||d	}	| jd
}
tj	t
|d}|d}t
|d }t
|d }tj }tjjt
|dd}| }|rt|d
r|jd urt|jnd}t||d t
|||||	jddd	}|  d}d}d}tt|D ]}| jdt
| d r| jdt
| d }| jdt
| d }|d | d }| jdt
| d }|d | d }d}tj	||tj	|| }tj	t
|d}|d}t|j|||d}|  tt|D ]}| jdt
| d  t
| d r| jdt
| d  t
| d }|d | d }| jd!t
| d  t
| d }t|j|t
|d |d"}|  tt|D ]K} | jd#t
| d  t
| d  t
|  d r| jd#t
| d  t
| d  t
|  d }!|d7 }t|j|!t
| d |d$}"|"  d}qpqq|j}#t|||	j|d  }$|jd% |	j }%t d&|#d'S d S )(Nr@   r-   r  rH  r   r8  rR   r!  r"  r   r#  r   r  r$  r  -creation_datetimer&   r   rY  r   rZ  r*  r+  r  r,  r-  r  r.  r  r/  r[  r5  r)  r2  r4  r6  r9  rd  r  )!r   r1   r;  r3   rA   r@   r   r2  r   r3  rD   r   r   r   r4   r   firsthasattrr   r4  r   r  r   r9  r  r;  r  r   r  rT  r<  rQ   r   )&r6   r  r-   r=  rH  r>  r8  r?  r@  rI  r   r%  r&  r   Zfiltered_playlistsZlast_playlistZlast_playlist_versionrA  rB  rn  r  r   ro  r  r1  rD  r:  r  rE  rp  rC  r  rq  r   rF  rw  rG  rg  r7   r7   r8   duplicate_playlist_update  s   


&
&$$20
r  c              	   C   s  t | d}|  }d}|D ]}|d}|d  dkr|d  d}d|d kr|d  d	d
}|dd }t|dd}|dd }	|	dd dkr^d|	dd   }	|dkrd|}
t|td |	 }|d d	}|d dd }|dd }|dd dd
}|d dd }|d7 }tt|d |  t	j
j|d}tj
j||||	||d}q|  t| |
S )Nr   r   |r&   infor   r  ZDetected,r/   r   r   r  Z2400r   ()   z Date : rI   )r   r   rR   r@  rd   rB  )r   read
splitlinesrh  rg  r   	date_timerD   r   r   r3   rA   Adbreak_historyr  r  )	file_pathr   r   r  linelmsgr   rR   r@  Z	first_dayr   r  r   chidrB  logr7   r7   r8   insert_logs"  s<   

r  c                 C   s   t | j | jdkrHt t| jd  dt| jd  }t| jd }tj|s3t d t| nt d t	| jd || t
|d | }tj tdd	 }tjj|d
}t| dd|iS )Nr@   Z	logs_filez
logs/user_r-   zpath not  existzpath exist r      r   )Zdatetime__gtezDAIManagementApp/add_logs.htmllogs)r   r   rD   r  r1   rE  r   rF  rG  r   r  r   r   r   AdbreakHistoryr3   r4   r   )r6   r   r   rR   r   r  r7   r7   r8   r  P  s   

r  c                 C   s.   |  d}|d d |d  d |d  }|S )Nr   r   r   r&   rh  )r   r   r7   r7   r8   r  h  s   
 r  c                 C   s   d}t j }| jdkr:| jd}| jd}td| d |  t jj|d}tjj||d}d	}|||d
}n||d}t	| dd|iS )NrA   r@   rH  rR   z ID : z DAY : rI   )r   rR   post)r  r   r   )r   r   zDAIManagementApp/get_logs.htmlrE   )
r   r3   r   r   r@   rA   r   r  r4   r   )r6   r   Zchanelsr  rR   r   r  rE   r7   r7   r8   get_logsm  s"   

r  c                 C   s  | j dkr| jd}| jd}| jd}| jd}| jd}| jd}| jd}| jd}| jd	}|d krAd
}| jd}	g }
tj }|D ]}|
| j|j qPtjj|d}t	jj|d}t
||||||t|t|t|	d	}|  |j }d}dt|j }t|||d}|  t|j t
jjt|jd}|
D ]}|d krtjj|d}t||d}|  qt
j }t| dd|iS tjj| jd djdd}tjj| jd d}t	jj|d}tt| tj }|||d}t| dd|iS )Nr@   campaign_namer   r  ri   r  r  volumedeliveryr   cpmrI   )	r   id_advertiserr   ri   r  r  r  r  r  Add CampaignAdmin Add Campaign  id: r  )id_timer	  z%DAIManagementApp/viwes_campaigns.htmlrz   r-   r.   rS   r  rR  )r   r   r   z"DAIManagementApp/add_campaign.htmlrE   )r   r@   rA   DayTimer3   r   r   r   r   r   r   r4  r`   r  r   r   rD   r	  r   r   	Placementr   r   r4   r1   r   r   )r6   r   r   r  ri   r%  r&  r  r  r  	placementr@  r   r   r  r  r   r   r  r  r  pr   r   r   rE   r7   r7   r8   add_campaign  s`   





r  c           '      C   s  ddl m } | jdkr| jd}| jd}| jd}| jd}| jd}| jd	}| jd
}| jd}	| jd}
| jd}| jd}| jd}| jd}| jd}| jd}| jd}d}|dkrud}n&|t|d}|t|	d}tt|| j t	|t	t|| j }t
d7i d|dtjj|ddtjj|dd|d|d|d
|d|	dt|
dt	|dt|dt|dt	|d|d|d|dt	|d| jd d dd!tj}|  tjj| jd d"}|dkrb|D ]-}| jd#t|j }|d kr7|D ]}tjjt	|d}t|||d$}|  qqd%}d&t|j }| }t|||d'}|  t
jj| jd d(d)} td*S | jd+}!|!d kr|!D ]}tjjd,d-}t|||d.}|  qot
jj| jd d(d)} td*S tjj| jd dd/}"tjj|"d0}#tjj| jd d"}tjj| jd d"jd1d2d }$t jj|d3}%tjj!d,d-}|#|"|%|||$d4}&t"| d5d6|&iS )8Nr   r  r@   r  r   r   product_nameid_category
id_countryr  r  budgetr  r  broadcasts_daygeneral_rotation
media_typeprogram_categoriesri   r/   r   r   r   rI   productr  countryr  program_categoryr  r-   r   r  r.   check_placement_r  r	  r   r  r  r  r  -id_campaignr  channels_checked   r  r  r	  r   r   Zid_agency__inrS   r  rR  )r   agencyr   r   r   advertisers_for_creativez&DAIManagementApp/add_campaign_new.htmlrE   r7   )#r   r   r@   rA   getlistr3  rD   r   r   r4  r   r   r3   r   r`   r1   CampaignStatus	Prebookedr  r   r4   r   r  r  r	  r   r   r   r   r   r?  r   r   excluder   )'r6   r   r   r   r   r  r  r  r%  r&  r  r  r  r  rotationr  r  ri   r  start_date1	end_date1r  r   r   channel_placementsplacement_of_channelr@  r  r  r  r   r  rz   r  r   r   r  r   rE   r7   r7   r8   add_campaign_new  s   	




&r  c                 C   s0   | j d}tjd| d }t| dd|iS )Nr   z(SELECT * FROM Adspots where id_brand = "z" GROUP BY ad_groupz/DAIManagementApp/dropdown_adspots_campaign.htmlr   )rI  rA   r   r3   rawr   )r6   r  r   r7   r7   r8   load_creativesF  s   r  c                 C   rK  )Nr   r   3DAIManagementApp/dropdown_advertisers_campaign.htmlr   )rI  rA   r   r3   r4   r   )r6   Z	agency_idr|   r7   r7   r8   load_advertisersP  rN  r  c                 C   s&   t jj| jd dd}t| dd|iS )Nr-   r   )r  r   .DAIManagementApp/dropdown_agency_campaign.htmlr   )r   r3   r4   r1   r   )r6   r   r7   r7   r8   load_agencyV  s   r  c                 C   s2   t jj| jd d}tjj|d}t| dd|iS )Nr-   r.   rR  z0DAIManagementApp/dropdown_creative_campaign.htmlr   )r   r3   r4   r1   r   r   )r6   r   r   r7   r7   r8   load_creative[  s   r  c                 C   s   | j d}t| | j d}t| | j d}| j d}tj }d}t|dd}|dd	}|d
 | d }| j d}	tjj|	d}	t	|	|||||d | d}
|

  t| jd |
j||d}|
  t| dS )Nr   r  r  r  r  r  r  r  r   r  r  r   r   r   )r   r   r  r  r  r   r-   r  r  )r@   rA   r   r   r   rD   r   r   r3   r   r  r   r1   r   r   )r6   r  r  r  r  r   r   r  r   r   r  r  r7   r7   r8   send_advertisersa  s$   

r  c                 C   s\   | j d}t| | j d}t| tj }t| jd |||dd}|  t| dS )Nname_agencydescription_agencyr-   r   r  r   r  r   r   r  )	r@   rA   r   r   r   r   r1   r  r   )r6   r  r  r   r   r7   r7   r8   send_agencyw  s   

r  c              
   C   s&  | j d}| j d}| j d}| j d}| j d}tj }t|dd}|dd	}d
t| jd  d t| }tj	d| sOt
d|  |dd	}	dt| d | d t| d |	 d }
t|
 t| jd d| |
 t||||||d |
 ||
d}|  t| dS )NrH  r  r  rP  r  r  r  r  r   r  r-   z/ch_r  r  Zch_r  r  r  r  r   )r   r  r  rB  r  r
  r   r   r  )r@   rA   r   r   rD   r   r1   rE  r   rF  rG  r   r   r  r   r  r   )r6   rH  r  r  rP  r  r   r  r   Znew_now_infiler   r  r7   r7   r8   send_creative  s$   
, 
r  c                 C   s   | j dkr.tjj|d}|jdk|_|  tj }d}dt| }t	|||d}|  tjj
| jd dd	}t| d
d|iS )Nr@   rI   FzDisable CampaignzAdmin Disable Campaign  id: r  r-   r  r  z&DAIManagementApp/disable_campaign.htmlrz   )r   r   r3   rA   r   r  r   r   rD   r   r4   r1   r   r   )r6   r	  r  r   r  r  r  rz   r7   r7   r8   disable_campaign  s   

r  c           6   
      sn  ddl m } | jdkr| jd}| jd}|rtjj|d}| jd}| jd}| jd	}| jd
}	| jd}
| jd}| jd}| jd}| jd}| jd}| jd}| jd}| jd}| jd}| jd}| jd}d}|dkr|d kr|d kr|t|d}|t|d}t	t|| j
 t|tt|| j
 }nd}|r||jkr||_|dkr|d kr||jkr||_|dkr|d kr||jjkrtjj|d|_|dkr|d kr||jjkrtjj|d|_|	dkr|	d kr|	|jkr|	|_|
dkr%|
d kr%|
|jkr%|
|_|dkr8|d kr8||jkr8||_|dkrK|d krK||jkrK||_|dkr^|d kr^||jkr^||_|dkrz|d krzt|rzt||jkrzt||_|dkr|d krt|rt||jkrt||_|dkr|d krt|rt||jkrt||_|dkr|d krt|rt||jkrt||_|dkr|d krt|rt||jkrt||_|dkr|d kr||jkr||_|dkr|d kr||jkr||_|dkr#|d kr#||jkr#||_|dkr?|d kr?t|r?t||jkr?t||_|   tjj|jjd}tjj|jjd}tjj!| j"d dd}tjj!|d}t#jj!| j"d d}t$jj!|d }t%jj&d!d"}t'jj!|j(d#)d$} d%d& t*| D } i }!g }"d}#|D ]-}$t'jj!|j(|$d')d(}%d)d& t*|%D }%|$j+|$j,|%d*|!|#< |"-|!|#  |#d+7 }#qt'jj!|j(d#)d(}%d,d& t*|%D }%t	|! |. }&d-}'d.t| }(t/|'|&|(d/})|)   ||||| |"|%||d0	}*t0| d1d2|*iS |rt1d3|d#S t1d4|d#S tjj!| j"d d52d6}+d7},d8}-d9}.dd:l3m4}/ d+d;l5m6 m7 |/8 }0|09|-  fd<d&|0: D }1W d    n	1 s\w   Y  |/8 }0|09|. fd=d&|0: D }2W d    n	1 sw   Y  |. }&g }3|+D ]}4z||4jd}5|&|5kr|3-|4j( W q   Y qt0| d>|+|3|2|1d?S )@Nr   r  r@   rx  ry  rI   r  r   r   r  r  r  r  r  r  r  r  r  r  r  r  ri   r/   rS   r   r   r   r-   r   r  r.   rR  r  r  r	  r   c                 S   rW   rX   r7   rY   r7   r7   r8   r\      r]   z!edit_campaign.<locals>.<listcomp>r	  r   r  c                 S   rW   rX   r7   rY   r7   r7   r8   r\     r]   rd   rH  r  r&   c                 S   rW   rX   r7   rY   r7   r7   r8   r\     r]   zEdit CampaignAdmin Edit Campaign  id: r  )	r  agency_by_brandr   r   placement_channelchannels_and_placementr  agenciesr|   z&DAIManagementApp/edit_campaign_id.htmlrE   view_campaign
load_excelr  r  rl   aP  
        SELECT
            Campaigns.id_campaign,
            Count(Verifs.airStatusCode) as total_ads,
            SUM(SFR_analytics.purcent*1.25*4500000/17) as Total_impressions
        FROM
            Campaigns
        INNER JOIN Adspots ON Campaigns.id_campaign = Adspots.id_campaign
        INNER JOIN Verifs ON Verifs.spotId = Adspots.filename
        INNER JOIN SFR_analytics ON SUBSTRING(Verifs.airTime, 12, 5) = SUBSTRING(SFR_analytics.`minute`, 1, 5)
        AND SUBSTRING(Verifs.airTime, 1, 10) = SUBSTRING(SFR_analytics.`day`, 1, 10)
        AND SFR_analytics.sfr_channel_name = '2M Maroc'
        WHERE Adspots.id_campaign is not null AND Verifs.airStatusCode= 0001
        AND SUBSTRING(Verifs.airTime, 1, 10) > Campaigns.start_day AND SUBSTRING(Verifs.airTime, 1, 10) < Campaigns.end_day
        GROUP BY Campaigns.id_campaign
    rm   rn   rp   c                    rs   r7   r7   rt   rv   r7   r8   r\   |  r]   c                    rs   r7   r7   rt   rw   r7   r8   r\     r]   z#DAIManagementApp/edit_campaign.html)rz   startr   r   );r   r   r@   rA   r   r3   r  r3  rD   r   r   r4  r  r   r   r   r   r   r  r  r  r  r  r`   r  r  r  r  r  r  ri   r  r  r4   r1   r   r   r  r  r  r	  r   r   rd   r   r   r   r   r   r   r   r   ro   r   rq   rr   r   r   r   )6r6   r	  r   rx  ry  r  r   r   r   r  r  r  r%  r&  r  r  r  r  r  r  r  ri   r  r  r  brands_for_agencyr  r!  r|   r   r   Z	datetime1r  r   tab_channels_and_placementch_nr   r  r   r  r  r  rE   rz   r   r   r   ro   r   r   r   r$  r   r   r7   rp   r8   r    s       .
.
.
.
.
   .
)




r  15c                 C   sH  t jj|d}tjj|jjd}tjj|jjd}tjj| j	d d}t
jj|d}tjjdd}tjj|jd	d
}dd t|D }i }	g }
d}|D ],}tjj|j|dd}dd t|D }|j|j|d|	|< |
|	|  |d7 }qMtjj|jd	d}dd t|D }t|	 ||||||
|d}t| dd|iS )NrI   r   r   r-   r.   rR  r  r  r  r   c                 S   rW   rX   r7   rY   r7   r7   r8   r\     r]   z!view_campaign.<locals>.<listcomp>r   r  r  c                 S   rW   rX   r7   rY   r7   r7   r8   r\     r]   r  r&   c                 S   rW   rX   r7   rY   r7   r7   r8   r\     r]   )r  r  r   r   r  r   r  z&DAIManagementApp/view_campaign_id.htmlrE   )r   r3   rA   r   r   r   r   r   r4   r1   r   r  r  r  r	  r   r   rd   r   r   r   r   )r6   r	  r  r%  r  r   r   r   r  r   r&  r'  r   r  rE   r7   r7   r8   r"    s:   
	r"  c                 C   s*   t jj| jd dd}t| dd|iS )Nr-   r.   r  z%DAIManagementApp/views_campaigns.htmlrz   )r   r3   r4   r1   r   r   )r6   r  r7   r7   r8   views_campaigns  s   r)  c                 C   sT  | j dkr(tjj|d}| jd}| jd}| jd}| jd}| jd}| jd}| jd	}	| jd
}
| jd}| jd}| jd}tjj|d}d}tjj|d}||_||_||_	|	|_
||_||_t|
|_||_zt||_W n   d|_Y t||_|  tj }d}dt| }t|||d}|  tjj| jd d}|dkrtjj|d}|  |D ]*}| jdt|j }|d kr|D ]}tjjt|d}t|||d}|  qqn2| jd}t | tjj|d}|  |d kr"|D ]}tjjdd}t|||d}|  qt!d|dS d S )Nr@   rI   r  r   r   r  r  r  ri   r  r  r  r     r   zEdite Campaignr  r  r-   r.   r   r  r  r  r  r  r  r  r"  )"r   r   r3   rA   r@   r   r   r   r  r   ri   r  r  r4  r  r  r  r`   r  r  r   r   rD   r   r   r4   r1   r  deleter  r   r  r   r   )r6   r	  r  r   r   r   r  r%  r&  ri   r  r  r  r  r   r  r   r   r  r  r  r   r  r   r	  r
  r@  r  r  r7   r7   r8   update_campaign  sv   




r,  c           
      C   s   | j d}| j d}tjj||d}tjj||jd}g }|D ]}tj|j	d}|
d}|| q!d|i}	t|	dd	S )
Nr   rQ   r8  r   )r   r   r   z%d.%m.%YdatesFsafe)rI  rA   r2  r3   r   r4   r   r   r3  r   r   r   r#   )
r6   rH  Z	region_idrl  r   r.  r   rJ  r   rE   r7   r7   r8   load_region_dates)  s   
r1  c                 C   s   | j dkrh| jd}| jd}| jd}| jd}| jd}tj  }}tjj| jd d}| jd	}	t|||	||||||d
	}
|
	  tj }d}dt
|
j }t|||d}|	  tdS t| dS )Nr@   rd   languagegenrerh  rK   r-   rI   channel_desc)	r-   rd   r4  channel_langchannel_genrer<  rK   r'  modif_datetimezAdd ChannelszAdmin Add Channels  id: r  /DAIManagement/edit_channels/z"DAIManagementApp/add_channels.html)r   r@   rA   r   r   r2   r3   r1   r   r  rD   r   r   r   r   )r6   r   langr3  rh  rK   creationmodifrf  channel_decsr  r   r  r  r  r7   r7   r8   add_channels9  s$   


r=  c                 C   sh   | j dkrt| tjj|d}t| dd|iS tjj| jd d}tjj|d	d}t| dd	|iS )
Nr@   rI   z&DAIManagementApp/edit_channels_id.htmlr   r-   r.   -id_channelz#DAIManagementApp/edit_channels.htmlr   )
r   r   r   r3   rA   r   r2   r1   r4   r   )r6   r   r   rf  r   r7   r7   r8   edit_channelsP  s   
r?  c                 C   s  | j dkr| jd}| jd}| jd}| jd}| jd}| jd}| jd}tj }	| jd	}
tjj| jd
 d}tjj	||dd }||_
||_||_||_||_||_||_|
|_|	|_tj }d}dt| }t|||d}|  |  tdS d S )Nr@   rd   r2  r3  rh  sfr	zone_namer   r4  r-   rI   )r   r-   r   zEdit ChannelzAdmin Edit Channel  id: r  r8  )r   r@   rA   r   r   r2   r3   r1   r   r4   rd   r5  r6  r<  rK   r8  rm  r4  r7  rD   r   r  r   )r6   r   r   r9  r3  rh  r@  rA  rm  r;  r<  rf  r   r   r  r  r  r7   r7   r8   update_channelsZ  s:   


rB  c           	      C   s   | j dkr&tjj|d }tj }d}dt| }t|||d}|	  t
jj| jd d}tjj|dd}t| d	d
|iS )Nr@   rI   zDelete ChannelzAdmin delete Channel  id: r  r-   r.   r>  z$DAIManagementApp/delete_channel.htmlr   )r   r   r3   rA   r+  r   r   rD   r   r  r2   r1   r4   r   r   )	r6   r   r  r   r  r  r  rf  r   r7   r7   r8   delete_channel{  s   

rC  c              
   C   s  | j dkr| jd}| jd}| jd}dt| jd  d | d | }tjd	| s7td	|  d
| j	d j
d
d   }|| }|d }t| j	d d	| | t| j	d d	| | tj  }}	tjj|d}
t|
||d | |d | ||	|dd}|tj   d}dt|j }t|||d}|  |  tdS tjj| jd d}tjj|d}t| dd|iS )Nr@   jingle_namerH  r  jingles/user_r-   	/channel_r   r  r  
video_filer&   .md5md5_filerI   r   )r   rD  video_jinglerI  r'  r7  r  	is_deletez
Add JinglezAdmin Add Jingle  id: r  /DAIManagement/edit_jingle/r.   z!DAIManagementApp/add_jingles.htmlr   )r   r@   rA   rD   r1   rE  r   rF  rG  r  r   rh  lowerr   r   r   r   r3   Jingles	id_jingler   r  r   r2   r4   r   )r6   r   r   r  r   filename_video_extfilename_videofilename_md5r   r;  r  jingler  r  r  rf  r   r7   r7   r8   
add_jingle  s2   
"(rT  c                 C   s   | j dkr*tjj|d}tjj| jd d}tjj|d}||d}t| dd|iS tjj| jd d}tjj|d}tjj|dd		d
}t| dd|iS )Nr@   rI   r-   r.   )rS  r   z%DAIManagementApp/edit_jingles_id.htmlrE   r   r   rK  
-id_jinglez"DAIManagementApp/edit_jingles.htmljingles)
r   rN  r3   rA   r2   r1   r   r4   r   r   )r6   rO  rS  rf  r   rE   rW  r7   r7   r8   edit_jingle  s   
rX  c                 C   sB  | j dkrtjj|d}| jd}| jd}t| | jd}dt| jd  d t| d	 | }z| jd
 j	
dd  }|| }W n   ddlm}	 |	|jj	}Y |d }
tjd| smtd|  zt| jd
 d| | td|j  W n    dd l}tjd|j r|d|j d| d	 |  Y zt| jd d| |
 td|j  W n    dd l}tjd|j r|d|j d| d	 |
  Y ||_tjj|d}||_|dk|_|d	 | |_|d	 |
 |_tj |_|  tj }d}dt| }t |||d}|  t!dS d S )Nr@   rI   rD  rH  r  rE  r-   rF  r   rG  r  r&   r   r   rH  r  rI  rS   zEdit JinglezAdmin Edit Jingle  id: r  rL  )"r   rN  r3   rA   r@   r   rD   r1   r  r   rh  rM  pathlibr   rJ  rE  r   rF  rG  r   remover  r  rI  rD  r   r   r  r   r   r7  r  r   r   )r6   rO  rS  r   r   r  r   rP  rQ  r   rR  r  r  r   r  r  r  r7   r7   r8   update_jingle  s^   &

r[  c           
      C   s   | j dkr1tjj|d}tj |_d|_|  tj }d}dt	| }t
|||d}|  tjj| jd d}tjj|d}tjj|d	d
d}	t| dd|	iS )Nr@   rI   r&   zDelete JinglezAdmin Delete Jingle  id: r  r-   r.   r   rU  rV  z$DAIManagementApp/delete_jingles.htmlrW  )r   rN  r3   rA   r   r   r7  rK  r  rD   r   r2   r1   r   r4   r   r   )
r6   rO  rS  r   r  r  r  rf  r   rW  r7   r7   r8   delete_jingle  s   

r\  c                 C   s   |dkrt jj|d}t| dd|iS tjj| jd d}tjj|d}g }|D ]}t jj|dd	}|t|d
}|	| q(t| dd|iS )Nr/   r   z+DAIManagementApp/views_channel_jingles.htmlrW  r-   rI   r.   r   )r   rK  )r   r  z#DAIManagementApp/views_jingles.htmlrE   )
rN  r3   r4   r   r2   rA   r1   r   r   r   )r6   r   rW  rf  r   r  r   restr7   r7   r8   views_jingles  s   r^  c                 C   x   | j dkr7t| jd dd}t| d| jd  }tj|s&t	| t
| jd || t|d |  t| dS )	Nr@   Zsfr_filer  r  z	sfr/user_r-   r   z DAIManagementApp/sfr_upload.html)r   rD   r  r   r   r1   rE  r   rF  rG  r   
insert_sfrr   r6   r   r   r7   r7   r8   
sfr_upload!     


rb  c                 C   sX   dd l }	 || }|j}|d t|ddD ]\}}|jd||d q||  dS )Nr   r&   )r$  )ru   columnvaluezLine added successfully.)openpyxlZload_workbookactiveZinsert_rows	enumeratecellr  )r  new_datarf  workbook	worksheetr  rE   r7   r7   r8   add_line_to_excel-  s   


rm  c                 C   s  dd l }dd l}ddlm} ddlm} g d}t| | || }dd tt	|j
D }||_
|ddd	g }i }	| D ]\}
}t|d d
v r\|d	 |	t|d dd < q@|	d d|	d< |dd  D ]}|dd  d|g }|jd | }| D ]\}
}d}t|d dkrA|	d D ]}|d7 }zt||	d d|	d d}tj|	d d}|d }t|tjr|}tj|dd }nt|tjr| }tj|dd }tj| tddd}tj| tddd}tj||}||  kr|k rn n|tdd n||_||_t|ddt|| |_|   W q |y@   t!d Y qw qqld S )Nr   parser)r   )ZNewRowZDataZHerec                 S      g | ]}d t | qS Zcols_rD   r   r7   r7   r8   r\   R      zinsert_sfr.<locals>.<listcomp>   cols_3cols_4)Dateu
   Chaîne(s)zCible(s)KPIz(s)r/   u   Chaînez, r   r  r&   ZHeureCiblerN   ry  rK   ciblerQ   
indicateurrx  %d/%m/%Yr.  r  r   r  z*This entry already exists in the database.)"repandasdateutilro  r   r   rm  
read_excelr   r   r  headiterrowsrD   r   rg  rh  ilocr   r   r3  
isinstancer@  r   combiner   r   rR   rU   r`   subrV   r  r   )r   r  pdro  r   rj  dfr  r  r   r   ru   r  testr   r  r   r@  r   heureZ
heure_timemidnightZam_3Zcombined_heurer7   r7   r8   r`  F  sh   

 0r`  c                 C   s
  dd l }dd l}|| }dd tt|jD }||_|dddg }i }t|}| D ]4\}}	t	|	d dksSt	|	d dksSt	|	d d	ksSt	|	d d
krc|	d |t	|	d 
dd < q/|dd  D ]}
t|
 |dd  d|
g }|jd |
 }| D ]|\}}	t	|	d dkrt||d	 |d |d d}t	|d dd }ddlm} tjt	|d}t	|	d d}t|d dkrt	t|d d d |d  }|tjdd7 }nt	t|d d |d  }||_||_|	|
 |_|  qqjd S )Nr   c                 S   rq  rr  rs  r   r7   r7   r8   r\     rt  z%insert_sfr_backup.<locals>.<listcomp>   Zcols_1Zcols_2rx  u   Régionrz  zIndicateur : r  r/   r   
   r   Z
Indicateurr{  r  r&   rn  r~     r   )numpyr  r  r   r   r  r  r   r  rD   r   rg  r   r  r   rh  r  ro  r   r3  r4  r   rR   rU   rV   r  )r   npr  r  r  r  r   tr   ru   r  r  r   r@  rR   ro  r   r  r@  r7   r7   r8   insert_sfr_backup  sD   
@  
r  c                 C   s   t jjdd}| jdkrH| jd}| jd}| jd}| jd}|dkr+||_|dkr4t||_|dkr=t||_	|dkrD||_
|  t| d	d
|iS )NrS   )r  r@   r   usersrV   rQ   r/   z&DAIManagementApp/edit_impressions.htmlimp)r   r3   rA   r   r@   	tool_namer4  r   r`   r   rQ   r  r   )r6   r  r   r  rV   rQ   r7   r7   r8   edit_impressions'  s    


r  c                    s  ddl m} | jdkr}tjj| jdd}| jd}tjj||jd}| jd}t	j	
|d	}|d
}tjjd|jt|ddd}tjjdd}t|jt|j  dd |D }	 fdd|D }
tjj| jd d}t| d|	|
||dS tjj| jd d}t| dd|iS )Nr   rn   r@   rH  rI   r8  r-  rR   r#  r   2M MarocrK   rQ   rR   rU   rV   rS   c                 S   rW   rX   r7   rY   r7   r7   r8   r\   H  r]   zchart_test.<locals>.<listcomp>c                        g | ]}t t|d    qS r^   r4  r`   rY   ra   r7   r8   r\   I       r-   r.   zDAIManagementApp/pie_chart.htmlry   rE   rR   r   r   )r   ro   r   r   r3   rA   r@   r2  r   r   r3  r   r   r4   rQ   rD   r   r   r`   r   r   r1   r   )r6   ro   r   r8  rQ   rR   r   r   rV   ry   rE   r   r7   ra   r8   
chart_test;  s"   

 r  c                 C   sd  ddl m } tjj| jd dd}dd |D }tj dd d	 }g }|D ]}|j	|v r)t
|jd
d d}|d d |d  d |d  }|j	|j|d}tjj|jd}|j|d< t
|jd
d }	t
|jd
d d}
|
d d |
d  d }||d< |	|d< d|d< t
|jdkrd|d< d|d< ztjj|d |d d}W n   tjjt
|jd
d |d d}Y t| |D ]#}|d |jv rtjjdd}t|jt|j t|j }||d < qn2t
|jd!krd"|d< d|d < n!t
|jd#krd$|d< d|d < d%|d< nd&|d< d|d < d%|d< || q*t| d'd(|iS ))Nr   r  r-   r.   rd   c                 S   rW   rX   r7   rY   r7   r7   r8   r\   U  r]   zverifs.<locals>.<listcomp>z-airTime,  r  r   r   r&   r   r   rR   r8  rQ   r  r,  rU   Z	minute_ss	#00800000r   re   Aired Successfullyr  	#2c2c8cb3rR   rR   rU   r   rS   rI   nb_wach0008Failed, Adspot cut1005Not aired yet	#c7c7c7b3zFailed, Other Reason.zDAIManagementApp/verifs.htmlrE   )r   r   r3   r4   r1   r   r   r   r   r   rD   r  rh  r&  r2  rA   r8  rQ   rf   r   r   rK   r   r`   rV   r   r   r   r   )r6   r   r   r   rE   r  rO  r  rQ   Zmin_ssminrU   Zdonneesr   rV   rb   r7   r7   r8   r   P  sb    
&


r   c           !      C   s  | j dkrP| jd}| jd}| jd}| jd}|r.tjj|d}tjj| jd d}tjj|j	j	d	}|j
}	tjj|jd
jddd}
i }g |d< d|d< d}tjj|jd
d}|D ]}i }||d< |d }|j|d< tj|jd}|d}tj|jd}|d}||d< ||d< g |d< d|d< ||d< tjj|jd}d}|D ]c}i }||d< |d }|j|d< tj|jd}|d}||d< g |d< |d | tjj|jd}||d< d}d|d< |D ]!}i }||d < |d }||d< |j|d!< |j|d"< |d | qq|d | qd|d ||||	|d d#}t| d$|S |rAtjj|d}tjj| jd d}tjj|j	j	d	}|j
}	tjj|jd
jddd}
i }g |d< d|d< d}tjj|jd
d}|D ]}i }||d< |d }|j|d< tj|jd}|d}tj|jd}|d}||d< ||d< g |d< d|d< ||d< tjj|jd}d}|D ]e}i }||d< |d }|j|d< tj|jd}|d}||d< g |d< |d | tjj|jd}||d< d}d|d< |D ]"}i }||d < |d }||d< |j|d!< |j|d"< |d | q q|d | qt|d ||||	|d d#}t| d%|S |rJtd&|d
S td|d
S t| jd }tjj|djd'dd}tjj|d(d)} d*| i}t| d+|S ),Nr@   rx  ry  r  r{  rI   r-   r.   r   r  r  TrL   rL  r   r|  r  r   r&   r  r}  r1  r~  r  r  rE  r  r<  r  r  r  rq  r  r  r  rQ  r  rd  r   rR  rS  r   z'DAIManagementApp/playlists_results.html)r   r@   rA   r   r3   r   r4   r1   r2  r   r   r9  r  r   r   r  r   r3  r  r   r1  r;  r  r<  r   r   r  r  r   r   rD   )!r6   r  rx  ry  rz  r{  r   r   rQ   rO  rU  rE   r   rL  rN  r  r  r  r  r  rO  rE  r   r  r  r  r   rq  r   r  r  r  r   r7   r7   r8   playlists_results  s   













r  c                 C   sp  | j dkrtjj|d}tjj| jd d}tjj|jjd}|j	}t
jj|jdjddd	}i }g |d
< d|d< d}t
jj|jd}	|	D ]}
i }||d< |d }|
j|d< tj|
jd}|d}tj|
jd}|d}||d< ||d< g |d< d|d< ||d< tjj|
jd}d}|D ]c}i }||d< |d }|j|d< tj|jd}|d}||d< g |d< |d | tjj|jd}||d< d}d|d< |D ]!}i }||d< |d }||d< |j|d< |j|d< |d | qq|d
 | qF|d
 |||||d d}t| d |S t| jd }tjj|djd!dd	}tjj|d"d#d$}d%|i}t| d&|S )'Nr@   rI   r-   r.   r   r  r  TrL   rL  r   r|  r   r&   r  r}  r  r1  r~  r  r  rE  r  r<  r  r  r  rq  r  r  r  z,DAIManagementApp/edit_draft_playlist_id.htmlr   rS   )r   r   rS  r   z$DAIManagementApp/draft_playlist.htmlr  r  r7   r7   r8   rf    st   






rf  c           )      C   s  | j dkr| jd}| jd}|r| jd }tjj|d}| jd}tjj|d}| jd}| jd	}	| jd
}
tjj||d}| jd}tj	t
|	d}	|	d}	t
|	d }t
|	d }tj }tjjt
|	dtdd}t|}tjjt
|	dtdd}t|}t|t|t
|	||||jdt|d	}|  d}d}d}tt|
D ]}| jdt
| d r| jdt
| d }| jdt
| d }|	d | d }| jdt
| d }|	d | d }d}tj	||tj	|| }tj	t
|d}|d}t|j|||d }|  tt|D ]}| jd!t
| d" t
| d r| jd!t
| d" t
| d } |	d |  d } | jd#t
| d" t
| d }!t|j| t
|d$ |d%}"|"  tt|!D ]K}#| jd&t
| d" t
| d" t
|# d r| jd&t
| d" t
| d" t
|# d }$|d$7 }t|"j|$t
|#d$ |d'}%|%  d$}qq0q|j}&t|	||jt
|t
|}'|jd( |j }(td)|&d*S | jd }tjj|d}| jd}tjj|d}| jd}| jd	}	| jd
}
tjj||d}| jd}tj	t
|	d}	|	d}	t
|	d }t
|	d }tj }tjjt
|	dtdd}t|}tjjt
|	dtdd}t|}t|t|t
|	||||jd+t|d$ d	}|  d}d}d}tt|
D ]}| jdt
| d r| jdt
| d }| jdt
| d }|	d | d }| jdt
| d }|	d | d }d}tj	||tj	|| }tj	t
|d}|d}t|j|||d }|  tt|D ]}| jd!t
| d" t
| d r| jd!t
| d" t
| d } |	d |  d } | jd#t
| d" t
| d }!t|j| t
|d$ |d%}"|"  tt|!D ]K}#| jd&t
| d" t
| d" t
|# d r| jd&t
| d" t
| d" t
|# d }$|d$7 }t|"j|$t
|#d$ |d'}%|%  d$}qjqq|j}&td,S d S )-Nr@   rW  rX  r-   r.   rH  r   r8  rR   r!  r"  r   r   r  r$  r  Zversion__maxr   r  r   rY  r   rZ  r*  r+  r  r,  r-  r  r.  r  r/  r[  r5  r)  r&   r2  r4  r6  r9  rd  r  rS   rf  )r   r@   rA   r1   r;  r3   r   r2  r   r3  rD   r   r   r   r4   r  r   r4  r   r  r   r9  r  r;  r  r   r  rT  r<  rQ   r   ))r6   r  rW  rX  r-   r=  rH  r>  r8  r?  r@  rI  r   r%  r&  r   r  r  r  r  rA  rB  rn  r  r   ro  r  r1  rD  r:  r  rE  rp  rC  r  rq  r   rF  rw  rG  rg  r7   r7   r8   update_draft_playlistQ  s   


""(
&$$20



"",
&$$20 r  c                    s   ddl m} | jdkrhtjj| jdd}| jd}tjj||jd}t	jj
d|jd	d
dd}tjjdd}t|\}}t|jt|j   fdd|D }	tjj
| jd d}
t| d||	d|
dS tjj
| jd d}
t| dd|
iS )Nr   rn   r@   rH  rI   r8  r-  r  )rK   rQ   rR   rU   rV   rS   c                    s   g | ]
}t t|  qS r7   r  rY   ra   r7   r8   r\     rc   zpredict_sfr.<locals>.<listcomp>r-   r.   zDAIManagementApp/predict.htmlz
2021-09=19r  r   )r   ro   r   r   r3   rA   r@   r2  r   r   r4   rQ   r   r   predictr`   r   r   r1   r   )r6   ro   r   r8  rQ   r   rV   ry   Zpredict_valrE   r   r7   ra   r8   predict_sfr  s   
r  c                 C   s  dd l }dd l}dd l}dd l}ddlm} |j| g dd}g }|d D ]}||j|j 	t
|d   q%|j  }	|j|	 d  }
||d< |j|d |
k }tdt|d	 |_dd
lm} ddlm} ddlm} d}|||| }||jdd}||d dd}||| ||}|jd	gd |	|jdd7 }	|d	 }tdt|D ]}td|	|| t|| d}|  q||fS )Nr   )ARIMA)DayMinutePurcentr  r  r   r&   ZDay_Weekr  )PolynomialFeatures)make_pipeline)LinearRegression   r   r  )subsetr  r   r  )rK   rR   rU   rV   ) r   r  r  calendarZstatsmodels.tsa.arima_modelr  	DataFramer   day_namer3  rD   weekdayr   locr   r   r   Zsklearn.preprocessingr  Zsklearn.pipeliner  Zsklearn.linear_modelr  arrayZreshapeZfitr  Zdrop_duplicatesr   Sfr_predictabsr  )r   r   r  r  r  r  r  weekr   rR   day_weekZdf_finalr  r  r  ZdegreeZpolyregXyr  labelr  r7   r7   r8   r    s<   $


r  c                    s  dd l }dd l}dd l}tj d }dd |D }|D ]H}tjj|ddddd	}|j	|g d
d}g }|d D ]}	|
|j|jt|	d   q>||d< dd |d D }
|
|d< dd |d D }g }|D ]/}	t|	dd  t|	dd  d  }tt|dkrt|d }|
|	d d t|  qp||d< |jdd}|j|  }|j|d |k }t|d  t } fdd|D }dd |D }||jdd7 }|D ]}	|	d dkra|j|d |	d k }t|d t|d  }t|d}tdt|	d  }tt|	d D ]J}|	d dd  }t|| t| }t|dkr5d| }|	d dd d | }tjj|d }td|j|t|d!d ||d"}|  qqt| d#  S d S )$Nr   r   c                 S   rW   rX   r7   r   r7   r7   r8   r\     r]   z'predict_adbreak_old.<locals>.<listcomp>r   rd   rR   r@  rB  Channelr  TimeDurationr  r  r   r  c                 S      g | ]}t |qS r7   r_   r   r7   r7   r8   r\     r]   r  c                 S      g | ]}|d d qS Nr7   rY   r7   r7   r8   r\      rt  r  r  r  r&   r   z
2022-05-26c                       g | ]
}|  |d qS )r@  r  r  r   Zall_timer7   r8   r\   0  rc   c                 S   "   g | ]}|d  |d d dqS r@  r  r  )r@  factr7   r   r7   r7   r8   r\   1     " ru  r   r  333333?r   r  rI   r  r   rd   r   rR   r@  rB  rx   )r  r  r   r  r3   r   r   r  r4   r  r   r  r3  rD   r  r4  r   r  r   r7  r   sumr   r   rh  r   rA   Adbreak_predictrd   r  r   )r6   r  r  r   r   r   adbreakr  r  r   durr@  r  mrR   r  df2time_adr  r  df3mean	intervalerE  rU   time_rH  adbreak_predictr7   r  r8   predict_adbreak_old  s`   
$$
$r  c                    s  dd l }dd l}dd l}tj d }dd |D }|D ]e}tjj|ddddd	}|j	|g d
d}g }|d D ]}	|
|j|jt|	d   q>||d< dd |d D }
|
|d< dd |d D }g }|D ]/}	t|	dd  t|	dd  d  }tt|dkrt|d }|
|	d d t|  qp||d< dd l}| }|ddD ]}|dkrd}dt| }|j|d}|j|  }|j|d |k }t|d  t } fdd|D }dd |D }||jdd7 }|D ]}	|	d dkr}|j|d |	d k }t|d t|d  }t|d }tdt|	d  }tt|	d D ]J}|	d dd  }t|| t| }t|dkrQd| }|	d d!d d! | }tjj|d"}td|j|t|d#d ||d$}|  q2qqt | d%  S d S )&Nr   r   c                 S   rW   rX   r7   r   r7   r7   r8   r\   N  r]   z#predict_adbreak.<locals>.<listcomp>r   rd   rR   r@  rB  r  r  r  r   r  c                 S   r  r7   r_   r   r7   r7   r8   r\   V  r]   r  c                 S   r  r  r7   rY   r7   r7   r8   r\   X  rt  r  r  r  r&   r   i  r  z	2022-06-0c                    r  r  r  r   r  r7   r8   r\   q  rc   c                 S   r  r  r7   r   r7   r7   r8   r\   r  r  ru  r   r  r  r   r  rI   r  r  rx   )!r  r  r   r  r3   r   r   r  r4   r  r   r  r3  rD   r  r4  r   Calendaritermonthdaysr  r   r7  r   r  r   r   rh  r   rA   r  rd   r  r   )r6   r  r  r   r   r   r  r  r  r   r  r@  r  r  calday1rR   r  r  r  r  r  r  r  r  rE  rU   r  rH  r  r7   r  r8   predict_adbreakH  sn   
$$
$r  c                    s  dd l }dd l}dd l}tj d }dd |D }|D ]}tjj|ddddd	}|j	|g d
d}g }|d D ]}	|
|j|jt|	d   q>||d< dd |d D }
|
|d< dd |d D }g }|D ]/}	t|	dd  t|	dd  d  }tt|dkrt|d }|
|	d d t|  qp||d< tt|  |j| d} |j|   }|j|d |k }t|d  t } fdd|D }dd |D }| |jdd7 } |D ]V}	|	d }|	d dkr\|	d dkr\|j|d |	d k }tt|d t|d  d}td}tdD ]?}|	d dd  }t|| t| }|	d d d | }t|d krE|d }tdd!| t| d"d ||d#}|  q|	d d$kr|	d d%kr|j|d |	d k }tt|d t|d  d}td&}td'D ]?}|	d dd  }t|| t| }|	d d d | }t|d kr|d }tdd!| t| d"d ||d#}|  q|	d d(kr<|	d d)kr<|j|d |	d k }tt|d t|d  d}td*}tdD ]?}|	d dd  }t|| t| }|	d d d | }t|d kr%|d }tdd!| t| d"d ||d#}|  qqqd S )+Nr   r   c                 S   rW   rX   r7   r   r7   r7   r8   r\     r]   z%predict_adbreak99.<locals>.<listcomp>r   rd   rR   r@  rB  r  r  r  r   r  c                 S   r  r7   r_   r   r7   r7   r8   r\     r]   r  c                 S   r  r  r7   rY   r7   r7   r8   r\     rt  r  r  r  r&   r   c                    r  r  r  r   r  r7   r8   r\     rc   c                 S   r  r  r7   r   r7   r7   r8   r\     r  ru  r   r  r  g      ?z.2fg      >@r   rU  r  r  g?g      @g      .@r   g@g      @g      $@)r  r  r   r  r3   r   r   r  r4   r  r   r  r3  rD   r  r4  r   r   r{  r  r   r7  r   formatr  r   ZAdbreaPredictrh  r  )rR   r  r  r   r   r   r  r  r  r   r  r@  r  r  r  r  r  r  r  Zifactr  r  r  rE  rU   r  r  r7   r  r8   predict_adbreak99  s   
$$
"""r  c                 C   s   | j dkr?| jd}| jd}tj }t| jd |||dd}|  tj }d}dt|j	 }t
|||d	}|  t| d
S )Nr@   r  r  r-   r   r  zAdd  AgencyzAdmin Add  Agency  id: r  z DAIManagementApp/add_agency.html)r   r@   rA   r   r   r   r1   r  rD   r   r   r   )r6   r  r  r   r   r  r  r  r7   r7   r8   
add_agency  s   



r  c                 C   sV   | j dkrtjj|d}d|i}t| dd|iS tjj| jd dd}t| d	d|iS )
Nr@   rI   r   z$DAIManagementApp/edit_agency_id.htmlrE   r-   r   r   !DAIManagementApp/edit_agency.html)r   r   r3   rA   r   r4   r1   )r6   r   r   rE   r7   r7   r8   edit_agency  s   
r  c                 C   s   | j dkrItjj|d}| jd}| jd}||_||_|  d}dt|j	 }t
|t|d}|  tjj| jd d	d
}t| dd|iS d S )Nr@   rI   r  r  zEdit  AgencyzAdmin Edit  Agency  id: r  r-   r   r   r  r   )r   r   r3   rA   r@   r   r  r  rD   r   r   r   r4   r1   r   )r6   r   r   r   r  r  r  r  r7   r7   r8   update_agency  s   
r  c                 C   s   | j dkr2tjj|d}t|j tj |_d|_|  d}dt	|j
 }t|t|d}|  tjj| jd dd	}t| d
d|iS )Nr@   rI   r&   zDelete   AgencyzAdmin Delete  Agency  id: r  r-   r   r   z#DAIManagementApp/delete_agency.htmlr   )r   r   r3   rA   r   r   r   r   r  rD   r   r   r4   r1   r   )r6   r   r   r  r  r  r7   r7   r8   delete_agency)  s   

r  c                 C   s0   | j d}|dkrd}nd}|| jd< tdS )Ncurrent_modelightr/   z	dark-mode
theme_mode)rI  rA   r1   r"   )r6   r  Zbody_class_themer7   r7   r8   r  <  s   
r  c                   C   s,   t  jdt 	 t   td q)Nz00:00Tr&   )	scheduleZeveryrR   atdor  Zrun_pendingr@  sleepr7   r7   r7   r8   func_predictG  s
   
r  c              	      sR  dd l }dd l}dd l}tjj|d}|j }d| jd  }tj	
|s*t| |d|j |d d 7 }tdd	}d
|j |d d |d< ||ddi}	|	 }
|	 }|d |d tjj|d}|
ddd|j d |j d |j d  |
ddd| |
ddd |
dd|j |
ddd |
dd|jjj |
ddd |
dd|jj |
ddd |
dd|jj |
ddd | |
d!dd" |
d!d|j |
d#dd$ |
d#d|j |
d%dd&| |
d'dd( |
d'd|j |
d)dd* |
d)d|jdk |
d+dd, |
d+d|j |
d-dd.| |
d/dd0 |
d/d|jdk t jj!|d1"d2}d3d4 t#|D }t$jj!|d5"d6d7}d8d4 |D }d9d4 |D }|
d:dd; |
d:dt%|&d<d= |
dd>d?| |
ddd@| |
dddA| |
dddB| |
dddC| |jj}t'jj!|dDdE"dFdGdH}|j(|g dIdJ}dKd4 |dL D }dMd4 |dNdLg j)D }||dN< ||dL< dOd4 |dP D }dQd4 |dP D }||dR< ||dS< |j*dPdTddU dVd4 |dS D |dS< g }|j)D ]}|+t,jj|d |d |d |dW dXj- qdYd4 |D }||dZ< t.jjd[d  fd\d4|dZ D }||dZ< d}|j)D ]>}|
d| d>|d  |
d| d|d  |
d| d|d  |
d| d|dW  |
d| d|d]  |d7 }qQ|
ddd^| |
ddt/t0| |	1  |S )_Nr   rI   zexcel/user_r-   r   z%d_%m_%Y.xlsxAapplication/vnd.openxmlformats-officedocument.spreadsheetml.sheetcontent_typezattachment; filename=Content-DispositionZ	in_memoryTr&   z#acbbfezAdtlas Reporting | z cash [ z to z ] ru  INFOr  zCampaign Namer   	   r   r  Z
Advertiser   ZCreativer  ZPERIOD   z
Start dater  zEnd date   ZBOOKED   ZVolume   ZPacing   ZCPM   Z	PLACEMENT   zGeneral Retationr  r   c                 S   rW   rX   r7   rY   r7   r7   r8   r\     r]   zgenExcel.<locals>.<listcomp>rR  rd   rK   c                 S   rW   rX   r7   r   r7   r7   r8   r\     r]   c                 S   rW   r^   r7   r   r7   r7   r8   r\     r]   r  r   'r/   r  ZCHANNELZREGIONDAYMINUTEZIMPRESSIONSre   )r&  rf   r   r8  r  )r  Regionr  r  c                 S   s   g | ]
}t jj|d jqS )r  )r2  r3   rA   rQ   r   r7   r7   r8   r\     rc   r  c                 S   s    g | ]}t jj|d  djqS )r   )rd   )r   r3   rA   rK   r   r7   r7   r8   r\     r  r  c                 S      g | ]	}| d d qS )r  r   r  r   r7   r7   r8   r\     r  r  c                 S   r  )r  r&   r  r   r7   r7   r8   r\     r  r  r  r  )Zaxisinplacec                 S   s   g | ]
}|d d d qS )Nr  r7   r   r7   r7   r8   r\     rc   r  )rK   rQ   rR   rU   c                 S   r  r7   r_   r   r7   r7   r8   r\     r]   r  rS   c                    s(   g | ]}t |t  j t  j qS r7   )r`   r   r   r   r  r7   r8   r\     s   ( r   zTOTAL IMPRESSIONS)2
xlsxwriterr  r   r   r3   rA   r   r1   rE  r   rF  rG  r   r   r"   ZWorkbookZadd_worksheetZ
add_formatZset_patternZset_bg_colorr   r  r  r   r   r   r  r  r  ri   r  r  r  r4   r   r   r   rD   r   r   r  valuesdropr   r   rV   r   r4  r  r  )r6   r	  r  r  r   r  r   r   responserk  rl  Zcell_formatr  r   Zchannels_namer   r   r   r  rQ   rR   rU   rV   r   rE  r7   r  r8   genExcelN  s   




,
0
r  c                 C   sL   t d|  t d|  t dt|  d| d | d t| }t|S Nzchannel:z	start_at:z	duration:)r   rD   r"   r6   r   start_atrB  Zalldatar7   r7   r8   	test_rest  s
   r  c                 C   sH   t d|  t d|  t dt|  d| d | d t| }d S r  )r   rD   r  r7   r7   r8   r    s    c                 C   s^   ddl m} t|dd}t|||d}|  t| d|| t|||| ||||dS )Nr   r#   r  r  )r   r  rB  a  https://videoapi.smartadserver.com/ac?siteid=385419&pgid=1633029&fmtid=92859&ab=1&tgt=&oc=1&out=vast3&ps=1&pb=0&visit=S&vcn=s&vph=%5BplayerHeight%5D&vpw=%5BplayerWidth%5D&vpmt=%5BplaybackMethod%5D&skip=&mabd=%5BmaxAdBreakDuration%5D&ctd=%5BcontentDuration%5D&tmstp=%5Btimestamp%5D&cklb=1)r   r  rB  )django.httpr#   rD   r   RealTimeAdbreakr  	loop_vastrealtime_filter)r6   r   r  rB  r  r#   r$  Zrealtimer7   r7   r8   realtime_adbreak  s   r   c           $      C   s  d}t jj| d}tjjdd}t|j||| t|dk rZtjj| d}t	jj| dd}tjjdd	}	t
jj||d
j|d }
t
jj|	d	 }d}|
rO|
jj}t| td d}td t|
jj |
j}ddlm} t| tj|d}t|
 |tjdd }|  t| ddlm} | }t|
jjdd}|
j}t|
jj}t|
jj}|dd}d}||f}||| | }|r|D ]0}|d }t|d  tjj|d d}t| ||_|j}|j |_!tdt|j!  |  qtjj|d	}t|j"d |_"|  ttjj|d	j"}ttjj|d	j#}|} |}!t$|| |j%t|t| }"|j&d |j' }#t(|j)|j*|j+|j,|"|#rOd}|dkr\td d S d S d S )Nr  r   r&   r.      r  r"  r   r  window_start__ltewindow_end__gter  r   6   📣 Short adbreak detected, updating the playlist ...r   I'm heeeerer  r  minutesrn   r   r/   a  
                              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 (Verifs.airStatusCode <> 0001 or Verifs.airStatusCode is null) and Avails.id_window= %s
                          r  the new avail winodws : r9     ✅ Playlist updated)-r   r3   rA   r;  send_msg_telegramrd   r4  r   r4   r2  r9  r  r  r  r|  r   r1  r   r   r3  r  r   ro   r   rD   r   r   r  r   r   r;  r   r  r<  r   r   rT  r   r<  rQ   r}  r  rt  r  rv  )$r   r  rB  r  rD  
my_channelr=  r   rI  ZotherplaylistrN  
new_windowupdate_statustheid_playlistpreview_window_endr   ro   r   broadcastDateForQueryid_windowForQueryr?  daydate_xmlqueriesr  ru   r   availoor   winddddpplayr  r  r  r  rG  rg  r7   r7   r8   r    s~   



mr  c           &      C   sz  d}t jj| d}tjjdd}t|j||| t|dk rtjj| d}t	jj| dd}t
jj||dj|d	 }	d
}
|	rtd d}
td t|	jj |	j}ddlm} t| tj|d}||	_|	j}tjt|	j|tjt|	j| }tjt|d}|d}||	_|	  t|	 |tjdd }tjt||tjt|| }tdt|  tdt|  tdt|  tjt|d}|d}t
|	jj|||d}|  |	jj}t| ddlm} | }t|	jjdd}|	j}t|	jj}t|	jj}|dd}d}||f}||| | }|r^|D ]1}|d }t|d  t jj|d d}t!| ||_|j}|j|_"tdt|j"  |  q,tjj|d}t|j#d |_#|  ttjj|dj#} ttjj|dj$}!|!}"| }#t%|| |j&t| t|"}$|j'd |j( }%t)|j*|j+|j,|j-|$|%rd}
|
dkrtd d S d S d S  Nr  r   r&   r.   r!  r  r"  r"  r  r   r%  r   r&  r  r.  r  r  r'  r  r0  znew window duration r/  rn   r   r/   a  
                              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 Avails.id_window= %s
                          r  r)  r  r9  r*  .r   r3   rA   r;  r+  rd   r4  r   r4   r2  r9  r  r|  r   r  r1  r   r   r3  rD   r  r   r:  r  r   ro   r   r   r   r  r   r   r;  r   r<  r   r   rT  r   r<  rQ   r}  r  rt  r  rv  )&r   r  rB  r  rD  r,  r=  r   rI  rN  r.  r0  r   endor:  new_window_durationr-  r/  ro   r   r1  r2  r?  r3  r4  r  ru   r   r5  r   r6  r7  r  r  r  r  rG  rg  r7   r7   r8   realtime_filter_new}  s   (
$


ar<  c           %      C   sT  d}t jj| d}tjjdd}t|j||| t|dk rtjj| d}t	jj| dd}t
jj||dj|d	 }	d
}
|	rtd d}
td t|	jj |	j}ddlm} t| tj|d}||	_|	j}tjt|	j|tjt|	j| }tjt|d}|d}||	_|	  t|	 |tjdd }tjt||tjt|| }tdt|  tdt|  tdt|  tjt|d}|d}t
|	jj|||d}|  |	jj}t| ddlm} | }t|	jjdd}|	j}t|	jj}t|	jj}|dd}d}||f}||| | }|r^|D ]1}|d }t|d  t jj|d d}t!| ||_|j}|j|_"tdt|j"  |  q,ttjj|dj#}ttjj|dj$} | }!|}"t%|| |j&t|t|!}#|j'd |j( }$t)|j*|j+|j,|j-|#|$rd}
|
dkrtd d S d S d S r8  r9  )%r   r  rB  r  rD  r,  r=  r   rI  rN  r.  r0  r   r:  r:  r;  r-  r/  ro   r   r1  r2  r?  r3  r4  r  ru   r   r5  r   r6  r  r  r  r  rG  rg  r7   r7   r8   realtime_filter_old  s   (
$


]r=  c           
      C   sD   ddl m} dd l}g }t|}i }d}d} t|  ddi}d}	|S )Nr   BeautifulSoupa  http://ads.stickyadstv.com/www/delivery/swfIndex.php?reqType=AdsSetup&protocolVersion=2.0&zoneId=33011444&playerSize=720x576&_fw_gdpr=0&_fw_us_privacy=1---&_fw_did_idfv=8D9E1F6C-5A2B-7143-9038-62471DC58C24&_fw_atts=0&ltlg=48.856,2.352&_fw_deviceMake=settopbox&_fw_devicemodel=set-top_box&_fw_content_genre=generalist&_fw_content_rating=+14&_fw_is_lat=1&_fw_coppa=0&withOMSDK=false&_fw_gdpr=0&_fw_gdpr_consent=0&_fw_gdpr_consented_providers=0
User-AgentzfMozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/103.0.0.0 Safari/537.36 )bs4r?  concurrent.futuresr4  r   )
urlrB  r?  
concurrentZlist_idr   dict_allcteZ
my_headersr  r7   r7   r8   get_info_from_vast_fileT  s   TrG  c                 C   s   t j }t| dd|idS )NzDAIManagementApp/pending.htmlurls)context)Pendingr3   r   r   )r6   rH  r7   r7   r8   pending  s   
rK  c           4      C   s  |}t jj|d}tjj| jd d}td tj 	d}tj
|d d}tj
|d d}	tj }
t|
d	d
 }tj tjdd }t|d	d
 }tj tjdd }t|d	d
 }tjj|d}|rtjj|ddd
 }|j}t|d }||_|  d
}tjj|d}|D ] }tjj|jd}|D ]}tjj|jd}|D ]}|d7 }qqqztjj|||d}W n tjy   tjj|||d}Y nw |r|j}|j}tjj|jd }t|d }d}tj
t|j|tj
t|j| }tj
t|d}|	d}niztjj|||d} W n tjy6   tjj|||d} Y nw | r>| j}!n|}!ztjj|||d}"W n tjy_   tjj|||d}"Y nw |"rg|"j}#n|}#d}t|j|!|#|d}$|$  |$j}|$j}d}t||||
d}%|%  |st|d|||	|
dddd	}|  t|jt|t|	dd}&|&  d}d
}|&j}|&j}t||d|
d}%|%  t||}'d
}(|' D ]o\})}*|(d7 }(|*d d }+|+ dd }+z	t!jj|+d!},W n t!jy   t!jj|+d!},Y nw |,r|d7 }t|%j|,j"|(|d"}-|-  q|*d# }.|*d d }t#jj|.|d$$ s@t#|.|d$}/|/  t%d%| d&  qd'}0t&jj||0d(}1t'|||1j(t|}2|j)d) |1j* }3d S )*Nr   r-   r.   z/echo 'inside the task function' > a_log/log.txtr   z	 00:01:00r  z	 23:59:00r  r   r   r'  ru  r  z-versionr&   r  r  r  )r  r#  r$  r.  r  Z00120000r/  r2  r   rY  Z23580000	MediaFilerC  r  r/   )url_from_vastr6  Creative_idr  rC  'New Creative Detected in VAST <a href=''>(Video_link)</a>r  r"  r9  )+r   r3   rA   r;  r1   rE  systemr   r   r   r3  rD   rh  r   r   r4   r   r   r4  r  r9  r;  r  r   r  rB   r  r  r1  r  rG  itemsr   r   r  rJ  rF  r+  r2  rT  r   r<  rQ   )4r6   rC  rB  r   rH  r>  r=  Zto_day_dater%  r&  r   Znow_strZ
now_minus2Z
now_plus10Zcheck_valuerA  r  r  Zcurrent_traffic_totalZall_windowsZwindZ
all_availsrP  Z	all_spotsadrN  r  r<  Znumberofavailsr>  rD  r:  Zprev_windowZnew_widdow_startZnext_windowZnew_widdow_endr-  r  r  rE  rC  keyre  ZCreative_urlr  rF  r  pandingr8  rI  rG  rg  r7   r7   r8   task  s   


(
rW  c           
      C   sp   t ||}d}| D ]*\}}|d }|d d }tjj||d s5t||d}	|	  td| d  qd S )Nr   rN  rL  rC  rO  rP  rQ  )rG  rS  rJ  r3   r4   rF  r  r|  )
r6   rC  rB  r   rE  rC  rU  re  r  rV  r7   r7   r8   r  ^  s   
r  c                 C   s4   ddl m} t|dd}t|| |||dS )Nr   r  r  r  )r  r  )r  r#   rD   r   send_ad_telegram)r6   r  r   r#   r$  r7   r7   r8   realtime_adspotv  s   
rY  c           	      C   s   dd l }dd l}d}d}d}	 td ||||}t|| d td|j||f  t|d}|d	|j| | W d    n1 sHw   Y  td
 |	  d S )Nr   rm  rn  ro  zConnecting to FTP...)dirpathz$Transferring %s and storing as %s...r]  zSTOR %szClosing session.)
rE  r_  r   r`  chdirr   basenamer   rc  rd  )	Z
DST_FOLDERZSRC_FILEPATHrE  r_  ZFTP_ADDRUSERNAMEPASSWORDr1   r   r7   r7   r8   send_adspot~  s   r_  c                 C   sf   t | |du r&td|  |d}| |d  | |d d |d   td|  | | dS )zChange to directory.FzCreating folder %s...r   r   r&   zChanging to directory %s...N)directory_existsr   rh  Zmkdra  )r1   rZ  Zfoldersr7   r7   r8   r[    s   
r[  c                 C   D   g }|  d|j |D ]}| d |kr| dr dS qdS z!Check if remote directory exists.LISTr   DTFZ	retrlinesr   rh  upper
startswithr1   rZ  filelistr[  r7   r7   r8   r`       r`  )settingsc                 C   sZ   d| d |  d | d | d }t j}tj|d d}|jd|d	  |tjjd
 d S )Nz5
    ====================
    New Adbreak Detected: [z]
    - channel: 
    - Time: [z]
    - Duration : []
    ====================
    	bot_tokentoken@%srd   chat_idtextZ
parse_moderk  TELEGRAMtelegramBotsend_messageZ	ParseModeZHTML)rd   r  rB  r  rc  telegram_settingsbotr7   r7   r8   r+    s,   
r+  c                 C   sB   d|  d }t j}tj|d d}|jd|d  |tjjd d S )N
    []
    rn  ro  rq  rd   rr  ru  r  rc  rz  r{  r7   r7   r8   r|    s   
r|  c                 C   s<   d|  d }t j}tj|d d}|jd|d  |d d S )Nr|  r}  rn  ro  rq  rd   )rs  rt  )rk  rv  rw  rx  ry  r~  r7   r7   r8   send_msg_telegram3  s   
r  c                 C   sJ   d| d |  d }t j}tj|d d}|jd|d  |tjjd d S )	Nz/
    ====================
    Adspot detected: rl  rm  rn  ro  rq  rd   rr  ru  )r  r   rc  rz  r{  r7   r7   r8   rX    s   
rX  c                 C   ra  rb  re  rh  r7   r7   r8   r`    rj  c           
         s   t d ddlm} tjj| jd dd}dd |D }tjj|d	d
	dj
|ddd}dd ld  fddtt|D }g }g }|D ]}||d  ||d  qLt | t | |||d}t| jd }	t| d||	dS )NZjjjjjjjjjjjjjjjjjjjjjjjjjjr   Countr-   r.   rd   c                 S   rW   rX   r7   rZ   r  r7   r7   r8   r\     r]   z!statscreative.<locals>.<listcomp>re   )Znetworkname__inrf   r&  dcount-dcount0123456789ABCDEFc              	      "   g | ]}d d  d qS #r/   r  rX  sampler   charsrandomr7   r8   r\     r  r  )ZlebelrE   r   z$DAIManagementApp/stats_creative.html)r   r   )r   r  r  r   r3   r4   r1   r   r   r  annotater   r  r   r   r   	ads_brandr   )
r6   r  r   r   r   r  rE   rT  r   r   r7   r  r8   statscreative  s*   (r  c           
         s   ddl m} dd ltjj| d}tjj|ddj|dd	d}d  fd	d
t
t|D }g }g }t|D ]}|tjj|d dj |t|d d d d q<|||d }}	|S )Nr   r  r.   rR  r   r  z-countr  c              	      r  r  r  r   r  r7   r8   r\     r  zads_brand.<locals>.<listcomp>rI   r  r   r	  r   r  rE   r   )r  r  r  r   r3   r4   r   r  r  r   r   r   r   r   r   rA   r   r   )
r1   r  r   adsr   r  rE   rT  r   r   r7   r  r8   r  	  s    &
r  c                 C   sl   t jj| jd dddd}t| }t| }t| }t jjdddd d }t	| d	||||d
S )Nr-   r.   r   r  z-volumer&   z-cpmr   z$DAIManagementApp/stats_campaign.html)ri   rg  r   cpms)
r   r3   r4   r1   r   r   campaign_pacingcampign_brandcampaign_avtiver   )r6   r  ri   r   rg  r  r7   r7   r8   statscampaign  s   "r  c                 C   sP   t jjddd}dd |D }|d|d|dd t| d	}|S )
Nr&   r.   ri   c                 S   rW   rX   r7   rZ   r  r7   r7   r8   r\   *  r]   z#campaign_pacing.<locals>.<listcomp>TFr   )truer  rV   )r   r3   r4   r   r  r   )r6   ri   r   r7   r7   r8   r  '  s   r  c                    s   ddl m  dd ltjj| jd djddjdddd	}fd
d|D } }d}d}d}|D ]}||d k rB|d7 }q5||d k rM|d7 }q5|d7 }q5|||g}d  fddt	dD }g d}	|	||d}
|
S )Nr   r  r-   r.   r/   )Zstart_day__exact)Zend_day__exactr  r  c                    s,   g | ]}  |d  d  |d dfqS )r   r   r&   )r3  )rZ   r   r  r7   r8   r\   8  s   , z#campaign_avtive.<locals>.<listcomp>r&   r  c              	      r  r  r  r   r  r7   r8   r\   F  r  r  )ZFinishedzIn progresszNot startedr  )
r   r  r   r3   r4   r1   r  r   r   r   )r6   Zetatr   finishedZ	not_startr$  r   rE   r   r  r   r7   )r  r   r  r8   r  2  s.   ,



r  c                    s   ddl m} dd ltjj| jd ddj|dd	d}g }g }d  fd	d
t
t|D }|D ]}|tjj|d dj ||d  q6|||d}|S )Nr   r  r-   r.   r   r  r  r  c              	      r  r  r  r   r  r7   r8   r\   Z  r  z!campign_brand.<locals>.<listcomp>rI   r  r  )r  r  r  r   r3   r4   r1   r  r  r   r   r   r   r   rA   r   )r6   r  r   rE   r  r   r   r   r7   r  r8   r  Q  s   ,r  c           
         s   ddl m} tjjdd}tjj| jd ddj|dd	d}g }g }dd l
d	  fd
dtt|D }|D ]}|tjj|d dj ||d  q=|||d}t| dd|iS )Nr   r  r&   r.   r-   r   r  r  r  c              	      r  r  r  r   r  r7   r8   r\   s  r  zstatsbrands.<locals>.<listcomp>r   r  r  z"DAIManagementApp/stats_brands.htmlr   )r  r  r   r3   r4   r   r1   r  r  r   r  r   r   r   r   rA   r   r   )
r6   r  r  r  r  rE   r   rT  r   Zbrand_adspotr7   r  r8   statsbrandsi  s    ,r  c           
         s   ddl m} dd ltjj| jd d}tjj|ddj	|dd
d	}d
d |D }dd |D }d  fddtt|D }|||d}t| }t| }t| }	t| d||||	dS )Nr   r  r-   r.   rR  rQ   r   r  r  c                 S   rW   )rQ   r7   )rZ   rQ   r7   r7   r8   r\     r]   z!statschannels.<locals>.<listcomp>c                 S   rW   r  r7   )rZ   rE   r7   r7   r8   r\     r]   r  c              	      r  r  r  r   r  r7   r8   r\     r  r  z$DAIManagementApp/stats_channels.html)r   r  r  r@  )r  r  r  r   r3   r4   r1   r2  r  r  r   r   r   channels_adschannels_adbreakr   r   )
r6   r  r   rl  r  rE   r   r  r  r@  r7   r  r8   statschannels  s    &r  c           	         s   ddl m} dd ltjj| jd d}tjj|ddj	|dd
d}d	  fd
dtt|D }g }g }|D ]}|tjj|d dj ||d  q=|||d}|S )Nr   r  r-   r.   rR  r   r  r  r  c              	      r  r  r  r   r  r7   r8   r\     r  z channels_ads.<locals>.<listcomp>r   r  r  )r  r  r  r   r3   r4   r1   r   r  r  r   r   r   r   rA   rd   )	r6   r  r   r  r   r  rE   rT  r   r7   r  r8   r    s    &r  c                    s   dd l dd l}ddlm} tjj| jd dd}dd |D }t	jj|ddd	d
}|j
|g dd}g }g }tt|d }d  fddtt|D }|D ]!}|j|d |k jddgd }	|t|	d  || qU|||d}
|
S )Nr   r  r-   r.   rd   c                 S   rW   rX   r7   rZ   r   r7   r7   r8   r\     r]   z$channels_adbreak.<locals>.<listcomp>)Zchannel_name__inrR   r@  )r  r  r  r  r  r  c              	      r  r  r  r   r  r7   r8   r\     r  r  byr  r  )r  r  r  r  r   r3   r4   r1   r   r  r  r   r7  r   r   r  groupbyr  r   r  )r6   r  r  r   rE   r  r  r   r   r  r   r7   r  r8   r    s*   "r  c                    s  dd l dd l}tjj| jd dd }}dd |D }tjj|ddd}|j|d	d
gd}dd |d
 D }||d
< t	t
jjddjtt
jjddj|jd	gd }t|j}fdd|jD }d  fddtt|D }	|||	d}
|
S )Nr   r-   r.   rK   c                 S   rW   rX   r7   r  r7   r7   r8   r\     r]   z channels_sfr.<locals>.<listcomp>)rP   rV   r  r  r  c                 S   r  r7   r_   r   r7   r7   r8   r\     r]   r   rI   r  c                    s   g | ]
}|d     qS rX   r7   r   )r   r   r7   r8   r\     rc   r  c              	      r  r  r  r   r  r7   r8   r\     r  r  )r  r  r   r3   r4   r1   r   r   r  r4  r   rA   r   r`   r   r  r  r   r   r  r   r   )r6   r  r   r@  r  rV   r  r  rE   r   r   r7   )r  r   r  r   r8   r     s(   
r   c                 C   s  | j dkrdd l}ddlm} | }| jd}| jd}| jd}| jd}t| t| t|	dd	}t|	dd	}d
}zc|
||||| | }	t|	 ||	}
|
g d }g d|_t 3}|j|dd}|j|dd |  |d | d }t| dd}d| |d< |W  d    W S 1 sw   Y  W n   td td td Y tjj| jd d}tjj|dd}dd |D }dd |D }t| t| d||d S )!Nr@   r   rn   r   r   r  r  r   r/   aT  
            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 < '{}'
        )r      r  r  r   rk   r  ru  r  r     r  r  )Z	id_verifsr  r  r   r#  r&  r  Z	airLenghtZ	airStatusr|  r}  rU   rV   r  )engineF)r   r  r  r  r  zattachment; filename=%sr  z3===================================================r-   r.   rR  r  c                 S   rW   rX   r7   )rZ   r  r7   r7   r8   r\   '  r]   z#generate_verifs.<locals>.<listcomp>c                 S      g | ]}|j qS r7   rT   r  r7   r7   r8   r\   (      z%DAIManagementApp/generate_verifs.html)r   r   )r   r  r   ro   r   r@   rA   r   rD   r   r   r  r   r  r  r	   ZExcelWriterZto_excelr  r"   getvaluer   r3   r4   r1   r   r   r   )r6   r  ro   r   r   r   r  r  qru   r  r  bwriterr   r  r7   r7   r8   generate_verifs  sT   


&
r  c                 C   r_  )	Nr@   Zbouygues_filer  r  zbouygues/user_r-   r   z%DAIManagementApp/bouygues_upload.html)r   rD   r  r   r   r1   rE  r   rF  rG  r   insert_bouyguesr   ra  r7   r7   r8   upload_bouygues-  rc  r  c              
   C   st  dd l }ddlm}m} || }|jd }|dddd }|d}||d d}||d	 d}|j	||d
}dd t
t|jD }	|	|_|d d }
t|d dd  }|D ]X}|jdd  D ]N}|| d }t|| dd  }t
dt|D ]4}|dk rt|||
|| || d d}|  q||d	d }t|||
|| || d d}|  qqhq_d S )Nr   r
   r   zau r/   zdu r  r~  r&   r$  endc                 S   rq  rr  rs  r   r7   r7   r8   r\   F  rt  z#insert_bouygues.<locals>.<listcomp>rw  r   rv  r  r  i  )rd   rR   r|  rU   rV   r   )r  r   r   r  r  r   rg  rh  r3  Z
date_ranger   r   r   Bouygues_analyticsr  )r   r  r   r   r  r   r  r  Zperioder  r|  rU   rR   r  r   r   r   ZbouygueZday_2r7   r7   r8   r  9  s6   




r  c                 C   s$   t jj| jd d}t| dd|iS )Nr-   r.   zDAIManagementApp/charts.htmlr   )r   r3   r4   r1   r   )r6   r   r7   r7   r8   charts_testY  s   r  c           	         s   dd l }| jd}| jd}| jd}t| tjj|dj}tjj|dj}t	jj
|||ddd	}tjjd
d}t|jt|j  dd |D } fdd|D }||d}t|ddS )Nr   r   rQ   rR   r   r  r  rU   rV   rS   rI   c                 S   rW   rX   r7   rY   r7   r7   r8   r\   l  r]   zsfr_channel.<locals>.<listcomp>c                    r  r^   r  rY   ra   r7   r8   r\   m  r  )rE   r  Tr/  )r   rI  rA   r   r   r3   rK   r2  rQ   r   r4   r   r   r`   r   r   r#   )	r6   r   r   rQ   rR   r   rV   ry   rE   r7   ra   r8   sfr_channel^  s   
r  c                  C   s~   ddl m }  |  }tjjddd}dd |D }tjj||dd	 }d
d |D }tjj|d}dd |D }d S )Nr   r  r&   r   r	  c                 S   rW   rX   r7   r   r7   r7   r8   r\   v  r]   z!adspost_avail.<locals>.<listcomp>r  Zend_day__ltr  c                 S   rW   rX   r7   r   r7   r7   r8   r\   x  r]   r   c                 S   rW   rX   r7   )rZ   rP  r7   r7   r8   r\   {  r]   )	r   r   r  r3   r4   r   r   r  r   )r   r   rz   r   r   r7   r7   r8   adspost_availq  s   r  c                 C   s   ddl m } | }| jd}tjj|dd}tjj| jdd}dd |D }t	jj||d}d	|i}t
| d
d	|iS )Nr   r  r   r   r	  c                 S   rW   rX   r7   r   r7   r7   r8   r\     r]   z&channels_campaigns.<locals>.<listcomp>r  rz   z(DAIManagementApp/dropdown_compaigns.html)r   r   rI  rA   r  r3   r4   r   r   r   r   )r6   r   r   r   rz   rE   r7   r7   r8   channels_campaigns}  s   r  c              	   C   s  ddl m }m} ddlm} | jd}| jd}| jd}g }tjj|d}t| t	|}	|j
}
| }|d krB||d}|d}|d	}|d}t| tt| t| tj }t| g }t| dd
lm} | }d}t| ||
|	|f}||| | }td t| |D ]_}|D ]Z}tjj|d}i }|j|d< |j|d< d|d< tjj|d}d|d< d|d< |D ](}|d  d|jj 7  < |d  dt|jj 7  < |d  t	|jj7  < q|| d}qqt | d||dS )Nr   r
   )deepflattenr   r   rQ   r   r   r   rn   a  
                    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
                  ru   rI   r  r$  r/   r   r  ZsommerB  z / z$DAIManagementApp/avails_details.html)rO  r4  )!r   r   Ziteration_utilitiesr  rI  rA   r   r3   r   r4  r   r   r3  r   r{  r   r  r   ro   r   r   r   r;  r  r<  r   r4   r  r  rD   rB  r   r   )r6   r   r   r  Zchannel2r   rQ   r   r   rH  Zchannel_networknamer   Zbroadcast_playlistZbroadcast_verifnrE   r   ro   r   r4  r  ru   Zid_avsZid_avr   rP  r   r   r7   r7   r8   delete_avail  sd   






r  c           !         s  | j d}| j d}| j d}td| |d kr*|}tj|d}|d}n
tj }|d}|d krGtjj	|dd j
}t| nd}|d kr`ttjj	| jd	 |d
jddd}nttjj	| jd	 djddd}tjj	||t|d}g }	g }
tjjdd}|D ]6}|j	|ddd}t|dkrt|jt|j  dd |D } fdd|D }|	| |
| qdd l}t}tjj	| jd	 dd}dd |D }tjj	ddd}g }|D ]}|j|v r|j|jt|jdd d}tjj	|jdd }|j
|d< t|jdd  d!}|d d! |d   d" }||d< d#|d$< t|jdkrsd%|d&< d'|d$< tjj	|d( |d d)D ]'}|d |j v rptjjdd}t|j!t|j t|j  t" |d*< qJn.t|jd+krd,|d&< d-|d*< nt|jd.krd/|d&< d-|d*< d0|d$< nd1|d&< d-|d*< || qtt#jj	| jd	 d}tt#jj	| jd	 dd2}t$| }t%| }t&| }t'| }t(j) *d3dd4 } tjj	| jd	 d}td5 t+||	|
|d6S )7Nr   rQ   r?  zload_charts ====== r   )Zzonename__containsr   rN   r-   )r-   r   rK   TrL   r.   rO   rS   rI   rT   rU   rV   c                 S   rW   rX   r7   rY   r7   r7   r8   r\     r]   zload_charts.<locals>.<listcomp>c                    r  r^   r  rY   ra   r7   r8   r\     r  rd   c                 S   rW   rX   r7   rY   r7   r7   r8   r\      r]   re   Z20211024)rf   r   r  r  r  r&   r  r,  r  r   r  r  r  rR   r  r  r  r  r   r  r  r  zFailed, Other Reason..rh   rj   rk   zend of loadchart)ry   r   r   r   ),rI  rA   r   r   r3  r   r   r2  r3   r4   rQ   r   r   r1   r   r   rD   r   r   r`   r   r   r   r  r   r   r   r&  r  rh  r8  rf   rK   rV   r4  r   r   r   r   r   r   r   r   r#   )!r6   rH  rQ   r?  rR   r   r8  r   r   r   r   rV   r   r   ry   rE   r  r   r   r   Zdata2r  r  r  rU   r   rz   r{   r   r}   r~   r   r   r7   ra   r8   load_charts  s   



&"





r  c                 C   s   t jj| jd d}d}|j}|j}|}tt d}t	jj|||d
 }|}td |d | d }	d	| d
 }
d| d }tj|sLt| tjj| jd d}t| t|j|j|j|
|	| t|d	 |	  rt|d	 |	 }|d}|jd}g }|D ]}|d}|d}|d}|dd}|dd}|d}|d}|d}z5t	jj|||||d}|r||_||_||_||_ ||_!|"  nt	||||||||||d
}|"  W q t	j#y   td t	||||||||||d
}|"  Y qw d S d S )Nr-   r.   r  r   r  r  r   r  r   r  r  r  r   r~  r#  r&  r  r  r  r  r/   r  r  r	  r  r  r  )$r   r3   r4   r1   r<  r   r   r   r   r   r  r   rE  r   rF  rG  r;  rA   rl  r  r  rv  r   r  r  r  r  r  r   r  r  rf   r	  r  r  rB   )r6   r   rQ   r<  r   r8  r   r   r  r  r  r  r=  r  r  r~  r  r  r#  r&  r  r  r  r  r  r   r  r  r7   r7   r8   insertion_verifs\  sd   








r  c                 C   s>   dd l }dd l}||j|j}|ddt| f |  d S )Nr   r  r&   )schedr@  	schedulerr  enterr  run)r6   r  r@  sr7   r7   r8   verifs_timer_insert  s   r  c           "      C   s  | j d}tjj|d}tjj|jjd}tjj|jjd}|j}|j	}|j
}|jdd}tjj|||d }	|j}
t|jdd}tjj|jdjd	d
d}i }g |d< d|d< d}tjj|d}|D ]}i }||d< |d }|j|d	< tj|jd}|d}tj|jd}|d}||d< ||d< g |d< d|d< ||d< tjj|jd}d}|D ]}i }||d< |d }|j|d< tj|jd}|d}||d< g |d< |d | tjj|jd}||d< d}d|d< |D ]U}i }||d< |d }||d< |j|d< |j |d < t!d! ztjj||j"t#|j$d"%d#}	|	j&|d$< |	j'|d%< W n tj(y=   t!d& Y nw t!d' |d | qq|d | d} qh|d ||||
|d | d(}!t)| d)|!S )*Nr  rI   r   r   r   r/   r  r  r  TrL   rL  r   r|  r   r&   r  r}  r  r1  r~  r  r  rE  r  r<  r  r  r  rq  r  r  r  r  r  r  r  r  r  r  zDAIManagementApp/expande.html)*rI  rA   r   r3   r   r   r2  r   r<  r   r8  r   r   r   r4   r  rD   r9  r  r   r  r   r3  r  r   r1  r;  r  r<  r   r   r  r  r   rM  r4  r   r  rf   r  rB   r   )"r6   r  r   r   rQ   r<  r   r8  r   r   rO  r  rU  rE   r   rL  rN  r  r  r  r  r  rO  rE  r   r  r  r  r   rq  r   r  rn  r  r7   r7   r8   expande  s   






 
r  c                 C   sn  | j dkr#tjj| jd d}| jd}tjj|d}| jd}| jd}tjj||d}tj	t
|d	}|d
}tjj| jd t
|t
|d}t| t
|d }t
|d }	tj }
t|dt
|||	|
|jddd	}|  d}d}tjjdd}|D ]}|j}|d | d }|j}|d | d }d}tj	||tj	|| }tj	t
|d}|d}t|j|||d}|  tjj|j|jdj||dd}|j|jd}|s|  d}|D ]"}|j}|d | d }t|j|t
|d |
d}|  |d }qqtjj| jd d}t| d d!d"|iiS tjj| jd d}t| d d!d"|iiS )#Nr@   r-   r.   rH  r   r8  rR   r"  r#  r   )r  start_day__lteend_day__gter  r$  rS   r   rY  r   r  r  r  r,  r  r.  r  r/  )Ztime__ltZ	time__gte)rR   r   r@  )r@  r&   r2  z7DAIManagementApp/generate_playlist_from_prediction.htmlrE   r   ) r   r;  r3   rA   r1   r@   r   r2  r   r3  rD   r   r   r4   r   r   r   r   r  r  r  r$  r  r9  r  r  r   r+  r@  r;  r  r   )r6   r=  rH  r>  r8  r?  rI  active_campaignsr%  r&  r   rA  rB  rn  Z	day_timesday_timer  r1  rD  r:  r  Zadbreaks_predected_in_windowrE  r  rp  r  r   r7   r7   r8   !generate_playlist_from_prediction  s\   
 
 
$
r  c              	   C   s  | j dkr'| jd}| }|dd}| jd}| jd}|dd }|dd	 }| jd
}|dd }|dd	 }	| jd}
| jd}| jd}| jd}| jd}| jd}| jd}|dd }t||||	|
dd tjj|d}d|j	dd d |dd d | d }t
jd| st
d|  z(|d }t
jd| st
d|  t| jd d| |d  ||d 7 }W n;   z0dd l}|d }| jd}||}td| | d d}||j |  ||d 7 }W n   d}Y Y t|  |||||	|d}|  ttS ztjj| jd d}t| d d!|iW S    tt Y S )"Nr@   r  r  r  r   emission_startr  r   r&   emission_end
short_descr3  r  episode_nameseason_numberepisode_numberextendedr   )seprI   z
emissions/r   r  zimage/imager  r-  r/   )r  r   r%  
start_timer&  end_timer  r-   r.   zemissions/converter.htmlr   )r   r@   rA   rg  r   rh  r   r   r3   rd   rE  r   rF  rG  r   r  requestsr   r   contentr  Z	EmissionsrM  r  r   Zedit_emissionsr4   r1   r   r9   )r6   emissionr  rH  r  r%  r  r  r&  r  r  r3  r  r  r  r  r  yearr   Zpath_emissionZ
path_imager  r  r  r   Znew_emissionr   r7   r7   r8   add_emissions6  sj   .
r  c                 C   r_  )	Nr@   Zepg_filer  r  z	epg/user_r-   r   z DAIManagementApp/epg_upload.html)r   rD   r  r   r   r1   rE  r   rF  rG  r   
insert_epgr   ra  r7   r7   r8   
upload_epg~  rc  r  c              
   C   s   dd l }ddlm} tjjdd}|| }|g d }|jD ]A}||d d}||d d}t|d d d	 d
 }	t|d d d	 d
 }
t	||d ||	
 ||

 |d d}|  qd S )Nr   r  r&   rI   )z
Event Namez
Start Datez
Start TimezEnd DatezEnd TimeZGenrer~  r  r   r  z.000000r   rk   )r   r  r%  r  r&  r  r3  )r  r   r   r3   rA   r  r  r3  rD   Epgrg  r  )r   r  r   r   r  Zepgr   Z	startdateZenddateZ	starttimeendtimer  r7   r7   r8   r    s   

&
r  c                 C   s.   | j d}t| j d| j dd d}d S )Nr  Zproduct_description)r   r  )r@   rA   ZProduct)r6   r  r  r7   r7   r8   insert_product  s   
r  c                    sb   ddl m   ddlm} d}g dd } fdd	}||}|r&|| ntd
 tdddS )Nr   r  r&   TelegramLogzqhttps://almatv-stor.vizionr.fr/synthesia/almatv/playlist/manager/php/_getPlaylist.php?fields=title,isPub,durationc                 S   s    t | }|jdkr| S d S )N   )r  rA   status_codejson)rC  r  r7   r7   r8   fetch_playlist_data  s   

z+process_adslot.<locals>.fetch_playlist_datac              
      s  d}t  }| D ]}|dd}z
 || }W n ty(      }Y nw |dg D ]}|dddkrztd|d	 t jj|d
|d	 d}td| W q/ t jy   |d
df|_	|d	df|_
|dd f|_|dd f|_|dd f|_|dd f|_|dd f|_|ddf|_|dd f|_|dd f|_|ddf|_d|_d|_d |_d|_d|_||_|  Y q/w q/qS )Nr   playlistDater/   rE   isPubr   rS   zSUB ID: subIdr  playlistdatesubidzFound Objectr$  r  startTsendTscutscenariorB  titler/   )rJ  rX   )AdslotsVisionrrA   r3  r   
ValueErrorr   r   r3   rB   adidr  r$  r  starttsendtsr  r  rB  ispubr  replacementadidreplacementpathreplacementdurationr  
updatesentr  r  )ad_slots_jsondate_formatZvisionerr   playlist_date_strplaylist_daterT  adslotr   r   r7   r8   insert_ad_slots  sR   
#z'process_adslot.<locals>.insert_ad_slotsFailed to fetch playlist dataFr  )r0  r  )r   r   r  r   r#   )Zreuqestr  rC  r  r   ad_slotsr7   r  r8   process_adslot  s   3
r  c                    sj   ddl m  dd lddlm d}fdd} fdd}||}|r/|| td	 d S td
 d S )Nr   rn   r  z~https://tvpitchoun-stor.easytools.tv/synthesia/tvpitchoun/playlist/manager/php/_getPlaylist.php?fields=title,internal,durationc                    s0   d}d} j | ||fd}|jdkr| S d S )N
advmanagerDFG$_Rv!934)authr  )rA   r  r  )rC  r:   r;   r  )r  r7   r8   r    s   
z-process_ad_slots.<locals>.fetch_playlist_datac           	         s     }d}d}| D ]v}|dd}z
|| }W n ty+     }Y nw |dg D ]M}|dd|dd|dd	|d
d	|dd	|dd	|dd	|dd|dd	|dd	|ddddd|f}|dd	dkr||| q2q
|  d S )Na  
            INSERT IGNORE INTO AdSlots_VisionR
            (AdId, SubId, Start, End, StartTs, EndTs, Cut, Scenario, Duration, IsPub, Title, ReplacementAdId, Status, UpdateSent, PlaylistDate)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        r   r  r/   rE   r  r  r$  r   r  r  r  r  r  rB  r  r  rJ  Finternalad_break)r   rA   r3  r   r  r   r   r  )	r  r   Zinsert_queryr  r   r  r  rT  r  )ro   r   r7   r8   r     s@   










z)process_ad_slots.<locals>.insert_ad_slotszAdslot Insert SuccessFullyr  )r   ro   r  r   r   )r6   rC  r  r   r  r7   )ro   r   r  r8   process_ad_slots  s   0r	  c           #      C   sr  dd l }ddlm} ddlm} ddlm} dd l}	dd l}
dd l	}dd l
m  m} ddlm} |
j d}|j| t|d }tjj|d}t  |j| ||d	}|jd
krd }|r||}t| td| td|j |
j }|}|d}|dr|djd }|dj}|dj}|dj}|dj}|dj}d }d }d }|d}td| |}td| t jj!||||||||| |||d} | j"r|j||d}!|!jpd | _#|r|j||d}"|"jpd | _$| %  n	t jj!||| d td|  ntd|j  W d    |S W d    |S W d    |S 1 s2w   Y  |S )Nr   HTTPProxyAuthr   	parse_urlr>  z%Y-%m-%d_%H%M%SZ_almar   )headersr  r  zthe ROOT is :zthe ROOT find is :r  z.//Adr  z.//Tracking[@event='start']z#.//Tracking[@event='firstQuartile']z.//Tracking[@event='midpoint']z#.//Tracking[@event='thirdQuartile']z.//Tracking[@event='complete']
ImpressionzDOUBLE CLICK: zEmpression: ad_idtracking_starttracking_firstquartiletracking_midpointtracking_thirdquartiletracking_completedatetime_timestampdatetime_stringvast_urlimpression_double_clickimpression_sprintservead_spotr  )r  r  r  zsent request num z5Error: Failed to make the VAST request. Status code: )&r  requests.authr  rB  r   urllib3.utilr  r  r   rE  xml.etree.ElementTreer   rD  rA  r?  r   r   r   rX  rb  rD   r   r3   rA   r%   atomicr  Z
fromstringr   rt  findattribVastResponsecreater  impression_sprintserve_statusimpression_double_click_statusr  )#rC  r  r  call_numr  r  r  r   r  r  r   rE  ETr?  r  folder_pathr   r  Zvast_responserK  Zcurrent_timer  r  r  r  tracking_first_quartiler  tracking_third_quartiler  r  r  Zdouble_clickvast_objsprintserve_responsedouble_click_responser7   r7   r8   vast_handling_oldF  s   








	

  
 
   r1  c           .      C   s  ddl m} tjj|d}t  t| }|d u r(td 	 W d    d S t	|}|d u r=td 	 W d    d S t
j
 }	|	d}
t|d\}}tt|D ]}|| }|| }d|v rd	|d v r|d d	 }t|d
ddi\}}t|d
ddi\}}t|d
ddi\}}t|d
ddi\}}t|d
ddi\}}t|d
ddi\}}t|d
ddi\}}t|d
ddi\}}t|d
ddi\}}t|dkr|d d nd }t|dkr|d d nd }t|dkr|d d nd }t|dkr|d d nd }t|dkr|d d nd }t|dkr |d d nd } t|dkr/|d d nd }!t|dkr>|d d nd }"t|dkrM|d d nd }#t|d\}}$d }%d }&t|$dkrj|$d d }%d }&n t|$dkrtdd |$D d }%tdd |$D d }&ntd |jj|||||||	|
| |%|&|d}'|&rtj|&|d}(|(jpd |'_|%rtj|%|d})|)j|'_|rtj||d}*|*j|'_|rtj||d}+|+j|'_|rtj||d},|,j|'_|rtj||d}-|-j|'_|'  qTW d    d S 1 sw   Y  d S )Nr&   VastResponseGor  z'Failed to get content, stopping processz%Failed to parse XML, stopping processr  ZAd
attributesr  ZTrackingeventr$  ZfirstQuartileZmidpointZthirdQuartileZcompleteZmuteZunmutepauseZ
fullscreenr   rt  r  r   c                 s   <    | ]}d |v rd|d  v r|d  d dkr|d V  qdS )r4  r  ZSpringServe_Impression_1rt  Nr7   rZ   itemr7   r7   r8   	<genexpr>     : z vast_handling.<locals>.<genexpr>c                 s   r7  )r4  r  ZSpringServe_Impression_3P_1rt  Nr7   r8  r7   r7   r8   r:    r;  zNeed To Be Handel r  r  )r   r3  r   r3   rA   r%   r"  r*   r   r+   r   r   r   r,   r   r   nextr&  r  rt  r'  r(  tracking_completed_statustracking_firstquartile_statustracking_midpoint_statustracking_thirdquartile_statusr  ).rC  r  r  r)  r  r3  r   Zxml_contentrK  r  r  Zad_componentsZad_component_listr   Zad_component_dataZad_componentr  r  Ztracking_start_listZtracking_first_quartile_listZtracking_midpoint_listZtracking_third_quartile_listZtracking_complete_listZtracking_mute_listZtracking_unmute_listZtracking_pause_listZtracking_fullscreen_listr  r,  r  r-  r  Ztracking_muteZtracking_unmuteZtracking_pauseZtracking_fullscreenZimpression_listZ
impressionZimpression_3pr.  r/  r0  Zcompleted_click_responseZfirst_quartile_responseZtracking_midpoint_responseZtracking_third_responser7   r7   r8   vast_handling  s   

$rA  c              	   C   s  ddl m} dd l}ddlm} ddlm} ddlm} dd l	}dd l
}dd l}dd lm  m}	 i ddd	d
dddd
ddddddddddddddddddddddddddddd d!d"d#d$}
d%d&dd'd(d)d*d+d,}d)}d-}d.d/i}|j|||d0}d1|d2d3d4d5|d2d3d4d6|d2d3d4d7|d2d3d4d8|d2d3d4g}d9d:id9d;id9d<id9d=id9d>ig}d S )?Nr&   	call_vastr   r
  r   r  
Feuilletoni! ZMagazinei% u   Mini-Sériez	Magazine z
Mini-SerieZSerieZInformationi+ zMagazine Sportifi- Dessin Animei$ zMagazine Sportif ZTelefilmZDocumentairei# Z	Religieuxi( ZSportzLong Metragei" ZNewszLong-Metragei& i' i, i. )zDes Histoires Et Des Hommesu   SérieZMusiqueZTheatreZ	SpectacleZJeux720567Abtal albiharar-MAr  2M_TVrN   whZcontent_genreZcontent_titler2  Zpod_max_durrd   r  a  https://tv.springserve.com/vast/850576?w=1920&h=1080&cb={{CACHEBUSTER}}&ip={{IP}}&ua={{USER_AGENT}}&pod_max_dur={{POD_MAX_DUR}}&pod_ad_slots={{POD_AD_SLOTS}}&app_bundle={{APP_BUNDLE}}&app_name={{APP_NAME}}&app_store_url={{APP_STORE_URL}}&did={{DEVICE_ID}}&us_privacy={{US_PRIVACY}}r@  eMozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/103.0.0.0 Safari/537.36)rC  num_requestsr  z185.124.241.179:12323Z14a2f5dfde475Z
51ae51af21)httpr  z89.116.242.185:12323z45.140.244.198:12323z193.106.198.128:12323z136.175.224.210:12323rP  z5http://14a2f5dfde475:51ae51af21@185.124.241.179:12323z4http://14a2f5dfde475:51ae51af21@89.116.242.185:12323z4http://14a2f5dfde475:51ae51af21@45.140.244.198:12323z5http://14a2f5dfde475:51ae51af21@193.106.198.128:12323z5http://14a2f5dfde475:51ae51af21@136.175.224.210:12323)tasksrC  r  r  r  rB  r   r   r  r  r   rE  r!  r   rD  delay)r6   rC  r  r  r   r  r  r   rE  r*  Ziab_catr  rO  rC  r  vastZ
proxy_listZproxies0r7   r7   r8   call_vast_apiU  s   	

	rT  c                    s^   ddl m  m t| }|jdkr-| d | d d } fdd|D }|S d S )Nr   r
   r  rE   c                    s   g | ]=}d |v r|d  dkri |d d d  d| dd  d  d| dd ddd dd	qS )
r  rS   r  r  r$  r/   %Y-%m-%d %H:%M:%S.%fr&   weeks)r  r   Zid_start_combinedZstart_minus_one_week)rA   r3  r   r8  r   Z	head_datar   r7   r8   r\     s    0z'fetch_playlist_data.<locals>.<listcomp>)r   r   r  rA   r  r  )rC  r  Zplaylist_datar  r7   rX  r8   r    s   


r  c                 C   s  dd l m  m} ddlm} dd l}dd l}dd l}|d}| d }td| | d d }| d d }	|	d| |	d	| |	d
|	 |
|d}
d}tjjdd}t|  |D ]q}t| t|j |t|j t| d d krtdt| d d  |
|
d}|	dt|j |	d|j  |	dt|j td|j ||j7 }tdt| d d |  d q^tdt| d d |  d q^d}||j|dd  }d}| }|d}tj||}tj|dd t| dddd dt|  d }tj||}t|d!dd}| | W d    n	1 s-w   Y  td" d#}d$}d%}d&}d'}t!||||d(}|"|| d}d S ))Nr   r  r   r   zDate: r  r  r  ZsynthesiaIdZsynthesiaSubIdrE   zTGTG_30_16x9.mp4r   rB  zwe Have new Ad :mediar   zinsert ad: zWe still have :z EmptyzDone and we have  :z'<?xml version="1.0" encoding="UTF-8"?>
r.  )r0  z1/var/www/html/DAI27/Adtlas_DAI/files/vast/pitchonr   Tr  r  r  r  z.xmlrL  zUploading to FTP...z	/PLAYLISTztvpitchoun-stor.easytools.tvr  r     )r  rs  r:   r;   )#r!  r   rD  r   uuidr  astr6  r   r7  r=  AlmaAdsr3   r4   r  rB  r4  rD   r  ads_pathr   tostringdecoder   r   rE  r   rX  rG  r   uuid4r   r   r)   rk  )rE   r*  r   r\  r  r]  r   r  Zsynthesia_idZsynthesia_sub_idZdata_elementZtotal_durationZalma_replacement_adsZvideoZmedia_elementr1  Z
xml_stringbase_dirtodayZdate_folderr+  r   r  Zxml_fileZ
remote_dirr  r:   r;   rs  Z
ftp_clientr7   r7   r8   create_playlist_xml  sf   



""
0re  c                   @   s"   e Zd ZddlmZ dddZdS )GetPlayListImpressionr   )ResponseNc              
   C   s  ddl m} ddlm} |jdkr9|j}tj }|tjdd }|	d}|	d	}	|td
d }
|td
d }zt
jj|d |d dd jdkrVtd tddiW S i |d|d i}td|d  z-|dd | D d}|r|d d }td| | D ]	}|d |kr	 qt| W n ty } z|d }~ww t
jj|d |d dd }d|_|  tdddW S  tjy   td tddd Y S w )Nr   )Counterr&   )visioner_playlistr@   ru  r   r   %H:%M:00r  r'  r   r  r  Z	Completedz!This Ad Has Already Been Replacedrc  z This Ad Has Already Been ReplacezCreating Playlistc                 s   s    | ]}|d  V  qdS )r  Nr7   r8  r7   r7   r8   r:        z-GetPlayListImpression.post.<locals>.<genexpr>zThe most replaced ads is:r  z&Alma Ads Has bees replaces Succesfullysuccess)rc  r  zUpdate Epg DatabasezEPG Unsynchronizedfailed)collectionsrh  r   ri  r   rE   r   r   r   r   r  r3   r4   r  r   r#   most_commonre  rv  r  r  rB   )selfr6   r  rh  ri  r   r   Znow_minus_weeklast_week_dayZlast_week_minuter  r  r  Zmost_common_titleZcommon_titler9  ry  r  r7   r7   r8   r  G  sT   



"

zGetPlayListImpression.postN)__name__
__module____qualname__Zrest_framework.responserg  r  r7   r7   r7   r8   rf  C  s    rf  )ViewQ)r  )parse_datetime)calculate_priority_scorerj  )Adbreaksr2  r  r   CampaignairtimelogCampaignPriorityScoresr   CampaignTimeIntervalsc                   @   s,   e Zd ZddlmZ edddZdd ZdS )	DynamicAdPlaylistr&   r  z.6599658496:AAFdK7Gsm1LvgYCIw2o8_frcbte1yNh-w44z-4175287619)Z	BOT_TOKENZGROUP_IDc           V         s	  dd l }ddlm}m} zqd}d}|jd}t|jdd}|jdd	}	||d
}
d}||}||
}|d}||
}|
|dd }
t	|
t
 }
|
 |
 tjj||||d}tjj||	d }tjj|||d}| r| dkr| j}| j}tjj||	 d}ntjj|	 d}fdd|D }g }|D ]}| }|jrdn|jr|r|j|jv rdn|jr|r|j|jv rdnd}tjj|dd }||j }t |j!t"r||j!d n|j! }d||#   j$d  }t%&|j'}dddd}t(|) t*|) } fdd|+ D  t( fdd|D }!|!|  }"t,||||"d }#|-||#d! zt.jj|d"}$|#|$_/|$0  W q t.j1yo   t.jj||#d! Y qw t2|d#d$ dd%}g }%|d& }&d'}'t3||'kr|	d(ks|	d	kr|D ]b}(t4jj|(d) j5|&d*6d})|) r|) }*|%rt7 }+|%D ]},|+t7|,j8|*j8d+t7|*j8|,j8d,B O }+qt9j|+ s|&|*j:8 }&|%-|* qt;d-|*j8 d. q|&|*j:8 }&|%-|* qd }-|%r&t<|%dkr&t4jjd/d0d1}.|.}/|%t=|/7 }%t>jj |j?d26d3}0|0 r|0 dkr|0 }1t@jj|1d4}2|1}-d |-_A|0 d  |-_Bt# |-_C|-0  tD|2D ]K\}3}4tEjj|4d5}5|4}6d |6_F|-|6_A|60  tD|5D ].\}3}7tGjj|7d6}8|7}9d |9_H|6|9_F|90  tD|8D ]\}3}:|:};d |;_I|9|;_H|;0  qqlqQnt>jj|d0  d7 d8t# |j?d9d9d:	}-t@jj|-d4}2|t"|d;t3| d<d=}<|<Jd>}<|
|d?d< Jd@}=|
|dAt3| d< Jd@}>|2 st@jj|-|=|>|<dB}?nc|2K }?||?jLd@}@||?jMd@}At	|@t
 }@t	|At
 }A|@|
  kr(|AkrSn n)||?jNd>|dAt3| d< }<|<Jd>}<|A|dC|dD Jd@|?_M|<|?_N|?0  n
t@jj|-|=|>|<dB}?tEjj|?d5 }BtEjj|?|
Jd@|Bd  |# dE}Cd}Dt@jj|-d4K }E|ErtEjj|Ed5K }F|FrtGjj|Fd6K }G|Gr|GjO}D|
}HtD|%D ]?\}I}*|Dd7 }DtGjj|C|*|Id |DdF tjj|*j5|*|dG }J|Jstjj|*j5|*||H|H||*j:d< ddH |H||*j:d< }HqtP|-}K|KdI rdJ}LdK}MdL}NdL}O|KdM }P|-j?jQdNkrdO}QtR|L|M|N|O}R|RS|P|Q n|-j?jQdPkrdQ}Qn	t;dR|KdS   dTTdUd |D }SdV| dW|	 dX| dY|dZ  d[|-d krG|-jAnd\ d]| rT| jnd\ d^| ra| jnd\ d_|S d`dad |%D  }T| jUV|T tWdSdbidcddW S  tXy }U z|Ud }U~Uww )eNr   r
   r&   z2M Monder$  rB  r  rQ   rN   rU  r  zAfrica/Casablancar   hours)r  rB  r   rd   )r   rQ   )r   start_time__lteend_time__gte)r  r  Zprogram_category__icontainsr  )r  r  r  c                    sB   g | ]}t jj| d d |jk rtjj|d r|qS )Tr  Zairtime_start__dateinsertion_status)r  r  r  )r|  r3   r4   r  r  r~  rF  )rZ   r  )r%  r  r7   r8   r\     s$    z)DynamicAdPlaylist.get.<locals>.<listcomp>g      ?Tr  r   r  )beforeduringafterc                    s   i | ]	\}}||  qS r7   r7   )rZ   rq  v)total_weight_sumr7   r8   
<dictcomp>%   r  z)DynamicAdPlaylist.get.<locals>.<dictcomp>c                 3   s    | ]} | V  qd S rr  r7   )rZ   Z
preference)normalized_weightsr7   r8   r:  '   rk  z(DynamicAdPlaylist.get.<locals>.<genexpr>)TSAARFUP)r  priority_score)r  c                 S   s   | d S )Nr  r7   )r[   r7   r7   r8   <lambda><   s    z'DynamicAdPlaylist.get.<locals>.<lambda>)rU  reverser  r  ZNetherlandsr  )r	  Zduration__lte)	ad_spot_1ad_spot_2_id)ad_spot_1_idr  zAd spot zJ has an exclusion relation with the already existing ad spots. Skipping...ZFiller_Ma_10mrS   )Zadspot_name__containsr  )r   r(  r  r  r  r  r  r$  r   rY  i  secondsr.  r  x   r  i  )r  r  r1  r:     )r(  r  )r  r<  r>  r   )r  r  rC  rM  )r  r  r  )r  r  r  airtime_startairtime_endr  r  r\  r]  r^  r   r`  r_  rU  ra  rb  rc  r  c                 s   s8    | ]}d |d j  d|d j d|d  dV  qdS )z * [r  r5  r  z] 
N)r	  r   )rZ   Zcampaign_scorer7   r7   r8   r:  H!  s   6 u*   🚨 AD-Break Detected! 🚨
Detected At: z  
====================
Region : z& 
====================
With Duration: z= Seconds 
====================
With Duration To Be Replaced: r  zC Seconds 
==================== 
This Will Update Playlist With ID: r/   z* 
====================
Based On Emission: z With Genre z/ 
====================
With Active Campaigns 
 z(====================
With This Ad_Spots c                 S   r  r7   )r  )rZ   r  r7   r7   r8   r\   Y!  r  z Ad playlist created successfullyr  r  )Yr  r   r   rI  rA   r`   r3  r  localize
make_awareget_current_timezoner   r@  r{  r3   r&  r2  r4   r  r  rF  r  r3  r  r   rM  exclusivityshows_preferencer  r  r|  r  r  r  rD   r   r   r]  literal_evalposition_preferencer  r  r   rS  rz  r   r}  r  r  rB   sortedr4  r   r	  r   rx  r  AdSpotExclusionrB  r   r   r   r   r   r9  r  r   r'  rh  r;  r  r   r  r  r   r  r  r1  r:  rM  ri  r   rj  rk  rX  telegram_botZsend_telegram_logr#   rv  )Vrp  r6   r  r   r   rH  rd   Zstart_date_time_strZduration_secondsZregion_testZstart_datetimeZlocal_timezoneZfrance_timezoneZstart_datetime_franceZmorocco_timezoneZstart_datetime_moroccor  rI  Z	emissionsZemissions_genresZemissions_categoriesr  Zpriority_score_listr  Zactual_emissionr  Zalready_aired_slotsr  r&  r  Zposition_preferencesweightsZ
max_weightZweighted_sumr  r  Zpriority_scores_objZad_spots_to_insertremaining_duration_secondsZmin_durationentryZlist_adspotr  Zexclusion_conditionsr  r   Zadspot_fillersZrandom_filler_adspotsr   Zold_playlistplaylist_windowsr  rN  window_availsr-  r   r  Z	new_availadspot_in_availZold_adspot_in_availZwindow_durationsr  r1  Zplaylist_windowZwindows_start_atZwindows_end_atZwindow_avail_countZwindow_availrB  Zlast_windowZ
last_availZlast_avail_linkr  r   Zexisting_Campaignairtimelogrr  rs  rt  ru  rv  rw  rx  rh  Zmessage_campaign_scoreZalert_messagery  r7   )r  r%  r  r  r8   rA     s  



	



(
"
(






 

	
	zDynamicAdPlaylist.getN)rs  rt  ru  r   r  r  rA   r7   r7   r7   r8   r    s    r  c                   @   s   e Zd ZdZdd ZdS )StaticVerifsValidationz
        A view to handle static verifications validation.

        This view is responsible for handling GET requests related to static verifications validation
        for a playlist identified by its primary key id.
    c                 C   s|  ddl m }m} tjj|d}|r| }|jdd}tjj|jd}t	jj|j
d}g }	|D ]S}
||
jd|dd	 }||
jd|dd	 }tjj||d
}tjj|
d}| }| }||krt|D ]\}}tjj|d}|| j}|| j}|D ]q}|jj|jj||j|jj|dtdtt|jj||jjkrdnd|jdd
}t|jj|jj||j|jj|dtdtt|jj||jjkrdnd|jdd
}|   ||jj8 }|||jjd7 }|	!| qqkq1t|D ]\}}tjj|| d}|j}|j}|D ]t}|jj|jj||j|jj|dtdtt|jj||jjkr6dnd|jdd
}t|jj|jj||j|jj|dtdtt|jj||jjkrcdnd|jdd
}|   ||jj8 }|||jjd7 }|	!| qqq1t"|j
|j||j#|j$|j%|j&|j'|j&|j(j(|j(j)d|jj|jj*|jj|jjdd|	dddS t"dddS )aK  
            Handle GET requests.

            Retrieve the playlist object with the specified primary key `playlist_id`.
            Retrieve all adbreaks and windows associated with the playlist.
            For each window, retrieve adbreaks within the window and corresponding avails.

            Args:
                request (HttpRequest): The HTTP request object.
                playlist_id (int): The primary key of the playlist to retrieve.

            Returns:
                HttpResponse: A JSON response containing the adbreaks, windows, and adspots information.
        r   r
   r  r   r/   )Zstart_at__dater  r   r  )Zstart_at__gteZstart_at__lter  r  r  re   r  T)
r   r8  r   r#  r&  r  r  r  r	  	is_activer  r  )r  r   )r  rQ   r8  r   )r  r   r   r%  r&  r   r   r'  r   r   rQ   rl  z*Static verifications validation successful)r   r   r  rc  FailedzPlaylist Not Foundr  rc  )+r   r   r   r3   r4   r  r   r   r{  r9  r  r3  r  r1  r;  r  rh  r   rB  r  r   r   r8  rM  r  r  r   r@  rA  r4  r   r   r  r   r#   r%  r&  r   r   r'  r   rd   rQ   )rp  r6   Zplaylist_idr   r   r   r   ZadbreaksrL  static_verifsrN  Zwindow_ad_startZwindow_ad_endZadbreaks_in_windowr  Zavails_countZadbreaks_countr   r   r  r  r  r  Z
verif_spotr  r  r7   r7   r8   rA   i!  s   


(&zStaticVerifsValidation.getN)rs  rt  ru  __doc__rA   r7   r7   r7   r8   r  b!  s    r  c                   @       e Zd ZdZdd Zdd ZdS )AdsFromConductorzconductor/index.htmlc                 C   s^   ddl m } |jdr||jdd n|  }tjj|d}t	|| j
||dS )Nr   r  r   r   r   )current_dateZads_planning)r   rI  rA   r3  r   r   ConductorDatar3   r4   r   template_name)rp  r6   r   r  Zconductor_data_objectsr7   r7   r8   rA   "  s   0zAdsFromConductor.getc           "         s  dd l }ddlm} d|jv rb|jd }d|jv r$|jd r$|jd nd}|jd }|jds8|jd	r[z||}|jd d g d
f }|j	j
jdddd|_	|jdddd}|jg ddd}|jddd | D ]:\} | d r| d r| d r| d r|j|d df  dt
 d  7  < |j|dd qq|jddd |d d|d< |jddd ||d t
j
d  }||d t
j
d  }|jddd ||d dk }|jddd |jdddd}|jdddd}|jd ddd}|jd!ddd}|jd"ddd}|jd#ddd}|jd$ddd}|jd%ddd}|jd&ddd}|jd'ddd}|jd(ddd}|jd)ddd}|jd*ddd}|jd+ddd}|jd,ddd}|jd-ddd}|jd.ddd}|jd/ddd}|jd0ddd}|jd1ddd}|jddd ||d t
j
d2  }||d t
j
d3  }||d t
j
d4  }||d t
j
d5  }||d t
j
d6  }||d t
j
d7  }||d t
j
d8  }||d t
j
d9  }|d t
j
 |d< |d t
j
 |d< |d j
jd:ddd|d< |d j
jd;ddd|d< |d j
jd<ddd|d< |d j
jd=ddd|d< |d j
jd>ddd|d< |jddd |jd?ddd}d@}	d }
| D ]\} g dA}|	st fdBdC|D rd}	|}
q||	r	t fdDdC|D r	d@}	|
}|j|  t
 d }t
 d }t
 d }|dkr|dEkr|dkr|d krd|vr||}|dFkrdG|vrdH|vrn'|d8 }|dkr|j|  t
 d }t
 d }t
 d }nn|dks|}|j|  t
 d }t
 d }t
 d }|t|k r|dEkr`|dkr`|d kr`d|vr`||}|dFkr`dG|vr`dH|vr`n+|d7 }|t|k r|j|  t
 d }t
 d }t
 d }nn|t|k s8|j|
d | }||d dIk }|d jd }|rt|dJdKkrdJdLdC |dJd dK D n|}||d dMk }|d jd }|rt|dJdKkrdJdNdC |dJd dK D n|}|d jd }tjj|||||||k||dO q|tdPdQdRW S  t yZ } z;dd l!}|"|j#}|dS \}}}} dTt
|t$|j%|||| dU}!t&dV| dJ| dW| dX|  t|!W  Y d }~S d }~ww tdTdYdRS tdTdZdRS )[Nr   r&   )hhmmssf_to_secondsconductor_filer   rU  r   r  z.csv)r&   r   r  r   r  ru  r  
r  T)regex)Source de DiffusionMedia IDVideo IDTitre   Duréer   )r  how)r  r  u   Heure de débutr  r  r  r  )r  r  r/   ZBAZMOSQUEz** BREAK **z\(MASTER DIGITAL\)z	\(XDCAM\)z\(VERSION HABILLEE\)z\(VERSION S/T FRANCAIS\)z
\(MASTER\)z\(VERSION CENSUREE\)z\(COPIE DE REMPLACEMENT\)z\(COPIE DE REMPLACEMEN\)z\(VERSION AVEC SPONSOR\)z\(VERSION CORRIGEE\)z\( HABILLEE DOUBLEE EN DAR\)z#\(VERSION HABILLEE DOUBLEE EN DAR\)z\(VERSION AVEC SPONSz\(VERSION AVEC SPz\(COPIE DE REMPLACEMz\(VERSION DOUBLEE EN DARIz\(VERSION CORRIGEz\(VERSION CORRIGz\(VERSIOz\(COPIEzDans les 7 jours - BAzDans les 7 jours - SPOTzCLIP :ZTAJNIDzVERSION PROMO - SPOTzVERSION PROMO - BAzVERSION PROMO - CAPSULEzSPOT :zEPI#\d{0,9}:zEPi#\d{0,9}:zEpi#\d{0,9}:zepi#\d{0,9}:zNOUREDDINE HADDIOUI -HIJAZI- z^S-\d{0,9}\/\d{0,9} :F)Z
JINCRYSTALZJINOCEANZ	JINETOILEZIDPUBZIPMZJP20NJZJP20WJZF0283832ZJP20SSZJP20NSZJINRAMA1ZJINRAMA2ZJINRAMA3ZJINRAMA4ZIPGZJP20BSc                 3        | ]}|t  d  v V  qdS r  Nrs  rZ   re  ru   r7   r8   r:  "      z(AdsFromConductor.post.<locals>.<genexpr>c                 3   r  r  rs  r  r  r7   r8   r:  "  r  r   <   z00:07:00z00:09:00u   Début publicitér  r  c                 s       | ]	}t |d V  qdS Z02dNr4  r  r7   r7   r8   r:  "      u   Fin Publicitéc                 s   r  r  r  r  r7   r7   r8   r:  "  r  )ad_break_start_atad_break_end_atdureeshow_before
show_afterif_show_duringr   r   SuccesszAds extracted successfully.r  r   r  )r  rc  Z
error_typer   r  functioncodez	Error in z in z(): z!Please upload a valid Excel file.No file uploaded.)'r  r   r  r  r@   r   endswithr  r  r  rD   r   ZdropnaZreset_indexr  Zisnar  r  ZfillnaZastypecontainsrg  lstripanyr   r  rh  rX  r  r3   r&  r#   rv  	traceback
extract_tb__traceback__r{  rs  r   )"rp  r6   r  r  r  rd   Z	file_dateZdata_conductorr   Zstart_foundstart_indexZvalues_to_checkZbefore_indexZshow_before_titleZshow_before_video_idZshow_before_durationZafter_indexZshow_after_titleZshow_after_video_idZshow_after_durationZpub_dataZdebut_pub_datar  Zfin_pub_datar  Zad_break_durationry  r  tbr   line_num	func_namert  Zerror_detailsr7   r  r8   r  "  s(  
"


8&

(



(
@@
 
zAdsFromConductor.postNrs  rt  ru  r  rA   r  r7   r7   r7   r8   r  "  s    r  c                   @   r  )VerifsStaticInsertzverifs/index.htmlc                 C   s   t || ji S rr  )r   r  rp  r6   r7   r7   r8   rA   #  s   zVerifsStaticInsert.getc                 C   s  d|j v r|j d }|jdrzdd lm  m} tj|jd 	dd }|
|}| }g }ddi}|d|D ]}	|jd	}
|jd
}tjj|
|d }t| |jd}|	d|D ]]}|jd}|jd}|jd}|jd}|jd}|jd}||jj|j||||dd	dd |||d	 tjj|jj|j||||dd	dd |||dd
 qeq=tdd|dW S  ty } zt| tdt|dW  Y d }~S d }~ww tdddS tdd dS )!Nverifs_filer  r   r   r   nsr  zns:CueMessager  r  )r   r  r   zns:Spotr#  r&  r  r  r  r	  r  r  +)	r   r8  r   r#  r&  r  r  r  r	  r  r  r  zVerifs Collected successfully.)r  rc  r   r  r  z"Please upload a valid Verifs file.r  )r  r   r  r!  r   rD  rE  r   splitextrh  r  Zgetrootfindallr$  rA   r2  r3   r4   r  r   r   r   rd   r   r   r   r  r#   rv  rD   )rp  r6   r  r*  Z	last_partrQ  rK  r  	namespaceZcue_messageZnetwork_namerA  rI  Zbroadcast_dater  Z
traffic_idspot_idZair_timeZ
air_lengthZair_status_coder	  ry  r7   r7   r8   r  #  sx   


"zVerifsStaticInsert.postNr  r7   r7   r7   r8   r  #  s    r  c                 C   s  zt tj| jdd}t }t| t| jj	 t| j
j t| j t| t| j
j t| j
j t| j | d| jj	 d| j
j d| j d| d| j
j d| j
j dt | j d}t| td}|dt | |d	t | j d
 |dt | j d |dt | j
j |dt | j
j |dt | j |dd |dd |dd d}tjj| d}t|D ]\}}t|d}	|	dt |jdd d |	d|j tjj|d}
t|
D ]\}}t|	d}|d t |jdd d |d!|j |d"d tjj|d#}t|D ]R\}}|d$7 }td%tt |j!j"}t|d&}|d'|j!j" d( |d)d* |d+| |d,t |j# |d-d. |d/|j!j$ |d0t | q&qqt%|}tj&tj'(|d1d2 t)|d3}|j*|d4d1d5 W d    n	1 sw   Y  d1|d6d7W S  t+y } zd8d d9| d7W  Y d }~S d }~ww ):Nr   r   z/files/schedules/r   r   r+  r   r   r  r  r  r  r  r  r	  r
  r   r  r  r  r  r   r  r  r  r  r  r  r  r  r  r  r  r  r  r&   r  r   r'  r(  r!  r"  r%  r$  r)  r*  r&  r#  Tr  r-  r.  r/  z!Playlist File Created Succesfully)r  r   rc  Fz	Exeption ),rD   r   r3  r   r   rE  rb  r   r   rd   r   rQ   r8  r   r   r5  r6  r7  r9  r3   r4   rh  r=  r  r   r:  r;  r<  r>  r   r@  rA  r4  r  rB  rC  r   rD  rG  r   rq  r   r   rv  )r   r   Zcurrent_directoryr  rK  rM  r  r   rN  Zwindow_elemr  Zavail_indexr   Z
avail_elemr  Zadspot_indexr   rP  Zadspot_elemrQ  r   ry  r7   r7   r8   ri  k#  s   

L
  
ri  c                 C   s0   ddl m} |tdd}|  t|  d S )Nr   r   )ZLeilarD  >   i'  )targetargs)	threadingr   rT  r$  r   rX  )r6   r   threadr7   r7   r8   	send_vast#  s   r  c                 C   s    t j | d}|tdd }|S )Nr  r&   rV  )r   r3  r   )r   	timestampZone_week_agor7   r7   r8   get_last_week#  s   r  c                   @      e Zd Zdd ZdS )PlaylistVastc           
      C   sf  ddl m} t|jd}|d}|d}zbztjj||dd}tt	|j
d d	 }td
|j W n tjyG   d}tddi Y nw W z!tdd|ii | d| }tjj||d }	td|	j W nB tjyz   tddi Y n3w z!tdd|ii | d| }tjj||d }	td|	j W w  tjy   tddi Y w w td|iS )Nr   )serializersZads_scheduler   rj  r  )rR   rU   rK   i D r  zAnalytic SRF: rc  zSFR Not FoundZEPG	last_weekr  )r  r  zEPG: zEPG Not FoundrE   )django.corer  r  r@   rA   r   r   r3   r   r`   rV   r   rU   rB   r  r4   r  r  r#   )
rp  r6   r  r  rq  Zlast_week_minutZanalytics_datar  Zlast_week_epgZepg_datar7   r7   r8   r  #  sD   

		zPlaylistVast.postN)rs  rt  ru  r  r7   r7   r7   r8   r  #      r  c                 C   s0   t j| |d}|jdkr|j|_|  |S d S )Nr  r  )r  rA   r  rt  r  r  )rC  r  replyr  r7   r7   r8   alma_callback$  s   
r  c                    s   t jjdd d}t jjdd}ddi|r>tdd  fdd	|D }d
d	 |D }W d    d S 1 s7w   Y  d S d S )Nz
2024-04-17)datetime_timestamp__icontainsr  )r  r@  rN  r  max_workersc                    s    g | ]} j t|j|d qS ))r  r  )submitr  r  )rZ   r  executorr  r7   r8   r\   $  r  zanswer_Alma.<locals>.<listcomp>c                 S   s   g | ]}|  qS r7   )r   )rZ   futurer7   r7   r8   r\   $  r]   )r%  r3   r4   r   )r6   Zupdated_repliesverify_repliesfuturesr   r7   r   r8   answer_Alma$  s   "r  c                 C   s(   ddl m} ddi}td | }d S )Nr&   )update_vastr@  rN  Zcalled)rQ  r  r   rR  )r6   r  r  Zupdaterr7   r7   r8   update_vast_response_v2$  s
   r  c                    s  dd l }d}ddifddd}dgd	gd
gd}tdd d}t|ddd}||}tddy t|dddb}||}|g d | D ]I\}}|D ]B fddt|D }	d}
|	D ].}z| }|
d7 }
tj	j
||j|
d}||j|
g W qg ty } z|d }~ww qTqNW d    n1 sw   Y  W d    n1 sw   Y  W d    n1 sw   Y  W d    d S W d    d S 1 sw   Y  d S )Nr   r  r@  rN  c                    s>   ddl m}m} || }||j}tj|  d}t|j |S )Nr   urlparseparse_qsrC  r  )urllib.parser	  r
  r  r  rA   r   r  )rC  r	  r
  
parsed_urlquery_paramsr  r  r7   r8   make_request_legod$  s   

z/update_vast_response.<locals>.make_request_legor&   a	  https://ad.doubleclick.net/ddm/trackimp/N2359742.5267928STAMP0/B32770836.406082389;dc_trk_aid=598249260;dc_trk_cid=223908355;ord=[timestamp];dc_lat=;dc_rdid=;tag_for_child_directed_treatment=;tfua=;gdpr=$%7BGDPR%7D;gdpr_consent=$%7BGDPR_CONSENT_755%7D;ltd=;dc_tdv=1a	  https://ad.doubleclick.net/ddm/trackimp/N2359742.5267928STAMP0/B32609683.406115909;dc_trk_aid=598248921;dc_trk_cid=223962908;ord=[timestamp];dc_lat=;dc_rdid=;tag_for_child_directed_treatment=;tfua=;gdpr=$%7BGDPR%7D;gdpr_consent=$%7BGDPR_CONSENT_755%7D;ltd=;dc_tdv=1zhttps://ad.doubleclick.net/ddm/trackimp/N2359742.5267928STAMP0/B32623870.406579509;dc_trk_aid=598397817;dc_trk_cid=224003872;ord=[timestamp];dc_lat=;dc_rdid=;tag_for_child_directed_treatment=;tfua=;ltd=;dc_tdv=1)CityzNinja-GOTechnicr  z$/var/www/html/DAI27/media/Vast-3.csvr   r/   )newliner   rL  )ZLinkzStatus CodezNumber of Requestsc                    s   g | ]}  qS r7   )r  )rZ   r  )r  r  rC  r7   r8   r\   $  rt  z(update_vast_response.<locals>.<listcomp>)r  rC  r  request_count)csvr   r   r  writerowrS  r   r   Trackinglinkr3   r&  r  rv  )r6   r  r  Ztracking_linksZcsv_filenameZcsvfiler  r  rH  r  r  r  r   Ztrakcingry  r7   )r  r  r  rC  r8   update_vast_response($  sf   -


"r  c                 C   s   ddl m} |  tdS )Nr&   )sample_taskz"Task is running in the background!)rQ  r  rR  r"   )r6   r  r7   r7   r8   run_task_view$  s   r  c              	      s   ddl m } dd l}ddlm} | }|d}tjj|d d d d d d d}ddifdd	t	   fd
d|D }W d    d S 1 sIw   Y  d S )Nr   r  rw  r   )r  tracking_start_statusr>  r?  r@  r'  r(  r@  rN  c                    s,  ddl m}m} || }||j}tj|  d}|jd kr|d}d| v r0|j|_|  d S d| v r>|j|_	|  d S d| v rL|j|_
|  d S d| v rZ|j|_|  d S d	| v rh|j|_|  d S d
| v rv|j|_|  d S d| v rtd|j |j|_|  d S |j|_|  d S d S )Nr   r  r  r5  Zjs_startZjs_first_quartileZjs_midpointZjs_third_quartilevast_impressionZdoubleclickZjs_completezCheck: )r  r	  r
  r  r  rA   rt  r  r  r>  r?  r@  r'  r(  r   r=  )rC  r  r	  r
  r  r  r  Zevent_valuer  r7   r8   make_requests%  s>   


z.update_vast_response_v3.<locals>.make_requestsc                    s.   i | ]}|j |jfD ]
} |||q
qS r7   )r  r  r  )rZ   r  rC  )r  r  r7   r8   r  =%  s    z+update_vast_response_v3.<locals>.<dictcomp>)
r   r  r  rx  r   r   r%  r3   r4   r   )r6   r   r  rx  r  r  r  r  r7   )r  r  r  r8   update_vast_response_v3$  s,   
-"r  c                   @   s&   e Zd Zdd ZdddZdd ZdS )	
VastReportc                 C   s   ddl m } ddlm} | d}ddg}g }|D ]}tjj|d}|| q|jj	|d	d

 }	|jj	| tdd dd	d

 }
t|d||	|
dS )Nr   r  r&   r2  r   Too Good To Go 30sToo Good To Go 15srY  F)datetime_timestamp__containsZ%impression_sprintserve_status__isnullr   z!DAIManagementApp/vast_report.html)rz   r  Zprev_impression)r   r   r3  r   r   r   r3   rA   r   r4   r  r   r   )rp  r6   r   r3  rd  campaigns_listrz   r  campaign_objZvast_total_impressionsZprev_vast_total_impressionr7   r7   r8   rA   L%  s2   zVastReport.getNc                 C   s   ddl m } ddlm} ddlm} |jd| d}|jd| d}|rE|rEt	d| t	d	| | j
||d
}t|ddS | d}	| tdd d}
| j
|
|	d
}t|ddS )Nr   r  r  	TruncDater$  r   r  zstart: zend: r  Fr/  ru  r   )r   r  r  r  r%  r@   rA   r   r   r   get_campaignsr#   r   )rp  r6   r  r   r  r%  r$  r  r   rd  r  r  r7   r7   r8   r  h%  s   

zVastReport.postc                 C   s   ddl m} ddlm} tjj||d}|j|dddj|dd	d}g }|D ]}|d }	|d	 }
t
 j|	d
}||	|
|d q,|S )Nr   r  r$  )Zdatetime_timestamp__date__gteZdatetime_timestamp__date__lter  r  r   )
date_countr'  )r%  )r   r  rz   )r  r  r  r%  r%  r3   r4   r  r  r   r   get_spots_impressionsr   )rp  r$  r  r  r%  Zvast_querysetZvast_data_groupedr   Z
date_groupr   r'  r   r7   r7   r8   r&  %  s.   
	zVastReport.get_campaignsrr  rs  rt  ru  rA   r  r&  r7   r7   r7   r8   r  J%  s    
r  c                   @   r  )	VastChartc                 C   sF   ddg}g }|D ]}t jj|d}|| qt| t|dd|iS )Nr  r   rY  DAIManagementApp/vast_call.htmlrz   )r   r3   rA   r   r   r   )rp  r6   r"  rz   r  r#  r7   r7   r8   rA   %  s   zVastChart.getNrs  rt  ru  rA   r7   r7   r7   r8   r*  %  r  r*  c                   @   r  )LoadCampaignc                 C   sf   |j d}ztjj|d}tjj|dd}tt|ddW S  tj	y2   tddid	d
 Y S w )Nr  rY  r  r   Fr/  rC   zCampaign not foundi  r  )
rI  rA   r   r3   r   r4   r  r#   r   rB   )rp  r6   r  r#  spotsr7   r7   r8   rA   %  s   zLoadCampaign.getNr,  r7   r7   r7   r8   r-  %  r  r-  c                   @   s$   e Zd Zdd Zdd Zdd ZdS )CallVastc                 C   s   t |dd|  iS )Nr+  rz   )r   r&  r  r7   r7   r8   rA   %  s   zCallVast.getc              	   C   s   ddl m} dd l}ddlm} |jd}|jd}|jd}t|}tj	j|d}|rf|rf|dkrft
d	| t
d
|j ddddddddd}	ddd}
ddi}t
d|
|  |j|
| ||	|d}t|dd|  iS )Nr&   rB  r   r   r  r   num_requestrY  zCampaign name: zCampaign END DATE: rF  rG  rE  rH  rI  r  rJ  rN   rK  a  https://tv.springserve.com/vast/881543?w=1920&h=1080&cb={{CACHEBUSTER}}&ip={{IP}}&ua={{USER_AGENT}}&pod_max_dur={{POD_MAX_DUR}}&pod_ad_slots={{POD_AD_SLOTS}}&app_bundle={{APP_BUNDLE}}&app_name={{APP_NAME}}&app_store_url={{APP_STORE_URL}}&did={{DEVICE_ID}}&us_privacy={{US_PRIVACY}}a  https://tv.springserve.com/vast/881550?w=1920&h=1080&cb={{CACHEBUSTER}}&ip={{IP}}&ua={{USER_AGENT}}&pod_max_dur={{POD_MAX_DUR}}&pod_ad_slots={{POD_AD_SLOTS}}&app_bundle={{APP_BUNDLE}}&app_name={{APP_NAME}}&app_store_url={{APP_STORE_URL}}&did={{DEVICE_ID}}&us_privacy={{US_PRIVACY}})r  r   r@  rN  zLink: )rC  rO  r  r  r+  rz   )rQ  rC  r  rB  r   r@   rA   r4  r   r3   r   r  rR  r   r&  )rp  r6   rC  r  r   r  r   r0  r#  r  Zlink_mappingr  rS  r7   r7   r8   r  %  s8   
zCallVast.postc                 C   s2   g d}g }|D ]}t jj|d}|| q|S )N)r  ZHarry_Potterr  ZStarWarsrY  )r   r3   rA   r   )rp  r"  rz   r  r#  r7   r7   r8   r&  &  s   zCallVast.get_campaignsNr)  r7   r7   r7   r8   r/  %  s    :r/  c                 C   s,   t jjdd}tjj|d}t|  d S )NzLegoPorsche.tsr  )r  )r   r3   rA   r%  r4   r   r  )r6   r  Zvastsr7   r7   r8   update_report&  s   r1  )r   r  )rS   )r(  )r&   r&   )r/   r/   r/   (  rE  r  r@  r  r  loggingargparser   r  r  mathr   r  r   rY  r   r   r   ior   r	   r   Znumpy.lib.stride_tricksr   ImportErrorZlxmlr   Zxml.dom.minidomZdomZminidomr  r!  rD  r5  r_  r   r   Zrest_framework.viewsr   r   rB  r   djangor   r  r   django.contribr   r   r   r   django.shortcutsr   r   Zdjango.contrib.sessions.modelsr   django.core.exceptionsr   django.template.loaderr   $django.contrib.sessions.base_sessionr    #django.contrib.sessions.backends.dbr!   ZDBStorer  r"   r#   r$   r%   r   r(   r)   r*   r+   r,   	getLoggerrs  loggerr   r9   rF   rH   r   r   r   r   r   r   r   rT  rV  r\  ri  rl  rp  ru  rz  r}  r  r  r  r  r  r  r  r  r  r  r   r  r  r  r   rH  rJ  rM  rV  re  r  r  r  r  r  rd  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r"  r)  r,  r1  r=  r?  rB  rC  rT  rX  r[  r\  r^  rb  rm  r`  r  r  r  r   r  rf  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r   r  r<  r=  rG  rK  rW  r  rY  r_  r[  r`  rw  django.confrk  r  r+  r|  r  rX  r  r  r  r  r  r  r  r  r  r  r   r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r	  r1  rA  rT  r  re  rf  r]  django.viewsrv  rx  django.utilsr  django.utils.dateparsery  rz  rj  r{  r2  r  r   r|  r}  r   r~  r  r  r  r  ri  r  r  r  r  r  r  r  r  r  r  r*  r-  r/  r1  r7   r7   r7   r8   <module>   s  
 e
 \




^	;
"
^ j b
"
;
8
L

5


 G 6  7
 @
v
 Q
=A
O.


@
}
	






 t'
`
	 
 7
 7+


: @
 
$
7
Cm



 

zohn

 


	
	
&7 cn9FQGNL rz^X*   ? '  W^(
	 @[[J