U
    &h                    @   sp  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mZ d dl	m
Z
mZmZ d dlmZ d dlmZ d dlmZ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mZm Z m!Z! d dl"m#Z# d dl$m%Z% d dl&m'Z'm(Z( d dl)m*Z* d dl+m,Z, d dl-m.Z.m/Z/m0Z0 d dl1m2Z2m3Z3m4Z4 d dl5m6Z6m7Z7m8Z8 d dl9m:Z:m;Z;m<Z<m=Z=m>Z>m?Z? G dd deeZ@edd ZAed@ddZBedAddZCedBddZDedCd d!ZEed"d# ZFed$d% ZGedDd&d'ZHed(d) ZIed*d+ ZJed,d- ZKed.d/ ZLedEd0d1ZMedFd2d3ZNed4d5 ZOed6d7 ZPed8d9 ZQed:d; ZRed<d= ZSed>d? ZTdS )G    N)renderredirect)HttpRequestHttpResponseJsonResponse)messages)LoginRequiredMixin)	Paginator	EmptyPagePageNotAnInteger)View
connection)timezone)login_required)CountSumFQ)Substr)get_client_ip)NoVastResult
VastResult)Verifs)Activity)ChannelChannelZoneChannelZoneRelation)Impressions	AnalyticsAnalyticsSource)BrandAgency
Advertiser)	CampaignsCampaignStatusAdspotsDayTime	PlacementAdspotsStatusChoicesc                       s   e Zd ZdZdgZdZdddddd	Z fd
dZdd Zdd Z	dd Z
dd Zdd Zdd Zdd Zdd Zdd Zdd Zd d! Zd"d# Zd$d% Zd&d' Zd(d) Zd*d+ Zd,d- Zd.d/ Zed0d1d2d3Zeed1 fd4d5Z  ZS )6CampaignsListViewa  
    Campaigns List View

    This view is responsible for rendering the campaigns list page of the application.
    It extends the Django View class and defines the HTTP GET method to handle
    the request and return the rendered campaigns list page template.

    Methods:
        get(request): Handles the HTTP GET request and returns the rendered
            campaigns list page template.

    Template:
        - 'index.html': The HTML template used to render the dashboard page.

    getzcampaigns/campaign/list.htmlzCampaigns - Adtlasz Adtlas Development TeamznCampaigns for Adtlas TV advertising solutions. This page provides insights into your TV advertising campaigns.zRAdtlas, campaigns, TV advertising solutions, campaign insights, data visualization	campaigns)titleauthordescriptionkeywordsZactive_menuc                    s$   t  jf |}|dtdi |S )z2
        Add dashboard page context data.
        r-   r$   )superget_context_dataupdate_)selfkwargscontext	__class__ //var/www/html/Focus/src/apps/campaigns/views.pyr2   =   s     z"CampaignsListView.get_context_datac           	      O   s   |j ddkr| |S tjj|jdd}t|| 	|}|j
dd}z||}W n< tk
rz   |d}Y n  tk
r   ||j}Y nX | | | |||}t|| j|S )z
        Handles the HTTP GET request and returns the rendered dashboard page template.

        Args:
            request (HttpRequest): The HTTP request object.

        Returns:
            HttpResponse: The rendered dashboard page template.
        zX-Requested-WithXMLHttpRequestcampaign_manager-created_atpage   )headersr+   _handle_ajax_requestr$   objectsfilteruserorder_byr	   _get_items_per_pageGETget_pager   r
   	num_pages_log_user_activity_build_contextr   template_name)	r5   requestargsr6   queryset	paginatorpage_numberpage_objr7   r:   r:   r;   r+   G   s$    

zCampaignsListView.getc                 C   sh   |j }|j|||jdk| || || || || ||  | 	|| 
 |j|j|jd}|S )a  
        Build template context with all necessary data.
        
        Args:
            request: Django HTTP request object
            page_obj: Paginated page object
            paginator: Paginator instance
            
        Returns:
            dict: Template context
        rA   )r,   rT   rR   is_paginatedchannelszonesagenciesadvertisersfilter_paramssort_optionsstats
