HEX
Server: Apache/2.4.52 (Ubuntu)
System: Linux spn-python 5.15.0-89-generic #99-Ubuntu SMP Mon Oct 30 20:42:41 UTC 2023 x86_64
User: arjun (1000)
PHP: 8.1.2-1ubuntu2.20
Disabled: NONE
Upload Files
File: //home/arjun/projects/buyercall/buyercall/blueprints/reports/__pycache__/tasks.cpython-310.pyc
o

�we�
�@sddlZddlZddlmZmZddlmZddlmZddl	m
Z
ddlmZddl
mZmZddlZddlmZmZmZdd	lmZdd
lmZddlmZe�e�Zee�Zejdd
�dd��Zejdd
�dd��Zejdd
�dd��Z ejdd
�dd��Z!dS)�N)�current_app�render_template)�create_celery_app)�send_daily_report)�PartnershipAccount)�SoftTimeLimitExceeded)�and_�text)�datetime�	timedelta�timezone)�db)�
create_app)�_try_renderer_templatei,)�soft_time_limitcKstpt�}|t_|���mddlm}m}|j�	|j
dk���}z6|rP|j�	|j|j
k���}|rJd}|D]}tj|j|j|jg|d�|d7}q5nt�d�nt�d�Wntyo}z
t�t���WYd}~nd}~ww	Wd�dS1s|wYdS)	zN
    Send daily leads report

    :return: Result of sending daily report
    r)�Report�
ReportUserTiezDaily Leads Report)�args�	countdown�<z6There is no daily reports in the report user tie tablezThere is no daily leads reportN)rrr
�app�app_context�#buyercall.blueprints.reports.modelsrr�query�filter�name�first�	report_id�id�all�/send_daily_report_email_per_partnership_account�apply_async�partnership_accounts_id�user_id�log�info�	Exception�error�	traceback�
format_exc)�kwargsrrr�report�
daily_reports�
count_down�e�r/�D/home/arjun/projects/buyercall/buyercall/blueprints/reports/tasks.py�send_daily_report_emails4

