B
    Zcc                 @   s  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m	Z	m
Z
mZmZmZmZ d dlmZmZ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mZm	Z	mZ d d	lmZ d d
l m!Z!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*m+Z+m,Z,m-Z-m.Z.m/Z/ d dl0m1Z1 d dl2m3Z3 d dl0m4Z4m5Z5 d dl)m6Z6m-Z- d dlmZ d dl7m8Z8 d dl9m:Z:m;Z; d dl0m<Z<m=Z= d Z>G dd deZ?G dd dej@ZAG dd dejBe%e'ZCdS )    N)APIException)CaseWhenIntegerFieldValue	TextField
FloatFieldQ)Sum	AggregateCount)gettext_lazyactivate)mixinsserializers)Response)MaxMinr   r   )NoneSerializer)get_categoriesget_leave_summary_dict)ReportGenericFilter)ReportUtilGenericViewSetIntegerFormatFieldWorkDayRelatedFieldsUpdateUtil)IntegerFormatLeaveSummaryField)PayloadBaseAttReportSettingLeaveSettingsLeaveAttScheduleShiftDetail)get_item_symbol)Employee)get_weekend_daysget_report_setting)TimeIntervalr   )r	   )
C_ATT_RULE)STATUS_VALIDSTATUS_RESIGN_PENDING)get_item_symbolsdates_functionc                   s@   e Zd Z fddZ fddZ fddZ fddZ  ZS )	GroupContactc                sH   d| _ d| _tt| j||f|\}}|dd}|jddd}||fS )zZ
        SQL Server 2017 Support STRING_AGG
        STRING_AGG(column, separator)
        ZSTUFFz%(function)s((
            SELECT ',' + %(expressions)s
            FROM [{table}] as {copy}
            WHERE {copy}.[emp_id] = [{table}].[emp_id]
            FOR xml path('')
        ), 1, 1, '')z[att_payloadbase]z{copy}Zatt_payloadbasea)tablecopy)functiontemplatesuperr,   as_sqlreplaceformat)selfcompiler
connectionextra_contextsqlparams)	__class__ QG:\easytimepro\master/mysite/att\api\views\view_report_monthlyattendancereport.pyr3   '   s    zGroupContact.as_sqlc       	         sj   ddl m} |d}|r"|d p$d}|dkr<d| _d| _nd| _d	| _tt| j||f|\}}||fS )
Nr   )	sql_utilsz7select DISTINCT(VERSION) from product_component_version z11.0ZlistaggzB%(function)s(%(expressions)s, ',') within group(order by att_date)Z	WM_CONCATz%(function)s(%(expressions)s))mysiter?   Zp_query_oner0   r1   r2   r,   r3   )	r6   r7   r8   r9   r?   Zrowsversionr:   r;   )r<   r=   r>   	as_oracle8   s    
zGroupContact.as_oraclec                s.   d| _ d| _tt| j||f|\}}||fS )NZGROUP_CONCATz%(function)s(%(expressions)s))r0   r1   r2   r,   r3   )r6   r7   r8   r9   r:   r;   )r<   r=   r>   as_mysqlE   s    zGroupContact.as_mysqlc                s.   d| _ d| _tt| j||f|\}}||fS )NZARRAY_TO_STRINGz-%(function)s(ARRAY_AGG(%(expressions)s), ','))r0   r1   r2   r,   r3   )r6   r7   r8   r9   r:   r;   )r<   r=   r>   as_postgresqlK   s    zGroupContact.as_postgresql)__name__
__module____qualname__r3   rC   rD   rE   __classcell__r=   r=   )r<   r>   r,   %   s   r,   c                   s  e Zd ZejedddZejeddddZejedd	ddZejed
dddZ	ej
eddZejedddZejdddZejeddddZejeddddZejeddddZejeddddZeeddddZeeddd dZeed!d"d#dZeed$d%d&dZeed'd(d)dZeed*d+d)dZeed,d-d)dZeed.d/d0dZejd1d2d3Zejd4d2d3Zejd5d2d3Zejd6d2d3Zejd7d2d3Z ejd8d2d3Z!ejd9d2d3Z"ejd2d:Z#ej$dd;Z%e Z&e Z' fd<d=Z(d>d? Z)G d@dA dAZ*  Z+S )B!MonthlyAttendanceReportSerializerZreport_column_empCodeemp__emp_code)labelsourceZreport_column_firstNameemp__first_nameT)rL   rM   
allow_nullZreport_column_lastNameemp__last_nameZreport_column_nickNameemp__nicknameZreport_column_gender)rL   Zreport_column_statusemp__statusemp__department_id)rM   rO   Zreport_column_departmentCodeemp__department__dept_codeZreport_column_departmentNameemp__department__dept_nameZreport_column_positionCodeemp__position__position_codeZreport_column_positionNameemp__position__position_nameZreport_column_lateDurationsum_latelate_in)rL   rM   Z
format_keyZ report_column_earlyLeaveDurationsum_early_leave	early_outZreport_column_absentDuration
sum_absentabsentZ report_column_actualWorkDuration
sum_workeddurationZreport_column_normalOvertimesum_notZovertimeZreport_column_weekendOvertimesum_wotZreport_column_holidayOvertimesum_hotZreport_column_leaveDuration	sum_leaveleavesum_absent_countr   )rM   defaultsum_halfday_countsum_half_present_leavesum_half_present_absentsum_late_countsum_earlyleave_countsum_present_count)rf   )rO   c       
         s  | d}tt| j|| t| }x>|jD ]4}dj|d d}t|d d|d |dd| j|< q,W x|j	D ]\}}}	t