date_rangetotal_resultscurrent_pagetotal_pages)rF   object_listrK   _get_user_channels_get_available_zones_get_agencies_get_advertisers_get_current_filters_get_sort_options
_get_stats_get_date_rangecountnumber)r5   rO   rT   rR   rF   r7   r:   r:   r;   rM   o   s$    z CampaignsListView._build_contextc                 C   s   t jjdd ddS )z
        Get zones available to the current user.
        
        Args:
            user: Current user object
            
        Returns:
            QuerySet: Available zones
        T)	is_activenamecode)r   rD   rE   distinctrG   r5   rF   r:   r:   r;   rc      s    

 z&CampaignsListView._get_available_zonesc                 C   s   t j dS )z
        Get channels available to the current user.
        
        Args:
            user: Current user object
            
        Returns:
            QuerySet: User's channels
        rm   )r   rD   allrG   rp   r:   r:   r;   rb      s    z$CampaignsListView._get_user_channelsc                 C   s   t jj|ddS )z
        Get agencies available to the current user.
        
        Args:
            user: Current user object
            
        Returns:
            QuerySet: Available agencies
        )ownerrm   )r"   rD   rE   rG   rp   r:   r:   r;   rd      s    
zCampaignsListView._get_agenciesc                 C   s   t jj|ddS )z
        Get advertisers available to the current user.
        
        Args:
            user: Current user object
            
        Returns:
            QuerySet: Available advertisers
        id_userbrand__name)r#   rD   rE   rG   rp   r:   r:   r;   re      s    
z"CampaignsListView._get_advertisersc                 C   s   |j dd|j dd|j dd|j dd|j dd|j dd|j d	d|j d
d|j dd|j dd|j dddS )z
        Get current filter parameters for template.
        
        Args:
            request: Django HTTP request object
            
        Returns:
            dict: Current filter values
        search channelrq   agency
advertiserzonestatus	date_fromdate_tor]   sort_byr?   per_page20)rv   rx   ry   rz   r{   r|   r}   r~   r]   r   r   )rI   r+   )r5   rO   r:   r:   r;   rf      s    z&CampaignsListView._get_current_filtersc                 C   s   ddddddgS )zv
        Get available sorting options.
        
        Returns:
            list: Sort options for template
        r?   zNewest First)valuelabel
created_atzOldest Firstr:   )r5   r:   r:   r;   rg      s    z#CampaignsListView._get_sort_optionsc              
   C   s   t jj|d}t  }|tjdd }| |jt	j
|d |jt	j|d |j|d |j|d |j|d |jt	j
|d |j|d d	S )
z
            Get campaigns statistics for dashboard.

            Args:
            user: Current user object

            Returns:
            dict: Campaigns statistics
        r=      days)r|   end_date__gte)end_date__lt)end_date)r   )r|   end_date__gt)total_campaignsactive_campaignspending_campaignsexpired_campaignstodays_campaignsrecent_campaignsupcoming_campaignspast_campaigns)r$   rD   rE   r   nowdatedatetime	timedeltarj   r%   In_progress	Prebooked)r5   rF   base_querysettodayweek_agor:   r:   r;   rh      s6    zCampaignsListView._get_statsc              	   C   sZ   t   }||tjdd |tj| d |jdd|tjdd |tjdd dS )zz
        Get date range information for filtering.
        
        Returns:
            dict: Date range options
        rA   r   )dayim  )r   	yesterday
week_startmonth_startmin_datemax_date)r   r   r   r   r   weekdayreplace)r5   r   r:   r:   r;   ri     s    
z!CampaignsListView._get_date_rangec                 C   sX   |j d}|dkr| |S |dkr0| |S |dkrB| |S |dkrT| |S dS )z
        Handle AJAX requests for dynamic updates.
        
        Args:
            request: Django HTTP request object
            
        Returns:
            JsonResponse: JSON response with data
        action	get_zonesget_agenciesget_advertisersquick_statsN)rI   r+   _get_zones_for_channel_ajax_get_agencies_ajax _get_advertisers_for_agency_ajax_get_quick_stats_ajax)r5   rO   r   r:   r:   r;   rC   +  s    



z&CampaignsListView._handle_ajax_requestc              	   C   s~   |j d}|stdddS z4t|}tjj|dddd}td	t|d
W S  t	t
fk
rx   tddd Y S X dS )z
        Get zones for a specific channel via AJAX.
        
        Args:
            request: Django HTTP request object
            
        Returns:
            JsonResponse: JSON response with zone data
        
channel_idFzChannel ID requiredsuccesserror)id_channel_idid_zone_channelnetworknameregionT)r   rW   zInvalid channel IDN)rI   r+   r   intr   rD   rE   valueslist
ValueError	TypeError)r5   rO   r   rW   r:   r:   r;   r   G  s$    
  
