o
    6i2$                     @   sf   d dl m Z  d dlmZ d dlZd dlmZ d dlmZ d dl	m
Z
mZmZmZmZ G dd dZdS )	    )datetime)BytesION)extract)get_db)Course
AttendanceParticipantPaymentDetailsPaymentc                   @   sL   e Z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S )ReportServicec                 C   s  t  }|ttj|jktj|jkttjtj	k}|j
r2|jr2|tj|jktj|j
k}n|jr>|tj|jk}|jrJ|tj|jk}|jrV|tj	|jk}g }dd dd dd dd dd dd dd d	d d
}| D ]4}d|jv }i }	| D ]!\}
}|s|
|jv rz|||	|
< W q ty   d |	|
< Y qw q||	 qwW d    n1 sw   Y  |t| dS )Nc                 S      | j S Nidrow r   /app/services/report.py<lambda>!       z7ReportService.fetch_attendance_report.<locals>.<lambda>c                 S      | j jS r   )coursenamer   r   r   r   r   "       c                 S   r   r   )participant
first_namer   r   r   r   r   #   r   c                 S   r   r   )r   	last_namer   r   r   r   r   $   r   c                 S   s
   | j jjS r   )r   	age_grouplabelr   r   r   r   r   %   s   
 c                 S   r   r   )attendance_dater   r   r   r   r   &   r   c                 S   r   r   )staffr   r   r   r   r   r   '   r   c                 S   r   r   )attendance_statusr   r   r   r   r   (   r   )attendance_idcourse_nameparticipant_first_nameparticipant_last_namer   r   Z
tutor_namer!   *datafields)r   queryr   filterr   academic_year_idterm_idjoin	course_idr   end_date
start_dater   course_category_idallr)   itemsAttributeErrorappendlistkeys)selfrequestuserdbr*   r(   field_mappingr   Z
return_allrow_datafield	extractorr   r   r   fetch_attendance_report   sV   



/z%ReportService.fetch_attendance_reportc                 C      |  ||S r   )rA   r9   r:   r;   r   r   r   view_attendance_report?      z$ReportService.view_attendance_reportc                 C   h   t | ||d }t }t j|dd}|j|ddd W d    n1 s(w   Y  |d |S )Nr(   
xlsxwriterengineFr   indexZ
sheet_namer   )pd	DataFramerA   r   ExcelWriterto_excelseekr9   r:   r;   dfoutputwriterr   r   r   view_attendance_report_downloadB      
z-ReportService.view_attendance_report_downloadc                 C   sx  t  }|t}|jr|tj|jk}|jr-t j}||j }|t	dtj
|k}|jr9|tj|jk}|jrE|tj|jk}dd dd dd dd dd dd d	d d
d dd dd dd d}g }| D ]2}	i }
| D ]$\}}d|jv s||jv rz||	|
|< W qw ty   d |
|< Y qww qw||
 qo|t| dW  d    S 1 sw   Y  d S )Nyearc                 S   r   r   r   r   r   r   r   r   ]   r   z2ReportService.fetch_student_data.<locals>.<lambda>c                 S   r   r   dobr   r   r   r   r   ^   r   c                 S   r   r   middle_namer   r   r   r   r   _   r   c                 S   r   r   r   r   r   r   r   r   `   r   c                 S      | j r| j jS d S r   parentr   r   r   r   r   r   a       c                 S   r^   r   r`   r   r   r   r   r   r   b   ra   c                 S   r^   r   r`   emailr   r   r   r   r   c   ra   c                 S   r^   r   r`   mobile_numberr   r   r   r   r   d   ra   c                 S   r   r   genderr   r   r   r   r   e   r   c                 S   r   r   is_participated_in_wacr   r   r   r   r   f   r   c                 S   r   r   photography_consentr   r   r   r   r   g   r   )r$   participant_dobparticipant_middle_namer%   parent_first_nameparent_last_nameparent_emailparent_mobile_numberrh   rj   rl   r&   r'   )r   r*   r   rh   r+   ager   nowrW   r   rZ   rj   rl   r3   r4   r)   r5   r6   r7   r8   )r9   r:   r;   r<   r*   Zcurrent_yearZtarget_dob_yearr=   r(   r   r>   r?   r@   r   r   r   fetch_student_dataJ   sP   


$z ReportService.fetch_student_datac                 C   rB   r   )ru   rC   r   r   r   view_student_data|   rE   zReportService.view_student_datac                 C   rF   Nr(   rG   rH   FZStudentrJ   r   )rL   rM   ru   r   rN   rO   rP   rQ   r   r   r   view_student_data_download   rV   z(ReportService.view_student_data_downloadc                 C   s  t  }|tttjtjkttjtjkt	tj
t	jkt	jdk}|jr3|tj|jk}|jr?|tj|jk}|jrK|tj|jk}|jrW|tj|jk}|jrc|tj|jk}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}g }| D ]8}i }| D ]$\}	}
d|jv s|	|jv rz|
|||	< W q ty   d ||	< Y qw qt|dkr|| q|t| dW  d    S 1 sw   Y  d S )NTc                 S   r   r   r   r   r   r   r   r      r   z3ReportService.fetch_enrolled_data.<locals>.<lambda>c                 S   r   r   rX   r   r   r   r   r      r   c                 S   r   r   )rd   r   r   r   r   r      r   c                 S   r   r   rg   r   r   r   r   r      r   c                 S   r   r   rY   r   r   r   r   r      r   c                 S   r   r   r[   r   r   r   r   r      r   c                 S   r   r   r]   r   r   r   r   r      r   c                 S   r^   r   r_   r   r   r   r   r      ra   c                 S   r^   r   rb   r   r   r   r   r      ra   c                 S   r^   r   rc   r   r   r   r   r      ra   c                 S   r^   r   re   r   r   r   r   r      ra   c                 S   r   r   rg   r   r   r   r   r      r   c                 S   r   r   ri   r   r   r   r   r      r   c                 S   r   r   rk   r   r   r   r   r      r   )participant_idr$   Zparticipant_emailZparticipant_genderrm   rn   r%   ro   rp   rq   rr   rh   rj   rl   r&   r   r'   )r   r*   r   r.   r	   purchased_for_idr   r   r/   r
   
payment_idr+   is_payment_completedr,   r-   age_group_idr2   r3   r4   r)   r5   lenr6   r7   r8   )r9   r:   r;   r<   r*   r=   r(   r   r>   r?   r@   r   r   r   fetch_enrolled_data   sV   F
$z!ReportService.fetch_enrolled_datac                 C   rF   rw   )rL   rM   r   r   rN   rO   rP   rQ   r   r   r   view_enrolled_download   rV   z$ReportService.view_enrolled_downloadN)__name__
__module____qualname__rA   rD   rU   ru   rv   rx   r   r   r   r   r   r   r      s    223r   )r   ior   ZpandasrL   
sqlalchemyr   core.configr   modelsr   r   r   r	   r
   r   r   r   r   r   <module>   s    