�
�
���$�r1��c�s�z�ddlm}ddlm}m}ddl}tpt�}|t_	dd�}	|�
����tj��}
|j
�|j|k���}|j
�|j|jk���}|����}
|��jdd���}|��j}|��jd	}|��j}|��jd	}|��j}|��jd	}|��jd
d���}|
jtd�||d�}g}|D]}t|j|j|j|jd
�}|�|�q�|
jtd�||d�}g}|D]}t|j|j|j|j |j!|j"|jd�}|�|�q�|
jtd�||d�}g}|D]} t| j| j| j| j#| j!| j"| jd�}!|�|!�q�|
jtd�||d�}"g}#|"D]}$t|$j|$j|$j$|$j"d�}%|#�|%��qd}&d}'d}(d})d}*d}+d},d}-d}.d}/d}0d}1d}2d}3d}4d}5d}6d}7d}8d}9d}:d};d}<d}=d}>|
jtd�|d�}?g}@d}A|?D]p}Bd}Cd}D|D]$}E|Ed|Bjk�r�|Ed�%d�}F|�&|F���}G|G|
k�r�|Cd	}Cd}A�q^|D]+}H|Hd|Bjk�r�|Hd�%d�}I|�&|I���}J|J|
k�r�|Hddk�r�|Dd	}Dd}A�q�|Bj'd|Bj(}Kt|K|C|Dd�}L|@�|L��qV|
jtd�|d�}Mg}Nd}O|MD]�}Pd}Qd}Rd}Sd}T|D]?}U|Ud |Pjk�r |Ud�%d�}V|�&|V���}W|W|
k�r|Udd!k�r|Qd	}Qd}O�q�|W|
k�r |Uddk�r |Rd	}Rd}O�q�|D]?}X|Xd |Pjk�rb|Xd�%d�}Y|�&|Y���}Z|Z|
k�rP|Xd"d!k�rP|Sd	}Sd}O�q$|Z|
k�rb|Xd"dk�rb|Td	}Td}O�q$t|Pj)|Q|R|S|Td#�}[|N�|[��q�|
jtd$�|d�}\g}]d}^|\D]8}_d}`|#D]$}a|ad%|_jk�r�|ad�%d�}b|�&|b���}c|c|
k�r�|`d	}`d}^�q�t|_j*|`d&�}d|]�|d��q�g}e|D�]���d�%d�}f|�&|f���}g|�&|g�j}h|�&|g�j}i|�&|g�j}j|g|
k�r�|j|k�r�|&d	}&d'}k|D]}l|ld(�d)k�rt|k�r|ld|kk�r|ld}kn|ld}k|ld*d+k�r-|�&|k�%d����|
k�r-|3d	}3n|�&|k�%d����|
k�r>|4d	}4|ld*d+k�sL|ld*d,k�rQ|7d	}7n|ld*d-k�sX|8d	}8|ldd!k�ri|9d	}9�q�|lddk�rt|:d	}:�q�d'}m|D]`}n|nd(�d)k�r�|m�r�|nd|mk�r�|nd}mn|nd}m|k�s�d}o|nd*d/k�r�|od	}o|nd*d0k�r�|od	}o|odk�r�|3d	}3n|4d	}4|nd*d/k�r�|;d	};�qz|nd*d0k�r�|<d	}<�qzd'}p|#D]C}q|qd(�d)k�r!|p�r�|qd|pk�r�|qd}pn|qd}p|
jtd1�|qd)d2�}r|r�r!|rD]}s|sj+d'k�r|>d	}>�q
|=d	}=�q
�q�|k�rY|m�rY|p�rY|k|mk�r;|k|pk�r;|-d	}-n�|m|kk�rJ|m|pk�rJ|1d	}1nt|p|kk�rX|p|mk�rX|/d	}/ne|k�rs|m�rs|k|mk�ri|-d	}-nU|m|kk�rr|1d	}1nK|k�r�|p�r�|k|pk�r�|-d	}-n;|p|kk�r�|/d	}/n1|m�r�|p�r�|m|pk�r�|1d	}1n!|p|mk�r�|/d	}/n|k�r�|-d	}-n|m�r�|1d	}1n|p�r�|/d	}/�dd3v�r�|5d	}5n|6d	}6d}tt,�fd4d5�|eD���s�t�d*d	d6�}u|e�|u�nt-�fd7d5�|eD��}v|vd8d	7<|g|k�r|j|k�r|'d	}'d'}w|D] }l|ld(�d)k�r.|w�r*|ld|wk�r(|ld}w�q|ld}w�qd'}x|D] }n|nd(�d)k�rS|x�rO|nd|xk�rM|nd}x�q4|nd}x�q4d'}y|#D] }q|qd(�d)k�rx|y�rt|qd|yk�rr|qd}y�qY|qd}y�qY|w�r�|x�r�|y�r�|w|xk�r�|w|yk�r�|.d	}.n�|x|wk�r�|x|yk�r�|2d	}2nt|y|wk�r�|y|xk�r�|0d	}0ne|w�r�|x�r�|w|xk�r�|.d	}.nU|x|wk�r�|2d	}2nK|w�r�|y�r�|w|yk�r�|.d	}.n;|y|wk�r�|0d	}0n1|x�r�|y�r�|x|yk�r�|2d	}2n!|y|xk�r�|0d	}0n|w�r|.d	}.n|x�r|2d	}2n|y�r|0d	}0|h|k�r#|j|k�r#|(d	}(|h|k�r1|j|k�r1|)d	})|i|k�r?|j|k�r?|*d	}*|i|k�rM|j|k�rM|+d	}+|id9k�r[|j|k�r[|,d	},�q�|'dk�rtt.t/|&�t/|'�t/|'�d:d�}znd}z|)dk�r�t.t/|(�t/|)�t/|)�d:d�}{nd}{|,dk�r�t.t/|*�t/|,�t/|,�d:d�}|nd}||+dk�r�t.t/|*�t/|+�t/|+�d:d�}}nd}}|.dk�r�t.t/|-�t/|.�t/|.�d:d�}~nd}~|2dk�r�t.t/|1�t/|2�t/|2�d:d�}nd}|0dk�r
t.t/|/�t/|0�t/|0�d:d�}�nd}�|	|z�}�|	|{�}�|	|}�}�|	|~�}�|	|�}�|	|��}�|zdk�r/d;t0|z�}z|{dk�r:d;t0|{�}{|}dk�rEd;t0|}�}}|~dk�rPd;t0|~�}~|dk�r[d;t0|�}|�dk�rfd;t0|��}�d<|i}�|@|�d=<|A|�d><|e|�d*<|N|�d?<|O|�d@<|]|�dA<|^|�dB<|
|�dC<||�dD<|&|�dE<|(|�dF<|*|�dG<|-|�dH<|1|�dI<|/|�dJ<|3|�dK<|4|�dL<|5|�dM<|6|�dN<|7|�dO<|8|�dP<|9|�dQ<|:|�dR<|;|�dS<|<|�dT<|=|�dU<|>|�dV<|'|�dW<|)|�dX<|+|�dY<|,|�dZ<|.|�d[<|2|�d\<|0|�d]<|z|�d^<|{|�d_<|}|�d`<|||�da<|~|�db<||�dc<|�|�dd<|�|�de<|�|�df<|�|�dg<|�|�dh<|�|�di<|�|�dj<|j1|�dk<|j2|�dl<|j2|�dm<ddnl3m4}�|�j
�t5|�j6|k|�j7|k|�j8|k����}�do|�j9v�r�||�j9do�}�t:�}�|��;||�j9do��dp}�t<|�fdqdri|���}�zddsl=m>}�|�|�|jdt|�du�Wnt?�y�}�z
t@�AtB�C��WYd}�~�nd}�~�wwtj�D�	Wd�WdvS1�s�wYWdStE�	yt�p�t�}|t_	|�
��&ddsl=m>}�dwt0|�dxt0|�}�|�dygd	dz|�d{�	Wd�YdS1�s�wYYdSw)|z�
    Send daily leads report per partnership account id

    :return: Result of sending daily report per partnership account
    r)�split_emails)�PartnershiprNcS�(|dkrd}|S|dkrd}|Sd}|S�Nrz#FF0000�#000z#008000r/��value�colorr/r/r0�color_indicatorI���zHsend_daily_report_email_per_partnership_account.<locals>.color_indicator���days��Az�SELECT co.id, co.created_on, co.agent_id, co.status FROM contacts co WHERE co.partnership_account_id = :pa_id AND co.created_on > :limit)�pa_id�limit)r�
created_on�agent_id�statusz�SELECT ca.id, ca.agent_id, ca.created_on, ca.call_type, ca.inbound_id, ca.contact_id, ca.status FROM leads ca WHERE ca.partnership_account_id = :pa_id AND ca.created_on > :limit)rrDrE�	call_type�
inbound_id�
contact_idrFz�SELECT mg.id, mg.agent_id, mg.created_on, mg.direction, mg.inbound_id, mg.contact_id, mg.status FROM messages mg WHERE mg.partnership_account_id = :pa_id AND mg.created_on > :limit)rrDrE�	directionrHrIrFz�SELECT fl.id, fl.created_on, fl.form_id, fl.contact_id FROM form_leads fl WHERE fl.partnership_account_id = :pa_id AND fl.created_on > :limit)rrD�form_idrIz_SELECT ag.id, ag.firstname, ag.lastname FROM agents ag WHERE ag.partnership_account_id = :pa_id)rB�NorErDz%Y-%m-%dT%H:%M:%SZ�YesrG�outbound� )�agent�
lead_count�outbound_callszxSELECT pn.id, pn.friendly_name FROM phonenumbers pn WHERE pn.partnership_account_id = :pa_id AND pn.is_deactivated = '0'rH�inboundrJ)�phone_number�in_calls�	out_calls�in_msgs�out_msgszySELECT fm.id, fm.display_name FROM external_forms fm WHERE fm.partnership_account_id = :pa_id AND fm.is_deactivated = '0'rK)�form�form_lead_count�rIrrF�	completedzin-progress�missed�ringing�received�sentzuSELECT flf.field_value FROM form_lead_fields flf WHERE flf.lead_id = :form_id AND flf.field_id = 'privacypolicyfield')rK)Nr[c3s"�|]}|�d��dkVqdS�rFN)�get)�.0�sd��	lead_itemr/r0�	<genexpr>�s� zBsend_daily_report_email_per_partnership_account.<locals>.<genexpr>)rFrQc3s$�|]
}|d�dkr|VqdSrar/)rc�statrer/r0rg�s�"rQ��d�+�leads�agents�agent_leads_exist�phonenumbers�pn_leads_exist�forms�form_leads_exist�current_date�
current_month�	day_leads�
week_leads�month_leads�day_call_leads�day_message_leads�day_form_leads�day_completed_leads�day_missed_leads�day_not_assigned_leads�day_assigned_leads�day_completed_calls�day_missed_calls�day_inbound_calls�day_outbound_calls�day_inbound_msg�day_outbound_msg�day_partial_form�
day_full_form�day_before_leads�week_before_leads�month_before_leads�last_year_month_before_leads�day_before_call_leads�day_before_message_leads�day_before_form_leads�day_leads_diff�week_leads_diff�month_leads_diff�last_year_month_leads_diff�day_calls_diff�day_messages_diff�day_forms_diff�day_diff_style�week_diff_style�month_diff_style�day_calls_diff_style�day_messages_diff_style�day_forms_diff_style�partner_logo�company�account_name)r�emailszmail/daily_leads�ext�html��send_ses_emailzDaily Lead Report)�
recipients�p_id�subjectr�Tz4The daily report failed for partnership account id: �
and user id: �admin@buyercall.comzDaily Report failed�r�r�r�r	)F�#buyercall.blueprints.widgets.modelsr3�'buyercall.blueprints.partnership.modelsr4r�pendulumrrr
rr�engine�connectrrrr�partnership_id�	yesterday�to_date_string�today�subtract�week_of_year�month�year�executer	�dictrDrErF�appendrGrHrIrJrK�strftime�parse�	firstname�lastname�
friendly_name�display_name�field_value�any�next�round�float�str�logorrrrr"rr#r��list�extendr�buyercall.lib.util_ses_emailr�r&r$r'r(r)�disposer)�rBrr#r*r3r4rr�rr;�conn�partner_account�partnerr��day_before_yesterday�current_week�
previous_weekrt�previous_month�current_year�	last_year�limit_lead_count_date�pa_contactsrl�co�co_dict�pa_calls�calls�cal�cal_dict�pa_messages�messages�msg�msg_dict�
pa_form_leads�
form_leads�fl�fl_dictrur�rvr�rwr�r�rxr�rzr�ryr�r{r|r}r~rr�r�r�r�r�r�r��account_agentsrmrn�arQ�out_call_count�ac�contact_lead_date_transform�contact_lead_date�aoc�out_call_lead_date_transform�out_call_lead_date�agent_fullname�
agent_dict�account_phonenumbersrorp�tnrUrVrWrX�tnc�call_date_transform�	call_date�tnm�msg_date_transform�msg_date�phone_number_dict�
account_formsrqrr�fmsrZ�efl�form_lead_date_transform�form_lead_date�form_lead_dict�statuses�lead_date_transform�	lead_date�	lead_week�
lead_month�	lead_year�latest_call_date�	call_item�latest_message_date�message_item�complete_msg_chat�latest_form_lead_date�form_lead_item�
form_field�fv�status_lead_count�status_dictrd�day_before_call_date�day_before_message_date�day_before_form_lead_dater�r�r�r�r�r�r�r�r�r�r�r�r��ctxrr+�
splited_emr��templater�r�r.�
email_messager/rer0r 9s�
	