z-CampaignsListView._get_zones_for_channel_ajaxc                 C   s*   |j }| |}tdt|dddS )z
        Get agencies available to the current user via AJAX.
        
        Args:
            request: Django HTTP request object
            
        Returns:
            JsonResponse: JSON response with agency data
        T	id_agencyrm   )r   rX   )rF   _get_available_agenciesr   r   r   )r5   rO   rF   rX   r:   r:   r;   r   c  s    

z$CampaignsListView._get_agencies_ajaxc                 C   s   t jj|dS )z
        Get agencies available to the current user.
        
        Args:
            user: Current user object
            
        Returns:
            QuerySet: QuerySet of available agencies
        rs   )r"   rD   rE   rp   r:   r:   r;   r   u  s    
z)CampaignsListView._get_available_agenciesc              	   C   s   |j }|jd}|s$tdddS z2t|}tjj|d}tdt|	ddd	W S  t
tfk
r|   tdd
d Y S X dS )z
        Get advertisers available to the current user via AJAX.
        
        Args:
            request: Django HTTP request object
            
        Returns:
            JsonResponse: JSON response with advertiser data
        	agency_idFzAgency ID requiredr   )brand__agency__idTidrm   )r   rY   zInvalid agency IDN)rF   rI   r+   r   r   r#   rD   rE   r   r   r   r   )r5   rO   rF   r   rY   r:   r:   r;   r     s    

z2CampaignsListView._get_advertisers_for_agency_ajaxc                 C   s   |j }| |}td|dS )z
        Get quick statistics via AJAX.
        
        Args:
            request: Django HTTP request object
            
        Returns:
            JsonResponse: JSON response with statistics
        T)r   r\   )rF   rh   r   )r5   rO   rF   r\   r:   r:   r;   r     s    

z'CampaignsListView._get_quick_stats_ajaxc                 C   s,   |  |}tdd|  dd| dS )z
        Export playlist data via AJAX.
        
        Args:
            request: Django HTTP request object
            
        Returns:
            JsonResponse: JSON response with export status
        TzExport prepared for z
 playlists#)r   messagedownload_urlrj   )_get_filtered_querysetr   rj   )r5   rO   rQ   r:   r:   r;   _export_campaigns_data_ajax  s    
z-CampaignsListView._export_campaigns_data_ajaxc              	   C   sD   z$t |jdd}tdt|dW S  ttfk
r>   Y dS X dS )z
        Get number of items per page from request or default.
        
        Args:
            request: Django HTTP request object
            
        Returns:
            int: Items per page
        r      
   d   N)r   rI   r+   maxminr   r   )r5   rO   r   r:   r:   r;   rH     s
    
z%CampaignsListView._get_items_per_pageN)rO   returnc              
   C   sB   |j }tj|dd|r|jnd d|t||jddi d dS )	aZ  
        Log user activity for auditing purposes.
        
        Records the user's action with relevant metadata including IP address,
        user agent, and timestamp for security and analytics purposes.
        
        Args:
            request (HttpRequest): The HTTP request object
            
        Returns:
            None
        views_campaignsUser Systemz performed views_campaignsHTTP_USER_AGENTrw   rF   r   r/   rO   
ip_address
user_agentmetadataN)rF   r   log_activityemailr   METAr+   )r5   rO   rF   r:   r:   r;   rL     s    z$CampaignsListView._log_user_activityc                    s   t  j|f||S )a0  
        Override dispatch method for additional request processing.
        
        This method is called before any HTTP method handler. Useful for
        adding common logic like additional authentication checks, rate limiting,
        or request preprocessing.
        
        Args:
            request (HttpRequest): The HTTP request object
            *args: Variable length argument list
            **kwargs: Arbitrary keyword arguments
            
        Returns:
            HttpResponse: Result from the appropriate HTTP method handler
        )r1   dispatch)r5   rO   rP   r6   r8   r:   r;   r     s    zCampaignsListView.dispatch)__name__
