o

�we�

�@s
dd
lZdd
l
Z
ddlmZmZ
ddlmZ
ddlmZ
ddl	m
Z

ddlmZ
ddl
mZmZ
dd
lZddlmZmZmZ
dd	lmZ
dd
lmZ
ddlmZ
e�e�
Zee�
Zejdd
�
dd��
Zejdd
�
dd��
Zejdd
�
dd��
Z ejdd
�
dd��
Z!d
S)�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_limitcKs
tpt
�}
|
t_|
���m
d
dlm}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}~w
w	Wd�
dS1s|w



Y
dS)	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�d
dlm
}
d
dlm}m}
d
dl}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,�f
d4d5�|eD�
�
�s�t�d*d	d6�}u|e�|u�

nt-�f
d7d5�|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�|'d
k�rtt.t/|&�
t/|'�
t/|'�
d:d�}znd
}z|)d
k�r�t.t/|(�
t/|)�
t/|)�
d:d�}{nd
}{|,d
k�r�t.t/|*�
t/|,�
t/|,�
d:d�}|nd
}||+d
k�r�t.t/|*�
t/|+�
t/|+�
d:d�}}nd
}}|.d
k�r�t.t/|-�
t/|.�
t/|.�
d:d�}~nd
}~|2d
k�r�t.t/|1�
t/|2�
t/|2�
d:d�}nd
}|0d
k�r
t.t/|/�
t/|0�
t/|0�
d:d�}�nd
}�|	|z�
}�|	|{�
}�|	|}�
}�|	|~�
}�|	|�
}�|	|��
}�|zd
k�r/d;t0|z�
}z|{d
k�r:d;t0|{�
}{|}d
k�rEd;t0|}�
}}|~d
k�rPd;t0|~�
}~|d
k�r[d;t0|�
}|�d
k�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<d
dnl3m4}�
|�j
�t5|�j6|k|�j7|
k|�j8|k��
��}�do|�j9v�r�||�j9do�
}�t:�}�|��;||�j9do�
�

dp}�t<|�f
dqdri
|��
�
}�zd
dsl=m>}�
|�|�|jdt|�du�
Wnt?�y�
}�
z
t@�AtB�C��

WYd}�~�nd}�~�w
wtj�D�
	Wd�
WdvS1�s�w



Y
WdStE�	y


t�p�t�}|t_	|�
��&
d
dsl=m>}�
dwt0|�
dxt0|�
}�|�dyg
d	dz|�d{�
	Wd�
YdS1�s�w



Y
YdSw)|z�
    Send daily leads report per partnership account id

    :return: Result of sending daily report per partnership account
    r
)
�split_emails)�PartnershiprNc
S�(|d
krd}
|
S|d
krd}
|
Sd}
|
S�Nr
z#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[c
3s"�|]}
|
�d�
�dkV
qd
S�rFN)
�get)�.0�sd�
�	lead_itemr/r0�	<genexpr>�
s� zBsend_daily_report_email_per_partnership_account.<locals>.<genexpr>)rFrQc
3s$�|]
}
|
d�dkr|
V
qd
Srar/)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 iXc
7
KsLd
dlm
}
|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}
|
d
k
rmd|
}
t�
tj�
jd}|d
k
r~d|}t�
tj�
jd}|d
k
r�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#|�
d
k�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#d
k�r�t'|/d$|/d#dS�
|/d.<|/d'd
k�r�t'|/d(|/d'dS�
|/d/<|/d)d
k�r�t'|/d*|/d)dS�
|/d0<|/d+d
k�r�t'|/d,|/d+dS�
|/dT<�q�|.d#d
k�r�t'|.d$|.d#dS�
|.d.<|.d'd
k�rt'|.d(|.d'dS�
|.d/<|.d)d
k�rt'|.d*|.d)dS�
|.d0<|.d+d
k�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#d
k�r�t'|d$|d#dS�
|d.<|d'd
k�r�t'|d(|d'dS�
|d/<|d)d
k�r�t'|d*|d)dS�
|d0<|d+d
k�r�t'|d,|d+dS�
|d1<t(dU||||||||dV�	}4|4WSt)�y%


d
dWl*m+}5
dXt|j%�
dYt|�
}6|5dZg
dd[|6d\�
YdSw)^Nr
)
�Userc
Sr5r6r/r8r/r/r0r;�r<zDgenerate_credit_service_provider_report_pdf.<locals>.color_indicatorc

Ss|d
kr
dt|�
}t|�
S)Nr
rk)
r��
r9r/r/r0�add_plus�s


z=generate_credit_service_provider_report_pdf.<locals>.add_plusc

Ss|d
krd}|Sd}|S)Nr
TFr/r
r/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_dataay
SELECT 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_coderN
z�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_idsa
SELECT 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)rU
rN
z%-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.modelsr
rrrrrrr
rr�r�r
�nowr�utcrX
rr�rbr��replace�lowerr�r�r�r�r�r	r�r�r�partner_account_coderN
r��len�tuple�partnership_account_idrD�intrrr�r�)7r#r*r
�userrr�r;r
r
�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�rX
rY
�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�r
r/r/r0�+generate_credit_service_provider_report_pdf�s�




	

�


















��������	�
���
������������������"
	
�




��������	�
���
�������������������!
��������	�
���
�������������������!






��������	�
���
�������������������!


��(



�

�









































�











�J
























�����������2











����


�






��r�
c%Cs��
z�tpt
�}|t_|���
�p
d
dlm}

|
���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}!�
qd
dlm}#
dt|�
dt|
�
dt|�
dt|�
dt|�
dt|�
d t|�
d!t|�
d"t|�
d#t|�
d$t|!�
}$|#d%g
d&d't|�
|$d(�
tj��
	Wd)�
Wd*S1�
s�w



Y
Wd)St�
y�


t�
p�t
�}|t_|���(
d
dlm}#
d+tt���
}$|#d%g
d&d,tt���
|$d(�
	Wd)�
Yd)S1�
s�w



Y
Yd)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_seqap
INSERT 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;a9
INSERT 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;a
INSERT 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�r
r/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