B
    u h                @   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 d d	lmZ 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l/m0Z0m1Z1 d dl2m3Z3m4Z4 d dl5m6Z6 d dl7m8Z8 d dl9m:Z: d dl;m<Z< d dl=m>Z? d dl@mAZAmBZBmCZC d dl/mDZD ddl0T ddlEmFZFmGZGmHZHmIZImJZJ eKeLZMddd d!d"gZNd#d$ ZOd%d& ZPd'd( ZQeFd)d* ZReFd+d, ZSeFd-d. ZTeFd/d0 ZUeFd1d2 ZVeFd3d4 ZWd5d6 ZXdd8d9ZYd:d; ZZd<d= Z[d>d? Z\d@dA Z]dBdC Z^dDdE Z_dFdG Z`dHdI ZaeFdJdK ZbeFdLdM ZceFddOdPZddQdR ZeeFddSdTZfeFddUdVZgeFddWdXZheFdYdZ ZieFdd\d]ZjeFdd^d_ZkeFdd`daZleFdbdc ZmeFddddeZneFdfdg ZoeFdhdg ZoeFdidj ZpeFdkdl ZqeFdmdn ZreFdodp ZseFddqdrZteFdsdt ZueFddudvZveFddwdxZweFdydz ZxeFd{d| ZyeFd}d~ ZzeFdd Z{eFdddZ|eFdd Z}dd Z~eFdd Zdd ZeFdd ZeFdd ZeFdd ZeFdd ZeFdd ZeFdd ZeFdd ZeFdd ZeFdd ZeFdd ZeFdddZeFdddZeFdddZeFdd ZeFdddZeFdddZeFdd ZeFdddZeFdddZeFdddZeFdd ZeFdddZeFdddZeFdddZeFdddZeFdd Zdd ZddÄ Zddń ZeFddǄ ZeFddɄ ZeFdd˄ ZeFddd̈́ZeFdddτZeFddф ZeFddӄ ZddՄ ZeFddׄ ZeFddل Zddۄ ZeFdd݄ ZeFddd߄ZeFdddZeFdddZeFdd Zdd ZeFdd ZeFdddZeFdddZeFdd Zdd Zdd Zdd Zdd ZeFdd ZeFdd ZeFdd ZeFdd Zdd  Zdd Zdd Zd dlZd dlmZ d dl9m:Z: d dlZdd Zdd	 Zd
d ZÐdd ZĐdd ZeFdd ZŐdd ZeFdd ZeFdd ZeFdd ZeFdd ZeFdd ZeFdd ZeFdd  ZeFd!d" ZeFd#d$ ZeFd%d& ZeFd'd( Zѐd)d* ZeFd+d, ZeFd-d. ZԐd/d0 ZeFd1d2 ZeFd3d4 ZeFd5d6 ZeFd7d8 ZeFd9d: ZeFd;d< ZeFd=d> ZeFd?d@ ZeFdAdB ZސdCdD ZeFdEdF ZdGdH ZdIdJ ZdKdL ZdMdN ZdOdP ZdQdR ZdSdT ZG dUdV dVe%Zd dlZd dWlmZ d dXl+mZ d dYlmZ d dZlmZ dd[lEmZmZ dd\l0mZmZmZmZmZmZmZmZ G d]d^ d^eZG d_d` d`eZG dadb dbeZG dcdd ddeZdedf Zdgdh Z didj ZG dkdl dle%Zdmdn Zdodp Zdqdr Zdsdt Zdudv Zdwdx ZG dydz dze%Z	G d{d| d|e%Z
G d}d~ d~e%ZG dd de%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_attributesz#C152B6z#EA792Dz#499E59z#23A8E2z#974BEFc             C   sL   d| j krB| j d rB| j d d krBtjj| j d dd krBtdS t| dS )Nid_user)r)    zDAIManagementApp/login.html)sessionUsersobjectsfilterr   r   )request r0   8/var/www/html/DAI27/Adtlas_DAI/DAIManagementApp/views.pylogin7   s    :r2   c             C   s   | j d}| j d}ytjj|dj|d}W n( tjk
rZ   t| d t| dS X |rd| j	d< t
|j| j	d	< td
S t| d d S )Nusernamepassword)	userlogin)userpasszSometing Wrong.zDAIManagementApp/login.htmlyesisloggedr)   z/DAIManagement/zError detected.)POSTgetr,   r-   r.   DoesNotExistr   errorr   r+   strr)   r   )r/   r3   r4   datar0   r0   r1   dologin<   s    
r?   c             C   s2   y| j d= | j d= W n tk
r(   Y nX tdS )Nr8   r)   r2   )r+   KeyErrorr   )r/   r0   r0   r1   logoutK   s    rA   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}x|D ]z}|j|d
dd}tdt| t|dkrxt|jt|j dd |D }	fdd|D }
||
 || qxW 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 Q R X |! &}|"| fd$d|# D }W d Q R X t$| d%|	|t||||||||||||tjj| j	d d||d&S )'Nr"   )pkz-id_impressionr   r)   )r)   sfr_channel_nameT)flatFrance)sfr_channel_name__inregionday1)rC   minutepurcentzres === c             S   s   g | ]}|d  qS )r   r0   ).0xr0   r0   r1   
<listcomp>F  s    zindex.<locals>.<listcomp>c                s   g | ]}t |d    qS )r"   )float)rL   rM   )nbr0   r1   rN   G  s    zlabels === channel_namec             S   s   g | ]}|d  qS )r   r0   )rL   rM   r0   r0   r1   rN   V  s    0001)airStatuscodez	-id_verifP   )r)   pacingz-id_activity   aM  
        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
    aj  
        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                s   g | ]} | qS r0   r0   )rL   row)rX   r0   r1   rN     s    c                s   g | ]} | qS r0   r0   )rL   rZ   )rY   r0   r1   rN     s    zDAIManagementApp/index.html)labelsrH   Znb_channels	campaignscampaigns_activeadvertisersbookedsagences	playlists	activiteschannelsresultchannels_sfrcolorcampaigns_datavast_resultsno_vast_results)%Channelsr-   r:   SfrAnalyticsallorder_byrH   listr.   r+   values_listSfr_analyticsr=   ImpressionsprintlenrO   total_usersmarket_share_purcentappendcolorsVerifs	Campaignsmost_advertisersbooked_adbreaksactive_agencyra   Activity	django.dbrW   utilsrX   rY   cursorexecutefetchallr   )r/   channel	dayformatre   valrd   channel_sfrrK   resr[   r>   rf   rc   linesr\   r]   
advertiserr_   r`   playlistrb   no_vast_query_oldno_vast_query
vast_queryrW   r   ri   rh   r0   )rX   rY   rP   r1   index/  sx    "


.*

 

 r   c             C   s  ddl m }m} dddd}tjj| jd d}| |dd }|d}y$tt	jj||d	d
 }|j
j}W n
   |S |dd}	tjj||dd}
dd |
D }
t|
dkr|S x6|
D ].}|dkr|d  d7  < q|d  d7  < qW |d d t|
 }t|d|d< |S )Nr   )r	   r
   )	delivered	cancelledrK   r)   )r)   r"   )daysz%Y-%m-%d)id_channel__inbroadcastdate-r*   )networknamebroadcastDaterS   c             S   s   g | ]}|d  qS )r   r0   )rL   ir0   r0   r1   rN     s    zplaylists.<locals>.<listcomp>rR   r   r   d      rK   )r	   r
   rj   r-   r.   r+   nowstrftimern   	Playlists
id_channelrQ   replacerx   ro   rs   round)r/   r	   r
   rd   rc   	yesterdaydater   r   rH   verifsr   rK   r0   r0   r1   ra     s0    

ra   c             C   s   t jj| jd ddd}g }xz|D ]r}i }tjj|d d}tjj|dd}tjj|d	}|d
 |d< t||d< t||d< t|dkr&|	| q&W |S )Nr)   )r)   r   rQ   r   )r   T)Zid_adpost__inbooked)id_adspot__inr"   r   r   avail)
rj   r-   r.   r+   ro   Adspotsry   AdspotsInAvailrs   rv   )r/   rc   rd   r   radspotr   r   r0   r0   r1   r{   )  s    
r{   c             C   s   t jj| jd ddddd}g }xl|D ]d}i }|d |d< tjj|d d}tjj|d	d
}t|dkr*|d |d< t||d< || q*W |S )Nr)   r   )r)   
is_deleted	id_agencynamer	   r"   )r   T)Zid_brand__inrU   r   r   r\   )	Agencyr-   r.   r+   ro   Brandsry   rs   rv   )r/   r`   rd   Zagencer   brandsr\   r0   r0   r1   r|   ;  s     
r|   c             C   s   t jj| jd dd}dd |D }tjj|dd}g }g }g }xR|D ]J}tjj|d}t|}|	| |j
|j||jd	}	t|	 |	|	 qNW t| x<td
dD ].}t|}
||
}||}|	||  qW t| |S )Nr)   )r)   id_brandc             S   s   g | ]}|d  qS )r   r0   )rL   rM   r0   r0   r1   rN   M  s    z$most_advertisers.<locals>.<listcomp>)pk__inz	-id_brand)r   )r   
name_brandtotal_adspotslogor      )UsersAdvertisersr-   r.   r+   ro   r   rm   r   rs   rv   r   
brand_namer   rr   rangemaxr   pop)r/   	brands_idr   Ztotalsdicrd   r   adspotstoutalbrand	max_valueZ	max_indexar0   r0   r1   rz   J  s0    




rz   c             C   s(   | j dkr| jd}t| t| dS )Nr9   Zoptionsnamez"DAIManagementApp/test_options.html)methodr9   r:   rr   r   )r/   optionsr0   r0   r1   test_optionse  s    
r   c          	   C   s>   t |d | d"}x|  D ]}|| qW W d Q R X d S )N/zwb+)openchunkswrite)filepathfilenamedestinationchunkr0   r0   r1   handle_uploaded_filel  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}x |D ]}t|jdd}|d }td||< || d| || d|j tjj|jd}t|dkr|||  i }d}xf|D ]\}t|jdd}|d }t|| d||< || d | || d!|j || d"d tjj|jd# d$}i }d}x|D ]}|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 W |d%7 }qW |d%7 }q
W |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 Q R X |S )9N)r   )id_zone_channelz%Y-%m-%dz%Y%m%dr   )id_channel_idversionr   r   is_draftdraft_versionSchedulexmlnsz&http://www.scte.org/schemas/118-3/201Xr   begDateTimezT00:01:00+00:00endDateTimezT23:59:59+00:00networkNamezoneNamerevisionlevelschemaVersionz http://www.w3.org/2001/XMLSchema)id_playlistwindow_startr    Tz+00:00WindowwindowStartwindowDuration)	id_windowAvail
availStartavailInWindowavailNum)id_avail	id_adspotr"   z%H%M%S00Spot	eventTypeLOI	trafficIdpositionInAvaillengthspotIdadIdsecschedSourceLocalr   z.schzfiles/DAI-Managementwbzutf-8T)encodingxml_declaration)1rj   r-   r:   ChannelsZoner	   strptimer   r=   r   intr   xmlElementsetr   zonenameWindowsr.   r   rm   r   r   window_durationAvailsr   rs   rv   avail_start
SubElementavailinwindowr   r   select_relatedtimegmtimer   durationpositioninavailr   ElementTreerr   rQ   r   osr   existsmakedirsr   r   ) rH   
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   filesr0   r0   r1   GenerateXMLfromDatabaser  s    
"





&
r  c             C   s   t tdddS )Nz
2021-08-162MrI   )r   r  )r/   r0   r0   r1   testGenerateFromDatabase  s    r  c             C   sP   xJt dD ]<\}}}x0|D ](}| |krt j||}t||}|S qW qW d S )Nr   )r  walkr   joinr   )r   moder   subdirsr  r   r   fr0   r0   r1   getfile  s    

r%  c             C   s   t | ||}d|_|| xptt D ]^\}}}xR|D ]J}	||	kr>tj||	}
t	|
 t
|
d}|d| | W d Q R X q>W q.W |  d S )Nzutf-8rbzSTOR )ftplibFTPr   cwdr  r   getcwdr   r!  rr   r   
storbinaryquit)hostuserr4   r   
path_inftpftpr   r#  r  r   r   r$  r0   r0   r1   
uploadFTP2  s    

$r1  c       
   	   C   s   t | ||}d|_|| |}|d | }t| y.t|d}	|d| |	j W d Q R X W n( tk
r   td| t	
| Y nX |  d S )Nzutf-8r   r   zRETR ZERR)r'  r(  r   r)  rr   r   Z
retrbinaryr   r   r  unlinkr,  )
r-  r.  r4   Zfilepath_inftpZ
file_inftpZ	localpathr0  r   	localfiler   r0   r0   r1   downloadFTP  s    
"
r4  c             C   s   t tddddddS )Nzuk06.tmd.cloudztestftp@epgsano.comzI?#=s3FfnSu_z/2M/schedules/ztest.txtz5/var/www/html/DAI-Management/DAIManagement/FTP_files/)r   r4  )r/   r0   r0   r1   test_download  s    r5  c       
   	   C   sp   |rt | |||nt | ||}|| tjt}d}t|d}	|d| |	 W d Q R X |	  d S )Nzfiles/DAI-Managementr&  zSTOR )
r'  r(  r)  r  r   dirname__file__r   r+  r,  )
r-  portr.  r4   r   r/  r0  
module_dirr   r   r0   r0   r1   	uploadFTP	  s    "
r:  c       	   
   C   s   ddl m} yb|| P}||| t|d}|d| | W d Q R X td| d| d W d Q R X W n0 tk
r } ztd|  W d d }~X Y nX d S )	Nr   )r(  r&  zSTOR zFile 'z' uploaded to 'z' on the FTP server.zError: )r'  r(  r2   r   r+  rr   	Exception)	r-  r3   r4   local_file_pathremote_file_pathr(  r0  r   er0   r0   r1   AlmaFtp  s    
$r?  c             C   s8  y\t | ||}|| tjt}d}t|d}	|d| |	 W d Q R X |	  dS  t
k
r2 }
 ztdt|
  td y\t | ||}|| tjt}d}t|d}	|d| |	 W d Q R X |	  dS  t
k
r  }
 ztdt|
  td dS d }
~
X Y nX W d d }
~
X Y nX d S )	Nzfiles/DAI-Managementr&  zSTOR Tz%suF   ⚠️ Connectivity issue with Enensys server, trying again ... ⚠️u=   ⛔️ update was not pushed, please check OpenVPN or EnensysF)r'  r(  r)  r  r   r6  r7  r   r+  r,  r;  rr   typesend_msg_telegram2)r-  r8  r.  r4   r   r/  r0  r9  r   r   r>  r0   r0   r1   
uploadFTP4/  s2    

rB  c             C   s&  d}t |}|jd}|jd}|jd}|jd}|d}g }x|D ]}	|	d}
|	d}|	d	}|d
d}|dd}|	d}|	d}|	d}y$tjj||||
||||||d
 W qR t	k
r } z*tjj
||||
|||||d	j|d}W d d }~X Y qRX qRW tdS )Nz%files/results/20210906-2005-00001.xmlr   r   r   verCompleter   r   r   airTimer   r   z+02:00r*   	airLengthairStatusCoder   )
r   r  r   r   r   rD  rE  rS   r   vercomplete)	r   r  r   r   r   rD  rE  rS   r   )rG  zdata has been inserted)xml2parse
firstChildgetAttributegetElementsByTagNamer   rx   r-   update_or_creater   r.   updater   )r/   r   docr   r  r   rC  Spotsresultsspotr   r   rD  
newAirTimenewAirTime2rE  rF  r   r>  Z	insertionr0   r0   r1   
saveXML2dbj  s,    








$<rU  c             C   s   t tdddddS )Nzuk06.tmd.cloudztestftp@epgsano.comzI?#=s3FfnSu_z,/files/DAI-Management/20210815-2005-2M-1.schz2M/schedules/France)r   r1  )r/   r0   r0   r1   test_upload  s    rV  r*   c             C   s  t d}tj|}|  d }ddkr2d}ddkr>d}dd }|d	krtjj|d
}ddl	m
} | }	d|jj d|jdd	|jdd	f}
|	||
 d}||	}x2|D ]*}|d d krt| q|t|d 7 }qW t| d||dS tdS d S )NzEurope/Parisi  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                s&   dd | j D   fdd|  D S )z'Return all rows from a cursor as a dictc             S   s   g | ]}|d  qS )r   r0   )rL   colr0   r0   r1   rN     s    z9campaign_report.<locals>.dictfetchall.<locals>.<listcomp>c                s   g | ]}t t |qS r0   )dictzip)rL   rZ   )columnsr0   r1   rN     s   )descriptionr   )r   r0   )rZ  r1   dictfetchall  s    
z%campaign_report.<locals>.dictfetchallr*   )rB   )rW   %r   total_volumez%DAIManagementApp/report_campaign.html)r>   totaledit_campaign)pytztimezoner	   r   	utcoffsettotal_secondsry   r-   r:   r~   rW   r   r   r   	start_dayr   end_dayr   rr   r   r   r   )r/   r>   paris_tz
paris_time
utc_offsetqueryr\  campaignrW   r   
data_tupler_  rZ   r0   r0   r1   campaign_report  s8    
	


rm  c                s&   dd | j D   fdd|  D S )z'Return all rows from a cursor as a dictc             S   s   g | ]}|d  qS )r   r0   )rL   rW  r0   r0   r1   rN     s    z dictfetchall.<locals>.<listcomp>c                s   g | ]}t t |qS r0   )rX  rY  )rL   rZ   )rZ  r0   r1   rN     s    )r[  r   )r   r0   )rZ  r1   r\    s    r\  c       &   	      s  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rd}d}d}d}ddlm    }d| d||f}||| d}||}g }d }xV|D ]N}|	|d d }|r||kr|dddddd || |}qBW x6|D ].}|d  d krt| n|t|d  7 }qW t| d!||||d"S tjj| jd# d$ d%}d&d' |D }t!jj|dd("d)} g }!xN| D ]F}"t#jj|"d*}#t$|#}$|"j%|"j&|"j'|$|"j(d+}%t|% |!|% q*W t| d,d-|!iS d S ).Nr   )FExpressionWrapper
FloatField)Substr)r
   c                s&   dd | j D   fdd|  D S )z'Return all rows from a cursor as a dictc             S   s   g | ]}|d  qS )r   r0   )rL   rW  r0   r0   r1   rN     s    zBreport_advertiser_backup.<locals>.dictfetchall.<locals>.<listcomp>c                s   g | ]}t t |qS r0   )rX  rY  )rL   rZ   )rZ  r0   r1   rN     s   )r[  r   )r   r0   )rZ  r1   r\    s    
z.report_advertiser_backup.<locals>.dictfetchallc          
      sb   y,   }|| | | }|S Q R X W n0 tk
r\ } ztd|  g S d }~X Y nX d S )NzAn error occurred: )r   r   r   r;  rr   )rj  paramsr   rowsr>  )rW   r0   r1   my_custom_sql  s    
z/report_advertiser_backup.<locals>.my_custom_sqlr9   )r	   z%Y-%m-%dzEurope/Parisi  z-Current UTC offset for France (Paris) is GMT+.re  r   r*   rf  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
            a  
            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
            )rW   r]  rD  z%Y-%m-%d %H:%M:%Sz--)r   r   rD  r^  emission_namer^  z!DAIManagementApp/view_report.html)r>   r_  rv  report_dater)   )r)   r   c             S   s   g | ]}|d  qS )r   r0   )rL   rM   r0   r0   r1   rN     s    z,report_advertiser_backup.<locals>.<listcomp>)r   statusz	-id_brand)r   )r   r   r[  r   r   z'DAIManagementApp/report_advertiser.htmlr   ))django.db.modelsrn  ro  rp  django.db.models.functionsrq  r	   r
   r   ra  r   r   rb  rc  rd  rr   r   r9   r:   r   r~   rW   r   r   r   r   rv   r   r   r-   r.   r+   ro   r   rm   r   rs   r   r   r[  r   )&r/   r   rn  ro  rp  rq  r
   r\  rt  r	   ra  current_datetimerx  rg  rh  ri  re  rf  rv  	query_oldZ	query_ol3Z
query_old4rj  r   rl  r_  r>   processed_data	last_daterZ   row_dater   r   r   r   r   r   r   r0   )rW   r1   report_advertiser_backup  sp    