__module____qualname____doc__http_method_namesrN   extra_contextr2   r+   rM   rc   rb   rd   re   rf   rg   rh   ri   rC   r   r   r   r   r   r   rH   r   rL   r   r   __classcell__r:   r:   r8   r;   r*   #   s<   
())r*   c           #      C   s  | j }tj }| 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}t	 }d}|dkrd}n:|
t|	d}|
t|
d}t|tt|| j }t|tjj|dtjj|d||||	|
t|t|t|t|t||||t||dtjd}|  |dkrH|D ]Z}| jdt|j }|d kr|D ].}tjjt|d}t|||d}|  q̐qtj|dd|r|jnd d| t| | jddd|jid  td!S | jd"}|d kr|D ]*}tjjd#d$}t|||d%}|  qbtd!S tjj|dd&}t jj|d'}tjj|dd(} t!jj|d)jd*d+"d }!tjj#d#d$}| |||||!d,}"t$| d-d.|"iS )/NPOSTcampaign_namer   id_brandproduct_nameid_category
id_country	start_dayend_daybudgetvolumecpmbroadcasts_daygeneral_rotation
media_typeprogram_categoriespacingrw   0r   %Y-%m-%dpk)rm   r   r   productcategorycountryr   r   r   r   deliveryr   r   r   program_categoryr   r   rt   bookedr|   check_placement_id_timeid_campaign
id_channeladd_campaignr   r   z performed add_campaignr   r   r   campaigns:views_campaignschannels_checked   r   r   r   r   rr   
is_deletedZchannel__inZagency__ownerZagency__is_deletedrs   1r|   )brandsry   adspotsrV   r   advertisers_for_creativezcampaigns/campaign/add_new.htmldata)%rF   r   rD   rq   methodr   r+   getlistr   r   strptimestrr   r   r$   r!   r"   floatr%   r   saver   r'   r(   r   r   r   r   r   r   r   rE   r&   r#   select_relatedexcluder   )#rO   rF   rV   rm   r   r   r   r   r   
start_dater   r   r   r   r   rotationr   r   r   r   r   start_date1	end_date1campaignrx   channel_placementsplacement_of_channeltime	placementr   rX   r
  rY   r  r  r:   r:   r;   r     s    


 
 r   c                 C   sX  | j }|d ks|dkrtdS tjj|d}tjj|jjd}tjj|j	jd}t
jj|d}tjj|d}tjjdd}tjj|jd	d
}	dd t|	D }	i }
g }d}|D ]X}tjj|j|dd}dd t|D }|j|j|d|
|< ||
|  |d7 }qtjj|jd	d}dd t|D }|||||	||d}t| dd|iS )Nrw   r   r   r   rs   r  r   r  r   r   c                 S   s   g | ]}|d  qS r   r:   .0xr:   r:   r;   
<listcomp>  s     z!view_campaign.<locals>.<listcomp>r   r   r   r   c                 S   s   g | ]}|d  qS r   r:   r!  r:   r:   r;   r$    s     channel_namer   r  rA   c                 S   s   g | ]}|d  qS r   r:   r!  r:   r:   r;   r$    s     )r  agency_by_brandrV   r   placement_channelchannels_and_placementr  zcampaigns/campaign/view_id.htmlr  )rF   r   r$   rD   r+   r!   r   r   r"   ry   r   rE   r&   r'   r  r(   r   values_listr   r'  r   appendr   )rO   r   rF   r  brands_for_agencyr(  rV   r
  r   r)  r*  tab_channels_and_placementch_nrx   r  r  r:   r:   r;   view_campaignu  s>    
	r0  c           (      C   s  | j }| jdkrb| 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r0|	d kr0|