j|	|dd	| j|< t
jd
|	 d
| dd	| jd
| < t
jd|	 d| dd	| jd| < t
jd|	 d| dd	| jd| < t
jd|	 d| dd	| jd| < t
jd|	 d| dd	| jd| < t
jd|	 d| dd	| jd| < t
jd|	 d| dd	| jd| < qnW d S )Nviewzleave_{index}id)indexZcategory_nameZemp__idT)rL   rM   Zformat_indexleave_summary_dictrO   )rL   rM   rO   OT_late_earlyLeave_checkin_	checkout_shift_totalt_)popr2   rJ   __init__r   
categoriesr5   r   fieldsdate_periodr   	CharField)
r6   argskwargsrm   rp   cgZfieldrM   ZfiledrL   )r<   r=   r>   ry   {   s2    
z*MonthlyAttendanceReportSerializer.__init__c             C   s*   d}|d dkrd}n|d dkr&d}|S )Nr@   emp__genderMZMaleFZFemaler=   )r6   objgenderr=   r=   r>   
get_gender   s    z,MonthlyAttendanceReportSerializer.get_genderc               @   s   e Zd ZeZdZdS )z&MonthlyAttendanceReportSerializer.Meta)emp_code
first_name	last_name	nick_namer   status	dept_name	dept_codeposition_codeposition_name
total_latetotal_early_leavetotal_absenttotal_worked	total_not	total_wot	total_hottotal_leavetotal_absent_counttotal_halfday_counttotal_late_counttotal_earlyleave_counttotal_present_countwork_dayr_   whole_day_durationtotal_weekenddept_idemp_idtotal_half_present_leavetotal_half_present_absentN)rF   rG   rH   r   modelr{   r=   r=   r=   r>   Meta   s   r   ),rF   rG   rH   r   r}   _r   r   r   r   ZSerializerMethodFieldr   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   ry   r   r   rI   r=   r=   )r<   r>   rJ   R   sN   

rJ   c                   s   e Zd ZeZej  Ze	Z
dZdddddddd	d
ddddgZeedZdZdZ fddZdd Z fddZdd Zdd Zdd Zdd Z  ZS )MonthlyAttendanceReportViewSetr@   r   r   r   r   r   r   r   r   r   r   r   r   r   )listZexportNc                s   | j jj}| j jj}| j jj}| j jj}tt|  j	|d}y~| j jj
s| rb|j	| d}| r~|j	| d }| r|j	| d}|	ttdtd dB ttdB }|S  tk
r   tdY nX d S )N)Zemp__department__company_id)Zemp__department__in)Zemp__area__in)Zemp__position__in)rR   zPlease Check with Admin)requestusercurrent_companyZget_auth_deptZget_auth_areaZget_auth_posr2   r   get_querysetfilterZis_superuserallZdistinctexistsr	   r(   r)   	Exceptionr   )r6   
company_idZ	auth_deptZ	auth_areaZauth_posqueryset)r<   r=   r>   r      s&    




 z+MonthlyAttendanceReportViewSet.get_querysetc             C   sn  | tddptdddddddd	d