r  c       &   	      s\  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rd}d}d}d}  }| }d| d||f}||| |	|}g }d }d}xt|D ]l}||d d }|r||kr|dddddd || |}|d d k	rB|t|d 7 }qBW t| d ||||d!S tjj| j d" d#j!d$d%d&}t"jj|d'd(#d)} g }!xF| D ]>}"t$jj|"d*}#t%|#}$|"j&|"j'|"j(|$|"j)d+}%|!|% qW t| d,d-|!iS d S ).Nr   )rn  ro  rp  )rq  )r
   r	   )rW   c                s&   dd | j D   fdd|  D S )z'Return all rows from a cursor as a dictc             S   s   g | ]}|d  qS )r   r0   )rL   rW  r0   r0   r1   rN     s    z;report_advertiser.<locals>.dictfetchall.<locals>.<listcomp>c                s   g | ]}t t |qS r0   )rX  rY  )rL   rZ   )rZ  r0   r1   rN     s   )r[  r   )r   r0   )rZ  r1   r\    s    
z'report_advertiser.<locals>.dictfetchallc          
      sb   y,   }|| | | }|S Q R X W n0 tk
r\ } ztd|  g S d }~X Y nX d S )NzAn error occurred: )r   r   r   r;  rr   )rj  rr  r   rs  r>  )rW   r0   r1   rt    s    
z(report_advertiser.<locals>.my_custom_sqlr9   z%Y-%m-%dzEurope/Parisi  z-Current UTC offset for France (Paris) is GMT+ru  re  r   r*   rf  rv  r   a  
            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
            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]  rD  z%Y-%m-%d %H:%M:%Sz--)r   r   rD  r^  rw  r^  z!DAIManagementApp/view_report.html)r>   r_  rv  rx  r)   )r)   r   T)rD   r"   )r   ry  z	-id_brand)r   )r   r   r[  r   r   z'DAIManagementApp/report_advertiser.htmlr   )*rz  rn  ro  rp  r{  rq  r	   r
   ra  r~   rW   r   r   r   rb  rc  rd  rr   r   r9   r:   r   r   
capitalizer   r   r   rv   r   r   r-   r.   r+   ro   r   rm   r   rs   r   r   r[  r   )&r/   r   rn  ro  rp  rq  r
   r	   ra  r\  rt  r|  rx  rg  rh  ri  re  rf  rv  r}  Zquery_11Zquery11rj  r   rl  r>   r~  r  r_  rZ   r  r   r   r   r   r   r   Z
brand_infor0   )rW   r1   report_advertiser  sh    



!5



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 }xH|D ]@}tjj|d}t|}	|j|j|	|jd}t| || qW t
| dd|iS d S )Nr*   )rB   )r   r   c             S   s   g | ]}|d  qS )r   r0   )rL   r   r0   r0   r1   rN     s    z$views_advertiser.<locals>.<listcomp>)r   z.DAIManagementApp/views_advertiser_adspots.htmlr>   )r   r   rc   r)   )r)   r   c             S   s   g | ]}|d  qS )r   r0   )rL   rM   r0   r0   r1   rN     s    z	-id_brand)r   r   r   r   z&DAIManagementApp/views_advertiser.htmlr   )r   r-   r:   r   r.   ro   distinctrn   rj   rm   r   r   r+   rs   r   r   r   rr   rv   )
r/   r   r   rc   r   r   r   r   r   r   r0   r0   r1   views_advertiser  s*    

r  c             C   s6  | j dkr(| jd}| jd}| jd}| jd}tj }d}t|dd}|d	d
}|d | d }yt| jd || W n(   dd l	}	|	
d|d |  Y nX t|||||d | d}
d}dt|
j }t|||d}|  |
  t| jd |
j||d}|  tdS t| dS d S )Nr9   advertiser_namecategoryadvertiser_desc
active_0_1zstatic/Advertisers_imgsr   _:r   __z.pngadvertiser_logor   zlogo.pngr   )r   r[  r  ry  r   zAdd AdvertirerzAdmin Add advertiser  id: )activityr   r[  r)   )
id_user_idid_brand_idry  r	   z /DAIManagement/views_advertiser/z$DAIManagementApp/add_advertiser.html)r   r9   r:   r	   r   r=   r   r   FILESshutilmover   r   r}   saver   r+   r   r   )r/   r  r  r  r  r   r   
now_infiler   r  Brandactivitedescr  Advertiser_userr0   r0   r1   add_advertiser  s2    
r  rI   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 d S )Nr9   )rB   z(DAIManagementApp/edit_advertiser_id.htmlr   r)   )r)   r   c             S   s   g | ]}|d  qS )r   r0   )rL   rM   r0   r0   r1   rN     s    z#edit_advertiser.<locals>.<listcomp>)r   z	-id_brandz%DAIManagementApp/edit_advertiser.htmlr   )rr   r   r   r-   r:   r   r   r.   r+   ro   rm   )r/   Z	id_brandsr   r   r   r0   r0   r1   edit_advertiser  s    

r  c             C   sH  t jj|d}| jd}| jd}| jd}| jd}tj }y\d}t|dd}	|	d	d
}	|d |	 d }
t| j	d d| |
 d| d |
 |_
W n   Y nX |dkr||_|dkr||_|dkr||_|dkr||_d}dt|j }t|||d}|  |  tjj| jd djddd }tdS )N)rB   r  r  r  r  ZAdvertisers_imgsr   r  r  r   r  z.pngr  zstatic/r   r*   zEdit AdvertirerzAdmin edit advertiser  id: )r  r   r[  r)   )r)   rI   )ry  r   z /DAIManagement/views_advertiser/)r   r-   r:   r9   r	   r   r=   r   r   r  r   r   r  r[  ry  r   r}   r  r   r.   r+   r  rl   r   )r/   r   r   r  r  r  r  r   r   r  r   r  r  r  r   r0   r0   r1   update_advertiser  s<    
&r  c       
      C   s   | j dkrlt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 )Nr9   zDisable AdvertirerzAdmin disable advertiser  id: )r  r   r[  )rB   r"   r   r)   )r)   r   c             S   s   g | ]}|d  qS )r   r0   )rL   rM   r0   r0   r1   rN   I  s    zdisable.<locals>.<listcomp>)r   z	-id_brandz(DAIManagementApp/disable_advertiser.htmlr   )r   r	   r   r=   r}   r  r   r-   r:   r   ry  r   r.   r+   ro   rm   r   )
r/   r   r   r  r  r  r   ry  r   r   r0   r0   r1   disable7  s    


r  c          
   C   sp  | j dkrD| jd}| jd}| jd}| jd}| jd}tj }t|ddd	d
dd
}|d kr<dt| jd  d t| }tj	d| dd d| j
kr| 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 d S )Nr9   campaign_idadspot_namer  r  creative_idr   r  r  r   ru  zadspots/user_r)   z
/campaign_zstatic/T)exist_okadspot_filer*   )rB   r   )id_campaignr  r  ry  original_filepathr	   r   r  zAdd CreativezAdmin Add creative  id: )r  r   r[  z/DAIManagement/edit_creative/)r)   z"DAIManagementApp/add_creative.htmlr>   r\   )r   r9   r:   r	   r   r=   r   r+   r  r  r  r   r   ry   r-   r   r  	id_adpostr}   r   r.   r   )r/   r  r  r  r  	vast_infor   r  r   r   rk  creativer  r  r  r\   r0   r0   r1   add_creativeO  sB    
 


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 d S )Nr9   )rB   r)   )r)   z&DAIManagementApp/edit_creative_id.html)Zadspostr\   r  T)rD   )id_campaign__inz
-id_adpostz#DAIManagementApp/edit_creative.htmlr   )r   r   r-   r:   ry   r.   r+   r   r=   ro   rm   )r/   r  adpostr\   
id_sessionZ
adspots_nnr0   r0   r1   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kryrt| jd d| | td| d | d}|d |d |d |d| d   || |  W nF   d!d l}tjd|j r|d|j d| d |  Y nX nd"}|d | |_d S )#Nr9   )rB   r  r  r  r  r  r  advertiser_idr   r  r  r   zadspots/user_r)   z
/campaign_	campaign_r  sec__z.tsz.m3u8zstatic/T)r  r  r   r   z	#EXTM3U 
z#EXT-X-VERSION:3 
z#EXT-X-MEDIA-SEQUENCE:0 
z#EXTINF:z, 
r   r*   )r   r   r-   r:   r9   ry   r	   r   r=   r   r+   r   r  r  r  r   r   r   closer  r   r  r  r  )r/   r  r  r  r  r  r  r  r  r  rk  r   r  r   r   filename_m3u8r$  r  r0   r0   r1   rN    sF    
0,



&rN  c             C   s  | j dkrt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kryrt| jd d| | td| d | d}|d |d |d |d| d  || |  W nF   dd l}tjd|j r|d|j d| d |  Y nX n|j}|d | |_|jj|kr||_|j|kr||_|j|kr&||_|j|kr8||_|j|krJ||_|	|_d }d!t|j }t||	|d"}|   |   t!d#S d S )$Nr9   )rB   r  r  r  r  r  r   r  r  r   zadspots/user_r)   z
/campaign_r  r  r  z.tsz.m3u8zstatic/T)r  r  r   r   z	#EXTM3U 
z#EXT-X-VERSION:3 
z#EXT-X-MEDIA-SEQUENCE:0 
z#EXTINF:z, 
r   zEdit CreativezAdmin Edit creative  id: )r  r   r[  z/DAIManagement/edit_creative/)"r   r   r-   r:   r9   ry   r	   r   r=   r   r+   r   r  r  r  r   r   r   r  r  r   r  r  r  r   r  r  r  ry  r  r   r}   r  r   )r/   r  r  r  r  r  r  r  rk  r   r  r   r   r  r$  r  r  r  r  r0   r0   r1   rN    s^    	
0,



&c             C   s    t j d}t| dd|iS )Nz-id_postz$DAIManagementApp/views_creative.htmlr   )r   r-   rl   rm   r   )r/   r   r0   r0   r1   views_creative1  s    r  c          	   C   s  | j dkrt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}xtt|D ]}| jd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|j|d|
d}|  xtt|D ]z}| jdt| d t| d r| jdt| d t| d }|d 7 }t|j|t|d  |d!}|  qW qW t|||jd}|jd" |j }t| d#S tjj| jd d}t| d#d$d%|iiS d S )&Nr9   r)   )r)   r  )r   r  rH   numofwin)r   r  ZTYYYYYYYYYYYYYYYYYYYYYPEr  z%m/%d/%Yz%Y-%m-%dzT00:01:00+00:00zT23:59:00+00:00rI   )r   r   r   
start_dateend_datecreation_datetimeid_zone_channel_idr   z	numofads[]zwindowstart[r   z:00z
windowend[z%Y-%m-%d %H:%M:%Sz%H:%M:%Sz%H%M%S00)id_playlist_idr   
window_endr  )id_window_idr  r  r	   zad[z][r"   )id_avail_idid_adspot_idr  r  z/schedules/z"DAIManagementApp/new_playlist.htmlr>   rc   )r   
Useraccessr-   r:   r+   r9   rj   r   rr   r@  r	   r   r=   r   r   r   r   r  r   r   r  r   r  r   r   r   r  ftp_channel_namerG   r   r.   )r/   
useraccessr  channeldatar  daydatenumber_of_windowsr  r  r  r   Playlisttrafficr   number_of_adsr   r  FMTr  r   r   jr   AdspotsInAvxmlfilenamer/  rc   r0   r0   r1   new_playlist_old7  sZ    



&$
r  c             C   s6   | j d}tjj|ddd }t| dd|iS )Nr   rI   )r   ry  r   z&DAIManagementApp/dropdown_adspots.htmlr   )GETr:   r   r-   r.   r  rl   r   )r/   r  r   r0   r0   r1   load_adspotsm  s    r  c             C   s*   | j d}tjj|d}t| dd|iS )Nr   )r   z%DAIManagementApp/dropdown_region.htmlregions)r  r:   r   r-   r.   r   )r/   r  r  r0   r0   r1   load_regionsu  s    r  c             C   s  | j dkrt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 d S )Nr9   )rB   r)   )r)   )r   )r   r   T)rD   )Zid_window__inr   )Zid_avail__in)r   rc   rG   dr  r   avz&DAIManagementApp/edit_playlist_id.htmlr   )r   z-id_playlistra   z#DAIManagementApp/edit_playlist.html)r   r   r-   r:   rj   r.   r+   r   r   r   r  r   ro   r  r   r   r=   rm   )r/   r   r   rc   rG   r  r  winr  r  r   r>   r  ra   r0   r0   r1   edit_playlist_old{  s"    
r  c       -      C   s  | j dkrl| jd}| jd}|r8t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}|r0t|dkr0xVtt|D ]D}| jdt| d r2| 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 }|  xbtt|D ]P}| 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%}|  xtt|D ]}| jd&t| d" t| d" t| d r| jd&t| d" t| d" t| d }|d$7 }t|j|t|d$ |d'}|  qW q W q2W t|}|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}xPt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 }|  x\tt|D ]J}| 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%}|  xtt|D ]}| jd&t| d" t| d" t| d r| jd&t| d" t| d" t| d }|d$7 }t|j|t|d$ |d'}|  qW qW qW |j}+t d:S n(tjj!| jd d},t"| d;d<d=|,iiS d S )>Nr9   	apply_btn	draft_btnr)   )r)   r  )r   r  r*   r   )r   rG   r  r   r  r   rH   z%m/%d/%Yz%Y-%m-%drI   zT00:01:00+00:00zT23:59:00+00:00r   )	r   r   r   r  r  r  r  r   r   r   znumofavails[r  zwindowstart[r   z:00z
windowend[z%Y-%m-%d %H:%M:%Sz%H:%M:%Sz%H%M%S00)r  r   r  r  zavailstart[z][z	numofads[r"   )r  r  r  r	   zad[)r  r  r  r  ry  z172.16.205.1i`  
SanoaMediar   z2M/schedules/REG1_NLREGNLr  z2M/schedules/REG1_FRz)Failed to generate playlist file. Error: messagezAdd PlayListzAdmin Add PlayList  id: )r  r   r[  view_playlist)r   new_playlist)r   r  draft_playlistz"DAIManagementApp/new_playlist.htmlr>   rc   )#r   r9   r:   r  r-   r+   rj   stripsplitr   r	   r   r=   r   r   r   r   r  r   r   r  r   r  r   r   r   generateScheduler   FTPConnectorupload_filerr   r}   r   r.   r   )-r/   r  r  r  r  r  r  rG   zonenetworkr  r  r   r  r  r  record_insertedr   numofavailsr   r  r  r  r   r  av_startr  r   kr   r  xml_playlist_resftp_hostftp_portftp_usernameftp_passwordr<  r=  r0  r  r  r  r   last_id_playlistrc   r0   r0   r1   r    s   

&$$
20



 
&$$20
r  c       )      C   s(  | j dkr$| jd}| jd}| jd}| jd}| jd}|rt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}x|D ]x}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}x|D ]}i }||d< |d }|j|d< tj|jd}|d}||d< g |d< |d | tjj|jd}||d< d}d|d < xL|D ]D}i }||d!< |d }||d < |j|d"< |j|d#< |d | qW qW |d | qW t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	|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}x|D ]z}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}x|D ]}i }||d< |d }|j|d< tj|jd}|d}||d< g |d< |d | tjj|jd}||d< d}d|d < xL|D ]D}i }||d!< |d }||d < |j|d"< |j|d#< |d | qzW qW |d | q\W |d |||	|
|d d'}#t#| d)|#S |rt$d*|dS t$d+|dS n 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 }&x|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< qW |%|&d8}t#| d9|S d S ):Nr9   edit_btnview_btninsertion_results_btnduplicate_btnr  )rB   r)   )r)   )r   rG   r  r   )r   r   T)rD   r  r   number_of_winsr   r   r"   z%Y-%m-%d %H:%M:%Sz%H:%Mr  avails_in_winnum_of_avails)r   r  r   r  adspots_in_avail)r   num_of_adspotsr  id_adsinavailr   zEdit PlayListzAdmin Edit PlayList  id: )r  r   r[  )r>   r   rc   rG   r  r  z&DAIManagementApp/edit_playlist_id.htmlz+DAIManagementApp/duplicate_playlist_id.htmlr  insertion_results   )r   r   r   )r   r   Zstart_date__gtez-broadcastdate)id_playlist__inr   r   r   r*   r  )ra   detailsz#DAIManagementApp/edit_playlist.html)(r   r9   r:   r   r-   rj   r.   r+   r   r   rG   r  r   r   r  r   ro   rm   r   r	   r   r   r   r  r  r   r  rv   r   r  r   r   r=   r}   r  r   r   r
   r  r   ))r/   r   r  r  r  r  r  r   rc   rG   r  r  r>   r   r  r  
window_dicwindow_start_formatedwindow_start_formated_2window_end_formatedwindow_end_formated_2r  r  r   	avail_dicavail_start_formatedavail_start_formated2r   r  r   
adspot_dicr   r  r  r  data_playlistr  ra   r  detailbroadcastDate_verifr0   r0   r1   edit_playlist[  s   




















r  c       &      C   sh  | j dkr| jd}| jd}| jd}| jd}| jd}|rt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}x|D ]x}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}x|D ]}i }||d< |d }|j|d< tj|jd}|d}||d< g |d< |d | tjj|jd}||d< d}d|d < xL|D ]D}i }||d!< |d }||d < |j|d"< |j|d#< |d | qW qrW |d | qW tj }d$} d%t|j }!t| ||!d&}"|"  |d |||	|
|d d'}#t | d(|#S |rt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}x|D ]z}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}x|D ]}i }||d< |d }|j|d< tj|jd}|d}||d< g |d< |d | tjj|jd}||d< d}d|d < xL|D ]D}i }||d!< |d }||d < |j|d"< |j|d#< |d | qVW qW |d | q8W |d |||	|
|d d'}#t | d)|#S |rt!d*|dS t!d+|dS nft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 d S )4Nr9   r  r  r  r  r  )rB   r)   )r)   )r   )r   r   T)rD   r  r   r  r   r   r"   z%Y-%m-%d %H:%M:%Sz%H:%Mr  r  r  )r   r  r   r  r  )r   r  r  r  r   zEdit PlayListzAdmin Edit PlayList  id: )r  r   r[  )r>   r   rc   rG   r  r  z&DAIManagementApp/edit_playlist_id.htmlz+DAIManagementApp/duplicate_playlist_id.htmlr  r  r  )r   r   r   )r   r   Zstart_date__ltez-broadcastdatera   z)DAIManagementApp/edit_older_playlist.html)#r   r9   r:   r   r-   rj   r.   r+   r   r   r   r  r   ro   rm   r   r	   r   r   r   r  r  r   r  rv   r   r  r   r   r=   r}   r  r   r   r
   )&r/   r   r  r  r  r  r  r   rc   rG   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  ra   r0   r0   r1   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}|