d kr0tt|	d}tt|
d}t|tt|| j	 }nd}t
jj|d}|rX||jkrX|n|j|_|dkr|d kr||jkr|n|j|_|dkr|d kr||jjkrtjj|dn|j|_|dkr|d kr||jjkrtjj|dn|j|_|dkr |d kr ||jkr |n|j|_|dkrL|d krL||jkrL|n|j|_|dkrx|d krx||jkrx|n|j|_|	dkr|	d kr|	|jkr|	n|j|_|
dkr|
d kr|
|jkr|
n|j|_|dkr|d krt|rt||jkrt|n|j|_|dkrL|d krLt|rLt||jkrLt|n|j|_|dkr|d krt|rt||jkrt|n|j|_|dkr|d krt|rt||jkrt|n|j|_|dkr|d krt|rt||jkrt|n|j|_|dkr2|d kr2||jkr2|n|j|_|dkr^|d kr^||jkr^|n|j|_|dkr|d kr||j kr|n|j |_ |dkr|d krt|rt||j!krt|n|j!|_!|"  tjj|jjd}tjj|j#jd}t$jj%|d}t&jj%|d}t'jj(dd}t)jj%|j*d+d}dd t,|D }i }g }d}|D ]Z} t)jj%|j*| d +d!}!d"d t,|!D }!| j-| j.|!d#||< |/||  |d$7 }q^t)jj%|j*d+d!}!d%d t,|!D }!t0j1|d&d'|r|j2nd( d)| t3| | j4d*dd+|j*id, |||||||!tjj%|dd-tjj%|dd.d/	}"t5| d0d1|"iS t
jj%|d6d2}#tj7 }$g }%|#D ]<}&z(t|&jd}'|$|'kr|%/|&j* W n   Y nX qt5| d3|#|%d4S )5Nr   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   rw   r  r   r   r   r  rs   r  r   r  r  r   c                 S   s   g | ]}|d  qS r   r:   r!  r:   r:   r;   r$    s     z!edit_campaign.<locals>.<listcomp>r%  r   c                 S   s   g | ]}|d  qS r   r:   r!  r:   r:   r;   r$    s     r&  rA   c                 S   s   g | ]}|d  qS r   r:   r!  r:   r:   r;   r$    s     edit_campaignr   r   z performed edit_campaignr   r   r   r  r  )	r  r(  rV   r   r)  r*  r  rX   rY   zcampaigns/campaign/edit_id.htmlr  -id_campaignzcampaigns/campaign/edit.html)r,   start)8rF   r  r   r+   r  r   r  r  r   r   r$   rD   r   rm   r   r   r!   r   r"   r   r   r   r   r   r  r   r   r   r   r   r   Zprogram_categorysr   r   r  ry   r   rE   r&   r'   r  r(   r   r+  r   r'  r   r,  r   r   r   r   r   r   rG   r   )(rO   r   rF   rm   r   r   r   r   r   r  r   r   r   r   r   r  r   r   r   r   r  r  r  r-  r(  rV   r
  Z	datetime1r)  r*  r.  r/  rx   r  r  r,   r   r3  ir   r:   r:   r;   r1    s    ,88,,,,,>>>>>,,,> J


r1  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}| jd}tjj|d}d}tjj|d}||_||_||_	|	|_
||_||_t|
|_||_z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}|  |D ]Z}| jdt|j }|d kr|D ].}tjjt|d}t|||d}|  qqnd| jd}t | tjj|d}|  |d krP|D ]*}tjjdd}t|||d}|  q$t!d|dS d S )Nr   r   r   r   r   	id_adpostr   r   r   r   r   r   r      r   zEdite CampaignzAdmin Edit Campaign  id: activityr   r/   rt   rs   r   r  r   r   r   r   r  r  zcampaigns:view_campaign)"r  r$   rD   r+   r   r!   r"   rm   id_advertiserr   r   r   r   r   r   r   r   r  r   r  r   r   r  r   r   rE   sessionr(   deleter  r   r'   printr   )rO   r   r  rm   r   r   r5  r  r   r   r   r   r   r  brandadpostry   r   activitedescr8  rV   prx   r  r  r  r  r   r:   r:   r;   update_campaignw  sp    





rB  rw   c                 C   s   | j dkr\tjj|d}|jdk|_|  tj }d}dt| }t	|||d}|  tjj
| jd dd	}t| d
d|iS )Nr   r   FzDisable CampaignzAdmin Disable Campaign  id: r7  rt   )
id_user_idr2  zcore/disable_campaign.htmlr,   )r  r$   rD   r+   r   r  r   r   r  r   rE   r:  rG   r   )rO   r   r  r   r?  r@  r8  r,   r:   r:   r;   disable_campaign  s    

rD  c                 C   s0   | j d}tjd| d }t| dd|iS )Nrz   z(SELECT * FROM Adspots where id_brand = "z" GROUP BY ad_groupz#core/dropdown_adspots_campaign.htmlr
  )rI   r+   r&   rD   rawr   )rO   advertiser_idr
  r:   r:   r;   load_creatives  s    rG  c                 C   s8   | j d}tjj|tjdd }t	| dd|iS )Nrx   )rx   r|   r=  zcampaigns/dropdown_adspots.htmlr
  )
rI   r+   r&   rD   rE   r)   ACTIVEr  rq   r   )rO   r   r
  r:   r:   r;   load_adspots  s    rI  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}||	}|D ]*}|d d krt| q|t|d 7 }qt| d||dS tdS d S )NzEurope/Parisi  r   rA   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   r:   )r"  colr:   r:   r;   r$  #  s     z9campaign_report.<locals>.dictfetchall.<locals>.<listcomp>c                    s   g | ]}t t |qS r:   )dictzip)r"  rowcolumnsr:   r;   r$  $  s   )r/   fetchall)cursorr:   rN  r;   dictfetchall!  s    