��


���	�	�	�	���
���
����
�
��
��
�
��

��
��

�


�


�


�


�������

�


�


�


�

�
���
���
���
���
���
���
���








��
���
����*�U
��*��r iXc7
KsLddlm}|j�|j|k���}�z�tpt�}|t_	tj
��}dd�}dd�}dd�}d	d
ddd
dddddddd�}	t�
tj���tdd�}
t�
tj���tdd�}t�
tj�j}t�
tj�jd}
|
dkrmd|
}
t�
tj�jd}|dkr~d|}t�
tj�jd}|dkr�d|}|	�|d�}|	�|
d�}|	�|d�}|	�|d�}|j�dd���}|r�|dvr�d}nd}tt���d ��}td]id!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�d0d�d1d�d2d�d3d�d4d�d5d�d6d7�d8d7�d9d7�d:d7�d;d<�d=d<�d>d<�d?d<�d@g��}|jtdA�dBdCdD�}g}g}|D�]�}|j|v�r0td]idE|j�dF|j�dGd�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�d0d�d1d�d2d�d3d�d4d�d5d�d6d7�d8d7�d9d7�d:d7�d;d<�d=d<�d>d<�d?d<�dHg��}td]idI|j�dJ|j�dK|j �dL|j!�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�d0d�d1d�d2d�d3d�d4d�d5d�d6d7�d8d7�d9d7�d:d7�d;d<�d=d<�d>d<�d?d<��}|dH�"|�|dGd7<|d@�"|�|j|v�r(|�"|j�|�"|j��q3|d@D]�}|j|dEk�r�td]idI|j�dJ|j�dK|j �dL|j!�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�d0d�d1d�d2d�d3d�d4d�d5d�d6d7�d8d7�d9d7�d:d7�d;d<�d=d<�d>d<�d?d<��}|dH�"|�|dGd7<|j|v�r�|�"|j��q4�q3t#|�|d!<t#|�|d"<t$|�}t#|�dk�r�|jtdM�|dN�} |jtdO�|dPdQ�}!nd} d}!| �r�| D�]�}"|d@D�]�}#|#dHD�]E}$|$dI|"j%k�rJ|"j&�d �t|
�k�r3|$d#d7<|#d#d7<|d#d7<|"j&�d �t|�k�rV|$d%d7<|#d%d7<|d%d7<|"j&�dR�t|�k�ry|$d'd7<|#d'd7<|d'd7<|"j&�dR�t|
�k�r�|$d)d7<|#d)d7<|d)d7<|"j&�dR�t|�k�r�|$d+d7<|#d+d7<|d+d7<|"j&�dR�t|�k�r�|$d-d7<|#d-d7<|d-d7<|$d#|$d%}%|$d'|$d)}&|$d)|$d+}'|$d+|$d-}(||%�|$d2<||&�|$d3<||'�|$d4<||(�|$d5<||%�|$d6<||&�|$d8<||'�|$d9<||(�|$d:<||%�|$d;<||&�|$d=<||'�|$d><||(�|$d?<�q|#d#|#d%})|#d'|#d)}*|#d)|#d+}+|#d+|#d-},||)�|#d2<||*�|#d3<||+�|#d4<||,�|#d5<||)�|#d6<||*�|#d8<||+�|#d9<||,�|#d:<||)�|#d;<||*�|#d=<||+�|#d><||,�|#d?<�q��q�|!�r3|!D�]t}-|d@D�]k}.|.dHD�]}/|/dI|-j%k�r�|-j&�d �t|
�k�r�|/d$d7<|.d$d7<|d$d7<|-j&�d �t|�k�r|/d&d7<|.d&d7<|d&d7<|-j&�dR�t|�k�r?|/d(d7<|.d(d7<|d(d7<|-j&�dR�t|
�k�rb|/d*d7<|.d*d7<|d*d7<|-j&�dR�t|�k�r�|/d,d7<|.d,d7<|d,d7<|/d#dk�r�t'|/d$|/d#dS�|/d.<|/d'dk�r�t'|/d(|/d'dS�|/d/<|/d)dk�r�t'|/d*|/d)dS�|/d0<|/d+dk�r�t'|/d,|/d+dS�|/dT<�q�|.d#dk�r�t'|.d$|.d#dS�|.d.<|.d'dk�rt'|.d(|.d'dS�|.d/<|.d)dk�rt'|.d*|.d)dS�|.d0<|.d+dk�r/t'|.d,|.d+dS�|.d1<�qĐq�|d#|d%}0|d'|d)}1|d)|d+}2|d+|d-}3||0�|d2<||1�|d3<||2�|d4<||3�|d5<||0�|d6<||1�|d8<||2�|d9<||3�|d:<||0�|d;<||1�|d=<||2�|d><||3�|d?<|d#dk�r�t'|d$|d#dS�|d.<|d'dk�r�t'|d(|d'dS�|d/<|d)dk�r�t'|d*|d)dS�|d0<|d+dk�r�t'|d,|d+dS�|d1<t(dU||||||||dV�	}4|4WSt)�y%ddWl*m+}5dXt|j%�dYt|�}6|5dZgdd[|6d\�YdSw)^Nr)�UsercSr5r6r/r8r/r/r0r;�r<zDgenerate_credit_service_provider_report_pdf.<locals>.color_indicatorcSs|dkr
dt|�}t|�S)Nrrk)r��r9r/r/r0�add_plus�sz=generate_credit_service_provider_report_pdf.<locals>.add_pluscSs|dkrd}|Sd}|S)NrTFr/rr/r/r0�
up_or_down�s
�z?generate_credit_service_provider_report_pdf.<locals>.up_or_down�January�February�March�April�May�June�July�August�	SeptemberzOctober z	November �December)r@r=�������	�
�rir@r>r=rir#r[rO)�
offerlogix�
offerlogixllcz:https://widget.buyercall.com/offerlogix/img/offerlogix.jpgzIhttps://buyercall-logo.s3.us-west-2.amazonaws.com/BuyerCall-main+logo.png�%Y-%m-%d�total_partners�total_partnership_accounts�yesterday_total_leads�yesterday_credit_leads�day_before_total_leads�day_before_credit_leads�current_month_total_leads�current_month_credit_leads�last_month_total_leads�last_month_credit_leads�last_two_month_total_leads�last_two_month_credit_leads�last_three_month_total_leads�percentage_success_yesterday� percentage_success_current_month�percentage_success_last_month�!percentage_success_last_two_month�yesterday_diff�month_1_diff�month_2_diff�month_3_diff�yesterday_diff_colorr7�month_1_diff_color�month_2_diff_color�month_3_diff_color�yesterday_total_checkF�month_1_total_check�month_2_total_check�month_3_total_check�partner_dataaySELECT pa.id, pa.name, pa.partner_account_code, pac.service_provider, pa.partnership_id, p.name as partner FROM partnership_accounts pa INNER JOIN partnerships as p ON pa.partnership_id = p.id INNER JOIN partnership_account_credit_tie as pac ON pa.id = pac.partnership_account_id WHERE pa.name != :pa_not_name AND pac.service_provider = :service_provider AND pa.is_active = '1'zMall Motors LTDr,)�pa_not_name�service_providerr��p_name�p_total_accounts�
p_accounts�a_id�a_name�a_p_coderNz�SELECT co.id, co.created_on, co.partnership_account_id FROM contacts co WHERE  co.partnership_account_id in :account_ids AND co.is_eq_lead = '1' AND co.created_on > current_timestamp - interval '160 days')�account_idsaSELECT DISTINCT ON (cr.contact_id) cr.id, cr.created_on, cr.contact_id, cr.partnership_account_id FROM credit_reports cr WHERE  cr.partnership_account_id in :account_ids AND cr.service_provider = :service_provider AND cr.created_on > current_timestamp - interval '160 days'�
Offerlogix)rUrNz%-mrj�percentage_success_two_monthz'credit_service_provider_report_pdf.html)r��dater��report_datart�
last_month�last_two_month�last_three_monthr�z0The credit account count report failed for user r�r�z"Credit Account Count Report Failedr�r/),� buyercall.blueprints.user.modelsrrrrrrrr
rr�r�r
�nowr�utcrXrr�rbr��replace�lowerr�r�r�r�r�r	r�r�r�partner_account_coderNr��len�tuple�partnership_account_idrD�intrrr�r�)7r#r*r�userrr�r;rr�months�yesterday_date�day_before_date�current_month_number�last_month_number�last_month_two_number�last_month_three_number�current_month_name�last_month_name�last_two_month_name�last_three_month_namer�r�rXrY�credit_accounts�partnership_list�credit_account_ids�pa�p_dict�pa_dict�ca�credit_account_id_tuple�credit_contacts�a_credit_reports�c_c�sp�spa�yesterday_compare�month_1_compare�month_2_compare�month_3_compare�sp_yesterday_compare�sp_month_1_compare�sp_month_2_compare�sp_month_3_compare�cr_c�spc�spac�all_yesterday_compare�all_month_1_compare�all_month_2_compare�all_month_3_comparer�r�rr/r/r0�+generate_credit_service_provider_report_pdf�s�

	
���������	�
���
������������������"	�
��������	�
���
�������������������!��������	�
���
�������������������!��������	�
���
�������������������!��(��
��J
�����������2�������r�c%Cs��z�tpt�}|t_|����pddlm}|���d�}|���d�}tj�	�}|�
td��}|�
td��}|�
td��}|�
td��}|�
td	��}	d}
|D]
}t|j
�|j
}
qPd}|D]
}
t|
j�|
j}q_d}|D]
}t|j�|j}qnd}|D]
}t|j�|j}q}d}|	D]
}t|j�|j}q�|j
td
�|
d�|�
td��}|j
td
�|d�|�
td��}|j
td�|d�|�
td��}|j
td�|d�|�
td��}|j
td�|d�|�
td��}d}|D]}|j}q�d}|D]}|j}q�d}|D]}|j}q�d}|D]} | j}�q	d}!|D]}"|"j}!�qddlm}#dt|�dt|
�dt|�dt|�dt|�dt|�d t|�d!t|�d"t|�d#t|�d$t|!�}$|#d%gd&d't|�|$d(�tj��	Wd)�Wd*S1�s�wYWd)St�y�t�p�t�}|t_|���(ddlm}#d+tt���}$|#d%gd&d,tt���|$d(�	Wd)�Yd)S1�s�wYYd)Sw)-z�
    Update the reporting tables with the current leads data for contacts, form_leads, messages,
    calls and credit reports

    return: True
    r)r