r|
jnd}|d | d |	 d }|dkrtd d| d}tj|dd d}d}d}d}d| }| d| }t||||}||| t| rt|}|d}|j d}g }x|D ]}| d}| d} | d}!|!
d d!}"|"
d"d}#| d#}$| d$}%| d%}&yptjj||j||| d&}'|'r<|#|'_!|$|'_"|%|'_#|&|'_$||'_|'%  n&t||j||| |#|$|%|&|d'
}(|(%  W nF tj&k
r   td( t||j||| |#|$|%|&|d'
}(|(%  Y nX qW tjj|jd	'd)})x|)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xB|0D ]8}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< x|6D ]}8i }9|7|9d7< |7d }7|7|3d6< |8j3|9d8< |8j4|9d9< td: y8tjj||8j5t6|j7d;8d<}
|
j#|9d$< |
j!|9d< W n  tj&k
r   td( Y n
X td= |3d4 1|9 qW qlW |d 1|+ d}qW |d |||||d |d>}:t9| d?|:S )@N)rB   )r   )r   r   r*   r"   2005)r   r  r   )r   r   T)rD   r  r   r  falsez.verzWait I'm in ftpzfiles/results/z/verifs)r  z172.16.205.1i`  r  z
2M/verifs/r   r   rC  r   r   rD  r   r   z+02:00rE  rF  r   )r   r  r   r   r   )
r   r  r   r   r   rD  rE  rS   r   rG  oupsr   r   z%Y-%m-%d %H:%M:%Sz%H:%Mr  r  r  )r   r  r   r  r  )r   r  r  r  r   zWait I'm in database)r   r   revision__lteid_verifzfile not ready yet)r>   r   rc   rG   r  r  r  z'DAIManagementApp/insertion_results.html):r   r-   r:   rj   r   r   r   r  r   r   r   r  verifs_numberrx   r.   lastr=   r  r   ro   rG  rr   r  r  r  Zdownload_filer   r  rH  rI  rL  rJ  rK  rD  rE  rS   r   r  r;   rm   r   r	   r   r   r   r  r  r   r  rv   r   r  r   r  r   r   latestr   );r/   r   r   rc   rG   r  r   r   r  r  rd   r  r  r  r>   r   r  rC  filename_in_ftppath_in_appr  r  r  r  Zremote_pathZ
local_pathr0  rO  rP  rQ  rR  r   r   rD  rS  rT  rE  rF  r   verif_to_updatenew_ad_verifr  r  r  r  r  r  r  r  r  r   r   r  r  r   r  r   r  r  r0   r0   r1   r  G
  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}|r|j}nd}|dkrtd |d | d }d| d }d| d }tj|st| tjjdd}t| t|j|j|j||| t|d |  rt |d | }|!d}|j"#d}g }x|D ]}|#d}|#d}|#d}|dd}|dd}|#d}|#d }|#d!}yltjj|||||d"} | r0|| _$|| _%|| _&|| _'|| _| (  n$t||||||||||d#
}!|!(  W nD tj)k
r   td$ t||||||||||d#
}!|!(  Y nX qW tjj|jd*d%}"x|"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}*xB|)D ]8}+i },|*|,d-< |*d }*|+j2|,d.< t,j,-|+j3d'}-|-/d(}.|.|,d/< g |,d0< |$d* 4|, t5jj|+j2d1}/|*|$d+< d}0d|,d2< x|/D ]}1i }2|0|2d3< |0d }0|0|,d2< |1j6|2d4< |1j7|2d5< td6 y8tjj|
|1j8t9|j:d7;d8}|j&|2d < |j$|2d< W n  tj)k
rv   td$ Y n
X td9 |,d0 4|2 qW q\W |d 4|$ d}qW |d ||||	|d |d:}3d S );N)rB   )r   )r   r   r*   )r   r  r   )r   r   T)rD   r  r   r  r
  zWait I'm in ftpz
-00001.verr   z/verifs/zfiles/results/z/verifsr"   )r)   r   rC  r   r   rD  r   r   z+02:00rE  rF  r   )r   r  r   r   r   )
r   r  r   r   r   rD  rE  rS   r   rG  r  r   r   z%Y-%m-%d %H:%M:%Sz%H:%Mr  r  r  )r   r  r   r  r  )r   r  r  r  r   zWait I'm in database)r   r   r  r  zfile not ready yet)r>   r   rc   rG   r  r  r  )<r   r-   r:   rj   r   r   r   r  r   r  r   r   rx   r.   r  r=   r  r   ro   rG  rr   r  r   r  r  r  r4  
ftp_serverftp_userr  r   rH  rI  rL  rJ  rK  rD  rE  rS   r   r  r;   rm   r   r	   r   r   r   r  r  r   r  rv   r   r  r   r  r   r   r  )4r   r   rc   rG   r  r   r  r   rd   r  r  r  r>   r   r  rC  r  path_in_ftpr  r  rO  rP  rQ  rR  r   r   rD  rS  rT  rE  rF  r   r  r  r  r  r  r  r  r  r  r  r  r   r   r  r  r   r  r   r  r  r0   r0   r1   insertion_results_getter  s    

















 

r  c       8      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
}
| 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}xTtt|	D ]B}| jdt	| d rr| 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}"|"  x`tt|D ]N}#| 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"}&|&  xtt|%D ]}'| 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W q`W qrW |j}*t|}+t|+ |+d% r>d&},d'}-d(}.d(}/|+d) }0d }1|jj d*kr
d+}1n|jj d,krd-}1|1rPt!|,|-|.|/}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}xPt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}"|"  x\tt|D ]J}#| 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"}&|&  xtt|%D ]}'| jd#t	| d  t	|# d  t	|' d r4| jd#t	| d  t	|# d  t	|' d }(|d7 }t|&j|(t	|'d |d$})|)  q4W qW qW |j}*t$d:S d S );Nr9   r  r  r)   )r)   r  )r   rH   r  r   r   z%Y-%m-%dzT00:01:00+00:00zT23:59:00+00:00)rB   )r   r   r"   r   )	r   r   r   r  r  r  r  r   r   r   znumofavails[r  zwindowstart[r   z:00zwinend_[z%Y-%m-%d %H:%M:%Sz%H:%M:%Sz%H%M%S00)r  r   r  r  zavailstart[z][z	numofads[)r  r  r  r	   zad[)r  r  r  r  ry  z172.16.205.1i`  r  r   r  z2M/schedules/REG1_NLr  z2M/schedules/REG1_FRz)Failed to generate playlist file. Error: r  zUpdate PlayListzAdmin Update PlayList id: )r  r   r[  r  )r   r  )r   r  )r   draft_version__maxrI   r  )(r   r9   r:   r+   r  r-   rj   r	   r   r=   r   r   r   r   r   r   r  r  r.   r   countr   r  r   r  r   r  r   r   r   r  rr   r   r  r  r}   r   r   	aggregater   )8r/   r   r  r  r)   r  r  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_versionr  r  r  var_testr   r  r   r  r  r  r   r  r  r  r   r  r   r  r  r  r  r  r  r  r<  r=  r0  r  r  r  max_version_draftnew_version_draftr0   r0   r1   update_playlist~  s6   



&$$
20


 
&$$20r!  c             C   s2  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}
x|
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
}x|D ]}i }||d< |d }|j|d< tj|jd}|d}||d< g |d< |d | tjj|jd}||d< d
}d
|d< xL|D ]D}i }||d< |d }||d< |j|d< |j|d< |d | qW q"W |d	 | d
}	qW |d	 |||||d |	d}t| d|S ) N)rB   r)   )r)   )r   )r   r   T)rD   r  r   r  r   r"   z%Y-%m-%d %H:%M:%Sz%H:%Mr   r  r  r  )r   r  r   r  r  )r   r  r  r  r   )r>   r   rc   rG   r  r  r  z&DAIManagementApp/view_playlist_id.html)r   r-   r:   rj   r.   r+   r   r   r   r  r   ro   r   r	   r   r   r   r  r  r   r  rv   r   r  r   r   )r/   r   r   rc   rG   r  r  r>   r   r  r  r  r  r  r  r  r  r  r  r   r   r  r  r   r  r   r  r  r0   r0   r1   r  O  sj    








r  c             C   s  | j dkr4t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}	x|	D ]x}
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}x|D ]}i }||d< |d }|j|d< tj|jd}|d}||d< g |d< |d | tjj|jd}||d< d}d|d< xL|D ]D}i }||d< |d }||d< |j|d< |j|d< |d | qW q*W |d
 | qW |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 d S )&Nr9   )rB   r)   )r)   )r   )r   r   T)rD   r  r   r  r   r"   z%Y-%m-%d %H:%M:%Sz%H:%Mr   r  r  r  )r   r  r   r  r  )r   r  r  r  r   )r>   r   rc   rG   r  r  z+DAIManagementApp/duplicate_playlist_id.htmlr   )r   z-id_playlistra   z(DAIManagementApp/duplicate_playlist.html)r   r   r-   r:   rj   r.   r+   r   r   r   r  r   ro   r   r	   r   r   r   r  r  r   r  rv   r   r  r   r   r=   rm   )r/   r   r   rc   rG   r  r  r>   r   r  r  r  r  r  r  r  r  r  r   r   r  r  r   r  r   r  r  r  ra   r0   r0   r1   duplicate_playlist  sr    








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}| }|rt|d
r|jd k	rt|jnd}t||d t
|||||	jddd	}|  d}d}d}xTtt|D ]B}| jdt
| d rJ| 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}|  x`tt|D ]N}| jdt
| d  t
| d r8| jdt
| d  t
| d }|d | d }| jd!t
| d  t
| d }t|j|t
|d |d"}|  xtt|D ]} | 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}qW q8W qJW |j}#t|||	j|d  }$|jd% |	j }%t d&|#d'S d S )(Nr9   r)   )r  r  )r   r  rH   r  )r   r  r   z%m/%d/%Yz%Y-%m-%dzT00:01:00+00:00zT23:59:00+00:00)r   z-creation_datetimer"   r   )	r   r   r   r  r  r  r  r   r   r   znumofavails[r  zwindowstart[r   z:00z
windowend[z%Y-%m-%d %H:%M:%Sz%H:%M:%Sz%H%M%S00)r  r   r  r  zavailstart[z][z	numofads[)r  r  r  r	   zad[)r  r  r  r  z/schedules/r  )r   )!r   r+   r  r-   r:   r9   rj   r   r	   r   r=   r   r   r   r.   rm   firsthasattrr   r   r   r  r   r  r   r  r   r   r   r  r  rG   r   )&r/   r   r)   r  r  r  r  r  r  r  r   r  r  r   Zfiltered_playlistsZlast_playlistZlast_playlist_versionr  r  r  r  r   r  r   r  r  r  r   r  r  r  r   r  r   r  r  r  r/  r0   r0   r1   duplicate_playlist_update  s~    


&
&$$20r%  c          	   C   s  t | d}|  }d}x`|D ]V}|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rd|	dd   }	|dkr|}
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"W |  t| |
S )Nr   r   |r"   infor   r   ZDetected,r*   r   r   r  Z2400r   ()   z Date : )rB   )r   r	   rH   r	  rQ   r  )r   read
splitlinesr  r  r   	date_timer=   rr   rj   r-   r:   Adbreak_historyrM  r  )	file_pathr   r   r  linelmsgr   rH   r	  Z	first_dayr	   r'  r   chidr  logr0   r0   r1   insert_logs  s:    

r8  c             C   s   t | j | jdkrt t| jd  dt| jd  }t| jd }tj|sft d t| nt d t	| jd || t
|d | }tj tdd	 }tjj|d
}t| dd|iS )Nr9   Z	logs_filez
logs/user_r)   zpath not  existzpath exist r      )r   )Zdatetime__gtezDAIManagementApp/add_logs.htmllogs)rr   r   r=   r  r+   r  r   r  r  r   r8  r	   r   r
   AdbreakHistoryr-   r.   r   )r/   r   r   rH   r   r:  r0   r0   r1   r:  M  s    

r:  c             C   s.   |  d}|d d |d  d |d  }|S )Nr   r   r   r"   )r  )r	   r   r0   r0   r1   r/  e  s    
 r/  c             C   s   d}t j }| jdkrt| jd}| jd}td| d |  t jj|d}tjj||d}d	}|||d
}n
||d}t	| dd|iS )Nr:   r9   r  rH   z ID : z DAY : )rB   )r   rH   post)r:  rc   r   )rc   r   zDAIManagementApp/get_logs.htmlr>   )
rj   r-   rl   r   r9   r:   rr   r0  r.   r   )r/   r   Zchanelsr5  rH   r   r:  r>   r0   r0   r1   get_logsj  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rd
}| jd}	g }
tj }x |D ]}|
| j|j qW t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}x:|
D ]2}|d kr`tjj|d}t||d}|  q`W t
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 )Nr9   campaign_namer   r  rU   re  rf  volumedeliveryr   cpm)rB   )	r   id_advertiserr   rU   re  rf  r?  r@  rA  zAdd CampaignzAdmin Add Campaign  id: )r  r   r[  )id_timer  z%DAIManagementApp/viwes_campaigns.htmlr\   r)   )r)   rI   )ry  )r   )r   r   r	   z"DAIManagementApp/add_campaign.htmlr>   )r   r9   r:   DayTimer-   rl   rv   r   r   r   ry   r   rO   r  r	   r   r=   r  r}   rr   	Placementr   r   r.   r+   rj   rs   )r/   r   r   r  rU   r  r  r?  r@  rA  	placementr	  r   r   r  rk  r	   r   r  r  r  pr   rc   r   r>   r0   r0   r1   add_campaign  sZ    







rH  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rd}nL|t|d}|t|	d}tt|| j t	|t	t|| j }t
|tjj|dtjj|d|||||	t|
t	|t|t|t	||||t	|| jd dtjd}|  tjj| jd d}|dkrzxf|D ]^}| jdt|j }|d krx6|D ].}tjjt	|d}t|||d}|  qW qW d}dt|j }| }t|||d}|  t
jj| jd d d!} td"S | jd#}!|!d krx2|!D ]*}tjjd$d%}t|||d&}|  qW t
jj| jd d d!} td"S tjj| jd dd'}"tjj|"d(}#tjj| jd d}tjj| jd djd)d*d }$t jj|d+}%tjj!d$d%}|#|"|%|||$d,}&t"| d-d.|&iS )/Nr   )r	   r9   r>  r   r   product_nameid_category
id_countryre  rf  budgetr?  rA  broadcasts_daygeneral_rotation
media_typeprogram_categoriesrU   r*   r   z%Y-%m-%d)rB   r)   )r   r   r   productr  countryre  rf  rL  r?  r@  rA  rM  rO  program_categoryrU   rN  r  r   ry  )r)   check_placement_)rC  r  r   zAdd CampaignzAdmin Add Campaign  id: )r  r   r[  )r  z-id_campaignr  channels_checked   )rC  )rC  r  r   )r)   r   )id_agency__inrI   )ry  )r   )r   agencyr   rc   r	   advertisers_for_creativez&DAIManagementApp/add_campaign_new.htmlr>   )#r	   r   r9   r:   getlistr   r=   rr   r   r   ry   r   r-   r   rO   r+   CampaignStatus	Prebookedr  rj   r.   r   rD  rE  r  r   r}   rm   r   r   r  rl   r   excluder   )'r/   r	   r   r   r   rQ  r  rR  r  r  rL  r?  rA  rM  rotationrO  rS  rU   r@  start_date1	end_date1rk  rc   r   channel_placementsplacement_of_channelr	  rF  r  r  r   r  r\   rU  rX  r   rY  r   r>   r0   r0   r1   add_campaign_new  s    






&rc  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   )r  r:   r   r-   rawr   )r/   r  r   r0   r0   r1   load_creativesC  s    re  c             C   s*   | j d}tjj|d}t| dd|iS )NrX  )r   z3DAIManagementApp/dropdown_advertisers_campaign.htmlr   )r  r:   r   r-   r.   r   )r/   Z	agency_idr^   r0   r0   r1   load_advertisersM  s    rf  c             C   s&   t jj| jd dd}t| dd|iS )Nr)   r   )r  r   z.DAIManagementApp/dropdown_agency_campaign.htmlrX  )r   r-   r.   r+   r   )r/   rX  r0   r0   r1   load_agencyS  s    rg  c             C   s2   t jj| jd d}tjj|d}t| dd|iS )Nr)   )r)   )r   z0DAIManagementApp/dropdown_creative_campaign.htmlr   )rj   r-   r.   r+   r   r   )r/   rc   r   r0   r0   r1   load_creativeX  s    rh  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[  ry  zstatic/Advertisers_imgsr   r  r  r   r  z.pngrX  )r   r   )r   r   r[  r  ry  r   r)   )r  r  ry  r	   z3DAIManagementApp/dropdown_advertisers_campaign.html)r9   r:   rr   r	   r   r=   r   r   r-   r   r  r   r+   r   r   )r/   r  r  r  r  r   r   r  r   rX  r  r  r0   r0   r1   send_advertisers^  s$    
ri  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   z.DAIManagementApp/dropdown_agency_campaign.html)	r9   r:   rr   r	   r   r   r+   r  r   )r/   rj  rk  r   rX  r0   r0   r1   send_agencyt  s    
rl  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| st
d|  |dd	}	dt| d | d t| d |	 d }
t|
 t| jd d| |
 t||||||d |
 ||
d}|  t| dS )Nr  r  r  r  r  r   r  r  r   zadspots/user_r)   z/ch_zstatic/ru  Zch_r  r  z.tsr  r   )r   r  r  r  ry  r  r	   r   z.DAIManagementApp/dropdown_agency_campaign.html)r9   r:   r	   r   r=   r   r+   r  r   r  r  rr   r   r  r   r  r   )r/   r  r  r  r  r  r   r  r   Znew_now_infiler   r  r0   r0   r1   send_creative  s$    
, rm  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 )Nr9   )rB   FzDisable CampaignzAdmin Disable Campaign  id: )r  r   r[  r)   )r  z-id_campaignz&DAIManagementApp/disable_campaign.htmlr\   )r   ry   r-   r:   r   r  r	   r   r=   r}   r.   r+   rm   r   )r/   r  rk  r   r  r  r  r\   r0   r0   r1   disable_campaign  s    

rn  c       6   
      sh  ddl m } | jdkrV| jd}| jd}|r8t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rt|d krt|d krt|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rtjj|d|_|	dkr@|	d kr@|	|jkr@|	|_|
dkrf|
d krf|
|jkrf|
|_|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||_|dkrH|d krHt|rHt||jkrHt||_|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rb|d krb||jkrb||_|dkr|d krt|rt||jkrt||_|   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}#xb|D ]Z}$t'jj!|j(|$d')d(}%d)d& t*|%D }%|$j+|$j,|%d*|!|#< |"-|!|#  |#d+7 }#qTW t'jj!|j(d#)d(}%d,d& t*|%D }%t	|! |. }&d-}'d.t| }(t/|'|&|(d/})|)   ||||| |"|%||d0	}*t0| d1d2|*iS |rJt1d3|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 Q R X |/8 &}0|09|. fd=d&|0: D }2W d Q R X |. }&g }3xD|+D ]<}4y(||4jd}5|&|5kr<|3-|4j( W n   Y nX qW t0| d>|+|3|2|1d?S )@Nr   )r	   r9   r  r  )rB   r>  r   r   rI  rJ  rK  re  rf  rL  r?  rA  rM  rN  rO  rP  rU   r*   rI   z%Y-%m-%d)r   )r   r)   )r)   r   )rW  )r)   )r   rV  )rC  )r  r   c             S   s   g | ]}|d  qS )r   r0   )rL   rM   r0   r0   r1   rN     s    z!edit_campaign.<locals>.<listcomp>)r  r   rC  c             S   s   g | ]}|d  qS )r   r0   )rL   rM   r0   r0   r1   rN     s    )rQ   r  rF  r"   c             S   s   g | ]}|d  qS )r   r0   )rL   rM   r0   r0   r1   rN     s    zEdit CampaignzAdmin Edit Campaign  id: )r  r   r[  )	rk  agency_by_brandrc   r	   placement_channelchannels_and_placementrF  agenciesr^   z&DAIManagementApp/edit_campaign_id.htmlr>   view_campaign
load_excel)r  z-id_campaignaM  
        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
    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
    aj  
        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
    )rW   )rX   rY   c                s   g | ]} | qS r0   r0   )rL   rZ   )rX   r0   r1   rN   y  s    c                s   g | ]} | qS r0   r0   )rL   rZ   )rY   r0   r1   rN   ~  s    z#DAIManagementApp/edit_campaign.html)r\   startrh   ri   );r	   r   r9   r:   ry   r-   rZ  r   r=   rr   r   r   r@  r   r   r   r   r   rQ  r  rR  re  rf  rO   rL  r?  rA  rM  rO  rS  rU   r^  r  r.   r+   rj   r   rD  r]  rE  r  ro   rn   rQ   r   rv   r   r}   r   r   rm   r~   rW   r   rX   rY   r   r   r   )6r/   r  r	   r  r  rk  r   r   r   rQ  r  rR  r  r  rL  r?  rA  rM  r^  rO  rS  rU   r@  r_  r`  brands_for_agencyro  rr  r^   rc   r   Z	datetime1rp  rq  tab_channels_and_placementch_nr   rF  r   r  r  r  r>   r\   r   r   r   rW   r   ri   rh   ru  r   r   r0   )rX   rY   r1   r`    s
    ""     .
.
.
.
.
   .

*

 

 