z%campaign_report.<locals>.dictfetchallrw   r   r   %-Ztotal_volumezcore/report_campaign.html)r  totalzcampaigns:edit_campaign)pytzr   r   r   	utcoffsettotal_secondsr$   rD   r+   	django.dbr   rQ  r   
brand_namer   r   r   executer<  r   r   r   )rO   r  Zparis_tzZ
paris_timeZ
utc_offsetqueryrR  r  r   rQ  
data_tuplerU  rM  r:   r:   r;   campaign_report  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   rx   )r   r   c                 S   s   g | ]}|d  qS r   r:   r"  r4  r:   r:   r;   r$  ]  s     z&channels_campaigns.<locals>.<listcomp>)Zid_campaign__inend_day__ltr,   zcore/dropdown_compaigns.html)r   r   rI   r+   r(   rD   rE   r+  r   r$   r   )rO   r   r   rx   r,   r  r:   r:   r;   channels_campaignsV  s    ra  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 )Nrt   rs   rm   r   z-volumerA   z-cpm   zcore/stats_campaign.html)r   activer	  cpms)
r$   rD   rE   r:  r+  rG   campaign_pacingcampign_brandcampaign_avtiver   )rO   r  r   r	  rc  rd  r:   r:   r;   statscampaignc  s    "rh  c              
   C   sX   | j }tj }tj|dd|r$|jnd d| t| | j	ddi d t
| dd	|iS )
Nviews_creativer   r   z performed views_creativer   rw   r   zcampaigns/creative/views.htmlr
  )rF   r&   rD   rq   r   r   r   r   r   r+   r   )rO   rF   r
  r:   r:   r;   ri  p  s    
	ri  c              
   C   s  | j }| jdkrҐzt| jd}| jdd}| jdd}|sXt| d tdW S ztjj|d}W n, tj	k
r   t| d	 td Y W S X d
}d| j
kr| j
d }|}t||t|dr|jnd
d
|tj|r|nd
dd}|rt|j\}	}
|	|_|  zDtj|dd|r"|jnd d| t| | jddd|jid W n   Y nX t| d|jpvd|j  d tdW S  tk
r } z&t| dt|  td W Y S d
}~X Y nX n,tjj|dd}dd|ii}t | d|S d
S ) zq
    View to handle adding new ad spots/creatives.
    Updated to work with the new Adspots model structure.
    r   campaign_idrm   rw   vast_urlzCampaign is required.zcampaigns:add_creativer   z!Selected campaign does not exist.Nadspot_filer=  T)rm   r  r=  rx   original_filer|   url_from_vastrl   add_creativer   r   z performed add_creativer   adspotr   z
Creative "zAd Spot z " has been created successfully.zcampaigns:views_creativezError creating creative: rs   r   r  r,   zcampaigns/creative/add.html)!rF   r  r   r+   r   r   r   r$   rD   DoesNotExistFILESr&   hasattrr=  r)   DRAFT	mimetypes
guess_typerm   	mime_typer  r   r   r   r   r   r   r   	Exceptionr  rE   r  r   )rO   rF   rj  rm   rk  r  rm  uploaded_filerp  rw  r4   er,   r7   r:   r:   r;   ro    st    



 
$
  ro  c                 C   s   | j }| jdkr>tjj|d}tjj|d}t| d||dS tjj| jd dj	ddd	}tjj|d

d}t| dd|iS d S )Nr   r   rs   zcampaigns/creative/edit_id.html)Zadspostr,   rt   r   T)flat)Zcampaign__inz
-id_adpostzcampaigns/creative/edit.htmlr
  )rF   r  r&   rD   r+   r$   rE   r   r:  r+  rG   )rO   r5  rF   r>  r,   r
  r:   r:   r;   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zrt| 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 )#Nr   r   rj  adspot_nameadspot_duration
active_0_1creative_idr   rF   r4   :rT  adspots/user_rt   
/campaign_	campaign___sec__.ts.m3u8static/Texist_okrl  /a	#EXTM3U 
#EXT-X-VERSION:3 
#EXT-X-MEDIA-SEQUENCE:0 
#EXTINF:, 
r   rw   )r  r&   rD   r+   r   r$   r   r   r  r   r:  slugifyosmakedirsrr  handle_uploaded_fileopenwritecloseshutilpathexistsoriginal_filepathmove)rO   r5  creativerj  r}  r~  r  	vast_infor   rF  r  r   
now_infiler  filenamefilename_m3u8fr  r:   r:   r;   update_creative  sF    
0,