dddddd}tdt dtdt dtdt dtdt dtdt dtdt dtdt dtdt dtttddddddt dtttdddddt dtttddddddt dtttdddddddt dtttdddddt dtttddd ddt dtttdd ddd!ddt dtd"t dtttdd#d$t dd%}| 	 }|t
kr| |}t | _x^| jD ]T}d&j|d' d(| jkr| jd&j|d' d( tdt d|d)j|d' d(< qW | jjd*d }| jjd+d }g }|r
|r
| jjj}td,}	tj|	d-|d d. d/}tj|	d-|d d. d/}|| _|| _x`||krttt|d0d1d1ttd2|d3t|d4d1d1ttd5|d3t|dttd6|d7t|d4ttd8|d7t|d0ttd9|d7t|dttd:|d;t|dttd|d<t|dttd=|d>t|dttd?|d@t|dttdA|dBt|dCttdD|dEt|dCttdF|dGt  d||!dH< ||!dH|!dI|!dJf ttt|dKdLt d|dM|!dH < ttt|ddd>t  d|dN|!dH < ttt|ddd@t  d|dO|!dH < t"tt|dPdLt  d|dQ|!dH < t"tt|dRdLt  d|dS|!dH < ttt|ddLt d|dT|!dH < |tj#ddU7 }qW || _$|j%f |}xL|D ]D}
t&|
d| j| j| jjj}x|' D ]\}}||
|< qNW q"W |S )VNr   )duration__gt)Zwhole_day_duration__gtr   rK   rN   rP   rQ   r   rR   rS   rT   rU   rV   rW   Zlate)output_fieldZearly_leaver]   r   Zovertime__normal_otZovertime__weekend_otZovertime__holiday_otrd      )
absent__gtrd   then)rf   )half_dayr   )r   Z	leave__gtr   )r   rd   Ztotal_time__gtr   )late__gtr   )early_leave__gtr   )duty_worked__gtr   rd   r   r_   r   )r   r   )rX   rZ   r\   r^   r`   ra   rb   rc   re   rg   rh   ri   rj   rk   rl   Zsum_durationZsum_work_dayzleave_{index}rn   )ro   zsum_leave{index}
start_dateend_datez[-:\s]r@      z%Y%m%d   FZ	holiday_p)att_dateday_offclock_in__isnullclock_out__isnullr      Zweekdayoff_pr   )r   r   r   ZweekendZholidayr   )r   r   r   )r   r   r   rY   )r   r   r   r[   )r   r   r   Zpresent)r   r   r   TZmiss_in)r   r   r   Zmiss_out)r   r   r   z%Y/%m/%dz%m/%dz%dZovertime__total_ot)r   r   rq   rr   rs   Zclock_inrt   Z	clock_outru   rw   )days)(r   r	   valuesZorder_byr
   r   r   r   r   Zget_work_day_display WORK_DAY_DISPLAY_BY_TOTAL_RECORDZupdate_fields_for_workdayr   rz   r5   summary_fieldsappendr   r   ZGETgetr   r   recompiledatetimestrptimesubr   r   r,   r"   r   strftimer   Z	timedeltar|   Zannotater+   items)r6   r   r   Zwork_day_displayr   r   r   r|   r   patterndataZ	data_dictkeyvalr=   r=   r>   annotate_queryset   s     

$



&&&$$&
z0MonthlyAttendanceReportViewSet.annotate_querysetc                s   | |d< t t| j||S )Nrm   )r2   r   get_serializer)r6   r~   r   )r<   r=   r>   r   <  s    z-MonthlyAttendanceReportViewSet.get_serializerc             C   s   | j | jtS )N)serializer_dictr   actionr   )r6   r=   r=   r>   get_serializer_class@  s    z3MonthlyAttendanceReportViewSet.get_serializer_classc             C   s   t d tdS )NZenZmenu_att_dailyReport)r   r   )r6   r=   r=   r>   get_file_titleJ  s    z-MonthlyAttendanceReportViewSet.get_file_titlec             C   s   |  |  }| |}|S )N)filter_querysetr   r   )r6   r   r=   r=   r>   get_export_querysetN  s    
z2MonthlyAttendanceReportViewSet.get_export_querysetc       !      O   s  |  |  }| |}| jjj}tjj|d}i }x|D ]}|j	|t
|j< q:W | |}	|	d k	r| j|	dd}
|
j}| jjd}| jjd}dd |D }dd |D }t|||| jjj}tjj| jjj|d	d
d}dd t|td
D }tjj|||dddd}dd t|tdD }xt|D ]\}}||d g }t|rz|d d |d< nd|d< tjj|d ||ddjtttddddt  dtttddddt  dd}|d rt!|d |d< nd|d< |d rt!|d |d< nd|d< |d d|dd |dd |d!d |d"d |d#< ||d }d}|r@|d$ rt"|# }d%d&d'd(d)d*d$d+h}t$|| }xj|D ]b}t%j%&|d,'d-}|t
| d.krtjjt(|d/t(|d0@ |d% d1) }|r|d7 }qW |d$ | |d$< |d& |d&< |d+ |d+< nd|d&< d|d$< d|d+< ||d g }xZ|D ]R} y| d |d2| d 'd3 < W n, t*k
r   d |d2| d 'd3 < Y nX qnW qDW | +|S | j|dd}
t,|
jS )4N)r   T)Zmanyr   r   c             S   s   g | ]}|d  qS )r   r=   ).0xr=   r=   r>   
<listcomp>b  s    z7MonthlyAttendanceReportViewSet.list.<locals>.<listcomp>c             S   s   g | ]}|d  |d dqS )r   r   )rn   Zdepartment_idr=   )r   r   r=   r=   r>   r   c  s    )r   Zemployee__inemployee_idleave_balancec             S   s   i | ]\}}t ||qS r=   )r   )r   r   r   r=   r=   r>   
<dictcomp>j  s    z7MonthlyAttendanceReportViewSet.list.<locals>.<dictcomp>)Z
emp_id__inZatt_date__gteZatt_date__lter   r   Ztimetable__aliasc             S   s   i | ]\}}t ||qS r=   )r   )r   r   r   r=   r=   r>   r   p  s    r   Zleave_balance_mrr   )r   Zstart_time__lteZend_time__gteZaudit_statusr   )Zleave_payment_typer   )rf   r   r   )paidunpaidr   Z
paid_leaver   Zunpaid_leaver   r   r   r   r   rn   Ztotal_workdayr   Ztotal_durationZtotal_breakZtotal_dayoffZtotal_holidayz%Y%m%dz%Y-%m-%dZWeekoff)Zend_time__date__gte)Zstart_time__date__lte)r   rv   z%m/%d)-r   r   r   r   r   r   r&   objectsr   aliasstrrn   Zpaginate_querysetr   r   Zquery_paramsr   r$   r   r   	itertoolsgroupbyoperator
itemgetterr   	enumeratelenr   Z	aggregater
   r   r   r   floatsetkeysr   r   r   r   r	   r   r   Zget_paginated_responser   )!r6   r   r~   r   r   r   Zshift_detailZshift_detailsr   ZpageZ