r`  15c             C   sL  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}x`|D ]X}tjj|j|dd}dd t|D }|j|j|d|	|< |
|	|  |d7 }qW tjj|jd	d}dd t|D }t|	 ||||||
|d}t| dd|iS )N)rB   )r   )r   r)   )r)   )r   rV  )rC  )r  r   c             S   s   g | ]}|d  qS )r   r0   )rL   rM   r0   r0   r1   rN     s    z!view_campaign.<locals>.<listcomp>r   )r  r   rC  c             S   s   g | ]}|d  qS )r   r0   )rL   rM   r0   r0   r1   rN     s    )rQ   r  rF  r"   c             S   s   g | ]}|d  qS )r   r0   )rL   rM   r0   r0   r1   rN     s    )rk  ro  rc   r	   rp  rq  rF  z&DAIManagementApp/view_campaign_id.htmlr>   )ry   r-   r:   r   r   r   r   rj   r.   r+   r   rD  r]  rE  r  ro   rn   rQ   r   rv   rr   r   )r/   r  rk  rv  ro  rc   r   r	   rp  rq  rw  rx  r   rF  r>   r0   r0   r1   rs    s8    
rs  c             C   s*   t jj| jd dd}t| dd|iS )Nr)   )r)   z-id_campaignz%DAIManagementApp/views_campaigns.htmlr\   )ry   r-   r.   r+   rm   r   )r/   rk  r0   r0   r1   views_campaigns  s    rz  c             C   sl  | j dkrht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|
|_||_yt||_W n   d|_Y nX t||_|  tj }d}dt| }t|||d}|  tjj| jd d}|dkrtjj|d}|  x|D ]^}| jdt|j }|d krx6|D ].}tjjt|d}t|||d}|  qW qW nh| jd}t | tjj|d}|  |d kr\x2|D ]*}tjjdd}t|||d}|  q.W t!d|dS d S )Nr9   )rB   r>  r   r   r  re  rf  rU   r?  r@  rA  rN     r   zEdite CampaignzAdmin Edit Campaign  id: )r  r   r[  r)   )r)   r   )r  rT  )rC  r  r   rU  rV  )rC  )rC  r  r   rs  )"r   ry   r-   r:   r9   r   r   r   rB  r   rU   re  rf  r   r?  rN  r@  rO   rA  r  r	   r   r=   r}   rj   r.   r+   rE  deleterZ  r   rD  rr   r   )r/   r  rk  r   r   r   r  r  r  rU   r?  r@  rA  r^  r   r  rX  r   r  r  r  rc   rG  r   ra  rb  r	  rF  rU  r0   r0   r1   update_campaign  sp    








r}  c       
      C   s   | j d}| j d}tjj||d}tjj||jd}g }x0|D ](}tj|j	d}|
d}|| qDW d|i}	t|	dd	S )
Nr   rG   )r  r   )r   r   z%Y-%m-%dz%d.%m.%YdatesF)safe)r  r:   r   r-   r   r.   r   r	   r   r   r   rv   r   )
r/   r  Z	region_idr  ra   r~  r   r  r   r>   r0   r0   r1   load_region_dates&  s    

r  c             C   s   | j dkr| 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 )Nr9   rQ   languagegenrer0  rC   r)   )rB   channel_desc)	r)   rQ   r  channel_langchannel_genrer  rC   r  modif_datetimezAdd ChannelszAdmin Add Channels  id: )r  r   r[  z/DAIManagement/edit_channels/z"DAIManagementApp/add_channels.html)r   r9   r:   r	   r   r,   r-   r+   rj   r  r=   r   r}   r   r   )r/   r   langr  r0  rC   creationmodifr.  channel_decsr5  r   r  r  r  r0   r0   r1   add_channels6  s$    

r  c             C   sh   | j dkr0t| 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 )
Nr9   )rB   z&DAIManagementApp/edit_channels_id.htmlr   r)   )r)   z-id_channelz#DAIManagementApp/edit_channels.htmlrc   )
r   rr   rj   r-   r:   r   r,   r+   r.   rm   )r/   r   r   r.  rc   r0   r0   r1   edit_channelsM  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 )Nr9   rQ   r  r  r0  sfr	zone_namer   r  r)   )rB   )r   r)   r   zEdit ChannelzAdmin Edit Channel  id: )r  r   r[  z/DAIManagement/edit_channels/)r   r9   r:   r	   r   r,   r-   r+   rj   r.   rQ   r  r  r  rC   r  r  r  r  r=   r}   r  r   )r/   r   r   r  r  r0  r  r  r  r  r  r.  r   r   r  r  r  r0   r0   r1   update_channelsW  s8    

r  c       	      C   s   | j dkrLt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 )Nr9   )rB   zDelete ChannelzAdmin delete Channel  id: )r  r   r[  r)   )r)   z-id_channelz$DAIManagementApp/delete_channel.htmlrc   )r   rj   r-   r:   r|  r	   r   r=   r}   r  r,   r+   r.   rm   r   )	r/   r   r5  r   r  r  r  r.  rc   r0   r0   r1   delete_channelx  s    

r  c          
   C   s  | j dkrT| jd}| jd}| jd}dt| jd  d | d | }tjd	| spt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 )Nr9   jingle_namer  r  zjingles/user_r)   z	/channel_r   zstatic/ru  
video_filer"   z.md5md5_file)rB   r   )r   r  video_jingler  r  r  ry  	is_deletez
Add JinglezAdmin Add Jingle  id: )r  r   r[  z/DAIManagement/edit_jingle/)r)   z!DAIManagementApp/add_jingles.htmlrc   )r   r9   r:   r=   r+   r  r   r  r  r  r   r  lowerr   r	   r   rj   r-   Jingles	id_jingler}   r  r   r,   r.   r   )r/   r   r   ry  r   filename_video_extfilename_videofilename_md5r   r  r5  jingler  r  r  r.  rc   r0   r0   r1   
add_jingle  s2    "(r  c             C   s   | j dkrTt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 )Nr9   )rB   r)   )r)   )r  rc   z%DAIManagementApp/edit_jingles_id.htmlr>   r   )r   r  z
-id_jinglez"DAIManagementApp/edit_jingles.htmljingles)
r   r  r-   r:   r,   r+   rj   r.   r   rm   )r/   r  r  r.  rc   r>   r  r0   r0   r1   edit_jingle  s    
r  c             C   sR  | j dkrNtjj|d}| jd}| jd}t| | jd}dt| jd  d t| d	 | }y&| jd
 j	
dd  }|| }W n$   ddlm}	 |	|jj	}Y nX |d }
tjd| std|  y*t| jd
 d| | td|j  W nF   dd l}tjd|j rJ|d|j d| d	 |  Y nX y*t| jd d| |
 td|j  W nF   dd l}tjd|j r|d|j d| d	 |
  Y nX ||_tjj|d}||_|dk|_|d	 | |_|d	 |
 |_tj |_|  tj }d}dt| }t |||d}|  t!dS d S )Nr9   )rB   r  r  r  zjingles/user_r)   z	/channel_r   r  ru  r"   r   )r   z.md5zstatic/r  rI   zEdit JinglezAdmin Edit Jingle  id: )r  r   r[  z/DAIManagement/edit_jingle/)"r   r  r-   r:   r9   rr   r=   r+   r  r   r  r  pathlibr   r  r  r   r  r  r   remover  r  r  r  rj   r   ry  r	   r   r  r  r}   r   )r/   r  r  r   r   ry  r   r  r  r   r  r  r5  r   r  r  r  r0   r0   r1   update_jingle  sX    &$$

r  c       
      C   s   | j dkrbt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 )Nr9   )rB   r"   zDelete JinglezAdmin Delete Jingle  id: )r  r   r[  r)   )r)   r   )r   r  z
-id_jinglez$DAIManagementApp/delete_jingles.htmlr  )r   r  r-   r:   r	   r   r  r  r  r=   r}   r,   r+   rj   r.   rm   r   )
r/   r  r  r   r  r  r  r.  rc   r  r0   r0   r1   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 }x4|D ],}t jj|dd	}|t|d
}|	| qRW t| dd|iS )Nr*   )r   z+DAIManagementApp/views_channel_jingles.htmlr  r)   )rB   )r)   r   )r   r  )r   r_  z#DAIManagementApp/views_jingles.htmlr>   )
r  r-   r.   r   r,   r:   r+   rj   rs   rv   )r/   r   r  r.  rc   r5  r   restr0   r0   r1   views_jingles	  s    
r  c             C   sx   | j dkrnt| jd dd}t| d| jd  }tj|sLt	| t
| jd || t|d |  t| dS )	Nr9   Zsfr_filer   r  z	sfr/user_r)   r   z DAIManagementApp/sfr_upload.html)r   r=   r  r   rr   r+   r  r   r  r  r   
insert_sfrr   )r/   r   r   r0   r0   r1   
sfr_upload  s    

r  c             C   sZ   dd l }|| }|j}|d x(t|ddD ]\}}|jd||d q0W ||  dS )Nr   r"   )ru  )rZ   columnvaluezLine added successfully.)openpyxlZload_workbookactiveZinsert_rows	enumeratecellr  )r1  new_datar  workbook	worksheetrW  r>   r0   r0   r1   add_line_to_excel*  s    



r  c             C   s  dd l }dd l}ddlm} ddlm} ddddg}t| | || }dd	 tt	|j
D }||_
|d
ddg }i }	xD| D ]8\}
}t|d dkr|d |	t|d dd < qW |	d d|	d< x|dd  D ]}|dd  d|g }|jd | }x| D ]\}
}d}t|d dkrxn|	d D ]`}|d7 }y0t||	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 rRn n|tdd n||_||_t|ddt|| |_|   W n |k
r   t!d  Y nX qDW qW qW d S )!Nr   )parser)r   ZNewRowZDataZHerec             S   s   g | ]}d t | qS )cols_)r=   )rL   r   r0   r0   r1   rN   O  s    zinsert_sfr.<locals>.<listcomp>   cols_3cols_4)Dateu
   Chaîne(s)zCible(s)KPIz(s)r*   u   Chaînez, r   rV  r"   ZHeureCiblerE   r  )rC   ciblerG   
indicateurr  z%d/%m/%Yz%H:%M:%Sr,  )r   r]  z*This entry already exists in the database.)"repandasdateutilr  r~   r   r  
read_excelr   rs   rZ  headiterrowsr=   r   r  r  ilocrp   r	   r   
isinstancer	  r   combiner   r
   rH   rJ   rO   subrK   r  rr   )r   r  pdr  r   r  dfrZ  r'  r   r   rZ   rW  testr   r2  r   r  r   heureZ
heure_timemidnightZam_3Zcombined_heurer0   r0   r1   r  C  s\    

$0r  c             C   s   dd l }dd l}|| }dd tt|jD }||_|dddg }i }t|}xt| D ]h\}}	t	|	d dkst	|	d dkst	|	d d	kst	|	d d
kr`|	d |t	|	d 
dd < q`W xL|dd  D ]:}
t|
 |dd  d|
g }|jd |
 }x| 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  }||_||_|	|
 |_|  qW qW d S )Nr   c             S   s   g | ]}d t | qS )r  )r=   )rL   r   r0   r0   r1   rN     s    z%insert_sfr_backup.<locals>.<listcomp>   Zcols_1Zcols_2r  u   Régionr  zIndicateur : r  r*   r   
   r   Z
Indicateur)rC   r  rG   r  r   r"   )r  z%d/%m/%Y   )r   )numpyr  r  r   rs   rZ  r  rn   r  r=   r   r  rr   r  rp   r  r  r  r	   r   r   r
   rH   rJ   rK   r  )r   npr  r  rZ  r'  r   tr   rZ   rW  r  r   r  rH   r  r   r  r	  r0   r0   r1   insert_sfr_backup  s<    
@$ 
r  c             C   s   t jjdd}| jdkr| jd}| jd}| jd}| jd}|dkrV||_|dkrht||_|dkrzt||_	|dkr||_
|  t| d	d
|iS )NrI   )r6  r9   r   usersrK   rG   r*   z&DAIManagementApp/edit_impressions.htmlimp)rq   r-   r:   r   r9   	tool_namer   rt   rO   ru   rG   r  r   )r/   r  r   r  rK   rG   r0   r0   r1   edit_impressions$  s     


r  c                s"  ddl m} | jdkrt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 d S )Nr   )rW   r9   r  )rB   r  )r  r   rH   z%m/%d/%Yz%Y-%m-%dz2M Maroc)rC   rG   rH   rJ   rK   rI   c             S   s   g | ]}|d  qS )r   r0   )rL   rM   r0   r0   r1   rN   E  s    zchart_test.<locals>.<listcomp>c                s    g | ]}t t|d    qS )r"   )r   rO   )rL   rM   )rP   r0   r1   rN   F  s    r)   )r)   zDAIManagementApp/pie_chart.html)r[   r>   rH   rc   rc   )r~   rW   r   rj   r-   r:   r9   r   r   r	   r   r   rp   r.   rG   r=   ro   rq   rO   rt   ru   r+   r   )r/   rW   r   r  rG   rH   r   r   rK   r[   r>   rc   r0   )rP   r1   
chart_test8  s"    

 r  c             C   sx  ddl m } tjj| jd dd}dd |D }tj dd d	 }g }x|D ]
}|j	|krXt
|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< ytjj|d |d d}W n0   tjjt
|jd
d |d d}Y nX t| x|D ]J}|d |jkrtjjdd}t|jt|j t|j }||d < qW ndt
|jd!krd"|d< d|d < nBt
|jd#krBd$|d< d|d < d%|d< nd&|d< d|d < d%|d< || qXW t| d'd(|iS ))Nr   )r	   r)   )r)   rQ   c             S   s   g | ]}|d  qS )r   r0   )rL   rM   r0   r0   r1   rN   R  s    zverifs.<locals>.<listcomp>z-airTimei,  r   r   r   r"   )r   r   rH   )r  rG   r  z:00rJ   Z	minute_ssz	#00800000rf   rR   zAired Successfullyry  z	#2c2c8cb3rH   )rH   rJ   r   rI   )rB   nb_wach0008zFailed, Adspot cut1005zNot aired yetz	#c7c7c7b3zFailed, Other Reason.zDAIManagementApp/verifs.htmlr>   )r	   rj   r-   r.   r+   ro   rx   rl   rm   r   r=   rD  r  r   r   r:   r  rG   rS   rp   rr   rC   rq   rO   rK   rt   ru   rv   r   )r/   r	   rc   r   r>   r2  r  rG  rG   Zmin_ssminrJ   Zdonneesr   rK   rP   r0   r0   r1   r   M  sZ    
 
*


r   c       !      C   s  | j dkr| jd}| jd}| jd}| jd}|rpt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}x|D ]x}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}x|D ]}i }||d< |d }|j|d< tj|jd}|d}||d< g |d< |d | tjj|jd}||d< d}d|d< xL|D ]D}i }||d < |d }||d< |j|d!< |j|d"< |d | qW qfW |d | qW |d ||||	|d d#}t| d$|S |rt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}x|D ]z}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}x|D ]}i }||d< |d }|j|d< tj|jd}|d}||d< g |d< |d | tjj|jd}||d< d}d|d< xL|D ]D}i }||d < |d }||d< |j|d!< |j|d"< |d | qW qW |d | q W |d ||||	|d d#}t| d%|S |rtd&|d
S td|d
S nNt| jd }tjj|djd'dd}tjj|d(d)} d*| i}t| d+|S d S ),Nr9   r  r  r  r  )rB   r)   )r)   )r   )r   r   T)rD   r  r   r  r   r   r"   z%Y-%m-%d %H:%M:%Sz%H:%Mr  r  r  )r   r  r   r  r  )r   r  r  r  r   )r>   r   rc   rG   r  r  z&DAIManagementApp/edit_playlist_id.htmlz+DAIManagementApp/duplicate_playlist_id.htmlr  r   )r   z-id_playlistra   z'DAIManagementApp/playlists_results.html)r   r9   r:   r   r-   rj   r.   r+   r   r   r   r  r   ro   rm   r   r	   r   r   r   r  r  r   r  rv   r   r  r   r   r   r=   )!r/   r   r  r  r  r  r   rc   rG   r  r  r>   r   r  r  r  r  r  r  r  r  r  r   r   r  r  r   r  r   r  r  r  ra   r0   r0   r1   playlists_results  s    

















r  c             C   s  | j dkr4t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}	x|	D ]x}
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}x|D ]}i }||d< |d }|j|d< tj|jd}|d}||d< g |d< |d | tjj|jd}||d< d}d|d< xL|D ]D}i }||d< |d }||d< |j|d< |j|d< |d | qW q*W |d
 | qW |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 d S )'Nr9   )rB   r)   )r)   )r   )r   r   T)rD   r  r   r  r   r"   z%Y-%m-%d %H:%M:%Sz%H:%Mr   r  r  r  )r   r  r   r  r  )r   r  r  r  r   )r>   r   rc   rG   r  r  z,DAIManagementApp/edit_draft_playlist_id.htmlr   rI   )r   r   z-id_playlistra   z$DAIManagementApp/draft_playlist.html)r   r   r-   r:   rj   r.   r+   r   r   r   r  r   ro   r   r	   r   r   r   r  r  r   r  rv   r   r  r   r   r=   rm   )r/   r   r   rc   rG   r  r  r>   r   r  r  r  r  r  r  r  r  r  r   r   r  r  r   r  r   r  r  r  ra   r0   r0   r1   r    sr    








r  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}xTtt|
D ]B}| jdt
| d rz| 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 }|  x`tt|D ]N}| jd!t
| d" t
| d rh| jd!t
| d" t
| d } |	d |  d } | jd#t
| d" t
| d }!t|j| t
|d$ |d%}"|"  xtt|!D ]}#| 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W qhW qzW |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}xTtt|
D ]B}| jdt
| d rR| 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 }|  x`tt|D ]N}| 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%}"|"  xtt|!D ]}#| 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$}qW q@W qRW |j}&td,S d S )-Nr9   r  r  r)   )r)   r  )r   r  rH   r  )r   r  r   z%Y-%m-%dzT00:01:00+00:00zT23:59:00+00:00)r   Zversion__maxr   r  r   )	r   r   r   r  r  r  r  r   r   r   znumofavails[r  zwindowstart[r   z:00z
windowend[z%Y-%m-%d %H:%M:%Sz%H:%M:%Sz%H%M%S00)r  r   r  r  zavailstart[z][z	numofads[r"   )r  r  r  r	   zad[)r  r  r  r  z/schedules/r  )r   rI   r  )r   r9   r:   r+   r  r-   rj   r   r	   r   r=   r   r   r   r.   r  r   r   r   r  r   r  r   r  r   r   r   r  r  rG   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/  r0   r0   r1   update_draft_playlistN  s    


""(
&$$20


"",
&$$20r  c                s   ddl m} | jdkrt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 d S )Nr   )rW   r9   r  )rB   r  )r  r   z2M Maroc)rC   rG   rH   rJ   rK   rI   c                s   g | ]}t t|  qS r0   )r   rO   )rL   rM   )rP   r0   r1   rN     s    zpredict_sfr.<locals>.<listcomp>r)   )r)   zDAIManagementApp/predict.htmlz
2021-09=19)r[   r>   rH   rc   rc   )r~   rW   r   rj   r-   r:   r9   r   r   rp   r.   rG   ro   rq   predictrO   rt   ru   r+   r   )r/   rW   r   r  rG   r   rK   r[   Zpredict_valr>   rc   r0   )rP   r1   predict_sfr  s    
r  c             C   s  dd l }dd l}dd l}dd l}ddlm} |j| dddgd}g }x2|d D ]&}||j|j 	t
|d   qNW |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 }x<tdt|D ]*}td|	|| t|| d}|  qpW ||fS )Nr   )ARIMADayMinutePurcent)rZ  z%Y-%m-%dr"   ZDay_Week)PolynomialFeatures)make_pipeline)LinearRegression   r   )subsetr  )r   z2M Maroc)rC   rH   rJ   rK   ) r	   r  r  calendarZstatsmodels.tsa.arima_modelr  	DataFramerv   day_namer   r=   weekdayr   locr   rs   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   rH   day_weekZdf_finalr  r  r  ZdegreeZpolyregXyr  labelr2  r0   r0   r1   r    s<    &

r  c                s  dd l }dd l}dd l}tj d }dd |D }x|D ]}tjj|ddddd	}|j	|d
dddgd}g }x2|d D ]&}	|
|j|jt|	d   qW ||d< dd |d D }
|
|d< dd |d D }g }xh|D ]`}	t|	dd  t|	dd  d  }tt|dkr4t|d }|
|	d d t|  qW ||d< |jdd}|j|  }|j|d |k }t|d  t } fdd|D }dd |D }||jdd7 }x|D ]
}	|	d dkr|j|d |	d k }t|d t|d  }t|d}tdt|	d  }xtt|	d D ]}|	d dd  }t|| t| }t|dkrd| }|	d dd d | }tjj|d }td|j|t|d!d ||d"}|  qFW qW t| d#S W d S )$Nr   r   c             S   s   g | ]}|d  qS )r   r0   )rL   r   r0   r0   r1   rN     s    z'predict_adbreak_old.<locals>.<listcomp>)r   rQ   rH   r	  r  Channelr  TimeDuration)rZ  z%Y-%m-%dr  c             S   s   g | ]}t |qS r0   )rO   )rL   r   r0   r0   r1   rN     s    c             S   s   g | ]}|d d qS )Nr0   )rL   rM   r0   r0   r1   rN     s    r,  r  r"   r   z
2022-05-26c                s   g | ]}|  |d qS ))r	  r  )r  )rL   r   )all_timer0   r1   rN   -  s    c             S   s"   g | ]}|d  |d d dqS )r	  r  r,  )r	  factr0   )rL   r   r0   r0   r1   rN   .  s    r  )r   r  g333333?r   r  )rB   r   )r   rQ   r	   rH   r	  r  zDAIManagementApp/index.html)r  r  r	   r;  r-   rl   ro   r  r.   r  rv   r  r   r=   r  r   rs   r  rn   r   r
   sumr   r   r  rj   r:   Adbreak_predictrQ   r  r   )r/   r  r  r	   rc   r   adbreakr  r  r   durr	  r  mrH   r  df2time_adr  r  df3mean	intervaler  rJ   time_r  adbreak_predictr0   )r  r1   predict_adbreak_old  s\    &