&r  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rzrt| 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 )$Nr   r   rj  r}  r~  r  r  r  r4   r  rT  r  rt   r  r  r  r  r  r  r  Tr  rl  r  r  r  r  r  r  r  r   zEdit CreativezAdmin Edit creative  id: r7  zcampaigns:edit_creative)"r  r&   rD   r+   r   r$   r   r   r  r   r:  r  r  r  rr  r  r  r  r  r  r  r  r  r  r  r   r}  durationr|   r  r   r   r  r   )rO   r5  r  rj  r}  r~  r  r  r  r   r  r  r  r  r  r  r?  r@  r8  r:   r:   r;   r3   5  s^    	
0,



&r3   c              	      sT  t jj|d}tj }dt| jj }tj	
|s@t| |d|j |d d 7 }tdd}d|j |d d |d	< t|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r|jjnd |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d/ |d.d|jdk t jj!|d0"d1}	d2d3 t#|	D }	t$jj!|	d4"d5d6}
d7d3 |
D }d8d3 |
D }|d9dd: |d9dt|%d;d |dd<d=| |ddd>| |ddd?| |ddd@| |dddA| |jrH|jj}t&jj!|dBdC"dDdEdF}t'j(|dGdHdIgdJ}dKd3 |dH D }dLd3 |dGdHg j)D }
|
|dG< ||dH< dMd3 |dI D }dNd3 |dI D }||dO< ||dP< |j*dIdQddR dSd3 |dP D |dP< g }|j)D ]2}|+t,jj|dT |d |d |dU dVj- q$dWd3 |D }||dX< t.jjdYd  fdZd3|dX D }||dX< dT}|j)D ]|}|d| d<|dT  |d| d|d  |d| d|d  |d| d|dU  |d| d|d[  |d7 }q|ddd\| |ddt/t0| |1  |S )]Nr   zexcel/user_r  z%d_%m_%Yz.xlsxzAapplication/vnd.openxmlformats-officedocument.spreadsheetml.sheet)content_typezattachment; filename=zContent-DispositionZ	in_memoryTrA   z#acbbfezAdtlas Reporting | z cash [ z to z ] r   INFO   zCampaign Name   	   r"   r   r#      Creativerw   r   ZPERIOD   z
Start date   zEnd date   ZBOOKED   ZVolume   ZPacingr   ZCPM   Z	PLACEMENT   zGeneral Retationr  r   c                 S   s   g | ]}|d  qS r   r:   r!  r:   r:   r;   r$    s     zgenExcel.<locals>.<listcomp>id_channel__inr'  sfr_channel_namec                 S   s   g | ]}|d  qS r   r:   r_  r:   r:   r;   r$    s     c                 S   s   g | ]}|d  qS )rA   r:   r_  r:   r:   r;   r$    s        Channels'   CHANNELZREGIONDAYMINUTEZIMPRESSIONS0001)spotIdairStatuscoder   zonenameairTimer   ZRegionTimerN  c                 S   s   g | ]}t jj|d jqS ))r  )r   rD   r+   r   r_  r:   r:   r;   r$    s     c                 S   s    g | ]}t jj|d  djqS )r   )r'  )r   rD   r+   r  r_  r:   r:   r;   r$    s     c                 S   s   g | ]}| d d qS )r  r   splitr_  r:   r:   r;   r$    s     c                 S   s   g | ]}| d d qS )r  rA   r  r_  r:   r:   r;   r$    s     DayMinuterO  )axisZinplacec                 S   s   g | ]}|d d d qS )NZ00r:   r_  r:   r:   r;   r$    s     r      )r  r   r   minutec                 S   s   g | ]}t |qS r:   )r  r_  r:   r:   r;   r$    s     Purcentr  c                    s(   g | ] }t |t  j t  j qS r:   )r  total_usersmarket_share_purcentr_  impr:   r;   r$    s     rb  zTOTAL IMPRESSIONS)2r$   rD   r+   r   r   r  rF   r   r  r  r  r  rm   strftimer   
xlsxwriterZWorkbookZadd_worksheetZ
add_formatZset_patternZset_bg_colorr  r   r   r   ry   r5  r}  r   r   r   r   r(   rE   r+  r   r   r   r   pd	DataFramer   dropr,  SfrAnalyticspurcentr   r   sumr  )rO   r   infor   r  responseZworkbookZ	worksheetZcell_formatr)  rV   Zchannels_namechannels_sfrrm   verifsdfr   r   r  r  r4  jr:   r  r;   genExcel  s    