serializerresultr   r   Zemp_idsZemp_dataZall_week_end_detailsZall_leave_settingsZall_payloadsir   r   Z
LeavetakenZweek_end_detailsZleave_countr   Zdatasr   ZdayZweekend_dateZ	leave_objZ
time_tableZtimeTableDatar=   r=   r>   r   S  s    








 

@



(
z#MonthlyAttendanceReportViewSet.list)rF   rG   rH   r   r   r   r   Zselect_relatedr   r   Zfilter_classr   r   rJ   r   rz   r|   r   r   r   r   r   r   r   rI   r=   r=   )r<   r>   r      s$   
f
r   )Dr   Zjsonr   r   r   Zrest_framework.exceptionsr   Zdjango.db.modelsr   r   r   r   r   r   r	   Zdjango.db.models.aggregatesr
   r   r   Zdjango.utils.translationr   r   r   Zrest_frameworkr   r   Zrest_framework.responser   r   r   Zmysite.att.api.serializersr   Zmysite.att.api.utilsr   r   Zmysite.att.api.utils_classr   r   r   r   r   Zmysite.att.modelsr   r   r   r   r    r!   Zmysite.att.utilsr"   Zmysite.personnel.modelsr#   r$   r%   r&   Zmysite.att.global_cacher'   Zmysite.admin.modelsr(   r)   r*   r+   r   r,   ZModelSerializerrJ   ZListModelMixinr   r=   r=   r=   r>   <module>   s8   $ -[