$
$r  c                s:  dd l }dd l}dd l}tj d }dd |D }x|D ]}tjj|ddddd	}|j	|d
dddgd}g }x2|d D ]&}	|
|j|jt|	d   qW ||d< dd |d D }
|
|d< dd |d D }g }xh|D ]`}	t|	dd  t|	dd  d  }tt|dkr4t|d }|
|	d d t|  qW ||d< dd l}| }x|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 }x|D ]
}	|	d dkr|j|d |	d k }t|d t|d  }t|d }tdt|	d  }xtt|	d D ]}|	d dd  }t|| t| }t|dkrd| }|	d d!d d! | }tjj|d"}td|j|t|d#d ||d$}|  qW qW qzW t | d%S W d S )&Nr   r   c             S   s   g | ]}|d  qS )r   r0   )rL   r   r0   r0   r1   rN   K  s    z#predict_adbreak.<locals>.<listcomp>)r   rQ   rH   r	  r  r  r  r  r  )rZ  z%Y-%m-%dr  c             S   s   g | ]}t |qS r0   )rO   )rL   r   r0   r0   r1   rN   S  s    c             S   s   g | ]}|d d qS )Nr  r0   )rL   rM   r0   r0   r1   rN   U  s    r,  r  r"   r   i  r  z	2022-06-0c                s   g | ]}|  |d qS ))r	  r  )r  )rL   r   )r  r0   r1   rN   n  s    c             S   s"   g | ]}|d  |d d dqS )r	  r  r,  )r	  r  r0   )rL   r   r0   r0   r1   rN   o  s    r  )r   r  g333333?r   r  )rB   r   )r   rQ   r	   rH   r	  r  zDAIManagementApp/index.html)!r  r  r	   r;  r-   rl   ro   r  r.   r  rv   r  r   r=   r  r   rs   Calendaritermonthdaysr  rn   r   r
   r  r   r   r  rj   r:   r  rQ   r  r   )r/   r  r  r	   rc   r   r  r  r  r   r  r	  r  r   calday1rH   r  r  r  r  r  r  r  r  r  rJ   r  r  r  r0   )r  r1   predict_adbreakE  sh    &
$

$r  c                s  dd l }dd l}dd l}tj d }dd |D }xf|D ]\}tjj|ddddd	}|j	|d
dddgd}g }x2|d D ]&}	|
|j|jt|	d   qW ||d< dd |d D }
|
|d< dd |d D }g }xh|D ]`}	t|	dd  t|	dd  d  }tt|dkr4t|d }|
|	d d t|  qW ||d< tt|  |j| d} |j|   }|j|d |k }t|d  t } fdd|D }dd |D }| |jdd7 } x|D ]}	|	d }|	d dkr|	d dkr|j|d |	d k }tt|d t|d  d}td}xtdD ]~}|	d dd  }t|| t| }|	d d d | }t|d kr|d }tdd!| t| d"d ||d#}|  qPW |	d d$kr|	d d%kr|j|d |	d k }tt|d t|d  d}td&}xtd'D ]~}|	d dd  }t|| t| }|	d d d | }t|d kr|d }tdd!| t| d"d ||d#}|  q4W |	d d(kr|	d d)kr|j|d |	d k }tt|d t|d  d}td*}xtdD ]~}|	d dd  }t|| t| }|	d d d | }t|d krj|d }tdd!| t| d"d ||d#}|  qW qW qBW d S )+Nr   r   c             S   s   g | ]}|d  qS )r   r0   )rL   r   r0   r0   r1   rN     s    z%predict_adbreak99.<locals>.<listcomp>)r   rQ   rH   r	  r  r  r  r  r  )rZ  z%Y-%m-%dr  c             S   s   g | ]}t |qS r0   )rO   )rL   r   r0   r0   r1   rN     s    c             S   s   g | ]}|d d qS )Nr  r0   )rL   rM   r0   r0   r1   rN     s    r,  r  r"   r   c                s   g | ]}|  |d qS ))r	  r  )r  )rL   r   )r  r0   r1   rN     s    c             S   s"   g | ]}|d  |d d dqS )r	  r  r,  )r	  r  r0   )rL   r   r0   r0   r1   rN     s    r  )r   r  g333333?g      ?z.2fg      >@r   r  r   )r   rQ   r	   rH   r	  r  g?g      @g      .@r   g@g      @g      $@)r  r  r	   r;  r-   rl   ro   r  r.   r  rv   r  r   r=   r  r   rs   rr   r@  r  rn   r   r
   formatr  r   ZAdbreaPredictr  r  )rH   r  r  r	   rc   r   r  r  r  r   r  r	  r  r   r  r  r  r  r  Zifactr  r  r  r  rJ   r  r  r0   )r  r1   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 )Nr9   rj  rk  r)   r   )r  r   r[  r	   r   zAdd  AgencyzAdmin Add  Agency  id: )r  r   r[  z DAIManagementApp/add_agency.html)r   r9   r:   r	   r   r   r+   r  r=   r   r}   r   )r/   rj  rk  r   rX  r  r  r  r0   r0   r1   
add_agency  s    


r  c             C   sV   | j dkr0tjj|d}d|i}t| dd|iS tjj| jd dd}t| d	d|iS )
Nr9   )rB   rX  z$DAIManagementApp/edit_agency_id.htmlr>   r)   r   )r)   r   z!DAIManagementApp/edit_agency.html)r   r   r-   r:   r   r.   r+   )r/   r   rX  r>   r0   r0   r1   edit_agency  s    
r  c             C   s   | j dkrt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 )Nr9   )rB   rj  rk  zEdit  AgencyzAdmin Edit  Agency  id: )r  r   r[  r)   r   )r)   r   z!DAIManagementApp/edit_agency.htmlrX  )r   r   r-   r:   r9   r   r[  r  r=   r   r}   r   r.   r+   r   )r/   r   rX  r   r[  r  r  r  r0   r0   r1   update_agency  s    
r  c             C   s   | j dkrdt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 )Nr9   )rB   r"   zDelete   AgencyzAdmin Delete  Agency  id: )r  r   r[  r)   r   )r)   r   z#DAIManagementApp/delete_agency.htmlrX  )r   r   r-   r:   rr   r   r	   r   r  r=   r   r}   r.   r+   r   )r/   r   rX  r  r  r  r0   r0   r1   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)r  r:   r+   r   )r/   r  Zbody_class_themer0   r0   r1   r  9  s    
r  c               C   s2   t  jdt xt   td qW d S )Nz00:00r"   )	scheduleZeveryrH   atdor  Zrun_pendingr	  sleepr0   r0   r0   r1   func_predictD  s    r  c          	      s\  dd l }dd l}dd l}tjj|d}|j }d| jd  }tj	
|sTt| |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(|dIdJdKgdL}dMd4 |dJ D }dNd4 |dIdJg j)D }||dI< ||dJ< dOd4 |dK D }dPd4 |dK D }||dQ< ||dR< |j*dKdSddT dUd4 |dR D |dR< g }x<|j)D ]2}|+t,jj|d |d |d |dV dWj- q&W dXd4 |D }||dY< t.jjdZd  fd[d4|dY D }||dY< d}x|j)D ]|}|
d| d>|d  |
d| d|d  |
d| d|d  |
d| d|dV  |
d| d|d\  |d7 }qW |
ddd]| |
ddt/t0| |	1  |S )^Nr   )rB   zexcel/user_r)   r   z%d_%m_%Yz.xlsxzAapplication/vnd.openxmlformats-officedocument.spreadsheetml.sheet)content_typezattachment; filename=zContent-DispositionZ	in_memoryTr"   z#acbbfezAdtlas Reporting | z cash [ z to z ] r  INFOr  zCampaign Namer   	   r   r  Z
Advertiser   ZCreativerV  ZPERIOD   z
Start dater9  zEnd date   ZBOOKED   ZVolume   ZPacing   ZCPM   Z	PLACEMENT   zGeneral Retation)r  r   c             S   s   g | ]}|d  qS )r   r0   )rL   rM   r0   r0   r1   rN     s    zgenExcel.<locals>.<listcomp>)r   rQ   rC   c             S   s   g | ]}|d  qS )r   r0   )rL   r   r0   r0   r1   rN     s    c             S   s   g | ]}|d  qS )r"   r0   )rL   r   r0   r0   r1   rN     s    r  rj   'r*   r  ZCHANNELZREGIONDAYMINUTEZIMPRESSIONSrR   )r   rS   r   r  rD  r  Regionr  )rZ  c             S   s   g | ]}t jj|d jqS ))r  )r   r-   r:   rG   )rL   r   r0   r0   r1   rN     s    c             S   s    g | ]}t jj|d  djqS )r   )rQ   )rj   r-   r:   rC   )rL   r   r0   r0   r1   rN     s    c             S   s   g | ]}| d d qS )r   r   )r  )rL   r   r0   r0   r1   rN     s    c             S   s   g | ]}| d d qS )r   r"   )r  )rL   r   r0   r0   r1   rN     s    r  r  rZ  )Zaxisinplacec             S   s   g | ]}|d d d qS )Nr)  r0   )rL   r   r0   r0   r1   rN     s    r,  )rC   rG   rH   rJ   c             S   s   g | ]}t |qS r0   )rO   )rL   r   r0   r0   r1   rN     s    r  rI   c                s(   g | ] }t |t  j t  j qS r0   )rO   rt   ru   )rL   r   )r  r0   r1   rN     s    r   zTOTAL IMPRESSIONS)2
xlsxwriterr  r	   ry   r-   r:   r   r+   r  r   r  r  r   r   r   ZWorkbookZadd_worksheetZ
add_formatZset_patternZset_bg_colorr   re  rf  r   r   r   r  r  r?  rU   rA  rN  rE  r.   ro   rn   rj   r=   r   rx   r  valuesdroprv   rk   rK   rq   r   r  r  )r/   r  r-  r  r	   r'  r   r   responser  r  Zcell_formatrp  rc   Zchannels_namere   r   r   r  rG   rH   rJ   rK   r   r  r0   )r  r1   genExcelK  s    




,2r1  c             C   sL   t d|  t d|  t dt|  d| d | d t| }t|S )Nzchannel:z	start_at:z	duration:)rr   r=   r   )r/   r   start_atr  alldatar0   r0   r1   	test_rest  s
    r4  c             C   sH   t d|  t d|  t dt|  d| d | d t| }d S )Nzchannel:z	start_at:z	duration:)rr   r=   )r/   r   r2  r  r3  r0   r0   r1   r4    s    c             C   s^   ddl m} t|dd}t|||d}|  t| d|| t|||| ||||dS )Nr   )r   r  r   )r   r2  r  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   r2  r  )django.httpr   r=   r   RealTimeAdbreakr  	loop_vastrealtime_filter)r/   r   r2  r  r4  r   ru  Zrealtimer0   r0   r1   realtime_adbreak  s    r9  c       $      C   s  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d	}	t
jj||d
j|d }
t
jj|	d	 }d}|
r|
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xj|D ]b}|d }t|d  tjj|d d}t| ||_|j}|j |_!tdt|j!  |  qW 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 )Nz%Y-%m-%d %H:%M:%S)r   r"   )r)      r	  )r   r  r   )r   )window_start__ltewindow_end__gte)r  r   u6   📣 Short adbreak detected, updating the playlist ...r   zI'm heeeere)r
   r9  )minutes)rW   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   zthe new avail winodws : z/schedules/u   ✅ Playlist updated)-rj   r-   r:   r  send_msg_telegramrQ   r   r   r.   r   r  r  r   r  rA  rr   r  r	   r
   r   r  r~   rW   r   r=   r   r   r   r   r   r  r   r   r  r   r   r  r   r  rG   rB  r  r  r  r  )$r   r2  r  r4  r  
my_channelr  r   r  Zotherplaylistr  
new_windowupdate_statustheid_playlistpreview_window_endr
   rW   r   broadcastDateForQueryid_windowForQueryr  daydate_xmlqueriesrl  rZ   r   availoor   winddddpplayr  r   r  r  r  r/  r0   r0   r1   r8     sz    



r8  c       &      C   sv  d}t jj| d}tjjdd}t|j||| t|dk rrtjj| d}t	jj| dd}t
jj||dj|d	 }	d
}
|	r`t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}||| | }|rxj|D ]b}|d }t|d  t jj|d d}t!| ||_|j}|j|_"tdt|j"  |  qZW 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-|$|%r`d}
|
dkrrtd d S ) Nz%Y-%m-%d %H:%M:%S)r   r"   )r)   r:  r	  )r   r  )r;  r<  )r  r   u6   📣 Short adbreak detected, updating the playlist ...r   zI'm heeeere)r
   z%H:%M:%Sz%H%M%S00r9  )r=  r2  rC  znew window duration )r  r   r  r  )rW   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   zthe new avail winodws : )r   z/schedules/u   ✅ Playlist updated).rj   r-   r:   r  r>  rQ   r   r   r.   r   r  r  rA  rr   r   r  r	   r
   r   r=   r   r   r  r  r~   rW   r   r   r   r   r   r   r  r   r  r   r   r  r   r  rG   rB  r  r  r  r  )&r   r2  r  r4  r  r?  r  r   r  r  rA  rC  r
   endor  new_window_durationr@  rB  rW   r   rD  rE  r  rF  rG  rl  rZ   r   rH  r   rI  rJ  r  r   r  r  r  r/  r0   r0   r1   realtime_filter_newz  s    (
$


rM  c       %      C   sP  d}t jj| d}tjjdd}t|j||| t|dk rLtjj| d}t	jj| dd}t
jj||dj|d	 }	d
}
|	r:t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}||| | }|rxj|D ]b}|d }t|d  t jj|d d}t!| ||_|j}|j|_"tdt|j"  |  qZW ttjj|dj#}ttjj|dj$} | }!|}"t%|| |j&t|t|!}#|j'd |j( }$t)|j*|j+|j,|j-|#|$r:d}
|
dkrLtd d S ) Nz%Y-%m-%d %H:%M:%S)r   r"   )r)   r:  r	  )r   r  )r;  r<  )r  r   u6   📣 Short adbreak detected, updating the playlist ...r   zI'm heeeere)r
   z%H:%M:%Sz%H%M%S00r9  )r=  r2  rC  znew window duration )r  r   r  r  )rW   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   zthe new avail winodws : )r   z/schedules/u   ✅ Playlist updated).rj   r-   r:   r  r>  rQ   r   r   r.   r   r  r  rA  rr   r   r  r	   r
   r   r=   r   r   r  r  r~   rW   r   r   r   r   r   r   r  r   r  r   r   r  r   r  rG   rB  r  r  r  r  )%r   r2  r  r4  r  r?  r  r   r  r  rA  rC  r
   rK  r  rL  r@  rB  rW   r   rD  rE  r  rF  rG  rl  rZ   r   rH  r   rI  r  r   r  r  r  r/  r0   r0   r1   realtime_filter_old  s    (
$


rN  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=0z
User-AgentzfMozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/103.0.0.0 Safari/537.36 )bs4rO  concurrent.futuresr   rr   )
urlr  rO  
concurrentZlist_idrd   dict_allcteZ
my_headersr  r0   r0   r1   get_info_from_vast_fileQ  s    TrV  c             C   s   t j }t| dd|idS )NzDAIManagementApp/pending.htmlurls)context)Pendingr-   rl   r   )r/   rW  r0   r0   r1   pending  s    
rZ  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}|r2tjj|ddd
 }|j}t|d }||_|  d
}tjj|d}xV|D ]N}tjj|jd}x6|D ].}tjj|jd}x|D ]}|d7 }qhW qNW q4W ytjj|||d}W n* tjk
r   tjj|||d}Y nX |rD|j}|j}tjj|jd }t|d }d}tj
t|j|tj
t|j| }tj
t|d}|	d}nytjj|||d} W n* tjk
r   tjj|||d} Y nX | r| j}!n|}!ytjj|||d}"W n* tjk
r   tjj|||d}"Y nX |"r|"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
}(x|' D ]\})}*|(d7 }(|*d d }+|+ dd }+yt!jj|+d!},W n& t!jk
r    t!jj|+d!},Y nX |,rN|d7 }t|%j|,j"|(|d"}-|-  nN|*d# }.|*d d }t#jj|.|d$$ st#|.|d$}/|/  t%d%| d&  qW d'}0t&jj||0d(}1t'|||1j(t|}2|j)d) |1j* }3d S )*N)r   r)   )r)   z/echo 'inside the task function' > a_log/log.txtz%Y-%m-%dz	 00:01:00z%Y-%m-%d %H:%M:%Sz	 23:59:00ru  r   r   )r=  r  )r   z-versionr"   )r   )r   )r   )r   r;  r<  z%H:%M:%Sz%H%M%S00Z00120000)r  r   r  r  )r  r  r  r	   r   )	r   r   r   r  r  r  r  r   r   Z23580000	MediaFilerR  r   r*   )url_from_vast)r  r  r  r  Creative_id)r  rR  z'New Creative Detected in VAST <a href='z'>(Video_link)</a>r	  )r   r  z/schedules/)+rj   r-   r:   r  r+   r  systemr	   r   r   r   r=   r  r
   r   r.   rm   r   r   r  r  r  r   r   r   r;   r   r  r  r   rV  itemsr   r   r  rY  r  r>  r   r  r   r  rG   )4r/   rR  r  r   r  r  r  Zto_day_dater  r  r   Znow_strZ
now_minus2Z
now_plus10check_valuer  r  r  Zcurrent_traffic_totalZall_windowsZwindZ
all_availsr  Z	all_spotsadr  r   r  Znumberofavailsr  r  r  Zprev_windowZnew_widdow_startZnext_windowZnew_widdow_endr@  r   r   rT  r  keyr  ZCreative_urlr  r  r  pandingr  r  r  r/  r0   r0   r1   task  s    