,
0
r  c           
         s   t d ddlm} tjj| jd dd}dd |D }tjj|d	d
	dj
|ddd}dd ld  fddtt|D }g }g }|D ] }||d  ||d  qt | t | |||d}t| jd }	t| d||	dS )NZjjjjjjjjjjjjjjjjjjjjjjjjjjr   )r   rt   rs   r'  c                 S   s   g | ]}|d  qS r   r:   )r"  chr:   r:   r;   r$    s     z!statscreative.<locals>.<listcomp>r  )Znetworkname__inr  r  )dcountz-dcount0123456789ABCDEFc              	      s"   g | ]}d d  d qS )r   rw   r  )joinsampler_  charsrandomr:   r;   r$    s     r  )Zlebelr  colorzcampaigns/creative/stats.html)r  r	  )r<  django.db.modelsr   r   rD   rE   r:  r+  r   r   annotaterG   r  rangelenr,  	ads_brandr   )
rO   r   rx   rp  r  r   r  adr  r	  r:   r  r;   statscreative  s*    (r  c                 C   s2   t jj| jd d}tjj|d}t| dd|iS )Nrt   rs   r  z$core/dropdown_creative_campaign.htmlr
  )r   rD   rE   r:  r&   r   )rO   rV   r
  r:   r:   r;   load_creative/  s    r  c              
   C   s&  | j d}| j d}| j d}| j d}| j d}tj }t|dd}|dd	}d
t| jd  d t| }tj	d| st
d|  |dd	}	dt| d | d t| d |	 d }
t|
 t| jd d| |
 t||||||d |
 ||
d}|  t| dS )Nr   rF  r}  r~  r  r  r4   r  rT  r  rt   z/ch_r  .Zch_r  r  r  rl  r  )r   Zid_brand_idr}  r  r|   r  r   r  z"core/dropdown_agency_campaign.html)r   r+   r   r   r  r   r:  r  r  r  r  r<  r  rr  r&   r  r   )rO   r   rF  r}  r~  r  r   r  r  Znew_now_infiler  r  r:   r:   r;   send_creative6  s$    
, r  c                 C   s  | j }| 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 }|D ]}|| j|j qt	jj|d}t
jj|d}t||||||t|t|	t|
d	}|  |j }d}dt|j }t|||d}|  t|j tjjt|jd}|D ]2}|d kr`tjj|d}t||d}|  q`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 )Nr   r   r   r5  r   r   r   r   r   r   r   r   )	rm   r9  r   r   r   r   r   r   r   zAdd CampaignzAdmin Add Campaign  id: r7  )r   r   zcampaigns/campaign/viwes_.htmlr,   rt   rs   r  r  r  )r	  r
  r   zcore/add_campaign.htmlr  )rF   r  r   r+   r'   rD   rq   r,  rm   r!   r&   r$   r   r  r  r   r   r  r   r   r<  r(   r   r#   rE   r:  r   r  )rO   rF   rm   r   r5  r   r  r   r   r   r   r  r  r4  r=  r>  r  r   r   r?  r@  r8  rA  r	  rV   r
  r  r:   r:   r;   add_campaign_oldM  sp    





r  )N)N)N)rw   )rw   )N)N)Ur  r   ru  r  pandasr  django.shortcutsr   r   django.httpr   r   r   django.contribr   django.contrib.auth.mixinsr   django.core.paginatorr	   r
   r   django.viewsr   rY  r   django.utilsr   django.contrib.auth.decoratorsr   r  r   r   r   r   django.db.models.functionsr   apps.common.utilsr   apps.campaigns.utilsr   r   apps.playlists.modelsr   apps.activities.modelsr   apps.channels.modelsr   r   r   Zapps.reports.modelsr   r   r    Zapps.agencies.modelsr!   r"   r#   apps.campaigns.modelsr$   r%   r&   r'   r(   r)   r*   r   r0  r1  rB  rD  rG  rI  r^  ra  rh  ri  ro  r|  r  r3   r  r  r  r  r  r:   r:   r:   r;   <module>   s       e
o+ VZ


_



U"9
O
 