r.z%Y-%m-%d %H:%M:%Sz>SELECT last_value as contact_id FROM contacts_reporting_id_seqz8SELECT last_value as call_id FROM leads_reporting_id_seqz>SELECT last_value as message_id FROM messages_reporting_id_seqzBSELECT last_value as form_lead_id FROM form_leads_reporting_id_seqzJSELECT last_value as credit_report_id FROM credit_reports_reporting_id_seqapINSERT INTO contacts_reporting SELECT co.created_on, co.id, co.city, co.state, co.zip, co.external_source_type, co.partnership_account_id, co.is_do_not_call, co.is_unsubscribe, co.agent_assigned, co.status, co.bdc_status, co.marketing_source, co.campaign_id, co.agent_id, co.api_source, co.api_token_id, co.is_eq_lead FROM contacts co WHERE co.id > :current_contact_id)�current_contact_idzySELECT SETVAL('public.contacts_reporting_id_seq', COALESCE(MAX(id), 1) ) as last_value_id FROM public.contacts_reporting;a�INSERT INTO leads_reporting SELECT ls.created_on, ls.id, ls.partnership_account_id, ls.status, ls.call_source, ls.progress_status, ls.duration, ls.starttime, ls.endtime, ls.call_type, ls.call_sid, ls.missed_call_cause, ls.cause_description, ls.recording_id, ls.widget_guid, ls.inbound_id, ls.agent_id, ls.contact_id, ls.response_time_seconds FROM leads ls WHERE ls.id > :current_call_id)�current_call_idzsSELECT SETVAL('public.leads_reporting_id_seq', COALESCE(MAX(id), 1) ) as last_value_id FROM public.leads_reporting;a9INSERT INTO messages_reporting SELECT ms.created_on, ms.id, ms.type, ms.provider, ms.provider_message_id, ms.num_media_files, ms.status, ms.delivery_code, ms.delivery_description, ms.direction, ms.inbound_id, ms.partnership_account_id, ms.contact_id, ms.agent_id FROM messages ms WHERE ms.id > :current_message_id)�current_message_idzySELECT SETVAL('public.messages_reporting_id_seq', COALESCE(MAX(id), 1) ) as last_value_id FROM public.messages_reporting;z�INSERT INTO form_leads_reporting SELECT fl.created_on, fl.id, fl.form_id, fl.partnership_account_id, fl.external_api_lead_id, fl.email_sent, fl.contact_id FROM form_leads fl WHERE fl.id > :current_form_lead_id)�current_form_lead_idz}SELECT SETVAL('public.form_leads_reporting_id_seq', COALESCE(MAX(id), 1) ) as last_value_id FROM public.form_leads_reporting;aINSERT INTO credit_reports_reporting SELECT cr.created_on, cr.id, cr.service_provider, cr.transaction_id, cr.credit_bureau, cr.product_type, cr.description, cr.contact_id, cr.partnership_account_id FROM credit_reports cr WHERE cr.id > :current_credit_report_id)�current_credit_report_idz�SELECT SETVAL('public.credit_reports_reporting_id_seq', COALESCE(MAX(id), 1) ) as last_value_id FROM public.credit_reports_reporting;r�z The daily reporting table sync: z