(

rd  c       
      C   st   t ||}d}x`| D ]T\}}|d }|d d }tjj||d st||d}	|	  td| d  qW d S )Nr   r]  r[  rR  )r  rR  z'New Creative Detected in VAST <a href='z'>(Video_link)</a>)rV  r_  rY  r-   r.   r  r  rA  )
r/   rR  r  r   rT  r  rb  r  r  rc  r0   r0   r1   r7  [  s    
r7  c             C   s4   ddl m} t|dd}t|| |||dS )Nr   )r   r  r   )r2  r  )r5  r   r=   r   send_ad_telegram)r/   r2  r   r   ru  r0   r0   r1   realtime_adspots  s
    
rf  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 Q R X td
 |	  d S )Nr   zuk06.tmd.cloudztestftp@epgsano.comzI?#=s3FfnSu_zConnecting to FTP...)dirpathz$Transferring %s and storing as %s...r&  zSTOR %szClosing session.)
r  r'  rr   r(  chdirr   basenamer   r+  r,  )	Z
DST_FOLDERZSRC_FILEPATHr  r'  ZFTP_ADDRUSERNAMEPASSWORDr+   r   r0   r0   r1   send_adspot{  s    "rl  c             C   sf   t | |dkrLt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_existsrr   r  Zmkdr)  )r+   rg  Zfoldersr0   r0   r1   rh    s    
rh  c             C   sF   g }|  d|j x.|D ]&}| d |kr| drdS qW dS )z!Check if remote directory exists.LISTr   DTF)	retrlinesrv   r  upper
startswith)r+   rg  filelistr$  r0   r0   r1   rm    s    
rm  )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: z
    - Time: [z]
    - Duration : [z]
    ====================
    	bot_token)tokenz@%srQ   )chat_idtext
parse_mode)rt  TELEGRAMtelegramBotsend_message	ParseModeHTML)rQ   r2  r  r4  r  telegram_settingsbotr0   r0   r1   r>    s    r>  c             C   sB   d|  d }t j}tj|d d}|jd|d  |tjjd d S )Nz
    [z]
    ru  )rv  z@%srQ   )rw  rx  ry  )rt  rz  r{  r|  r}  r~  r  )r4  r  r  r  r0   r0   r1   rA    s    rA  c             C   s<   d|  d }t j}tj|d d}|jd|d  |d d S )Nz
    [z]
    ru  )rv  z@%srQ   )rw  rx  )rt  rz  r{  r|  r}  )r4  r  r  r  r0   r0   r1   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: z
    - Time: [z]
    ====================
    ru  )rv  z@%srQ   )rw  rx  ry  )rt  rz  r{  r|  r}  r~  r  )r2  r   r  r  r  r0   r0   r1   re    s    re  c             C   sF   g }|  d|j x.|D ]&}| d |kr| drdS qW dS )z!Check if remote directory exists.rn  r   ro  TF)rp  rv   r  rq  rr  )r+   rg  rs  r$  r0   r0   r1   rm    s    
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 }x(|D ] }||d  ||d  qW t | t | |||d}t| jd }	t| d||	dS )NZjjjjjjjjjjjjjjjjjjjjjjjjjjr   )Countr)   )r)   rQ   c             S   s   g | ]}|d  qS )r   r0   )rL   r5  r0   r0   r1   rN     s    z!statscreative.<locals>.<listcomp>rR   )networkname__inrS   r   )dcountz-dcount0123456789ABCDEFc          	      s"   g | ]}d d  d qS )#r*   r  )r!  sample)rL   r   )charsrandomr0   r1   rN     s    r  )Zlebelr>   rf   z$DAIManagementApp/stats_creative.html)r   r   )rr   rz  r  rj   r-   r.   r+   ro   rx   r.  annotaterm   r  r   rs   rv   	ads_brandr   )
r/   r  r   r   rf   r  r>   ra  r   r   r0   )r  r  r1   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 }xFt|D ]:}|tjj|d dj |t|d d d d qzW |||d }}	|S )Nr   )r  )r)   )r   r   )r  z-countr  c          	      s"   g | ]}d d  d qS )r  r*   r  )r!  r  )rL   r   )r  r  r0   r1   rN     s    zads_brand.<locals>.<listcomp>)rB   r  r   r&  r   )r  r>   rf   )rz  r  r  rj   r-   r.   r   r.  r  rm   r   rs   rn   rv   r   r:   r   r   )
r+   r  r   adsrf   r  r>   ra  r   r   r0   )r  r  r1   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)rU   r  r   cpms)
ry   r-   r.   r+   ro   rm   campaign_pacingcampign_brandcampaign_avtiver   )r/   rk  rU   r   r  r  r0   r0   r1   statscampaign  s    "r  c             C   sP   t jjddd}dd |D }|d|d|dd t| d	}|S )
Nr"   )r)   rU   c             S   s   g | ]}|d  qS )r   r0   )rL   rG  r0   r0   r1   rN   '  s    z#campaign_pacing.<locals>.<listcomp>TFr   )truer
  rK   )ry   r-   r.   ro   r  rs   )r/   rU   rd   r0   r0   r1   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}x@|D ]8}||d k r|d7 }ql||d k r|d7 }ql|d7 }qlW |||g}d  fddt	dD }dddg}	|	||d}
|
S )Nr   )r	   r)   )r)   r*   )Zstart_day__exact)Zend_day__exactre  rf  c                s,   g | ]$}  |d  d  |d dfqS )r   z%Y-%m-%dr"   )r   )rL   r   )r	   r0   r1   rN   5  s    z#campaign_avtive.<locals>.<listcomp>r"   r  c          	      s"   g | ]}d d  d qS )r  r*   r  )r!  r  )rL   r   )r  r  r0   r1   rN   C  s    r,  ZFinishedzIn progresszNot started)r  r>   rf   )
r	   r  ry   r-   r.   r+   r]  ro   r   r   )r/   Zetatr   finishedZ	not_startru  r   r>   rf   r  rd   r0   )r  r	   r  r1   r  /  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 }x4|D ],}|tjj|d dj ||d  qnW |||d}|S )Nr   )r  r)   )r)   r   )r  z-dcountr  c          	      s"   g | ]}d d  d qS )r  r*   r  )r!  r  )rL   r   )r  r  r0   r1   rN   W  s    z!campign_brand.<locals>.<listcomp>)rB   r  )r  r>   rf   )rz  r  r  ry   r-   r.   r+   r.  r  rm   r   rs   rv   r   r:   r   )r/   r  r   r>   r  rf   r   rd   r0   )r  r  r1   r  N  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 }x4|D ],}|tjj|d dj ||d  q|W |||d}t| dd|iS )Nr   )r  r"   )r)   r)   r   )r  z-dcountr  c          	      s"   g | ]}d d  d qS )r  r*   r  )r!  r  )rL   r   )r  r  r0   r1   rN   p  s    zstatsbrands.<locals>.<listcomp>)r   r  )r  r>   rf   z"DAIManagementApp/stats_brands.htmlr   c                 s   ddl m}  tjjdd}tjj|ddd}dd	 |D }tjjt|d
	dj
| ddd}dd ld  fdd	tt|D }g }g }x4|D ],}|tjj|d dj ||d  qW d S )Nr   )r  r"   )r)   rR   )r  rS   r   c             S   s   g | ]}|d  qS )r   r0   )rL   Zverifr0   r0   r1   rN     s    z5statsbrands.<locals>.brand_adspot.<locals>.<listcomp>)Zadspot_name__inr   r   )r  z-dcountr  c          	      s"   g | ]}d d  d qS )r  r*   r  )r!  r  )rL   r   )r  r  r0   r1   rN     s    )r   r  )rz  r  rj   r-   r.   rx   ro   r   vr.  r  rm   r  r   rs   rv   r   r:   r   )r  r5  r   r  rf   r  r>   ra  r0   )r  r  r1   brand_adspot}  s    (
z!statsbrands.<locals>.brand_adspot)rz  r  rj   r-   r.   ry   r+   r.  r  rm   r  r   rs   rv   r   r:   r   r   )
r/   r  r5  r  r  r>   rf   ra  r   r  r0   )r  r  r1   statsbrandsf  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)   )r   rG   r   )r  z-dcountc             S   s   g | ]}|d  qS )rG   r0   )rL   rG   r0   r0   r1   rN     s    z!statschannels.<locals>.<listcomp>c             S   s   g | ]}|d  qS )r  r0   )rL   r>   r0   r0   r1   rN     s    r  c          	      s"   g | ]}d d  d qS )r  r*   r  )r!  r  )rL   r   )r  r  r0   r1   rN     s    )r  r>   rf   z$DAIManagementApp/stats_channels.html)rc   r  r  r  )rz  r  r  rj   r-   r.   r+   r   r.  r  rm   r   rs   channels_adschannels_adbreakre   r   )
r/   r  rc   r  r  r>   rf   r  r  r  r0   )r  r  r1   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 }x4|D ],}|tjj|d dj ||d  q|W |||d}|S )Nr   )r  r)   )r)   )r   r   )r  z-dcountr  c          	      s"   g | ]}d d  d qS )r  r*   r  )r!  r  )rL   r   )r  r  r0   r1   rN     s    z channels_ads.<locals>.<listcomp>)r   r  )r  r>   rf   )rz  r  r  rj   r-   r.   r+   r   r.  r  rm   r   rs   rv   r:   rQ   )	r/   r  rc   r  rf   r  r>   ra  rd   r0   )r  r  r1   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
|dddgd}g }g }tt|d }d  fddtt|D }xJ|D ]B}|j|d |k jddgd }	|t|	d  || qW |||d}
|
S )Nr   )r  r)   )r)   rQ   c             S   s   g | ]}|d  qS )r   r0   )rL   r   r0   r0   r1   rN     s    z$channels_adbreak.<locals>.<listcomp>)Zchannel_name__inrH   r	  r  r  r  )rZ  r  c          	      s"   g | ]}d d  d qS )r  r*   r  )r!  r  )rL   r   )r  r  r0   r1   rN     s    )by)r  r>   rf   )r  r  rz  r  rj   r-   r.   r+   ro   r;  r  rn   r   r   rs   r  groupbyr  rv   r  )r/   r  r  rc   r>   r  r  rf   r   r  rd   r0   )r  r  r1   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)   rC   c             S   s   g | ]}|d  qS )r   r0   )rL   r   r0   r0   r1   rN     s    z channels_sfr.<locals>.<listcomp>)rF   rK   r  r  )rZ  c             S   s   g | ]}t |qS r0   )rO   )rL   r   r0   r0   r1   rN     s    r   )rB   )r  c                s   g | ]}|d     qS )r   r0   )rL   r   )ru   rt   r0   r1   rN     s    r  c          	      s"   g | ]}d d  d qS )r  r*   r  )r!  r  )rL   r   )r  r  r0   r1   rN     s    )r  r>   rf   )r  r  rj   r-   r.   r+   ro   rk   r  r   rq   r:   rt   rO   ru   r  r  rn   r   r.  r   rs   )r/   r  rc   r  r  rK   r  r  r>   rf   rd   r0   )r  ru   r  rt   r1   re     s&    
re   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
}y|
||||| | }	t|	 ||	}
|
dddddddddddddg }dddddddddd d!d"d#g|_t V}|j|d$d%}|j|d&d' |  |d( | d) }t| d*d+}d,| |d-< |S Q R X W n$   td. td. td. Y nX tjj| jd/ d0}tjj|d1d2}d3d4 |D }d5d4 |D }t| t| d6||d7S )8Nr9   r   )rW   rc   r   re  rf  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   rV   r  r  r  r9     r!  r"  Z	id_verifsr  r*  r   r   r   rD  Z	airLenghtZ	airStatusr  r  rJ   rK   r-  )engineF)r   r  z.xlsxzAapplication/vnd.openxmlformats-officedocument.spreadsheetml.sheet)r  zattachment; filename=%szContent-Dispositionz3===================================================r)   )r)   )r   r  c             S   s   g | ]}|d  qS )r   r0   )rL   r  r0   r0   r1   rN   $  s    z#generate_verifs.<locals>.<listcomp>c             S   s   g | ]
}|j qS r0   )rC   )rL   r5  r0   r0   r1   rN   %  s    z%DAIManagementApp/generate_verifs.html)r   rc   )r   r  r~   rW   r   r9   r:   rr   r=   r   r   r  r   r  rZ  r   ZExcelWriterZto_excelr  r   getvaluerj   r-   r.   r+   r   ro   r   )r/   r  rW   r   rc   r   re  rf  qrZ   r  r  bwriterr   r0  r0   r0   r1   generate_verifs  sP    
" r  c             C   sx   | j dkrnt| jd dd}t| d| jd  }tj|sLt	| t
| jd || t|d |  t| dS )	Nr9   Zbouygues_filer   r  zbouygues/user_r)   r   z%DAIManagementApp/bouygues_upload.html)r   r=   r  r   rr   r+   r  r   r  r  r   insert_bouyguesr   )r/   r   r   r0   r0   r1   upload_bouygues*  s    

r  c          
   C   s  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  }x|D ]}x|jdd  D ]}|| d }t|| dd  }x~t
dt|D ]l}|dk r>t|||
|| || d d}|  n4||d	d }t|||
|| || d d}|  qW qW qW d S )Nr   )r	   r
   r   zau r*   zdu r   z%d/%m/%Yr"   )ru  endc             S   s   g | ]}d t | qS )r  )r=   )rL   r   r0   r0   r1   rN   C  s    z#insert_bouygues.<locals>.<listcomp>r  r   r  rV  r  i  )rQ   rH   r  rJ   rK   )r   )r  r	   r
   r  rZ  r   r  r  r   Z
date_ranger   rs   rn   Bouygues_analyticsr  )r   r  r	   r
   r  r   re  rf  ZperioderZ  r  rJ   rH   rW  r   r   r   ZbouygueZday_2r0   r0   r1   r  6  s0    





r  c             C   s$   t jj| jd d}t| dd|iS )Nr)   )r)   zDAIManagementApp/charts.htmlrc   )rj   r-   r.   r+   r   )r/   rc   r0   r0   r1   charts_testV  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   rG   rH   )r   )r  )rC   rG   rH   rJ   rK   rI   )rB   c             S   s   g | ]}|d  qS )r   r0   )rL   rM   r0   r0   r1   rN   i  s    zsfr_channel.<locals>.<listcomp>c                s    g | ]}t t|d    qS )r"   )r   rO   )rL   rM   )rP   r0   r1   rN   j  s    )r>   r  T)r  )r	   r  r:   rr   rj   r-   rC   r   rG   rp   r.   ro   rq   rO   rt   ru   r   )	r/   r	   r   rG   rH   r   rK   r[   r>   r0   )rP   r1   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   s   g | ]}|d  qS )r   r0   )rL   r   r0   r0   r1   rN   s  s    z!adspost_avail.<locals>.<listcomp>)r  end_day__ltr  c             S   s   g | ]}|d  qS )r   r0   )rL   r   r0   r0   r1   rN   u  s    )r   c             S   s   g | ]}|d  qS )r   r0   )rL   r  r0   r0   r1   rN   x  s    )	r	   r   rE  r-   r.   ro   ry   r  r   )r	   r   r\   r   r   r0   r0   r1   adspost_availn  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   s   g | ]}|d  qS )r   r0   )rL   r   r0   r0   r1   rN     s    z&channels_campaigns.<locals>.<listcomp>)r  r  r\   z(DAIManagementApp/dropdown_compaigns.html)r	   r   r  r:   rE  r-   r.   ro   rj   ry   r   )r/   r	   r   r   r\   r>   r0   r0   r1   channels_campaignsz  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r||d}|d}|d	}|d}t| tt| t| tj }t| g }t| dd
lm} | }d}t| ||
|	|f}||| | }td t| x|D ]}x|D ]}tjj|d}i }|j|d< |j|d< d|d< tjj|d}d|d< d|d< xZ|D ]R}|d  d|jj 7  < |d  dt|jj 7  < |d  t	|jj7  < qW || d}q<W q2W t | d||dS )Nr   )r	   r
   )deepflattenr   r   rG   )r   z%Y-%m-%dz%Y%m%d)rW   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
                  rZ   )rB   r   ru  r*   r   )r   Zsommer  z / z$DAIManagementApp/avails_details.html)r  rG  )!r	   r
   Ziteration_utilitiesr  r  r:   rj   r-   rr   r   r   r   r   r   r@  r   r  r~   rW   r   r   r   r  r   r  r   r.   r   r  r=   r  rv   r   )r/   r	   r
   r  Zchannel2r   rG   rd   r   r  Zchannel_networknamer   Zbroadcast_playlistZbroadcast_verifnr>   ra   rW   r   rG  rl  rZ   Zid_avsZid_avr   r  r   r   r0   r0   r1   delete_avail  sb    










r  c       !         s  | j d}| j d}| j d}td| |d krT|}tj|d}|d}ntj }|d}|d krtjj	|dd j
}t| nd}|d krt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}xx|D ]p}|j	|ddd}t|dkrt|jt|j  dd |D } fdd|D }|	| |
| qW dd l}t}tjj	| jd	 dd}dd |D }tjj	ddd}g }x|D ]}|j|k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rd%|d&< d'|d$< xtjj	|d( |d d)D ]N}|d |j krtjjdd}t|j!t|j t|j  t" |d*< qW n\t|jd+krd,|d&< d-|d*< n:t|jd.krDd/|d&< d-|d*< d0|d$< nd1|d&< d-|d*< || qW t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   rG   r  zload_charts ====== z%Y-%m-%d)Zzonename__containsr   rE   r)   )r)   r   rC   T)rD   )r)   )rF   rG   rH   rI   )rB   )rC   rJ   rK   c             S   s   g | ]}|d  qS )r   r0   )rL   rM   r0   r0   r1   rN     s    zload_charts.<locals>.<listcomp>c                s    g | ]}t t|d    qS )r"   )r   rO   )rL   rM   )rP   r0   r1   rN     s    rQ   c             S   s   g | ]}|d  qS )r   r0   )rL   rM   r0   r0   r1   rN     s    rR   Z20211024)rS   r   r   )r   r   rH   )r  r"   r  z:00z	#00800000rf   zAired Successfullyry  z	#2c2c8cb3rH   )rH   rJ   r  r  zFailed, Adspot cutr   r  zNot aired yetz	#c7c7c7b3zFailed, Other Reason..)r)   rU   z-id_activityrV   zend of loadchart)r[   rd   re   rf   ),r  r:   rr   r	   r   r   r   r   r-   r.   rG   rn   rj   r+   ro   rp   r=   rq   rs   rO   rt   ru   rv   r  rw   rx   r   r   rD  r  r  rS   rC   rK   r   ry   rz   r{   r|   ra   r}   rl   rm   r   )!r/   r  rG   r  rH   r   r  re   r   rd   r   rK   r   r   r[   r>   r  rf   rc   r   Zdata2r2  rG  r  rJ   r   r\   r]   r   r_   r`   r   rb   r0   )rP   r1   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|st| tjj| jd d}t| t|j|j|j|
|	| t|d	 |	  r(t|d	 |	 }|d}|jd}g }x|D ]}|d}|d}|d}|dd}|dd}|d}|d}|d}ylt	jj|||||d}|r||_||_||_||_ ||_!|"  n$t	||||||||||d
}|"  W nD t	j#k
r    td t	||||||||||d
}|"  Y nX qW d S )Nr)   )r)   r	  z%Y%m%d)r   r  r   zWait I'm in ftpr   z
-00001.verr   z/verifs/zfiles/results/z/verifsr   rC  r   r   rD  r   r   z+02:00r*   rE  rF  r   )r   r  r   r   r   )
r   r  r   r   r   rD  rE  rS   r   rG  r  )$rj   r-   r.   r+   r  r   r	   r   r   rx   r  rr   r  r   r  r  r  r:   r4  r  r  r  r   rH  rI  rL  rJ  rK  r   rD  rE  rS   r   rG  r  r;   )r/   rc   rG   r  r   r  r   rd   r  r  r  r  r  rO  rP  rC  rQ  rR  r   r   rD  rS  rT  rE  rF  r   r  r  r0   r0   r1   insertion_verifsY  s\    








