o
    I@h#                     @   sv   d 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
Z
ddlmZmZmZ ddlmZ G d	d
 d
ZdS )u   
StatsService — один-единственный класс, который:
  • пишет события в activity_log  (log)
  • выдаёт агрегаты для Dashboard’а
    )Workbook)get_column_letter)datetime	timedelta)StringIOBytesION)AnyDictList)get_db_connectionc                
   @   s   e Zd ZeddedededB ddfddZedeeef fdd	Zeddede	e
eef  fddZeddede	e
eef  fddZede
eef fddZeddede	e
eef  fddZed dedefddZed dedefddZdS )!StatsServiceNuser_idactionobj_inforeturnc              	   C   s~   t  2}| }|d| ||f |  W d   n1 s w   Y  W d   dS W d   dS 1 s8w   Y  dS )u*   Пишем событие в activity_log.zFINSERT INTO activity_log (user_id, action, obj_info) VALUES (%s,%s,%s)N)r   cursorexecutecommit)r   r   r   conncur r   +/var/www/html/app/services/stats_service.pylog   s   
PzStatsService.logc               	   C   s>  t  } | tdd }| tdd }t w}| c}|d|f | d }|d|f | d }|d|f | d }|d|f | d }|d|f | d }	|d | d }
|d	 | d }|
| }W d
   n1 sw   Y  W d
   n1 sw   Y  ||	||||||
dS )u  
        Возвращает словарь с ключами:
          • gen_day      — количество генераций за последние 24 часа
          • uploads_day  — количество UPLOAD-ов за 24 часа
          • new_day      — количество новых клиентов за 24 часа
          • gen_week     — количество генераций за последние 7 дней
          • new_week     — количество новых клиентов за 7 дней
          • clients_all  — общее число клиентов
          • clients_ext  — число внешних клиентов
          • clients_pf   — число PF-клиентов
           days   zZSELECT COUNT(*) AS count   FROM activity_log  WHERE action='generate' AND created_at >= %scountz=SELECT COUNT(*) AS count   FROM users  WHERE created_at >= %szXSELECT COUNT(*) AS count   FROM activity_log  WHERE action='upload' AND created_at >= %sz=SELECT COUNT(*) AS count   FROM users  WHERE is_pf_client = 1z=SELECT COUNT(*) AS count   FROM users  WHERE is_pf_client = 0N)gen_dayuploads_daynew_daygen_weeknew_weekclients_allclients_ext
clients_pf)r   utcnowr   r   r   r   fetchone)nowZday_agoZweek_agor   r   r   r    r!   r"   r   r%   r$   r#   r   r   r   kpi_counters$   sd   
 BzStatsService.kpi_counters   limitc              	   C   x   t  /}| }|d| f | W  d    W  d    S 1 s%w   Y  W d    d S 1 s5w   Y  d S )Na2  
                SELECT u.username, COUNT(*) AS total
                  FROM activity_log a
                  JOIN users            u ON u.id = a.user_id
                 WHERE a.action='generate'
              GROUP BY a.user_id
              ORDER BY total DESC
                 LIMIT %s
                r   r   r   fetchallr+   r   r   r   r   r   	top_users   s   	RzStatsService.top_users   r   c              	   C   s   t  t| d }t /}| }|d|f | W  d    W  d    S 1 s.w   Y  W d    d S 1 s>w   Y  d S )Nr   z
                SELECT DATE(created_at) AS day, COUNT(*) AS cnt
                  FROM activity_log
                 WHERE action='generate'
                   AND created_at >= %s
              GROUP BY day
              ORDER BY day
                )r   r&   r   r   r   r   r.   )r   Z	date_fromr   r   r   r   r   daily_generations   s   RzStatsService.daily_generationsc               	   C   sz   t  0} |  }|d dd |D W  d    W  d    S 1 s&w   Y  W d    d S 1 s6w   Y  d S )Nz
                SELECT subscription_plan, COUNT(*) AS cnt
                  FROM users
              GROUP BY subscription_plan
                c                 S   s   i | ]	}|d  |d qS )subscription_plancntr   ).0rowr   r   r   
<dictcomp>   s    z0StatsService.plans_breakdown.<locals>.<dictcomp>)r   r   r   )r   r   r   r   r   plans_breakdown   s   RzStatsService.plans_breakdown   c              	   C   r,   )Nz
                SELECT a.created_at, a.action, a.obj_info, u.username
                  FROM activity_log a
                  JOIN users u ON u.id = a.user_id
              ORDER BY a.created_at DESC
                 LIMIT %s
                r-   r/   r   r   r   last_activity   s   
RzStatsService.last_activitym  c                 C   sJ   t | }t }t|}|d |dd |D  t| 	dS )NdayZgenerationsc                 s   s     | ]}|d  |d fV  qdS )r=   r4   Nr   )r5   rr   r   r   	<genexpr>   s    z5StatsService.csv_daily_generations.<locals>.<genexpr>zutf-8)
r   r2   r   csvwriterwriterow	writerowsr   getvalueencode)r   rowsbuffwr   r   r   csv_daily_generations   s   


z"StatsService.csv_daily_generationsc                 C   s   t | }t }|j}d|_|d |D ]}||d |d f q|jD ]}tdd |D d }||jt	|d j
 _q&t }|| |d |S )	NZGenerationsr<   r=   r4   c                 s   s    | ]
}t t|jV  qd S N)lenstrvalue)r5   cellr   r   r   r?      s    z6StatsService.xlsx_daily_generations.<locals>.<genexpr>   r   )r   r2   r   ZactivetitleappendcolumnsmaxZcolumn_dimensionsr   columnwidthr   saveseek)r   rF   wbwsr>   colrU   bufr   r   r   xlsx_daily_generations   s   




z#StatsService.xlsx_daily_generationsrJ   )r*   )r1   )r9   )r;   )__name__
__module____qualname__staticmethodintrL   r   dictr)   r
   r	   r   r0   r2   r8   r:   r   rI   r\   r   r   r   r   r      s"    "_"""r   )__doc__Zopenpyxlr   Zopenpyxl.utilsr   r   r   ior   r   r@   typingr   r	   r
   app.databaser   r   r   r   r   r   <module>   s   