current contact id: z
current contact report id: z

current call id: z
current call report id: z

current message id: z
current message report id: z

current form lead id: z
current form lead report id: z

current credit report id: z"
current credit report report id: r�r@zDaily Reporting Table Sync - r�NTz1The daily update of reporting tables failed for: z'Daily Reporting Table Updates Failed - )rrr
rrr
r�r�r�r�r�r	�printrI�call_id�
message_id�form_lead_id�credit_report_id�
last_value_idr�r�r�r�rr�)%rr
�
today_date�today_date_timer��id_query_contacts�id_query_calls�id_query_messages�id_query_form_leads�id_query_credit_reportsr��iqcsr��iqclr��iqmr��iqflr��iqcr�contact_reporting_id�call_reporting_id�message_reporting_id�form_lead_reporting_id�credit_report_reporting_id�contacts_reporting_set_value�c�calls_reporting_set_value�cl�messages_reporting_set_value�m�form_leads_reporting_set_value�flr�"credit_reports_reporting_set_value�crrr�rr/r/r0�update_reporting_tableses







�
�
�
�
�


����������������	�	�
�
�
�
�*�
�*��r�)"�loggingr(�flaskrr�
buyercall.appr�"buyercall.blueprints.reports.viewsrr�r�celery.exceptionsr�
sqlalchemyrr	r�r
rr�buyercall.extensionsr
r�buyercall.lib.flask_mailplusr�	getLogger�__name__r$�celery�taskr1r r�r�r/r/r/r0�<module>s<



$

|

5