r  c             C   s>   dd l }dd l}||j|j}|ddt| f |  d S )Nr   i,  r"   )schedr	  	schedulerr  enterr  run)r/   r  r	  sr0   r0   r1   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}x|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}xB|D ]8}i }||d< |d }|j|d< tj|jd}|d}||d< g |d< |d | tjj|jd}||d< d}d|d< x|D ]}i }||d< |d }||d< |j|d< |j |d < t!d! y8tjj||j"t#|j$d"%d#}	|	j&|d$< |	j'|d%< W n  tj(k
r   t!d& Y n
X t!d' |d | qW qpW |d | d} qW |d ||||
|d | d(}!t)| d)|!S )*Nr   )rB   )r   )r   r   r*   )r   r  r   )r   r   T)rD   r  r   r  r   r"   z%Y-%m-%d %H:%M:%Sz%H:%Mr   r  r  r  )r   r  r   r  r  )r   r  r  r  r   zWait I'm in database)r   r   r  r  rF  rD  r  zfile not ready yet)r>   r   rc   rG   r  r  r  zDAIManagementApp/expande.html)*r  r:   r   r-   rj   r   r   r   r  r   r  r   r   rx   r.   r  r=   r  r   ro   r   r	   r   r   r   r  r  r   r  rv   r   r  r   rr   r  r   r   r  rS   rD  r;   r   )"r/   r   r   rc   rG   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  r   r  r  r  r0   r0   r1   expande  s    







 
r  c             C   s  | j dkrXt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}x&|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}xN|D ]F}|j}|d | d }t|j|t
|d |
d}|  |d }qW qW t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 )#Nr9   r)   )r)   r  )r   r  rH   )r   r  z%m/%d/%Yz%Y-%m-%d)r  start_day__lteend_day__gtezT00:01:00+00:00zT23:59:00+00:00rI   r   )	r   r   r   r  r  r  r  r   r   r   rV  )rC  r   z:00z%Y-%m-%d %H:%M:%Sz%H:%M:%Sz%H%M%S00)r  r   r  r  )Ztime__ltZ	time__gte)rH   r   r	  )r	  r"   )r  r  r  r	   z7DAIManagementApp/generate_playlist_from_prediction.htmlr>   rc   ) r   r  r-   r:   r+   r9   rj   r   r	   r   r=   r   ry   r.   rr   r   r   r   r  rD  r]  ru  r  r  r   r  rm   r|  r	  r  r   r   )r/   r  r  r  r  r  r  active_campaignsr  r  r   r  r  r  Z	day_timesday_timer   r  r  r  r   Zadbreaks_predected_in_windowr  r  r  r   rc   r0   r0   r1   !generate_playlist_from_prediction  sZ    
 
 
$
r  c          	   C   s  | j dkrZ| 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| sZt
d|  yR|d }t
jd| st
d|  t| jd d| |d  ||d 7 }W n~   y`dd l}|d }| jd}||}td| | d d}||j |  ||d 7 }W n   d}Y nX Y nX t|  |||||	|d}|  ttS y$tjj| jd d}t| d d!|iS    ttS d S )"Nr9   rw  r   r  r   emission_startr   r   r"   emission_end
short_descr  r  episode_nameseason_numberepisode_numberextendedr   )sep)rB   z
emissions/r   zstatic/zimage/imagez.pngr   r*   )rw  r   r  
start_timer  end_timer  r)   )r)   zemissions/converter.htmlrc   )r   r9   r:   r  r   r  rr   rj   r-   rQ   r  r   r  r  r   r  requestsr   r   contentr  Z	Emissionsr  r  r   Zedit_emissionsr.   r+   r   r2   )r/   emissionrw  r  r  r  r  r  r  r  r  r  r  r  r  r  r  yearr   Zpath_emissionZ
path_imager  r  r0  r   Znew_emissionrc   r0   r0   r1   add_emissions3  sf    .
r  c             C   sx   | j dkrnt| jd dd}t| d| jd  }tj|sLt	| t
| jd || t|d |  t| dS )	Nr9   Zepg_filer   r  z	epg/user_r)   r   z DAIManagementApp/epg_upload.html)r   r=   r  r   rr   r+   r  r   r  r  r   
insert_epgr   )r/   r   r   r0   r0   r1   
upload_epg{  s    

r  c          
   C   s   dd l }ddlm} tjjdd}|| }|ddddd	d
g }x|jD ]}||d d}||d d}t|d d d d }	t|d d d d }
t	||d ||	
 ||

 |d d}|  qHW d S )Nr   )r	   r"   )rB   z
Event Namez
Start Datez
Start TimezEnd DatezEnd TimeZGenrez%d/%m/%Yr,  r   r  z.000000r   rV   )r   rw  r  r  r  r  r  )r  r	   rj   r-   r:   r  r.  r   r=   Epgr  r  )r   r  r	   r   r  Zepgr   Z	startdateZenddateZ	starttimeendtimer  r0   r0   r1   r    s    
&r  c             C   s.   | j d}t| j d| j dd d}d S )NrI  Zproduct_description)r   r[  )r9   r:   ZProduct)r/   rI  rQ  r0   r0   r1   insert_product  s
    
r  c                sb   ddl m   ddlm} d}g dd } fdd	}||}|rL|| 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 d S )N   )r  r:   status_codejson)rR  r0  r0   r0   r1   fetch_playlist_data  s    

z+process_adslot.<locals>.fetch_playlist_datac          
      s  d}t  }x| D ]}|dd}y || }W n  tk
rX      }Y nX xX|dg D ]F}|dddkrjy<td|d	 t jj|d
|d	 d}td| W qj t jk
r   |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 qjX qjW qW S )Nz%Y-%m-%dplaylistDater*   r>   isPubr   rI   zSUB ID: subIdr6  )playlistdatesubidzFound Objectru  r  startTsendTscutscenarior  title)r*   )rY  )r   )AdslotsVisionrr:   r   r   
ValueErrorr   rr   r-   r;   adidr  ru  r  starttsendtsr  r  r  ispubr  replacementadidreplacementpathreplacementdurationry  
updatesentr  r  )ad_slots_jsondate_formatZvisionerr   playlist_date_strplaylist_datera  adslot)r	   ra   r0   r1   insert_ad_slots  sH    z'process_adslot.<locals>.insert_ad_slotszFailed to fetch playlist dataFr  )r  ry  )r	   r   r  rr   r   )Zreuqestr  rR  r  r  ad_slotsr0   )r	   ra   r1   process_adslot  s    3
r  c                sh   ddl m  dd lddlm d}fdd} fdd}||}|r\|| td	 ntd
 d S )Nr   )rW   )r	   z~https://tvpitchoun-stor.easytools.tv/synthesia/tvpitchoun/playlist/manager/php/_getPlaylist.php?fields=title,internal,durationc                s4   d}d} j | ||fd}|jdkr,| S d S d S )N
advmanagerzDFG$_Rv!934)authr  )r:   r  r  )rR  r3   r4   r0  )r  r0   r1   r    s    
z-process_ad_slots.<locals>.fetch_playlist_datac       	         s     }d}d}x| D ]}|dd}y|| }W n  tk
rZ     }Y nX x|dg 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|f}|dd	dkrj||| qjW qW |  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)
        z%Y-%m-%dr  r*   r>   r6  r  ru  r   r  r  r  r  r  r  r  r  rY  Finternalad_break)r   r:   r   r   r  r   r   r  )	r  r   Zinsert_queryr  r   r  r  ra  rl  )rW   r	   r0   r1   r  
  s8    











z)process_ad_slots.<locals>.insert_ad_slotszAdslot Insert SuccessFullyzFailed to fetch playlist data)r~   rW   r  r	   rr   )r/   rR  r  r  r  r0   )rW   r	   r  r1   process_ad_slots  s    0
r  c       #      C   sF  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r8d }|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 Q R X |S )Nr   )HTTPProxyAuth)r   )	parse_url)rO  z%Y-%m-%d_%H%M%SZ_alma)r   )headersrr  r  zthe ROOT is :zthe ROOT find is :z%Y-%m-%d %H:%M:%Sz.//Adr6  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  rQ  r   urllib3.utilr  r  r	   r  xml.etree.ElementTreer   r  rP  rO  r   r   r   r!  r*  r=   r   r-   r:   r!   atomicr  
fromstringrr   rx  findattribVastResponsecreater	  impression_sprintserve_statusimpression_double_click_statusr  )#rR  r  rr  call_numrR  r  r  r   r  r  r	   r  ETrO  r|  folder_pathr   r0  Zvast_responser  Zcurrent_timer  r  r  r   tracking_first_quartiler  tracking_third_quartiler  r  r	  Zdouble_clickvast_objsprintserve_responsedouble_click_responser0   r0   r1   vast_handling_oldC  s    






	
/r  c       .      C   s  ddl m} tjj|d}t  t| }|d krBtd d S t	|}|d kr^td d S t
j
 }	|	d}
t|d\}}xXtt|D ]F}|| }|| }d|krd	|d k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rH|d d nd }!t|dkrf|d d nd }"t|dkr|d d nd }#t|d\}}$d }%d }&t|$dkr|$d d }%d }&n@t|$dkrtdd |$D d }%tdd |$D d }&ntd |jj|||||||	|
| |%|&|d}'|&rDtj|&|d}(|(jp@d |'_|%r`tj|%|d})|)j|'_|r|tj||d}*|*j|'_|rtj||d}+|+j|'_|rtj||d},|,j|'_|rtj||d}-|-j|'_|'  qW W d Q R X d S )Nr"   )VastResponseGo)r   z'Failed to get content, stopping processz%Failed to parse XML, stopping processz%Y-%m-%d %H:%M:%SZAd
attributesr6  ZTrackingeventru  ZfirstQuartileZmidpointZthirdQuartileZcompleteZmuteZunmutepauseZ
fullscreenr   rx  r  r   c             s   s:   | ]2}d |krd|d  kr|d  d dkr|d V  qdS )r   r6  ZSpringServe_Impression_1rx  Nr0   )rL   itemr0   r0   r1   	<genexpr>  s    z vast_handling.<locals>.<genexpr>c             s   s:   | ]2}d |krd|d  kr|d  d dkr|d V  qdS )r   r6  ZSpringServe_Impression_3P_1rx  Nr0   )rL   r#  r0   r0   r1   r$    s    zNeed To Be Handel )r  r   r  r  r  r  r  r  r  r  r	  r
  )r  )r   r  r   r-   r:   r!   r  r&   rr   r'   r	   r   r   r(   r   rs   nextr  r  rx  r  r  tracking_completed_statustracking_firstquartile_statustracking_midpoint_statustracking_thirdquartile_statusr  ).rR  r  rr  r  rR  r  r   Zxml_contentr  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  r  Zcompleted_click_responseZfirst_quartile_responseZtracking_midpoint_responseZtracking_third_responser0   r0   r1   vast_handling  s    

r*  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}	 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|||d }d!|d"d#d$d%|d"d#d$d&|d"d#d$d'|d"d#d$d(|d"d#d$g}d)d*id)d+id)d,id)d-id)d.ig}d S )/Nr"   )	call_vastr   )r  )r   )r  i! i% i+ i- i$ i# i( i" i& i' i, i. )
FeuilletonZMagazineu   Mini-Sériez	Magazine z
Mini-SerieZSerieZInformationzMagazine SportifzDessin AnimezMagazine Sportif ZTelefilmZDocumentaireZ	ReligieuxZSportzLong MetrageZNewszLong-MetragezDes Histoires Et Des Hommesu   SérieZMusiqueZTheatreZ	SpectacleZJeux720567zDessin AnimezAbtal albiharzar-MAr$  2M_TVrE   )whcontent_genrecontent_titler  pod_max_durrQ   rR  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}}z
User-AgentzeMozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/103.0.0.0 Safari/537.36)rR  num_requestsrr  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:12323r6  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)tasksr+  r  r  r  rQ  r   r  r  r  r	   r  r  r   r  delay)r/   r+  r  r  r   r  r  r	   r  r  Ziab_catrr  r5  rR  r  vastZ
proxy_listZproxies0r0   r0   r1   call_vast_apiR  sl    
	r:  c                sb   ddl m  m t| }|jdkrZ| d | d d } fdd|D }|S d S d S )Nr   )r	   r
   r  r>   c                s~   g | ]v}d |kr|d  dkr|d d d  d| dd  d  d| dd ddd dd	qS )
r  rI   r6  r   ru  r*   z%Y-%m-%d %H:%M:%S.%fr"   )weeks)r  r   Zid_start_combinedZstart_minus_one_week)r:   r   r   )rL   r#  )r	   	head_datar
   r0   r1   rN     s   z'fetch_playlist_data.<locals>.<listcomp>)r	   r
   r  r:   r  r  )rR  r0  Zplaylist_datar~  r0   )r	   r<  r
   r1   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|  x|D ]}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 qtdt| d d |  d qW 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 Q R X td" d#}d$}d%}d&}d'}t!||||d(}|"|| d}d S ))Nr   )r	   r   r   zDate: r6  r  r  ZsynthesiaIdZsynthesiaSubIdr>   zTGTG_30_16x9.mp4)r   r  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"?>
zutf-8)r   z1/var/www/html/DAI27/Adtlas_DAI/files/vast/pitchonz%Y-%m-%dT)r  r   r  r  z.xmlr0  zUploading to FTP...z	/PLAYLISTztvpitchoun-stor.easytools.tvr  zDFG$_Rv!934   )r  r8  r3   r4   )#r  r   r  r	   uuidr  astr   rr   r   r  AlmaAdsr-   r.   r  r  r   r=   r6  ads_pathr   tostringdecoder   r   r  r   r!  r  r   uuid4r   r   r%   r  )r>   r  r	   r?  r  r@  r   r  Zsynthesia_idZsynthesia_sub_idZdata_elementZtotal_durationZalma_replacement_adsZvideoZmedia_elementr   Z
xml_stringbase_dirtodayZdate_folderr  r   r1  Zxml_fileZ
remote_dirr  r3   r4   r8  Z
ftp_clientr0   r0   r1   create_playlist_xml  sd    



 
"$
0rH  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rr|j}tj }|tjdd }|	d}|	d	}	|td
d }
|td
d }yt
jj|d |d dd jdkrtd tddiS |d|d i}td|d  yb|dd | D d}|r"|d d }td| x| D ]}|d |kr
q
W t| W n( tk
rT } z|W d d }~X Y nX t
jj|d |d dd }d|_|  tdddS W n* tjk
r   td tdddS X d S )Nr   )Counterr"   )visioner_playlistr9   r  )r   z%Y-%m-%dz%H:%M:00r  )r=  r   r  )r  r  Z	Completedz!This Ad Has Already Been Replacedr  z This Ad Has Already Been ReplacezCreating Playlistc             s   s   | ]}|d  V  qdS )r  Nr0   )rL   r#  r0   r0   r1   r$    s    z-GetPlayListImpression.post.<locals>.<genexpr>zThe most replaced ads is:r  z&Alma Ads Has bees replaces Succesfullysuccess)r  ry  zUpdate Epg DatabasezEPG Unsynchronizedfailed)collectionsrK  r   rL  r   r>   r	   r   r
   r   r  r-   r.   ry  rr   r   most_commonrH  r;  r  r  r;   )selfr/   r  rK  rL  r   r   Znow_minus_weeklast_week_dayZlast_week_minuter  r  r~  Zmost_common_titleZcommon_titler#  r>  r  r0   r0   r1   r<  D  sF    



"
zGetPlayListImpression.post)N)__name__
__module____qualname__Zrest_framework.responserJ  r<  r0   r0   r0   r1   rI  @  s   rI  )View)Q)rb  )parse_datetime)calculate_priority_scorer  )Adbreaksr   r  ry   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} 	yd}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}| r6| dkr6| j}| j}tjj||	 d}ntjj|	 d}fdd|D }g }x|D ]}| }|jrdnB|jr|r|j|jkrdn"|jr|r|j|jk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! y t.jj|d"}$|#|$_/|$0  W n( t.j1k
r   t.jj||#d! Y nX qlW t2|d#d$ dd%}g }%|d& }&d'}'t3||'kr<|	d(ksF|	d	krx|D ]}(t4jj|(d) j5|&d*6d})|) rL|) }*|%rt7 }+x2|%D ]*},|+t7|,j8|*j8d+t7|*j8|,j8d,B O }+qW t9j|+ s|&|*j:8 }&|%-|* nt;d-|*j8 d. n|&|*j:8 }&|%-|* qLW d }-|%rt<|%dkrt4jjd/d0d1}.|.}/|%t=|/7 }%t>jj |j?d26d3}0|0 rn|0 dkrn|0 }1t@jj|1d4}2|1}-d |-_A|0 d  |-_Bt# |-_C|-0  xtD|2D ]\}3}4tEjj|4d5}5|4}6d |6_F|-|6_A|60  xltD|5D ]`\}3}7tGjj|7d6}8|7}9d |9_H|6|9_F|90  x.tD|8D ]"\}3}:|:};d |;_I|9|;_H|;0  q:W qW qW n2t>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 s&t@jj|-|=|>|<dB}?n|2K }?||?jLd@}@||?jMd@}At	|@t
 }@t	|At
 }A|@|
  kr|Akrn nR||?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rttEjj|Ed5K }F|FrttGjj|Fd6K }G|Grt|GjO}D|
}HxtD|%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qW tP|-}K|KdI rpdJ}LdK}MdL}NdL}O|KdM }P|-j?jQdNkr\dO}QtR|L|M|N|O}R|RS|P|Q n|-j?jQdPkrdQ}Qnt;dR|KdS   dTTdUd |D }SdV| dW|	 dX| dY|dZ  d[|-d kr|-jAnd\ d]| r| jnd\ d^| r| jnd\ d_|S d`dad |%D  }T| jUV|T tWdSdbidcddS  tXk
	rX }U z|UW d d }U~UX Y nX d S )eNr   )r	   r
   r"   z2M Monderu  r  r$  rG   rE   z%Y-%m-%d %H:%M:%S.%fzEurope/PariszAfrica/Casablancar   )hours)r2  r  r   rQ   )r   rG   )r   start_time__lteend_time__gte)r  r  Zprogram_category__icontainsrR  )r  r  rR  c                sB   g | ]:}t jj| d d |jk rtjj|d r|qS )T)rk  airtime_start__dateinsertion_status)rk  r`  ra  )r[  r-   r.   r  rM  r]  r  )rL   rk  )r  r  r0   r1   rN     s    
z)DynamicAdPlaylist.get.<locals>.<listcomp>g      ?T)rk  rb  rc  z%Y-%m-%dr,  )beforeduringafterc                s   i | ]\}}|  |qS r0   r0   )rL   r  r  )total_weight_sumr0   r1   
<dictcomp>"   s    z)DynamicAdPlaylist.get.<locals>.<dictcomp>c             3   s   | ]} | V  qd S )Nr0   )rL   Z
preference)normalized_weightsr0   r1   r$  $   s    z(DynamicAdPlaylist.get.<locals>.<genexpr>)TSAARFUP)rk  priority_score)rk  c             S   s   | d S )Nrn  r0   )rM   r0   r0   r1   <lambda>9       z'DynamicAdPlaylist.get.<locals>.<lambda>)rb  reverser  r  ZNetherlandsrk  )r  Zduration__lte)	ad_spot_1ad_spot_2_id)ad_spot_1_idrs  zAd spot zJ has an exclusion relation with the already existing ad spots. Skipping...ZFiller_Ma_10mrI   )Zadspot_name__containsry  )r   r  z-creation_datetime)r   )r   )r   zT00:01:00+00:00zT23:59:00+00:00r   )	r   r   r   r  r  r  r  r   r   i  )secondsz%H:%M:%Sz%H%M%S00x   z%Y-%m-%d %H:%M:%Si  )r   r   r  r     )r=  ru  )r   r  r  r	   )r   r   r  r  )rk  rR  r  )rk  rR  r  airtime_startairtime_endrc  ry  z172.16.205.1i`  r  r   r  z2M/schedules/REG1_NLr  z2M/schedules/REG1_FRz)Failed to generate playlist file. Error: r  r   c             s   s6   | ].}d |d j  d|d j d|d  dV  qdS )z * [rk  z][rn  z] 
N)r  r   )rL   Zcampaign_scorer0   r0   r1   r$  E!  s    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   s   g | ]
}|j qS r0   )r  )rL   r
  r0   r0   r1   rN   G!  s    z Ad playlist created successfullyr  )ry  )Yra  r	   r
   r  r:   rO   r   rb  localize
make_awareget_current_timezoner   r	  rZ  r-   r  r   r.   r#  r  r  r  r  r  ry   r  exclusivityshows_preferencerw  rS  r[  rM  r  rf  r=   r   r   r@  literal_evalposition_preferencer  r.  r   r_  rY  rv   r\  rn  r  r;   sortedr   r   r  rm   rW  r  AdSpotExclusionr  rr   rs   rn   r   r   r  r   r   r  r  r  r   r   r   r  r   r  r   r  r  r  r  r   r  r  r!  telegram_botZsend_telegram_logr   r;  )VrQ  r/   ra  r	   r
   r  rQ   Zstart_date_time_strZduration_secondsZregion_testZstart_datetimeZlocal_timezoneZfrance_timezoneZstart_datetime_franceZmorocco_timezoneZstart_datetime_moroccor  r  Z	emissionsZemissions_genresZemissions_categoriesr  Zpriority_score_listrk  Zactual_emissionrj  Zalready_aired_slotsrk  r  rl  Zposition_preferencesweightsZ
max_weightZweighted_sumrm  rn  Zpriority_scores_objZad_spots_to_insertremaining_duration_secondsZmin_durationentryZlist_adspotr
  Zexclusion_conditionsrR  r   Zadspot_fillersZrandom_filler_adspotsra   Zold_playlistplaylist_windowsr  r  window_availsr@  r   r  Z	new_availadspot_in_availZold_adspot_in_availZwindow_durationsr   r  Zplaylist_windowZwindows_start_atZwindows_end_atZwindow_avail_countZwindow_availr  Zlast_windowZ
last_availZlast_avail_linkrx  r   Zexisting_Campaignairtimelogr  r  r  r  r  r<  r=  r0  Zmessage_campaign_scoreZalert_messager>  r0   )ri  r  r  rg  r1   r:     s   





 $
*"


*






 


zDynamicAdPlaylist.getN)rS  rT  rU  r   r  r  r:   r0   r0   r0   r1   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 }	x|D ]}
||
jd|dd	 }||
jd|dd	 }tjj||d
}tjj|
d}| }| }||krxXt|D ]\}}tjj|d}|| j}|| j}x|D ]}|jj|jj||j|jj|dtdtt|jj||jjkr`d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 }|	!| qW qW qfx(t|D ]\}}tjj|| d}|j}|j}x|D ]}|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 }|	!| q>W qW qfW t"|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 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   r*   )Zstart_at__datez%Y-%m-%d %H:%M:%Sr   )r_  )Zstart_at__gteZstart_at__lte)r   )r   z%H%M%S00rR   r  T)
r   r  r   r   r   rD  rE  rF  r   	is_active)
r   r  r   r   r   rD  rE  rS   r   rG  )ru  )r6  r   )r6  rG   r  r   )r6  r   r   r  r  r   r   r  r   r   rG   rM  z*Static verifications validation successful)r   r   ry  r  FailedzPlaylist Not Found)ry  r  N)+r	   r
   r   r-   r.   r#  r   r   rZ  r  r   r   r   r  r  r  r  r   r  r2  r   r   r  r  r   r  r   r	  r
  r   r   rx   r  rv   r   r  r  r   r   r  r   rQ   rG   )rQ  r/   Zplaylist_idr	   r
   r   r   Zadbreaksr  static_verifsr  Zwindow_ad_startZwindow_ad_endZadbreaks_in_windowr  Zavails_countZadbreaks_countr   r   r  r  rD  r  Z
verif_spotr  r  r0   r0   r1   r:   f!  s    






zStaticVerifsValidation.getN)rS  rT  rU  __doc__r:   r0   r0   r0   r1   r  _!  s   r  c               @   s    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r0||jdd n
|  }tjj|d}t	|| j
||dS )Nr   )r	   r   z%Y-%m-%d)r   )current_dateZads_planning)r	   r  r:   r   r   r   ConductorDatar-   r.   r   template_name)rQ  r/   r	   r  Zconductor_data_objectsr0   r0   r1   r:   "  s    0zAdsFromConductor.getc                s  dd l }ddlm} d|jkr|jd }d|jkrH|jd rH|jd nd}|jd }|jdsp|jd	ry||}|jd d dd
dddddgf }|j	j
jdddd|_	|jdddd}|jdddddgdd}|jddd x| D ]t\} | d r| d r| d r| d r|j|d df  dt
 d  7  < |j|dd qW |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d0ddd}|jd1ddd}|jd2ddd}|jd3ddd}|jd4ddd}|jd5ddd}|jddd ||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 }
x| D ]\} dAdBdCdDdEdFdGdHdIdJdKdLg}|	st fdMdN|D rd}	|}
q|	rt fdOdN|D rd@}	|
}|j|  t
 d }t
 d }t
 d }x|dkr|dPkr|dkr|d krd|kr||}|dQkrdR|krdS|krP |d8 }|dkr|j|  t
 d }t
 d }t
 d }nP qFW |}|j|  t
 d }t
 d }t
 d }x|t|k r|dPkr||dkr||d kr|d|kr|||}|dQkr|dR|kr|dS|kr|P |d7 }|t|k r|j|  t
 d }t
 d }t
 d }nP qW |j|
d | }||d dTk }|d jd }||d dUk }|d jd }|d jd }tjj|||||||k||dV qW tdWdXdYS  tk
r } zt| tdZt
|dYS d }~X Y nX ntdZd[dYS ntdZd\dYS d S )]Nr   r"   )hhmmssf_to_secondsconductor_filer   r  r   z.xlsxz.csvr   r,  r   r  r  r  
r   T)regexzSource de DiffusionzMedia IDzVideo IDZTitreu   Duréerl   )r  how)r/  r+  u   Heure de début)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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} :FZIDPUBZIPMZJP20NJZJP20WJZF0283832ZJP20SSZJP20NSZJINRAMA1ZJINRAMA2ZJINRAMA3ZJINRAMA4ZIPGc             3   s   | ]}|t  d  kV  qdS )zVideo IDN)r=   )rL   r  )rZ   r0   r1   r$  "  s    z(AdsFromConductor.post.<locals>.<genexpr>c             3   s   | ]}|t  d  kV  qdS )zVideo IDN)r=   )rL   r  )rZ   r0   r1   r$  "  s    r   <   z00:07:00z00:09:00u   Début publicitéu   Fin Publicité)ad_break_start_atad_break_end_atdureeshow_before
show_afterif_show_duringr   r   SuccesszAds extracted successfully.)ry  r  r  z!Please upload a valid Excel file.zNo file uploaded.) r  r   r  r  r9   r   endswithr  r  rZ  r=   r   ZdropnaZreset_indexr  Zisnar  r/  ZfillnaZastypecontainsr  lstripanyrs   r.  r  r-   r  r   r;  rr   )rQ  r/   r  r  r  rQ   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_durationr>  r0   )rZ   r1   r<  "  s    
"


 8&
(


(
"zAdsFromConductor.postN)rS  rT  rU  r  r:   r<  r0   r0   r0   r1   r  "  s   r  c               @   s    e Zd ZdZdd Zdd ZdS )VerifsStaticInsertzverifs/index.htmlc             C   s   t || ji S )N)r   r  )rQ  r/   r0   r0   r1   r:   "  s    zVerifsStaticInsert.getc             C   s  d|j kr|j d }|jdrydd lm  m} tj|jd 	dd }|
|}| }g }ddi}x"|d|D ]}	|jd	}
|jd
}tjj|
|d }t| |jd}x|	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
 qW qW tdd|dS  tk
r } zt| tdt|dS d }~X Y nX ntdddS ntdd dS d S )!Nverifs_filez.verr   r   r   nsz&http://www.scte.org/schemas/118-3/201Xzns:CueMessager   r   )r   r  r   zns:Spotr   r   rD  rE  rF  r   r   r   +)	r   r  r   r   r   rD  rE  rF  r   r
  )
r   r  r   r   r   rD  rE  rS   r   rG  r  zVerifs Collected successfully.)ry  r  rd   r  )ry  r  z"Please upload a valid Verifs file.zNo file uploaded.)r  r   r  r  r   r  r  r   splitextr  rI  Zgetrootfindallr  r:   r   r-   r.   r#  rr   rv   r   rQ   r   r   rx   rM  r   r;  r=   )rQ  r/   r  r  Z	last_partr  r  rQ  	namespaceZcue_messageZnetwork_namer  r  Zbroadcast_daterR  Z
traffic_idspot_idZair_timeZ
air_lengthZair_status_coder   r>  r0   r0   r1   r<  "  sh    


"zVerifsStaticInsert.postN)rS  rT  rU  r  r:   r<  r0   r0   r0   r1   r  "  s   r  c             C   s  y\t 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}xt|D ]t\}}t|d}	|	dt |jdd d |	d|j tjj|d}
xt|
D ]\}}t|	d}|d t |jdd d |d!|j |d"d tjj|d#}xt|D ]\}}|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 | qZW qW qW t%|}tj&tj'(|d1d2 t)|d3}|j*|d4d1d5 W d Q R X d1|d6d7S  t+k
r } zd8d d9| d7S d }~X Y nX d S ):Nz%Y-%m-%dz%Y%m%dz/files/schedules/r   r   z.schr   r   r   zT00:01:00+00:00r   zT23:59:59+00:00r   r   r   r   r   r   z&http://www.scte.org/schemas/118-3/201Xr   z http://www.w3.org/2001/XMLSchemar   )r   r   r   r   r   z+00:00r   )r   r   r   r   r   )r   r"   z%H%M%S00r   r   r   r   r   r   r   r   r   r   r   T)r  r   zutf-8)r   r   z!Playlist File Created Succesfully)ry  r   r  Fz	Exeption ),r=   r	   r   r   r   r  r*  rr   r   rQ   r   rG   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   r6  r   r   r;  )r   r   Zcurrent_directoryr1  r  r  r  r   r  Zwindow_elemr  Zavail_indexr   Z
avail_elemr  Zadspot_indexr   r  Zadspot_elemr  r   r>  r0   r0   r1   r  E#  st    

L
  "
r  c             C   s0   ddl m} |tdd}|  t|  d S )Nr   )r   )ZLeilar,  >   i'  )targetargs)	threadingr   r:  ru  rr   r!  )r/   r   threadr0   r0   r1   	send_vast#  s    r  c             C   s    t j | d}|tdd }|S )Nz%Y-%m-%d %H:%M:%Sr"   )r;  )r	   r   r
   )r   	timestampZone_week_agor0   r0   r1   get_last_week#  s    r  c               @   s   e Zd Zdd ZdS )PlaylistVastc       
      C   s
  ddl m} t|jd}|d}|d}zdy8tjj||dd}tt	|j
d d	 }td
|j W n& tjk
r   d}tddi Y nX W d yBtdd|ii | d| }tjj||d }	td|	j W n" tjk
r   tddi Y nX X td|iS )Nr   )serializersZads_schedulez%Y-%m-%dz%H:%M:00z2M Maroc)rH   rJ   rC   i D r!  zAnalytic SRF: r  zSFR Not FoundZEPG	last_weekr   )r`  ra  zEPG: zEPG Not Foundr>   )Zdjango.corer  r  r9   r:   r   rp   r-   r   rO   rK   rr   rJ   r;   r  r.   r#  rw  r   )
rQ  r/   r  r  rR  Zlast_week_minutZanalytics_datar^  Zlast_week_epgZepg_datar0   r0   r1   r<  #  s,    

	zPlaylistVast.postN)rS  rT  rU  r<  r0   r0   r0   r1   r  #  s   r  c             C   s0   t j| |d}|jdkr,|j|_|  |S d S )N)r  r  )r  r:   r  rx  ry  r  )rR  r  replyr0  r0   r0   r1   alma_callback#  s
    
r  c          	      sf   t jjdd d}t jjdd}ddi|rbtdd(  fdd	|D }d
d	 |D }W d Q R X d S )Nz
2024-04-17)datetime_timestamp__icontainsry  )r  z
User-AgentzeMozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/103.0.0.0 Safari/537.36r$  )max_workersc                s    g | ]} j t|j|d qS ))r  r  )submitr  r  )rL   r  )executorr  r0   r1   rN   #  s    zanswer_Alma.<locals>.<listcomp>c             S   s   g | ]}|  qS r0   )rd   )rL   futurer0   r0   r1   rN   #  s    )r  r-   r.   r   )r/   Zupdated_repliesverify_repliesfuturesrd   r0   )r  r  r1   answer_Alma#  s    r  c             C   s(   ddl m} ddi}td | }d S )Nr"   )update_vastz
User-AgentzeMozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/103.0.0.0 Safari/537.36called)r7  r  rr   r8  )r/   r  r  Zupdaterr0   r0   r1   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0 d}t|ddd}||}tdd t|ddd}||}|dddg x| D ]\}}x|D ] fddt|D }	d}
xr|	D ]j}y<| }|
d7 }
tj	j
||j|
d}||j|
g W q tk
rB } z|W d d }~X Y qX qW qW qW W d Q R X W d Q R X W d Q R X W d Q R X d S )Nr   r  z
User-AgentzeMozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/103.0.0.0 Safari/537.36c                s>   ddl m}m} || }||j}tj|  d}t|j |S )Nr   )urlparseparse_qs)rR  r  )urllib.parser  r  rj  r  r:   rr   r  )rR  r  r  
parsed_urlquery_paramsr0  )r  r0   r1   make_request_lego>$  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Technic)r  z$/var/www/html/DAI27/media/Vast-3.csvr   r*   )newliner   r0  ZLinkzStatus CodezNumber of Requestsc                s   g | ]}  qS r0   )r  )rL   r  )r  r  rR  r0   r1   rN   v$  s    z(update_vast_response.<locals>.<listcomp>)rR  rR  r  request_count)csvr   r   r  writerowr_  r   rd   Trackinglinkr-   r  r  r;  )r/   r  r  Ztracking_linksZcsv_filenameZcsvfiler  rR  rW  r  r  r  r   Ztrakcingr>  r0   )r  r  r  rR  r1   update_vast_response$  s@    ,





r  c             C   s   ddl m} |  tdS )Nr"   )sample_taskz"Task is running in the background!)r7  r  r8  r   )r/   r  r0   r0   r1   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 Q R X d S )Nr   )r	   )rW  z%Y-%m-%d)r  tracking_start_statusr'  r(  r)  r  r  z
User-AgentzeMozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/103.0.0.0 Safari/537.36c                s  ddl m}m} || }||j}tj|  d}|jd kr|d}d| kr`|j|_|  nd| krz|j|_	|  nd| kr|j|_
|  nd| kr|j|_|  nld	| kr|j|_|  nRd
| kr|j|_|  n8d| kr
td|j |j|_|  n|j|_|  d S )Nr   )r  r  )rR  r  r!  Zjs_startZjs_first_quartileZjs_midpointZjs_third_quartilevast_impressionZdoubleclickZjs_completezCheck: )r  r  r  rj  r  r:   rx  r  r  r'  r(  r)  r  r  rr   r&  )rR  r  r  r  r  r  r0  Zevent_value)r  r0   r1   make_requests$  s<    









z.update_vast_response_v3.<locals>.make_requestsc          	      s.   i | ]&}|j |jgD ]}| ||qqS r0   )r	  r  r  )rL   r  rR  )r  r  r0   r1   rh  %  s   z+update_vast_response_v3.<locals>.<dictcomp>)
r	   ra  rz  rW  r   r   r  r-   r.   r   )r/   r	   ra  rW  r|  rx  r  r  r0   )r  r  r  r1   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 }x$|D ]}tjj|d}|| q8W |jj	|d	d

 }	|jj	| tdd dd	d

 }
t|d||	|
dS )Nr   )r	   r"   )r  z%Y-%m-%dzToo Good To Go 30szToo Good To Go 15s)r   F)datetime_timestamp__containsZ%impression_sprintserve_status__isnull)r   z!DAIManagementApp/vast_report.html)r\   r  Zprev_impression)r	   r   r  r   r   ry   r-   r:   rv   r.   r  r
   r   )rQ  r/   r	   r  rG  campaigns_listr\   r>  campaign_objZvast_total_impressionsZprev_vast_total_impressionr0   r0   r1   r:   &%  s$    
zVastReport.getNc             C   s   ddl m } ddlm} ddlm} |jd| d}|jd| d}|r|rt	d| t	d	| | j
||d
}t|ddS | d}	| tdd d}
| j
|
|	d
}t|ddS d S )Nr   )r	   )r  )	TruncDateru  z%Y-%m-%dr  zstart: zend: )ru  r  F)r  r  )r   zwe will get campaigns from z intel )r	   rz  r  r{  r  r9   r:   r   r   rr   get_campaignsr   r
   )rQ  r/   r  r	   r  r  ru  r  rd   rG  r  rQ  r0   r0   r1   r<  B%  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 }x<|D ]4}|d }	|d	 }
t
 j|	d
}||	|
|d qZW |S )Nr   )r  )r  )Zdatetime_timestamp__date__gteZdatetime_timestamp__date__lter  )r   r   )
date_countr  )r  )r   r  r\   )rz  r  r{  r  r  r-   r.   r  r.  rm   r   get_spots_impressionsrv   )rQ  ru  r  r  r  Zvast_querysetZvast_data_groupedrd   Z
date_groupr   r  rg   r0   r0   r1   r  [%  s&    
zVastReport.get_campaigns)N)rS  rT  rU  r:   r<  r  r0   r0   r0   r1   r  $%  s   
r  c               @   s   e Zd Zdd ZdS )	VastChartc             C   sJ   ddg}g }x$|D ]}t jj|d}|| qW t| t|dd|iS )NzToo Good To Go 30szToo Good To Go 15s)r   zDAIManagementApp/vast_call.htmlr\   )ry   r-   r:   rv   rr   r   )rQ  r/   r  r\   r>  r  r0   r0   r1   r:   %  s    
zVastChart.getN)rS  rT  rU  r:   r0   r0   r0   r1   r  %  s   r  c               @   s   e Zd Zdd ZdS )LoadCampaignc             C   sf   |j d}y2tjj|d}tjj|dd}tt|ddS  tj	k
r`   tddid	d
S X d S )Nr>  )r   )r  r   F)r  r<   zCampaign not foundi  )ry  )
r  r:   ry   r-   r   r.   r.  r   rn   r;   )rQ  r/   r>  r  spotsr0   r0   r1   r:   %  s    zLoadCampaign.getN)rS  rT  rU  r:   r0   r0   r0   r1   r  %  s   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 )NzDAIManagementApp/vast_call.htmlr\   )r   r  )rQ  r/   r0   r0   r1   r:   %  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}|r|r|dkrt
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"   )r+  r   )r   r  r   num_request)r   zCampaign name: zCampaign END DATE: r-  r.  zDessin AnimezAbtal albiharzar-MAr$  r/  rE   )r0  r1  r2  r3  r  r4  rQ   rR  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}})zToo Good To Go 30szToo Good To Go 15sz
User-AgentzeMozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/103.0.0.0 Safari/537.36zLink: )rR  r5  rr  rR  zDAIManagementApp/vast_call.htmlr\   )r7  r+  r  rQ  r   r9   r:   r   ry   r-   rr   rf  r8  r   r  )rQ  r/   r+  r  r   r>  r   r  r  rr  Zlink_mappingr  r9  r0   r0   r1   r<  %  s2    
zCallVast.postc             C   s:   ddddg}g }x$|D ]}t jj|d}|| qW |S )Nr  ZHarry_Potterr  ZStarWars)r   )ry   r-   r:   rv   )rQ  r  r\   r>  r  r0   r0   r1   r  %  s    
zCallVast.get_campaignsN)rS  rT  rU  r:   r<  r  r0   r0   r0   r1   r  %  s   :r  c             C   s,   t jjdd}tjj|d}t|  d S )NzLegoPorsche.ts)r   )r
  )r   r-   r:   r  r.   rr   r  )r/   rR  Zvastsr0   r0   r1   update_report%  s    r  )r   )r*   )r*   )r*   )r*   )rI   )r*   )r*   )rI   )rI   )rI   )rI   )rI   )r*   )r*   )ry  )r*   )r"   r"   )r*   )r*   )r*   )r*   )r*   )r*   )r*   )rI   )rI   )r*   )r*   )r*   )r*   r*   r*   )r*   r*   r*   (  r  r  r	  ra  r  loggingargparser	   r  r  mathr   r  r   r  r   r   r   ior   r   r
   Znumpy.lib.stride_tricksr   Zlxmlr   Zxml.dom.minidomZdomZminidomrH  r  r  r   r'  r   r   Zrest_framework.viewsr   r   rQ  r   djangor   rz  r   django.contribr   r~   r   r   Z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DBStorer5  r   r   r    r!   r   r$   r%   r&   r'   r(   	getLoggerrS  loggerrw   r2   r?   rA   r   ra   r{   r|   rz   r   r   r  r  r%  r1  r4  r5  r:  r?  rB  rU  rV  rm  r\  r  r  r  r  r  r  r  r  r  rN  r  r  r  r  r  r  r  r  r  r  r!  r  r"  r%  r8  r:  r/  r=  rH  rc  re  rf  rg  rh  ri  rl  rm  rn  r`  rs  rz  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  r1  r4  r9  r8  rM  rN  rV  rZ  rd  r7  rf  rl  rh  rm  r{  django.confrt  r  r>  rA  r  re  r  r  r  r  r  r  r  r  r  r  re   r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r*  r:  r  rH  rI  r@  Zdjango.viewsrV  rW  django.utilsrb  django.utils.dateparserX  rY  r  rZ  r   r  ry   r[  r\  r   r]  r^  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r0   r0   r0   r1   <module>   s,  
 e ]^	;#^ j b"<9M6 H 6  7 Aw R>AP.A~
 t'`	 !7 7+; @ $8Dm 
zohn 
	
		'8 do:GRHNL rz^X*   ? ' jW^(
	 @[[J