o
    ނi'3                    @   s  d dl Z d dlZd dlZd dlZd dlZd dlZ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mZmZ d dlmZmZ d dlmZmZmZmZ d dlmZ d dlmZ d dlmZ d dlmZ d d	lmZmZ d d
lmZ d dlmZ d dl m!Z! d dl"m#Z#m$Z$ ddl%m&Z& d dl'm(Z( d dl)m*Z* ddl+m,Z, d dl-m.Z.m/Z/m0Z0m1Z1m2Z2m3Z3m4Z4m5Z5m6Z6m7Z7m8Z8m9Z9m:Z:m;Z;m<Z<m=Z=m>Z>m?Z?m@Z@mAZAmBZBmCZCmDZDmEZEmFZFmGZGmHZHmIZImJZJmKZKmLZLmMZMmNZNmOZOmPZPmQZQmRZRmSZSmTZTmUZUmVZVmWZWmXZXmYZYmZZZm[Z[m\Z\m]Z] d dl^m_Z_m`Z`maZambZbmcZcmdZd d dlemfZf d dlgmhZh ddl%miZimjZj ddlkmlZl d dlmmnZn ddlompZp ddlqmrZr ddlsmtZtmuZu dd Zvdd  Zwd!d" Zxd#d$ Zyd%d& Zzd'd( Z{d)d* Z|d+d, Z}d-d. Z~d/d0 Zd1d2 Zd3d4 Zd5d6 Zd7d8 Zdd:d;Zd<d= Zd>d? Zd@dA ZdBdC ZdDdE ZdFdG ZdHdI ZdJdK ZdLdM ZdNdO ZdPdQ ZdRdS ZdTdU ZdVdW ZdXdY ZdZd[ Zd\d] Zd^d_ Zd`da Zdbdc Zddde Zdfdg Zdhdi Zdjdk Zdldm Zdndo ZddpdqZdrds Zdtdu Zdvdw Zdxdy Zdzd{ Zd|d} Zd~d Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd ZdddZdd Zdd Zdd Zdd Zdd Zdd Z			dddZdd Zdd Zd dlmZ 				dddZdd Zdd Zdd Zdd Zdd Zdd Zd dlmZ 				dddZdd Zdd Zdd Zdd ZddÄ Zd dlmZmZmZmZ ddƄ ZddȄ Zddʄ Zdd̄ Zdd΄ ZddЄ ZdS )    N)QCountAvg)datetimedate)r   	timedeltatimer   )r   defaultdict)timezone)redirect)urljoinurlparse)Token)pisa)load_workbook)BorderSide   )public_email_domains)	getConfig)BASE_DIR)sendRegistrainMail)0AccountCompanyCreditsCompanyDataCreditsJobDesc	CandidatePaymentsRegistrationReferenceIdCompanyUser	User_dataRolesPermissionsWorkflowCallScheduleVacationWorkCalExtendedHours
HolidayCal	QResponseCdnData
IvFeedbackEmail_templateInterviewMediaBrulesBrandingRole
JdAnalysisSourceUploadsResumeProfileProfileActivityProfileEducationProfileExperienceProfileProjectsProfileSkillsProfileAwardsProfileCertificatesProfileAddress
ResumeFileLookupmasterProfileAnalysis	WorkspaceClientJobBoardJobBoardCredentialJDJobBoards)saveJdNewTest	saveAddJDsaveUpdateJddeleteTestInJdDBsaveInterviewersJDaddProfileActivityDB)	sendEmail)model_to_dict)const_candidate_statusconst_paper_types)validate_excel_with_json)settings)addCandidateDB)calculateJDProfileMatching)convertProfilingVideocompareVoicesc              
   C   s   z;t | d }|dd }|tv rW dS tjj|d}|r!W dS t| d || d | d | d	 t d
}|  W dS  t	yH } z d }~ww )N
reg-bemail@r   )companyemail   reg-companyreg-locationreg-namereg-companytype)companynamer[   locationcontactpersoncompanytyperegisterationtimer   )
strsplitr   r   objectsfilterr   nowsave	Exception)dataObjsbussiness_emailemail_domainemail_checkcompany_datae rs   3/home/acert/hirelines/app_api/functions/services.pyaddCompanyDataServicec   s,   ru   c              
   C   s  zLt | d }|dd }|tv rW dS tjj|d }|r$W dS tjj|d }t }|r5W dS t| d ||| d	 t	
 d
dd}|  t| d t	
 | d |jd||d
d}|  t d }t|jt	
 dd|d d}	|	  tjj|jd\}
}|	j|
_|
jrt|	jt|
j n|	j|
_|
  t|j|	jd|j|	j|	j|
jd}|  t|jd|d d  t|jd|d d  t|jd|d d  tjjdd }t|j|j|jr|jnd |jr|jnd d
d!}|  d"|jd#}t||j|j|jd$}|  t d% d& }d'}t||}|j|j|j|t |j|jd(}t j!||d)}t d% d* }|j|j"|j#| d+d,}t$| W dS  t%ya } zt&t |  d }~ww )-NrY   rZ   r   emailr\   )emaildomain   r]   r`   AI)namerx   rw   rd   registrationdatestatus	freetrailr_   r^   HR-Admin)r|   	datentimerb   	companyidrolepasswordrw   r~   	FREETRAILTi0u  registration_grace_credits)r   	dateofpay	modeofpayamountcreditsr   C)r   transdatetime	transtypeusertransidpointsbalanceSscreening_credits_charges)r   r   r   Ecoding_credits_chargesinterview_credits_chargesr    )r   contentlogourlsociallinksr~   RC03codelabelr   useridDOMAINacertz/api/add-company)idcompany_namebrand_contentcompany_emailcompany_logor   json	hirelinesz/login)r|   rw   r   url)'rf   rg   r   r#   rh   ri   lastr"   generate_random_passwordr   rj   rk   r   r   r   r   get_or_creater   creditr   intr   r   r   r2   r   r   r   r5   r|   r   r   requestspostrw   r   r   rl   print)rm   rn   user_email_domain
user_checkcompany_checkrandom_passwordcompanyr   free_trail_datapaymentscompany_accountcompany_account_flagr   default_brandingcompany_brandingr   sourceacert_domainendpointr   rq   send_company_datahirelines_domain	mail_datarr   rs   rs   rt   registerUserService   s   	

	

	r   c                 C   s  zt jj| d }tjj| ddjddd}|rd}d}d}d}d}d}	d}
d}d}tjj|| dj|d	}|D ]X}|jd
krX|d7 }|j	dkrN|d7 }q9|j	dkrW|d7 }q9|jdkru|d7 }|j	dkrk|d7 }q9|j	dkrt|	d7 }	q9|jdkr|
d7 }
|j	dkr|d7 }q9|j	dkr|d7 }q9|j
|j||||  ||||||	  ||	|
|
||  |||jr|jndd}|W S W d S  ty } z d }~ww )Nr   Y)jobid
deleteflagr   Tflatr   r   r   )candidateid__inr   r   FPr   r{   ORN)r   titlescreening_testsscreening_pendingscreening_failscreening_passcoding_testscoding_pendingcoding_failcoding_pass
interviewsinterview_pendingoffer_lettersrejecteddisplay_flag)r   rh   ri   r   r   values_listr    exclude	papertyper~   r   r   dashboardflagrl   )jid
company_idjob_descdeleted_candidate_idsr   r   r   r   r   r   r   r   r   registrationsregistationjd_datarr   rs   rs   rt   getJobDescData  s   













=r   c              
   C   s6   z| rt | |}|W S W d S  ty } z d }~ww N)rI   rl   )jdDatacompyId	test_datarr   rs   rs   rt   	jdTestAddi  s   
r   c              
   C   s.   z	t | ||}|W S  ty } z d }~ww r   )rJ   rl   )	addjdData	companyIDhrEmailr   rr   rs   rs   rt   addJdServicesr  s   r   c           
   
   C   s   z;g }t jj| dd}|D ]}t|}|| qtj }g }|D ]}t|}|d dkr5|| q$||dW S  tyH }	 z d }	~	ww )Nr   -idNamer   )usrsroles)	r#   rh   ri   order_byrP   appendr3   allrl   )
r   usersDataLstuserLstr   userDatalstRolesroleslstroleeuserRolerr   rs   rs   rt   companyUserLstz  s$   

r  c              
   C   s  zt jj| d d }|ri d|jd|jd u rdn|jd|jd u r&dn|jd|jd u r1dn|jd|jd u r<dn|jd	|j	d u rGdn|j	d
|j
d u rRdn|j
d|jd u r]dn|jd|jd u rhdn|jd|jd u rsdn|j d|jd u rdn|j d|jd u rdn|jd|jd u rdn|jd|jd u rdn|jd|jd u rdn|jd|jd u rdn|j}|W S W d S  ty } z d }~ww )NJdIDr   r   r   r   r   descriptionexpminexpmax
departmentrb   budgetskillsetsecondaryskills
skillnotesexpjoindate	positionsr~   hiringmanager)r   rh   ri   r   r   r   r   r  r	  r
  r  rb   r  r  stripr  r  r  r  r~   hiringmanageridrl   )r   r   
jdDataDictrr   rs   rs   rt   updateJdDataService  sT   	
#r  c              
   C   s.   z	t | || W d S  ty } z d }~ww r   )rK   rl   )r   r   r   rr   rs   rs   rt   updateJdServices  s   r  c                 C   s  z| d }| d }| d }| d }| d }| d }| d }t |||||||t dd		}t d
}	tjjdd||	d\}
}|dkrNd}||
_|
	  |dkr^|
jd }||
_|
	  t
dt|
j}|
j |
j | }||_|	  t d d }d}t||}|||||||jd}tj||d}|j}|rt|d}t|j|j|jd|j|j|d d}|	  W d S W d S  ty } z d }~ww )Nfnamelnamerw   mobilepaper_idr   job_idr   )		firstnamelastnamer   paperidrw   r  r   r}   r~   z%yr   z{:03})typeprefix1prefix2Tr   Fz{:05}r   r   z/api/hirelines-add-candidate)r  r  rw   r  r  r   reference_idr   utf-8data)candidateidr  r}   r~   r   r   r   )r   r   rj   strftimer!   rh   r   formatlastidrk   rf   r   r!  r"  r&  r   r   r   r   r   r   loadsdecoder    r   r  r}   r   r   rl   )rm   r  r  rw   r  r  r   r  	candidateyear	refid_obj
refid_flagr)  candidate_id_seqcandidate_coder   r   r   candidate_datasend_candidate_dataresponse_content	json_datac_registrationrr   rs   rs   rt   candidateRegistrationService  s   




r7  c              
   C   s  zt jj|d}tjj| |jdjddd}g }|D ]b}tjj|j	|jd}|j	|j
|j d|j |jg g g dd	}|D ]8}|jd
|jd}	|jdkr[|d d |	 q@|jdkrj|d d |	 q@|jdkrx|d d |	 q@|| q|W S  ty }
 z d }
~
ww )Nr   )r   r   r   r   r   r&  r    r   r   r{   )r   cidr|   rw   r   %Y-%m-%d)r   r~   r   r   r   r{   )r#   rh   getr   ri   r   r   r   r    r   r&  r  r  rw   r}   r'  r~   r   r   rl   )r   r   r   
candidatescandidates_datar,  r   candidate_inforegistrationregistration_inforr   rs   rs   rt   getJdCandidatesData  sF   




rD  c                 C   s   zHt jj| d}g }tjj|jdjddd}|D ]*}t|j	d}t
jj|jd }||j|j|j|j|j||r@|jndd q|W S  tyU } z d }~ww )Nr   r   r   r8  r   r   )r   candidate_idr  r  rw   r~   jd_title)r#   rh   r>  r   ri   r   r   r   rQ   r~   r   r   r   r   r   r&  r  r  rw   r   rl   )r   r   candidates_listr?  r,  c_statusr   rr   rs   rs   rt   getCandidatesDataF  s4   rI  c              
   C   s  zt jj| d | d dd}W n   Y dS z\|rptjj|j|jdd}|s4t|j|j|jddd	  tjj|jd
	d
 }tjj|j|jdd}d|_|  |rY|jW S tjj|jd
 }tjj|d\}}|jW S W d S  ty } ztt|  d }~ww )Nrw   r   rz   )rw   r   r~   r   r   )	usr_emailusr_passwordr   T)usernamerJ  rK  is_staffr   )user__usr_emailz-created)rJ  )r   )r#   rh   r>  r$   ri   rw   r   rk   r   r   firstrM  keyr   rl   r   rf   )rm   r   check1	token_obj	user_datacheck
token_flagrr   rs   rs   rt   authentication_servicek  sP   


rV  c              
      s   z]t jj| dd}g }|D ]I  fdd|D }|s8 j j jg d}|d  j j	d || q|rX fdd|D }|d	 d  j j	d ||d	  q|}|W S  t
yp } ztt|  d }~ww )
N)enable__containsorderbyc                    s   g | ]}|d   j kr|qS menuItemParentfunction).0funcr[  rs   rt   
<listcomp>  s
    z)get_functions_service.<locals>.<listcomp>)rZ  UIIconmenuItemKeychildrb  )menuItemNamemenuItemLinkc                    s   g | ]}|d   j kr|qS rY  r[  )r]  menuItemr[  rs   rt   r_    s    r   )r%   rh   ri   r   r\  function_iconfunction_categoryr   sub_functionfunction_linkrl   r   rf   )	user_role	functionstempfunctionObjList	uifuncObjmenuItemListrr   rs   r[  rt   get_functions_service  sR   

rp  c              
   C   s   zTt jj| d }|rLtjj|jd}t d d }|jdkr?|j	dkr?|j
tt|d }t |kr?d|_|  W d	S |jdkrO|j	dkrRW d	S W d S W d S W d S  tya } z d }~ww )
Nrv   r   r   daysr{   r   rq  r   T)r#   rh   ri   r   r"   r>  r   r   r   r~   r}   r   r   r   rj   rk   rl   )	user_mailr   r   
trial_daystrial_end_daterr   rs   rs   rt   checkCompanyTrailPeriod  s*   rv     c              
      sJ   zt jt j  d fddt| D }|W S  ty$ } z d }~ww )Nr   c                 3   s    | ]}t  V  qd S r   )secretschoice)r]  _
charactersrs   rt   	<genexpr>  s    z+generate_random_password.<locals>.<genexpr>)stringascii_lettersdigitsjoinrangerl   )lengthr   rr   rs   r{  rt   r     s   r   c              
   C   sb   z#t jj| djddgd}g }|r!|D ]}||j|jd q|W S  ty0 } z d }~ww )Nr   Dr{   
status__inr   r   )r   rh   ri   r   r   r   r   rl   )r<  company_jdsjds_listjdrr   rs   rs   rt   getCompanyJdData  s   r  c           	   
   C   s   zrt tjj| d }t|dkrm|D ]}tjj|d d }d}|r(|j}||d< q|	  g }g }|D ]/}|d dkrE|
| q7|d	kra|d d
krT|d sTq7|d dkr[q7|
| q7|
| q7||dW S g g dW S  ty } z d }~ww )Nr   r   	createdbyr   r   createdbyUserNamer~   r{   Hiring-Managerr  approval_statusr   )activeJd
inactiveJd)listr   rh   ri   valueslenr#   r   r|   reverser   rl   )		companyIdrj  r  r  r  userNameInactiveJds	activeJdsrr   rs   rs   rt   getCompanyJDsList  s:   

r  c              
   C   s  zt jj| d }g }|j}|rIg }td| t|}|D ](}t|	 d }d|| v r7|| 
dn|| g}	|	D ]	}
||
  q>q g }g }|rtjjd|g dddd	}tjj| dd
 }i }|D ]}dd | D }|||d < qm|D ]C}|r||d |d	 d g }|jrt|j}|D ]%}|rttjj|ddd	}|r|r||d d |d d	 d qqi d|jd|jd u rdn|jd|jd u rdn|jd|jd u rdn|jd|jd u rdn|jd|jd u rdn|jd|jd u r
dn|jd|jd u rdn|jd|jd u r"dn|jd|jd u r.dn|jd|jd u r:dn|jd|jd u rFdn|jd|jd u rRdn|jd|jd u r^dn|jd|jd u rjdn|jd|jd u rvdn|jd|j d u rdn|j |j!d u rdn|j!|j"d u rdn|j"|j#d u rdn|j#|||||j$d u rdn|j$d}|W S W d S  t%y } z d }~ww ) Nr   zskillLst :: r   ,rz   )Interviewerr   zHR-Executive)r~   r   role__inr   r|   )r   
teststatusc                 S   s"   i | ]\}}||d ur|ndqS )Nr   rs   )r]  rP  valuers   rs   rt   
<dictcomp>  s    zjdDetails.<locals>.<dictcomp>r   r|   jdlibraryidr   r   r   r  r	  r
  r  rb   r  r  r  interviewersr  r  r  r~   )commentsr  r   interviewes_lstselectedInterviewerLst
skillsListworkFlowDetailsr  )&r   rh   ri   r   r  r   astliteral_evalr  keysrg   r   r  r#   r  r&   itemsr  r   r  r   r   r  r	  r
  r  rb   r  r  r  r  r  r~   r  r  r   r  rl   )jdIdr  r   r  skillLstmodifiedSkillListparsed_skillLstskillrP  	subSkillssubSkillr  total_interviewers_lstr  workFlowListworkFlowData	cleanDatainterviewerjd_interviewersselectedInterviewerr  r  rr   rs   rs   rt   	jdDetailsz  s   
$


	
r  c              
   C   sz   z/|d r+t jj|d d }|r&|jr!|j|j|j|jdW S ddiW S ddiW S ddiW S  ty< } z d }~ww )N
workFlowIdr   )paperIdtestid	libraryId	paperTyper  r   )	r&   rh   ri   r   r  r   paperlibraryidr   rl   )r   rm   r  rr   rs   rs   rt   checkTestHasPaperService  s"   


r  c              
   C   *   zt |}|W S  ty } z d }~ww r   )rL   rl   r   rm   testDetailsrr   rs   rs   rt   deleteTestInJdService     r  c              
   C   r  r   )rM   rl   r  rs   rs   rt   saveInterviewersService   r  r  c              
   C   sZ  zt jjdd|d}g }|D ]}i }|j|d< |j|d< || qg }tjj| d }|D ]$}tjj|d |d |d	 d
	 }|rS|j
|d< |j|d< |j|d< q/ttjj| d }	d }
d}t|	dkr|	d d }
g }|	d d d}|D ]	}||  q||	d d rt|	d d }t|||
||dW S  ty } z d }~ww )Nrz   r  )r~   r   r   userIdr|   r   r   r   r   )
workflowid	jobdescidr   promotholdholdpercentager   r   r   r~   r  r  r  )r  jdInterviewersjdStatusselectedJDInterviewers	skillsLst)r#   rh   ri   r   r|   r   r&   r  r1   r   	passscorer  r  r  r   r  rg   r  r  r  rl   )r%  cmpyIdintervierDeatilsinterviewersLstr  interviewerDatapapersDetailstestbrulesDetailsr   JdStatusselectedJdInterviewersjdSkillsList	skillListr  rr   rs   rs   rt   workFlowDataService5  s\   




r  c           
   
   C   sp  zC|  dg }|std W d S t d d }d}t||}d|i}tj||d}|  |j}|r=t	|
d}|W S td	 W d S  tjjy_ }	 ztd
|	  W Y d }	~	d S d }	~	w tjyy }	 ztd|	  W Y d }	~	d S d }	~	w ty }	 ztd|	  W Y d }	~	d S d }	~	w ty }	 ztd|	  W Y d }	~	d S d }	~	w ty }	 z d }	~	ww )NskillsSetLstz*Error: 'skillsSetLst' is empty or missing.r   r   z$/api/getTopicAndSubtopicAndQuestionsr%  r   r$  zResponse content is empty.zHTTP request failed: zFailed to decode JSON: z!Configuration error: Missing key An unexpected error occurred: )r>  r   r   r   r   r   raise_for_statusr   r   r*  r+  
exceptionsRequestExceptionJSONDecodeErrorKeyErrorrl   )
rm   r  r   r   r   startQuestionDataresponser4  r5  rr   rs   rs   rt   1skillsWithTopicsWithSubtopicsWithQuestionsServicek  sB   
r  c              
   C   sh   z&t jj| |ddd}g }|r$|D ]}||j|j|j|jd q|W S  t	y3 } z d }~ww )NF)r   r   paperid__isnullorder)r   r   r   r  )
r&   rh   ri   r   r   r   r   
papertitler  rl   )r   r<  jd_workflowsworkflow_dataworkflowrr   rs   rs   rt   getJdWorkflowService  s*   	r  c           	   
   C   s   zIt jj| d}tjj|jd}|jrt|j}nd}g }|D ]}tjj|d}|	|j
|jd q |j
|j d|j |j|jd}||fW S  tyV } z d }~ww )Nr   r   r  r:  )r<  c_namec_emailc_mobile)r   rh   r>  r   r   r  r  r  r#   r   r   r|   r  r  rw   r  rl   )	r<  r,  r  r  job_interviewersjob_interviewerr  r2  rr   rs   rs   rt   getCallScheduleDetails  s(   
r  c           9   
      sB  zt  d }t|d }t|d }t|d }t j|dddd}g }ttjt	ddt	d	dB 
d
d}g }	|D ]}
|
d rWt|
d }|	|d|
d g q>ttjj|dddd}ttjj|d }tjj|dd }i }|D ]0}|d |d  jd }t|D ]}|d t|d }|d}|d |d |d d||< qqtddD ]}g }g }g }ttjjd|dj
ddd}|t|d }|d}|d  |d!}t|d" d#}|D ]0}t|d tj}t|d tj}||  kr|krn q|d |v r||d  qtjj|d$ rOt|d% D ]} |td&|  d' }!||!d( |d) |g  q.n!td|d% D ]} |td&|  d' }"|"d(}#||# t t|d&d* }$|t dko|"|$k}%|%r|d+ |g  qVt  }&|D ]d,}'||v r|| }(|(d kr|(d })t!|(d }*t|" |)}+|+t|*d- },|+|"  kr|,k rn nMd}'nJ fd.d/|D }-|-D ]=}.|.d })t#|)t$rt|)d0 })t!|.d1 }*t|" |)}/|/t|*d- }0|/|"  kr|0k rn qd}' nq|'r(|&% qg }1|&D ],| d2|# }2d,}3|	D ]}4|4d |2krO|4d krOd}3 nq;|3sY|1 q.|1sd|d3 n|d4 ||1 qVd5d/ |D }5|||||5d6}6||6 q|W S  t&y }7 zdd l'}8t(|8)  |7d }7~7ww )7NCALL_SCHEDULING_CONSTRAINTS
work_hoursstarting_hourblock_hoursr   )hourminutesecondmicrosecondr   r~   r   r   intervieweridz%Y-%m-%d %I:%M %pr   )empidr  fromdatetodater   rz   )r  r~   rr  r=  	starttime	workhoursr   )r  r  r   rw  )r~   r   r   Tr   %Az%a-%d-%b-%Yz	 00:00:00z%Y-%m-%d %H:%M:%S)	holidaydtr\      minutesz%I:%M %pHoliday)hoursr  BlockedFr  c                    s(   g | ]}|d  kr|d  kr|qS )r   startdayrs   )r]  w	_day_nameuser_idrs   rt   r_  H  s    

z.interviewSchedulingService.<locals>.<listcomp>z%H:%M:%Sr  r:  
No_Vacancy	Availablec                 S      g | ]}t |qS rs   )r  r]  srs   rs   rt   r_        )day
hours_list
slots_listr~   ids)*r   r   r   todayreplacer  r'   rh   ri   r   r   r   	localtimer   r'  r(   r  r)   r*   rq  r  r   r#   strptimecombiner   minmaxremover+   existsrj   setfloatr   
isinstancerf   addrl   	tracebackr   
format_exc)9aplidint_idcall_scheduling_constraints
WORK_HOURSSTARTING_HOURBLOCK_HOURSbasedtscheduling_datascheduled_callsscheduled_calls_listscheduled_calllocal_db_timevacation_dataworkcal_dataalter_timings_dataalter_timings_listalter	days_diffxcurr_alter_datedate_strr  r  status_listtelecallerscurrent_date_obj	_date_strdate_formattedcurrent_date_startvacationv_fromv_toi	slot_timeslot_dtslot_time_strcurr_time_limitis_past_or_blockedworking_users_this_slot
is_working
alter_datastart_tw_hoursstart_dt_alterend_dt_alteruser_shiftsshiftstart_dt_shiftend_dt_shiftavailable_for_slotcall_key	is_bookedcallr  dataObjrr   r-  rs   r  rt   interviewSchedulingService  s>  













rc  c                 C   s   zBt jj| ddd}g }|D ]/}tjj|jd}tjj|jd}|	|j
|j d|j |jd|j|j|j|j
d q|W S  tyO } z d }~ww )Nr   )r  r~   r   r   r:  %d-%b-%Y %I:%M %p)r   r|   scheduled_timerw   c_coder  scd_id)r'   rh   ri   r   r   r>  r&  r   r   r   r   r  r  r   r'  rw   r   rl   )r   call_detailsr?  	call_datar,  r  rr   rs   rs   rt   getInterviewerCandidates  s2   
rj  c              
   C   s&  zd d d d d d dd}t  d d }d}t||}tjj| d}tjj|jd}|j|jd}tj	||d}|j
}	|	ryt|	d	}
|
d
 d }|
d
 d }|
d
 d }|
d
 d }td| |rmt||j d|d< ||d< ||d< ||d< tjj|jd}d}|r|jrt|j}d}|D ]}tt| }|d7 }t||kr||7 }q||d 7 }q|j|j|j||j|jd}tjj |jd! j"}|j#$dd }|j|j% d|j& |j'|j(|j|r|nd||jd}|j)rt*|j)}||d< ||d< ||d < |W S  t+y } z d }~ww )!Nr   )job_desc_datar2  interview_datascreening_datacoding_dataprofile_dataprofiling_videor   r   z/api/candidate-interviewdatar   )rf  int_paperidr   r$  r%  interviewdatascreeningdata
codingdataprofiling_video_urlr   rp  rl  rm  rn  r   r   r   , )rF  r   rb   skillsnotesinstructions)r  apir:  zN/A)r   r|   r  rw   r   	int_papermeetinglinkschd_idro  rk  r2  ),r   r   r'   rh   r>  r   r&  r  r   r   r   r   r*  r+  r   rW   r   r   r   r  r  r  nextiterr  r  r   r   rb   r  ry  r&   ri   r   r  r|  rg   r  r  r  rw   	profileidgetProfileDetailsServicerl   )rg  respr   r   r   rh  r,  candidate_int_datar3  r4  r5  rl  rm  rn  ru  r   skillsString
skillesSet
skillCountr  r  rk  int_paper_titlemeeting_linkr2  profile_detailsrr   rs   rs   rt   getCandidateInterviewData  s   







r  c           	   
   C   s   z9| d }| d }t jj|d }d}|r|j}tjj||d}g }|r4|D ]}||j|jd q'||dW S  t	yF } z d }~ww )N
candid__idcandid_call_sched_idr   r   )callscheduleidr&  )q_idq_res)q_lstremark_note)
r'   rh   ri   r   intnotesr,   r   qidqraterl   )	rm   rE  call_schedule_idcall_schd_dataremark_note_dataques_lstquestions_lstquesrr   rs   rs   rt   questionsResponseService  s&   r  c              
   C   sJ   zt jj| d | d d}|jdkrW dS W dS  ty$ } z d }~ww )Nschedule_idrE  )r   r&  r   
call_endedcall_active)r'   rh   r>  callendflagrl   )rm   call_schedulerr   rs   rs   rt   getInterviewStatusService5  s   
r  c               
   C   s>   zt j  } | j| jd}|W S  ty } z d }~ww )N)auth_key	libraryid)r-   rh   ri   r   authkeyr  rl   )cdncdn_datarr   rs   rs   rt   
getCdnDataE  s   r  c              
   C   s  zt jj| d d }t |_d|_|  z t	jj|j
|d }| d |_| d |_|jf|_|  W n   t	|j
|| d | d |jd}|  Y tjj|j
d}d|_|  tjj|jd}t|j}d	d
 |D }tjj|jdj}g }	tjjd|jd}
d}|
D ]}|j|v r|	|j |j|jkr|j}qd|	}| d| }|j
|j|||j|jd}t  d d }d}t!||}t"j#||d}|j$rt%|j$dd| W d S W d S  t&y } z d }~ww )Nsch_idr   r   r&  r  gonogorx  )r&  r  r  rx  r   r{   c                 S   r  rs   r   r]  itemrs   rs   rt   r_  r  r  z.interviewCompletionService.<locals>.<listcomp>rz   )r~   r   r   rv  r  )r1  rF  interviewed_byto_mailr  	int_notesr   r   z/api/interview-completionr   ICzInterview Completed)'r'   rh   ri   r   r   rj   callendeddttr~   rk   r.   r&  r  rx  r   r   r>  r   r   r  r  r  r#   hridrw   r   r   r  r|   r  r   r  r  r   r   r   r   r  rN   rl   )rm   r  call_sch_detailsfeedbackr,  r  interviewers_datar  hr_emailinterviewers_email_listusersr  r   interviewers_emailsr  rl  r   r   r   send_interview_datarr   rs   rs   rt   interviewCompletionServiceR  sx   






	
r  c                 C   s8  zt jj| d}ttjj|j|jdjddd}tt	jj|j|djddd}t
jjd|j|dd	}g }|D ]Q}t	jj|jd}t jj|jd}t jj|jd}	d
}
tjj|j|jd }|rg|j}
|j}||j|j|j d|j |j|j|	j|
|r|dnd
d q:|W S  ty } z d }~ww )Nr   )r   interviewers__containsr   Tr   r   	jobid__inr   )r~   r   r   
-datentimer   r  r:  z%d %b %Y, %I:%M %p)r<  rf  r  r  hrinterviewbyr  interview_datetime)r#   rh   r>  r  r   ri   r   r   r   r   r'   r   r&  r  r  r.   r   r  r   r   r  r  rw   r|   r'  rl   )r   r   job_desc_idscandidate_idscompleted_interviewsuser_interviewsr  r,  r  	interviewr  iv_feedbackinterview_dtrr   rs   rs   rt   getInterviewCandidates  sf   r  c           
   
   C   s   zft jj| d }t }|d }|rdd}tjj| |d }ztjj|jd j}d| d| }W n   d}Y |rR|j	|j
 d|j |j|j|d	}|W S | |j
 d|j d
d|d	}|W S W d S  tys }	 z d }	~	ww )Nr   r  r   r  r&  z'https://iframe.mediadelivery.net/embed//r:  )r&  r|   r  rx  
media_pathr   )r   rh   ri   r   r  r.   r0   r   recordedr&  r  r  r  rx  rl   )
r<  r  r,  r  
library_idfeedback_datainterview_feedbackinterview_file
video_pathrr   rs   rs   rt   getInterviewFeedback  sN   )r  c           #   
   C   s  zt jj| d }d d d d}|rtjj|jd}d}|jr9tjj|j	|jd }|r8|j
 d|j }nd}|j|j|j|j|j|j|j|d}||d< tjj|j|j	d	}d
}d}	g }
d|ji}t d d }d}t||}tj||d}t|jd}|r|D ]1}tjj|j|jd}d}d}d}d}d}d}d}|jdkrd}tjj|j|jd}|j d ur|j nd}|j!r|j!nd}|j"}nn|jdkrd}tjj|j|jd}|j r|j nd}|j!r|j!nd}|j"}nI|jdkr7d}t#jj|j|jd }|r6|j$}|j%r|j%&dnd}|r"t'jj|j(dj)}|j$dkr6d}|j*r4|j*&dnd}	nd}d}d}d}d}|d D ]} | d |jkra| d  }| d! }t+| d" }| d# }qE|
,i d$|jd%|j-d&|jd'|jd(|d)|d*|jd+|j$d,|j.r|j.&dndd-|d.|	d/|d0|d1|d2|d"|d ||||rt+|d3 nd|d4 q|
|d5< ||d d-< t/|j}!|!|d6< |W S  t0y }" z d }"~"ww )7Nr   )rA  registrations_datafeedbacks_datar   r   r    - NA)r<  rf  r  r  rw   r  r  source_labelrA  r9  r   r1  r   r   z!/api/candiate-workflow-percentager   r$  )r   r  r   	Screening)r  r  r   Codingr{   	Interview)r&  r  rd  r   r   r   r%  r  scored_markspaper_marksscore_percentage	star_zeroreg_idpaper_titler  
paper_type
type_titlecall_statusr&  
reg_statuscompletion_datenotify_checkcall_completion_datere  interviewer_namehold_percentagepass_percentager   )r  
hold_check
hold_ranger  r  r  )1r   rh   ri   r   r   r>  r   r   r5   r   r   r   r   r&  r  r  rw   r  r   r    r   r   r   r   r   r*  r   r+  r&   r  r   r1   r  r  r  r'   r~   r   r'  r#   r  r|   r  r   r   r  completiondatefeedbacksDatarl   )#r<  r,  r2  r  r  r   rA  r   r  r  r  registration_datar   r   r   r  workflow_response_datarB  r  r  r  re  r  r  r  r  paper_brulesr  r  r  r  r  percentage_datar  rr   rs   rs   rt   getCandidateWorkflowData  s\  




	
"
r  c               
   C   s  z>t  d d }d}t||}d| i}tjj| d}tjj|jd}tj||d}|j	}|r=d}	d}
d}d}t
|d	}|d
 }t}t|d d}| }|d }	|d }
|d }tjj|jd}|d|j}|d|j d|j }|d|j}|d|j}|d|	}|d|
}|d|}|d|j}|dt|jrt|jnd}d}|jrtjj|j|jd j}|d|}t jj|j!d }|r|j"}|rt#jj|dj$}nd}|d|}t%|j!}|rd}|D ]}d|d   d!|d"  d!|d#  d$}|| }qd%| d&}|d'|}|d(|j d) }t|d*}t&j'||d+}|(  |d,|j)d-W S W d S  t*yS } zt+t|  d }~ww ).Nr   r   z/api/hireline-candidate-reportr1  r  r   r   r   r$  r%  z$/media/reports/candidate_report.htmlrrm  rn  rl  r   z{candidate_id}z{candidate_name}r:  z{candidate_email}z{candidate_mobile}z{screening_section}z{coding_section}z{interview_section}z{#jd_title#}{#comapany_logo#}r  r  z{#c_source#}z#interviewer_name#zb
                            <tr>
                                <td class="td_q"><p class="q_a">r|   zJ</p></td>
                                <td class="td_q"><p class="q_a">decisionrx  zD</p></td>
                            </tr>
                        at  
                        <span class="heading p-clr">Interviewer's Feedback:</span><br><br>
                        <table cellpadding=0 cellspacing=0 class="t0">
                            <thead>
                                <tr style="background-color: #808080;color:#fff">
                                    <th class="td_fd">Interviewer</th>
                                    <th class="td_fd">Decision</th>
                                    <th class="td_fd">Notes</th>
                                </tr>
                            </thead>
                            <tbody>
                                zc
                            </tbody>
                        </table><br><br>
                    z{#feedback_data#}z/media/reports/.pdfw+bdestreport	file_path	file_namepisa_err),r   r   r   rh   r>  r2   r   r   r   r   r   r*  r+  r   openreadr   r   r!  r&  r  r  rw   r  r   rf   r   r   r5   ri   r   r   r'   r   r  r#   r|   r  r   	CreatePDFcloseerrrl   r   ) r<  r   r   r   r2  r,  brandingr3  r4  rm  rn  rl  r  r5  
acert_data	root_pathreport_template_pathreport_templater  updated_reportr  r  r  r  	feedbacksfeedback_rowsfd_datafd_troutput_filepathresult_filepisa_statusrr   rs   rs   rt   generateCandidateReport  s   



|r  c              
   C   sz   z/t jj| d}g }|D ] }tjj|jd}|j}|jdkr dnd}||j	||d q|W S  t
y< } z d }~ww )Nr  r   r   HirezNot Hire)r|   rx  r  )r.   rh   ri   r#   r>  r  rx  r  r   r|   rl   )r<  r  int_feedbacksjob_intr  rx  r  rr   rs   rs   rt   r  l	  s   r  c              
   C   s  zg }| d rt jj| d d }tjj| d d}|D ]}|jd kr/d|j|jd  W S q|r;| d |_|  t jj| d d }d}|D ]?}||_	|d7 }t
|}tjj||jd }	|	rt|	j|d	< |	j|d
< |	j|d< nd |d	< d|d
< d|d< || |  qJ|}
g }tt|
d D ]}|
| |
|d  g}|| q|||jdW S W d S  ty } z d }~ww )NjobDescriptionIdr   r  r   )noPaper
paperTitle	jdStatus_nextStatus_r   )r   r  promotPercentager  r  r   r   )r   
papersDatar  )r   rh   ri   r   r&   r  r  r~   rk   r  rP   r1   r   r  r  r  r   r  r  rl   )rm   r  	paperDataJdDataworflowDatar  latestJDorderCountertempDct
brulesDatapaperLstnewPaperLstpapertempLstrr   rs   rs   rt   jdPublishService	  sf   






;r,  c              
   C   s  z|  d}|  d}tjj |d}tjj |jd}tjj |jd}tjj|j	dd
 }tjj|j	d
 }d}	|rFtjj |jd}|j}	t d d	 }
d
}t|
|}|j d|	 }|||j|j|j|j||j|jd	}tj||d}tjj |j	|jd}||_|  ||_|  |jrtjj |jd}|dkrt|jdd|j	 d|_n|dkrd|_|  W d S W d S  ty } zt t!|  d }~ww )Nnotifyr<  r  r   r{   )r   r   r   r   r   z/api/candidate-notificationrv  )	r1  r-  r  r  	job_titler  tomailsr   r  r   )r&  r  r   OLzOffer letter SentHr   r   )"r>  r   rh   r   r   r"   r   r&   ri   r   r   r'   r#   r  rw   r   r   r  r  r   r  r|   r  r   r   r    r~   rk   r  r8   rN   rl   r   rf   )rm   r   r-  r<  r,  r  r   r  rh  hr_mailr   r   r   to_mailsr2  r3  rB  profilerr   rs   rs   rt   notifyCandidateService	  s`   


r5  c           
   
   C   s  z|d dkrZt jj|d d }|rddiW S tjj|d d }t | |d	 |d |d
 |j|d dd}|  d|jd}t||j	|j
|jd}|  dd|jdW S |d dkrt jj|d d }tjj|jd }|r|d
 |_|  |r|d	 |_	|d
 |_|d |_|  d|jd}tjj||j
d }|r|js|j	|_|j|_nt||j	|j
|jd}|  d|j|j	|j|jd}|W S W d S W d S  ty }	 z d }	~	ww )Neventcreate	userEmailrv   userAlreadyExistedr   r  r   r  userPswdnewUserLocationrz   )r   r|   rw   r   r   rb   r~   r   r   r   r   created)r9  r6  r   update)rL  )r   r   )r6  r   r|   pswdrb   )r#   rh   ri   r   r3   r   rk   r   r5   r|   r   r$   rw   rK  r   rb   r   r   rl   )
r   rS  userFindr  	save_userr   r   userAuthdatauser_data_responserr   rs   rs   rt   addNewUserService6
  s   
	




'rC  c              
   C   X   z|d }t jj|d }|r|d |_|  W d S W d S  ty+ } z d }~ww Nr   r   r~   )r#   rh   ri   r   r~   rk   rl   r   rS  r   userdatarr   rs   rs   rt   changeUserstatusService
     
rH  c           	   
   C   s   zDt jj| d}tjj| |d}|j|j |_|  tjj| |d	 }t
jj|jd}t| t d||j|j||jd}|  W d S  tyQ } z d }~ww )Nr   r   r   )r   r   r   r  )r   r   r   r   r   r   r   r   )r   rh   r>  r   r   r   rk   r&   ri   r   r   r   r   r   rj   r  rl   )	r   r  r  r   company_creditsr  rk  transactionrr   rs   rs   rt   deductCreditsService
  s8   
rM  c              
   C   s  zt jj| d d d}g }|D ]}|d r$tjj|d dj}nd}t|}||d< d|d< d|d< d|d	< |d
 	d|d
< |d dkrt
jj| d |d |d d }tjj|jd}t|d d}|ro|jnd}	| d|	 d|j d|d	< |d  |d< |d dkrtjj|d d}
|
jdkrd|d  d|d	< nd|
j d|d  d|d	< |d  |d< || q|W S  ty } z d }~ww )Nr<  r   r   r   r   r   r   debitr  r   rd  r   r  r   r   )r   r   r  -z
                z - JD ()r   r   r   zFree Trial
                    z# Credits added
                    zPayment Successfull z INR
                    )r   rh   ri   r  r   r#   r>  r|   dictr'  r&   r   r   r   rR   r  r   r   r   r   r   rl   )rm   company_credits_usage
usage_listusager   
usage_dictr  JDr  r  r   rr   rs   rs   rt   getCompanyCreditsUsageService
  st   

rW  c                 C   s  zd}t jj| d }|rtjj|jd }g d}dd |D }|r]|j}|rI|dd}|D ]}|rH|d	d
\}	}
|	|v rH|
||	< q4|d }|d }|d }|d }|d }|j|j	re|j	nd|j
rl|j
nd|jrs|jnd|jrz|jnd|jr|jnd|jr|jnd|jr|jnd|jr|jnd|r|nd|r|nd|r|nd|r|nd|r|nd|jr|jndd}|W S  ty } z d }~ww )Nr   r   r   )LinkedinFacebook	InstagramYoutubeTwitterc                 S      i | ]}|d qS )r   rs   )r]  rP  rs   rs   rt   r        z"getCompanyData.<locals>.<dictcomp>r  rv  :r   rX  rY  rZ  r[  r\  )r   r|   rw   websitephonecompany_typeaddresscitycountryrX  rY  rZ  r[  r\  contact_person)r"   rh   ri   r   r2   r   r   r  rg   r|   rw   r`  phone1rd   address1rd  re  rc   rl   )r<  rq   r   r	  keywordssocial_links_varssocial_links_stringsocial_linkslinkrP  r  rX  rY  rZ  r[  r\  rr   rs   rs   rt   getCompanyData
  sV   




rn  c              
   C   sR   zt | d | d | d | d t d}|  |jW S  ty( } z d }~ww )Nzcompany-namerw   rb   zcontact-person)ra   r[   rb   rc   re   )r   r   rj   rk   r   rl   )rm   rq   rr   rs   rs   rt   demoUserService3  s   ro  c              
   C   s  zlt jj| d d }|rktjj|jd}tjj|jd}|jd |j	|j
|j|jr/|jnd|jr6|jndd}t d d }d}t||}| d d	kr2| d |d< tjj|j|j|j	d
 }|r/tjj|j|j|jdd }	tjj|jd}
tjj|j|	j
d}|
j|jkr,tj||d}|j}|r)t|d}|d dkr&t |j|	j	|j|j|jd|	j
d}|  |j
dkrt |j|	j	d|jd}|  |j!r|j
dkrt"|j!dd n|j
dkrt"|j!dd |j
dkrd|_#d	|_#|  |  n|j
dkrd|_#d	|_#|  |  t$|j|j
|j	 W dS W dS W dS W dS W dS | d dkrh| d |d< tj||d}|j}|rht|d}|d dkred |_#|  W dS W dS W dS W d S  t%y{ } z d }~ww )!Nr  r   r   )r1  r~   r  r  r   r   r.  r   r   z/api/update-candidate-workflowr~   r   )r   r   r  )r   r   id__gtr   r   rJ  r   r$  
statusCoder   r{   )r&  r  r}   r   r   r~   r   r   )r&  r  r~   r   r   SCScreening Sentr   CTCoding Test Sentr\   r   r   r   )&r    rh   ri   r   r   r>  r&  r   r   r  r   r   r  r   r   r   r&   r   r   rO  r   r   r   r   r   r   r   r   r*  r+  r}   rk   r'   r  rN   r~   rM  rl   )rm   rB  r,  r   r  r   r   r   current_workflownext_workflowr   rK  send_workflow_datar4  r5  r6  r  rr   rs   rs   rt   updateCandidateWorkflowServiceF  s   







ry  c              
      s  zd d d}t jj| d }|rttjj|jddjddgddj	d	d
dd d }t
|dk r\|ttjj|jdjddgdj|ddj	d	d
dd dt
|  7 }tjj|dd}g g g g g d}g }g }g }g }	g }
|D ]d}tjj|j|jd}d}d}d}d}|r|D ])}|jdkr||j7 }q|jdkr||j7 }q|jdkr||j7 }||jpd7 }qtjj|jd}||j || || |	| |
| qz||d< ||d< ||d< |	|d< |
|d< g g g g d}t  }|tdd fddtdD }dd  |D  tjj|jd!d"d#jtd	d$}|D ]J}|d# d%}|d" }| v rw|dkrS | d&  |d' 7  < q.|dkrf | d(  |d' 7  < q.|dkrw | d)  |d' 7  < q.d*d |D |d+<  fd,d D |d&<  fd-d D |d(<  fd.d D |d)< ||d/< ||d0< |W S  ty } ztt|  d }~ww )1N)line_graph_datajd_reg_datar   r   )r   r   r  r{   r  
-createdonr   Tr      r   id__in)jdtitlescreening_countcoding_countinterview_countoffered_countr   r   r   r   r  r  r  r  r  )dates	screeningcodingr  rw  rr  c                       g | ]	} t |d  qS rr  r   r]  rM  
start_daters   rt   r_  )      z-dashBoardGraphDataService.<locals>.<listcomp>   c                 S   s    i | ]}| d ddddqS )r=  r   )r  r  r  r'  r]  r   rs   rs   rt   r  +  s    z-dashBoardGraphDataService.<locals>.<dictcomp>)registrationdate__gter   r   r}   )countr=  r  r  r  r  c                 S      g | ]}| d qS %d-%m-%Yr  r  rs   rs   rt   r_  D      r  c                       g | ]} | d  qS )r  rs   r  date_maprs   rt   r_  E      c                    r  )r  rs   r  r  rs   rt   r_  F  r  c                    r  )r  rs   r  r  rs   rt   r_  G  r  rz  r{  )r"   rh   ri   r   r  r   r   r   r   r   r  r4   r   rB  
efficiencyr>  r   r   r   rj   r   r   r  r    r  annotater   r'  rl   r   rf   )r   dashboard_datar   jd_ids_with_dashboard	job_descsr{  	jd_titlesr  r  r  r  job_datajd_analysissc_reg_countcd_reg_countint_reg_countoffer_countanalysis_datar   
line_graphcurrent_date
date_rangequerysetentrydate_keyr   rr   rs   )r  r  rt   dashBoardGraphDataService  s   










r  c                 C   sX  zd d d}t jj| d }|ri 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dddddddi}dddd}tjj|jddjtdtdtdtdtd td!d"}|D ]q}|	|d }|r|d# rt
|d# nd|| d$< |d% rt
|d% nd|| d&< |d' rt
|d' nd|| d(< |d) rt
|d) nd|| d*< |d+ rt
|d+ nd|| d,< |d- rt
|d- nd|| d.< qttjj| d}|rg }	|D ]}
tjj| |
jd/}d}d}d}d}d}d}|D ]E}|jd0kr%||jpd7 }||jp#d7 }|jd1kr;||jp1d7 }||jp9d7 }|jd2krQ||jpGd7 }||jpOd7 }q|dkr^|| d3 nd}|dkrk|| d3 nd}|dkrx|| d3 nd}|	|
j|||t|t|t||d4 q|	|d5< ||d6< |W S W d S  ty } z d }~ww )7N)durations_datasources_datar   screening_minr   screening_avgscreening_maxscreening_min_ltscreening_avg_ltscreening_max_lt
coding_min
coding_avg
coding_maxcoding_min_ltcoding_avg_ltcoding_max_ltinterview_mininterview_avginterview_maxinterview_min_ltinterview_avg_ltinterview_max_ltr  r  r  r;  r   r   durationmindurationavgdurationmaxleadtimeminleadtimeavgleadtimemax)avg_duration_minavg_duration_avgavg_duration_maxavg_leadtime_minavg_leadtime_avgavg_leadtime_maxr  _minr  _avgr  _maxr  _min_ltr  _avg_ltr  _max_lt)r   
sourcecoder   r   r{   d   )r  r  r  r  screening_efficiency_percentagecoding_efficiency_percentageinterview_efficiency_percentageofferedr  r  )r"   rh   ri   r   r4   r   r  r  r   r>  format_durationr5   r   r   rB  r  r   r   r   rl   )r   r  r   r  papertype_mappingpaperwise_durationduration_data
key_prefixcompany_sourcesr  r   source_datar  screening_efficiencyr  coding_efficiencyr  interview_efficiencyr%  r  r  r  rr   rs   rs   rt   getDashboardDataS  s   
	
  r  c              
   C   s  zxt | } | dk r|  dW S | dk r9| d }| d }|dkr!dnd}|r1| d| d| dW S | d| W S | d }| d }|d }|d }|dkrOdnd	}|dkrWdnd}| d| }|rl|d| d| 7 }|rv|d| d7 }|W S  ty } z d }~ww )
N<   z minutesi  r   r  r  r:  r  rq  )r   rl   )r  r  remaining_minuteshour_strrq  day_strresultrr   rs   rs   rt   r    s:   r  c              
   C   sf   z%t jj| dd}|r#g }|D ]}||j|j|jd q|W S W d S  ty2 } z d }~ww )Nr   r   )r   r   r   )	r5   rh   ri   r   r   r   r   r   rl   )r   sourcesr  r   rr   rs   rs   rt   getCompanySourcesData!  s   r  c              
   C   s2  z| d}|rtj|j}|d }t| d}| d| }tjtj	dd|}t
|d}	| D ]}
|	|
 q5W d    n1 sGw   Y  t d d	 }| d
}t||}tjj| dd }|ry||_||_d|_|  |W S t| d||dd}|  |W S W d S  ty } z d }~ww )Nfiler   ry   _candidatesuploadscandidate_uploadzwb+DIRr  z,/app_api/functions/data_configs/mapping.jsonr   r   r   U)r   r   filepathfilenamer~   )r>  ospathsplitextr|   rf   zfillr  rT   
MEDIA_ROOTr  chunkswriter   rS   r6   rh   ri   r   r  r  r~   rk   rl   )r   r   fileObjsuploaded_excelexcel_filenameexcel_extensionformatted_company_iduser_file_name
excel_path	user_filechunkr  r  mappings_datacandidate_upload_fileuploaded_filerr   rs   rs   rt   mapUploadedCandidateFields6  sV   



/r
  c           "      C   s  zt jj| dd }|rd|_|  t| d}tj	
tjdd}t|}d }|D ]}|| dr<|} nq.|rtj	
||}	t|	}
|d }|d	 }tjj|d
 j}|d }g }|
 D ]\}}z|dddkr|j|dd nd }|dddkr|j|dd nd }|dddkr|j|dd nd }|dddkr|j|dd nd }t|rd n|}t|rd n|}t|rd n|}t|rd n|}g }|s|d |s|d n	t|s|d |r|dd
|  W qh|||||d	 ||d}z$t|| d |}|dkr'|d n|dkr2|d n|d W n tyP } z|d W Y d }~nd }~ww W qh typ } z|dt| d W Y d }~qhd }~ww ||
d< | d }tj	
tjdd|}|
j|d!d" t|}|j}tt d d#t d d#t d d#t d d#d$}|d% D ]} || _!q|| |rd|_|  W d S W d S W d S  ty }! z
t"d&t|!   d }!~!ww )'Nr   r  r{   ry   r  r  r  zcolumns-datar  r  source-codez
First Namez	Last NameEmailMobilezMissing First NamezMissing EmailzInvalid Emailz	Skipped: rv  )r  r  rw   r  r  
begin-fromr  insufficient_creditszSkipped: Insufficient Creditscandidate_already_registeredz%Skipped: Candidate Already RegisteredzCandidate is addedzSkipped: Error adding candidatezSkipped: Error occurred (rP  zhirelines-status_candidate_report.xlsxF)index)border_style)leftrighttopbottomr   zError occurred: )#r6   rh   ri   r   r~   rk   rf   r  r  r  r  rT   r  listdir
startswithpd
read_excelr&   rO  r  iterrowsr>  ilocisnar   is_valid_emailrU   rl   to_excelr   activer   r   borderr   )"r   rm   r  r  r  
upload_dirfilesmatching_filer  excel_file_pathdfcolumn_mappingjd_id
begin_fromsource_coderD  r  row
first_name	last_namerw   r  skip_reasonsr2  c_datadb_error	row_errorreport_fileoutput_file_pathworkbooksheet	no_bordercellrr   rs   rs   rt   processAddCandidateServicen  s   







"



  r:  c                 C   s   d}t || duS )z-Basic regex pattern to validate email format.z0^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$N)rematch)rw   patternrs   rs   rt   r     s   r   c              
   C   sJ   zt jj| d d }|rd}|W S d}|W S  ty$ } z d }~ww )Nr*  r  r   r   )r    rh   ri   r   rl   )rm   r   registrations_flagrr   rs   rs   rt   #checkJdCandidateRegistrationService  s   r?  c              
   C   s^   z!i }t jj| dd }|r|j|d< d|d< |W S d|d< |W S  ty. } z d }~ww )Nr   r  r~   r   r   r   )r6   rh   ri   r   r~   rl   )r   upload_datacandidate_uploaded_datarr   rs   rs   rt   getCompanyCandidateUploadData,  s"   
rB  c              
   C   st   z,t jjd| dd }|r*t| d}| d}tjt	j
dd|}||dW S W d S  ty9 } z d }~ww )Nr   )r   r   r~   ry   r  r  r  )report_file_pathreport_file_name)r6   rh   ri   r   rf   r  r  r  r  rT   r  rl   )r   uploaded_datar  r4  rC  rr   rs   rs   rt   downloadUploadReportServiceA  s(   

rF  c              
   C   s  zt jj| d  }|si W S i d|dd|ddd|dr.|dd ndd|dr>|dd n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|ddd|ddd|ddd|dd|dd|dd|ddd}tjj| ddddddd}t	||d < t
jj| dd!d"ddd}t	||d#< d$}|D ]#}zt|dd$}t|dd$}||kr||| 7 }W q   Y q| d%|d&< tjj| dd'd( }	|	r|	nddd)|d*< |d* d'd}
|d* d(d}g }|
rE|d+d, |
d-D  |rU|d.d, |d-D  d/||d0< tjj| dd1d2d3d4d5d6d}t	||d7< tjj| dd8d9d}t	||d:< tjj| dd;d9d}t	||d<< t	tjj| dd=d>d?d@dAdB}||dC< tjj|dDdEdF }tjj|dGddH }|r|dH nd |dH< |r|dFr|dF|dI< |W S d |dI< |W S  ty } z d }~ww )JNr   r   
middlenamer   r  r  r   rw   r  dateofcreationlinkedindateofbirthnativeoffacebookpassportnumstrengtheducationscore0awardsscorecertificatesscoreexperiencescoreprojectsscoreskillsscore)rS  rT  rU  r  course	instituteyearfromyeartogradesequence	educationjobtitler   
experiencer   z Yearsfinal_experienceprimaryskillsr  )ra  r  rw  c                 S      g | ]
}|  r|  qS rs   r  r  rs   rs   rt   r_        z,getProfileDetailsService.<locals>.<listcomp>r  c                 S   rb  rs   rc  r  rs   rs   rt   r_    rd  rv  skills_displayprojectname
clientname
roleplayed
skillsused	yearsfromyearstoprojects	awardnamer-  awardscertnamecertificatesaddline1addline2rd  statere  zipcoderc  r   r   r   resumeiddocparseridlogo)r8   rh   ri   r  rO  r>  
capitalizer:   r   r  r;   r   r=   extendrg   r  r<   r>   r?   r@   r2   r7   rl   )pidr4  profile_deteducation_listexperience_list	total_expexpyfytrw  primary	secondaryskills_listprojects_listawards_list	cert_listaddress_listr	  resumerr   rs   rs   rt   r    s   
	



r  c                 C   s  zt jj| d d}g }dd tjjddddd	D }d
d |D }dd tjj|dddD }tjj| dddd }|D ]p}|	d}|rZt
|drZ|dnd}	|	d}
|	d}||	d|	d|	d|	|
|	|
|	d||	|d|	d|	d|	d|r|d nd|r|	dr|d  nd|r|	dr|d  ndd qG|W S  ty } ztd |  d }~ww )!NrV  z	-sequencec                 S      i | ]	}|d  |d qS )lookupparam1
lookupnamers   )r]  lrs   rs   rt   r  &      z4getProfileactivityDetailsService.<locals>.<dictcomp>r   rz   )lookupidr~   r  r  c                 S   s    h | ]}| d r| d qS )acvityuserid)r>  r]  r-  rs   rs   rt   	<setcomp>.  s    z3getProfileactivityDetailsService.<locals>.<setcomp>c                 S   r  r  rs   r]  urs   rs   rt   r  4  r  r~  r   r|   r   r   r  r  r   r'  rd  r   activitycoder  r  r\  activitynameactivityremarksactivitystatus)r   r  r\  r   r  activity_textactivityuseridactivityusernamer  r  r  r^  r  r  z'getProfileactivityDetailsService error:)r9   rh   ri   r  r   rB   r#   r8   rO  r>  hasattrr'  r   rx  rl   r   )rz  activity_list
final_list
lookup_mapuser_idsuser_mapr.  r-  dtformatted_dtr   r   rr   rs   rs   rt    getProfileactivityDetailsService  sx   






r  c           	   
   C   s   zkg }| j dkr tjj| jd}|r|dr|j|d d}ntjj| jd}|jddd}tjj|d	j	d
d
d}|ri|D ]'}|j|jd }||j|jpTd|jpXd|jra|jdnd|jd qA|W S  tyx } z d }~ww )Nr   r   
source_idsr~  r  r   Tr   )sourceid__inr  r   r  r   r   rd  )r   r|   r   r   r~   )r   r5   rh   ri   r   r>  r   r   r7   r   r   sourceidrO  r   r  r   r   r'  r~   rl   )	rS  filtersresumes_datar  r  resumesr  r   rr   rs   rs   rt   getResumeDatao  s@   
r  c               
   C   s@   zt tjjdddddd} | W S  ty } z d }~ww )Nrz   r   r   rw   r   r|   )r  r#   rh   ri   r  rl   )r  rr   rs   rs   rt   getEmailFetchUsers  s   r  c              
   C   s>   zt jj| d}d|_|  W d S  ty } z d }~ww )Nr   r  )r7   rh   r>  r~   rk   rl   )ridr  rr   rs   rs   rt   softDeleteResume  s   r  c              
   C   s  zt jj| |jd }|sW d S tjj| d }d d d d d d d d d}i d|jp,dd|jp2dd|jp8dd|j	p>dd	|j
pDdd
|jpJdd|jpPdd|jpVdd|jp\dd|jpbdd|jphdd|jd|jprdd|jr}|jdndd|r|jndd|r|jndd|r|jnd|r|jnd|r|jnd|r|jndd}tjj|jdd}g }|r|D ]}||jpd|j pd|j!pd|j"pd|j#pdd qt$jj|jdd}	g }
|	r|	D ]}|
|j%pd|j&pd|j!pd|j"pdd qt'jj|jdd}g }|rA|D ]'}||j(p"d|j)p'd|j*p,d|j+p1d|j,p6d|j-p;dd qt.jj|jdd}g }|rg|D ]}||j/p\d|j0padd qSt1jj|jdd}g }|r|D ]}||j2pd|j0pdd qyt3jj|jd }g }|r|j4r|j45d}d}|j6rt7jj8|j6d}t9:|j;<d }||d!< |
|d"< ||d#< ||d$< ||d%< ||d&< ||d'< d(| |d)< |W S  t=y } z d }~ww )*Nr   r   rV  )personalr]  r_  rl  rn  rp  rw  resume_filer   r   r  rG  r  rw   r  rI  rL  rM  
fathernamerK  r~   rN  rJ  r=  rq  rr  rd  )rs  re  rt  r\  )rW  rX  rY  rZ  r[  )r^  r   rY  rZ  )rf  rg  rh  ri  rj  rk  )rm  r-  )ro  r-  r  )ru  r$  r  r_  r]  rl  rn  rp  rw  zdata:application/pdf;base64,r  )>r8   rh   ri   r   r   r@   r   r  rG  r  rw   r  rI  rL  rM  r  rK  r~   rN  rJ  r'  rq  rr  rd  rs  re  rt  r:   r   r   r   rW  rX  rY  rZ  r[  r;   r^  r   r<   rf  rg  rh  ri  rj  rk  r>   rm  r-  r?   ro  r=   ra  rg   ru  rA   r>  base64	b64encodefilecontentr+  rl   )rz  rS  r4  profile_addressro  personal_detailseducation_dataprofile_educationr]  experience_dataprofile_experiencer_  projects_dataprofile_projectsprojectawards_dataprofile_awardsawardcertificates_dataprofile_certificatescertificateskills_dataprofile_skillsfile_base64r  rr   rs   rs   rt   getProfileData  s  








	















r  c                 C   s  zt | }tjj|jd}t}t|d d}| }|d|d r&|d nd}|d|d 	d	}|d
|d }|d|d }|d|d }|d|d }|d|d rb|d 	dnd}|d|d rp|d nd}|d|d r~|d nd}|d|d }|d|j
}|d|j
}|d|d r|d  dnd}d}|d  r|d  D ] }	|d!|	d"  d#|	d$  d%|	d&  d#|	d'  d(|	d)  d*7 }qnd+}d}
|d, r|d, D ]}|
d!|d-  d.|d/  d%|d&  d#|d'  d0	7 }
qnd1}
d}|d2 d3 rd4|d2 d3  d5}nd6}d}|d7 rH|d7 D ]&}|d!|d8  d#|d9  d:|d;  d<|d=  d%|d>  d#|d?  d07 }q nd@}d}|dA ri|dA D ]}|d!|dB  d#|dC  dD7 }qUndE}d}|dF r|dF D ]}|d!|dG  d#|dC  dD7 }qvndH}|dI|}|dJ|
}|dK|}|dL|}|dM|}|dN|}|dO|  dP }t|dQ}tj||dR}|  |d  dS|d  dS|d  }||pd|jdTW S  ty } ztt|  d }~ww )UNr   z3/media/branded_resumes/branded_resume_template.htmlr  r  rw  r   z{#creation_date#}rH  %d-%B-%Yz	{#title#}r   z{#firstname#}r  z{#lastname#}r  z	{#email#}rw   z{#dob#}rJ  r  z{#linkedin#}rI  z{#nativeof#}rK  z{#final_experience#}r`  z{#user_name#}z{#company_name#}z{#strength#}rN  %r]  z/
                    <li class="custom-list">  rW  r  rX  z (rY  rZ  z) - Grade: r[  z </li>
                zY
                <li class="custom-list">No education details available</li>
            r_  r^  z at r   z)</li>
                zH
                <li class="custom-list">No Experience</li>
            rw  ra  z)
                <li class="custom-list">z</li>
            zD
                <li class="custom-list">No Skills</li>
            rl  rf  rg  z	, Role : rh  z , Skills Used : ri  rj  rk  zF
                <li class="custom-list">No Projects</li>
            rp  ro  r-  z</li>
                zJ
                <li class="custom-list">No Certificates</li>
            rn  rm  zD
                <li class="custom-list">No Awards</li>
            z{#education_data#}z{#experience_data#}z{#skills_data#}z{#projects_data#}z{#certificates_data#}z{#awards_data#}z/media/branded_resumes/resume_r  r  r  r:  r   )r  r"   rh   r>  r   r   r  r  r!  r'  r|   r   r  r  r  rl   r   rf   )
profile_idrS  r  r   r  resume_template_pathresume_templateupdated_resumer  r]  r  r_  r  r  r  r  r  r  r  r  r  r  r  rr   rs   rs   rt   generateBrandedProfilee  s   "





 r  c              
   C   sH  z^t jj| d}tjj|jd}|j}tjj|d }|r!|j	nd}|j
}|j}tjj|d}|j}	|jrDt|j}
dd |
D }ng }|sTtd|j d g W S t|}|||	|fW S  t jyr   td|  d	 g  Y S  tjy   td
|  d|j d g  Y S  ty } ztd|  g W  Y d}~S d}~ww )z
    Main function to find available interview slots for a candidate's job.

    :param cid: Candidate ID.
    :return: List of available 30-minute slots.
    r   r  Nc                 S   r  rs   r  r  rs   rs   rt   r_    r  z%getSlotsAvailable.<locals>.<listcomp>z!No interviewers found for job ID .zError: Candidate with ID z not found.z%Error: Job Description for candidate z	 (Job ID z) not found.r  )r   rh   r>  r   r   r   r'   ri   rO  r~   r   r   r"   r|   r  r  r  r   get_open_slotsDoesNotExistrl   )r<  r,  r  rE  r  r~   r.  r   r   r   jd_interviewers_rawjob_interviewer_idsslots_availablerr   rs   rs   rt   getSlotsAvailable  s@   r  c                 C   s
   |  dS )Nr  r  )date_objrs   rs   rt   get_full_day_name+  s   
r  ry   r
  c           2      C   s  t  }t }tjj| d}tt}|D ]}|jdur$||j 	| q|t
|d d }	tjjtddtddB | ||	dd	}
d
d | D }t
|d}|
D ]}|j}|| }||j 	||f qQdd | D }t
|d}dd }t|D ]}|t
|d  }|d}||}|dkrt|t }nt |tdd}t|t }| D ]}||g }g }|D ]}||j}||kr|	| q|sq|D ]}||j}||j}|||fv rqz|jr|jsW qt ||j} t| t }!t|j}"|!t
|"d }#t|!|}$|$jddd}$|$j | }%|%dkr(||% }&|$t
|&d7 }$|dkr6|$|k r6|$|7 }$|$| }'|'|#krud}(||g })|)D ]\}*}+|$|+k r[|'|*kr[d}( nqI|(sj|| 	|$|'d |'}$|$| }'|'|#ks?W q t!y }, zt"d|j# d|,  W Y d},~,qd},~,ww qqyi }-|$ D ]4\}.}/|/D ],}0|0d % }1|1|-vr|1|0d % g d|-|1< |.|-|1 d vr|-|1 d 	|. qqt&|-' dd dS ) zO
    Returns available slots considering multiple shifts per day per user.
    )
userid__inNr   rr  r   r   r   )interviewerid__indatentime__gtedatentime__lter   c                 S      i | ]}|g qS rs   rs   r]  iidrs   rs   rt   r  R  r^  z"get_open_slots.<locals>.<dictcomp>r  c                 S   r  rs   rs   r  rs   rs   rt   r  \  r^  c                 S   s   | r|    d d S dS )Nry   r   )r  lower)d_namers   rs   rt   	clean_day`  s   z!get_open_slots.<locals>.clean_dayr  r   r  )r  r  FT)startendzError processing shift : r  r  )
start_timeend_timeavailable_interviewersr  c                 S      | d S )Nr  rs   rA  rs   rs   rt   <lambda>      z get_open_slots.<locals>.<lambda>)rP  )(r   rj   r   r)   rh   ri   r
   r  r   r   r   r'   r   r   r   r  r  r   r'  
make_awareget_current_timezoner$  r   r>  r  weekoff1weekoff2r  r  r*  r&  r!  r  rl   r   r   r  	isoformatsortedr  )2r  num_daysslot_duration_minutesscheduled_call_buffer_minutes	now_naive	now_aware	work_calswork_cal_mapwcend_date_limitr7  unavailable_slotsbuffer_timedeltara  r  r  slots_available_per_interviewerslot_timedeltar  
day_offsettarget_datetarget_day_fulltarget_day_abbrday_min_timestart_of_dayinterviewer_id
all_shiftstodays_shiftswc_dayr[  w_off1w_off2naive_shift_startshift_startr  	shift_endcurrent_slot_start	remainderadd_minscurrent_slot_endr`  user_bookingsb_startb_endrr   all_available_slotsr  slotsslotkey_isors   rs   rt   r  /  s   













\


r  c              
   C   sv   z-t jj|ddd}tjj| d}|j}g }|r(dd |dD }|j|d}||d	W S  ty: } z d }~ww )
N	Recruiterrz   r   r   r~   r   c                 S   s$   g | ]}|   rt|  qS rs   )r  isdigitr   )r]  r  rs   rs   rt   r_    s    

z%getRecruitersData.<locals>.<listcomp>r  r~  )all_recruitersselected_recruiters)r#   rh   ri   r   r>  recruiteridsrg   rl   )jdidr   
recruitersr  jd_recruitersr  recruiter_idsrr   rs   rs   rt   getRecruitersData  s"   r$  c              
   C   s  z	t jj| d}W n t jy   td|  d Y dS w t d d }| d}dtfd	d
}||j\}}d|j|j|fi}|j|dddd}z"t	j
|||dd}	|	  |	 }
td|
  |
di dW S  ty } ztd|  d|  W Y d}~dS d}~ww )zQ
    Send ResumeFile to doc parser API and return docparser_id if successful
    r   zResumeFile z
 not foundNr   	docparserz/api/add-document-to-queue
file_bytesc                 S   s.   |  drdS |  drdS |  drdS dS )Ns   %PDF)pdfzapplication/pdfs   )doczapplication/mswords   PK)docxzGapplication/vnd.openxmlformats-officedocument.wordprocessingml.document)unknownzapplication/octet-stream)r  )r&  rs   rs   rt   detect_file_type  s   


z2send_resume_to_docparser.<locals>.detect_file_typer  GSSPEC	Hirelinesr7   )r  filetyper   applicationdocumenttype
   )r%  r%  timeoutzDoc parser API response: r%  docparser_idu4   ⚠️ Failed to call doc parser API for ResumeFile r  )rA   rh   r>  r  r   r   bytesr  r  r   r   r  r   rl   )resumefile_idr  base_urlr   r+  file_ext	mime_typer%  r%  r  	resp_jsonrr   rs   rs   rt   send_resume_to_docparser   s:   
	r:  )Sumc                 C   s`  t  }|rtt|d\}}n|j|j}}tjj	| ||d}|dkr,|j	|d}n|dkr8|r8|j	|d}|
djtdd}	d	d
 |	D }
||jkoS||jk}|rXdnd}|
dd}|
dd}|
dd}|
dd}|
dd}|
dd}|
dd}dd }||||||||rdnd|||||||||||||||||||dS )NrO  )r   r-  monthr  r  r   r  profilescounttotalc                 S   s   i | ]}|d  |d pdqS )r  r?  r   rs   r  rs   rs   rt   r    s    z.getRecritmentDashboardData.<locals>.<dictcomp>zcurrent monthz
last monthPCr   SLRJCLr   CSWFc                 S   s   |dkrdS t | | d S )Nr   r  )round)r  r?  rs   rs   rt   percent  s   z+getRecritmentDashboardData.<locals>.percentr  )	submittedprofiledr   sent_clientrejected_clientselected_clientwaiting_feedbacksubmitted_percentageprofiled_percentagerejected_percentagesent_client_percentageselected_client_percentagerejected_client_percentagewaiting_feedback_percentagecomparison_text)r   rj   mapr   rg   r-  r<  rC   rh   ri   r  r  r;  r>  )r   rj  logged_recruiter_idselected_recruitermonth_valuerj   r-  r<  qsactivity_countsactivity_mapis_current_monthrU  rH  rI  r   rJ  rK  rL  rM  rG  rs   rs   rt   getRecritmentDashboardData  s^   


r^  c                 C   s   zfg }t jj| jdd}ttjjd| j dddddd	}|rb|D ]9}tjj	|j
d
}||j|j|j|j|jrB|jnd|jrL|jdnd|jrS|jnd |jrZ|jnd |jd	 q(||fW S  tys } z d }~ww )N)r  z
-createdatz(^|,)z(,|$)rz   )recruiterids__regexr~   r|  r   r   r   r   r  )	r   clientidclientr  rx  	startdatestartdate_dbjd_idsr~   )rD   rh   ri   r   r   r  r   r  rE   r>  r`  r   r|   r  rx  rb  r'  rd  r~   rl   )rS  workspaces_data
workspacesrec_jds	workspacera  rr   rs   rs   rt   getWorkspaces  s.   ,

ri  c              
   C   sZ   zg }t jj| jdd}|r|D ]}||j|jd q|W S  ty, } z d }~ww )Nrz   r   r~   r  )rE   rh   ri   r   r   r   r|   rl   )rS  clientscompany_clientsra  rr   rs   rs   rt   getCompanyClients  s   
rm  c           
   
   C   s   zMg }t jj|d}tjj|jd}|jrdd |jD ng }tjj|ddd}|D ]}|	|j
|j|j|j|j|jd q,|j
|j|j|d}|W S  tyZ }	 z d }	~	ww )	Nr   c                 S   r  rs   r  )r]  r*  rs   rs   rt   r_  .  r  z$getWorkspaceData.<locals>.<listcomp>rz   )r  r~   r|  )r   r   r	  r
  r  r  )widra  r  jds_data)rD   rh   r>  rE   r`  rd  r   ri   r   r   r   r   r	  r
  r  r  r|   r  rl   )
rS  rn  assigned_jdsrh  ra  rd  rg  recjdworkspace_datarr   rs   rs   rt   getWorkspaceData&  s2   

rs  c                 C   s  z:t jj| d d}g }g }tjj|jdjdgd}t|j|}dd |D }t	jj|j|jd
 }|D ]}	||	ji }
tt|
d	d
}tt|
dd
}tt|d |d  }t|d }t|d }ddd |
dg D }ddd |
dg D }tjj|	j| d d
 }|rt|jd}|j|jkr||	j|	j|	j|	j|	j|	jr|	jnd
|t|
dd
|||||||d q7||	j|	j|	j|	j|	j|	jr|	jnd
|t|
dd
||||||d q7|jdd dd d}|jr-t|j}d
}|D ]"}tt| }|d7 }t ||kr%||7 }q
||d 7 }q
|j|j!|j"|j#|||dW S  t$yI } z d }~ww ) Nr   r   r   r  r  c                 S      i | ]}|d  |qS )r  rs   r  rs   rs   rt   r  W      z$getJdProfileData.<locals>.<dictcomp>)r   r   exp_strengthr   skill_strengthg333333?g?r  c                 s       | ]}|  V  qd S r   rx  r  rs   rs   rt   r}  g      z#getJdProfileData.<locals>.<genexpr>matched_skillsc                 s   rx  r   ry  r  rs   rs   rt   r}  h  rz  not_matched_skills)r  r   r   total_experience)r   r  rG  r  rw   profile_strengthrv  r}  rw  r{  r|  overall_strengthcandidate_statusskill_math_strengthexp_math_strength)r   r  rG  r  rw   r~  rv  r}  rw  r{  r|  r  r  r  c                 S   r  )Nr  rs   r  rs   rs   rt   r    r  z"getJdProfileData.<locals>.<lambda>T)rP  r  r   rv  )r   r   r	  r
  jd_skills_primaryshortlisted_profilesmatched_profiles)%r   rh   r>  r8   ri   r   r   rV   r   r5   r   r   rF  r  r   rQ   r~   r   r   r   r  rG  r  rw   rN  sortr  r  r  r~  r  r  r  r   r	  r
  rl   )rm   rS  r   r  r  profilesjd_profile_data	match_mapuser_sourcer4  
match_inforv  rw  r  r  r  r{  r|  r,  rH  primary_skillsr  r  r  r  rr   rs   rs   rt   getJdProfileDataK  s   


r  c           	   
   C   sd  zt jj| d dd }|ri }tjj| d d}|jr |jnd|d< |jr*|jnd|d< |j|d	< |j	r9|j	nd|d
< | d |d< |j
|d< tjj|j|jd }|r[|j|d< t||jd |j}d|v rtjj|d d}|j|_|  |d dkrt|jdd|j n|d dkrt|jdd|j d|_|  |W S W d S  ty } z d }~ww )Nr   r   )r   r  r  r   r   r  r  rw   r  r  r  )r   r   r  r&  r   r   rr  rs  r   rt  ru  r{   )r&   rh   ri   r   r8   r>  r  r  rw   r  r  r5   r   r   r   rU   r   r  rk   rN   r~   rl   )	rm   rS  r  candidate_objr4  r   r,  new_candidaterr   rs   rs   rt   shortlistProfileService  s>   


!r  c                    sh  zt jj| d}|j}|j}|j}tjj| d }|s)tjjdd }tj	}|j	}i |r\|j
r\|j
d}|D ] }	d|	v r[|	dd\}
}|
 }
| }|r[|dkr[||
< q;g d}rg }n|}t d	 d
  dd dd fdd|D  d}tjj| d }|st d	 d
  d}tj|dd}| }|ddkrW d S |d d }|d|}i |d ||r|jnd|r|j	rt|j	ndd|||dW S |jd|}|j|j|j|j||j|j|j|jpd|j pd|r|jnd|r|j	rt|j	ndd|||dW S  t!y3 } zt"d| W Y d }~d S d }~ww )Nr   r   r   r  r_  r   #)rX  rY  r[  r\  rZ  r   r   z#/static/lib/img/social_links_logos/z
            <a style="text-decoration:none; margin:0 5px;" href="{url}" target="_blank">
                <img style="width:34px; padding:3px;" src="{icon}" alt="{platform}" />
            </a>
        z3
        <div id="previewSocialIcons">
            r   c                 3   s2    | ]} j |d  | d|dV  qdS )r  z.png)r   iconplatformN)r(  r>  )r]  r  footer_iconr  rl  rs   rt   r}    s    

z5get_default_email_template_service.<locals>.<genexpr>z
        </div>
        )r   z/api/get-default-emailr1  )r2  rq  r%  
email_bodyz[footer_div])r   r   rl  )r  r	  r   company_websiter   )r   template_namer  email_subjectr  template_headingsender_emailsender_labelemail_attachmentemail_attachment_namer	  r   r  r   zEmail template service error:)#r"   rh   r>  r|   r`  rw   r2   ri   rO  r   r   rg   r  r  r   r  r/   r   r   r   r!  r   rf   r  r   r  r  r  r  r  r  r  r  rl   r   )r   r   r   r  r   r	  brandinglogobranding_logor  r  r  r   all_platformsplatforms_to_showfooter_div_html
email_temp	acert_urlresr%  raw_email_bodyr  rr   rs   r  rt   "get_default_email_template_service  s   



r  )
monthrangec              
      sV  zt  }|rtt|d\}}n|j|j}}||jkr)||jkr)| }nt||d }	t|||	}|t	dd   fddt
dD }
tjj| d} j|jkri j|jkri|j j j j|jd	}n|t j j jd
t|j|j|jdB }|dkr|j|d}n|dkr|r|j|d}|ddddjtdd}dd |
D }dddddddd}|D ]/}|d  d|d dd|d d}||v r||d }|r|| |  |d  pd!7  < qd"d |
D g g g g g g g d#}|
D ]}|d$}|D ]}|d%kr|| || |  qqd&|iW S  ty* } z d }~ww )'NrO  r      rr  c                    r  r  r  r  r  rs   rt   r_  x  r  z(dashBoardDataService.<locals>.<listcomp>rw  r   )r-  r<  day__gteday__lte)r-  r<  r  )r-  r<  r  r  r  r   r-  r<  r  r  r=  r>  c                 S   s(   i | ]}| d ddddddddqS )r=  r   )rH  rI  r   rJ  rK  rL  rM  r  r]  drs   rs   rt   r    s    
z(dashBoardDataService.<locals>.<dictcomp>rH  rI  r   rJ  rK  rL  rM  )r@  rA  rB  rC  r   rD  rE  02dr?  r   c                 S   r  r  r  r  rs   rs   rt   r_    r  )r  rH  rI  r   rJ  rK  rL  rM  r=  r  rz  )r   rj   rV  r   rg   r-  r<  r   r  r   r  rC   rh   ri   r  r   r  r  r;  r>  r'  r   rl   )r   rj  rW  rX  rY  rj   r-  r<  end_datelast_dayr  rZ  r  r\  r-  r  fieldr  r  rP  krr   rs   r  rt   dashBoardDataService`  s   



$

r  c              
   C   sZ   zt jj| ddd}t jj| |d }|r|jnd}||fW S  ty, } z d }~ww )Nr  rz   r  )r   rw   r   )r#   rh   ri   rO  r   rl   )r   rs  hiring_managersuser_objcurrent_user_idrr   rs   rs   rt   getHiringManagersData  s   
r  c              
   C   sZ   zg }t jj| dd}|D ]}t|}|| qd|iW S  ty, } z d }~ww )Nr   r   r   )rE   rh   ri   r   rP   r   rl   )r   clientDataLst	clientLstr   r  rr   rs   rs   rt   companyClientLst  s   
r  c              
   C   s   zU| d}| d}|dkr$t|| t dd}|  d|jdW S |dkrS| d	}td
| tjj|| d	 }|sCddiW S ||_
|  d|j|j
dW S W d S  tyc } z|d }~ww )Nr6  r  r7  rz   )r|   r   	createdatr~   r<  )r6  r`  r=  clientId	client_idr  errorzClient not foundupdated)r6  r`  r|   )r>  rE   r   rj   rk   r   r   rh   ri   r   r|   rl   )r   rS  r6  client_namera  r  rr   rs   rs   rt   addNewClientService  sH   




r  c              
   C   rD  rE  )rE   rh   ri   r   r~   rk   rl   rF  rs   rs   rt   changeClientstatusService,  rI  r  c              
   C   sd   z$t  d d }| d}d| d i}tj||d}|  | }|W S  ty1 } z d }~ww )Nr   r%  z/api/request-prefilldoc_parser_id)r%  )r   r   r   r  r   r   rl   )rm   r6  r   r%  r  r9  rr   rs   rs   rt   getDocParsedData8  s   
r  )r   CaseWhenIntegerFieldc           ,      C   sX  | d }t  }| d}| d}|rt|d  n|jdd}|r,t|d  n|}t|tj}t|tj	}dd t
jj|d	D }	tjj|||fd
ddjtttdddt dtttdddt dtttdddt dtttdddt dtttdddt dtttdddt dtttdddt dtttdddt dd}
t|
jddd}i }tjj|d	dddd}|D ] }|d }|	|}|sq|d dkr|d |vr|||d < qdd tjj|d	D }dd tjj|d	D }tt}tjj|ddd D ]}||d  |d   qd!d" | D }d#d tjj|d$d%d&D }tjj|||fd'd(gd)d%d*}tt}td+d, }|D ]c}|d% }||d* }||}|syqbd }|r|	|}|r|d dkr|}n||}|sqb|	| d- }||  d7  < ||g D ]} || }!|!r|| |!  d7  < qqbtd.d, }"td/d, }#|
D ]u}|d }|	|}|sq|d dkr|d- }$n||d }|sq|	| d- }$|D ]}%|%d0vr|"|$ |%  ||% 7  < q||d g D ]&} || }!|!s-q!|D ]}%|%d0vrD|#|$ |! |%  ||% 7  < q/q!q|  D ]\}$}&|"|$ d1  |&7  < qNi }'t|"t|#B D ]7}$g }(|(!d2d3i|"|$  |#|$i   D ]\})}*d2|)i|*}+||$ |)d4|+d1< |(!|+ q|(|'|$< qh|"d|"d|'d5S )6Nr<  	from_dateto_dater=  r   r  c                 S   s&   i | ]}|j |j|jpd  dqS )r   )r|   r   )r   r|   r   upperr  rs   rs   rt   r  f  s    z0RecruitersPerformanceService.<locals>.<dictcomp>r   )r   datentime__ranger  r  r@  r  thenoutput_fieldrr  CIr  rC  CFrB  rD  )rI  screenedclient_interviewinternal_interviewrH  waitingr   selectedTr   r  r   	RECRUITERc                 S      i | ]}|j |jqS rs   )r   r   r  rs   rs   rt   r    ru  c                 S   s   i | ]
}|j r|j |jqS rs   )ru  r   r]  prs   rs   rt   r    s    profileid__inr   c                 S   s   h | ]	}|D ]}|qqS rs   rs   )r]  jidsr   rs   rs   rt   r    r  z/RecruitersPerformanceService.<locals>.<setcomp>c                 S   r  r  rs   r]  jrs   rs   rt   r    r  r~  r   r   r   rz   )r   r  r  r  c                   S      t tS r   r
   r   rs   rs   rs   rt   r    r  z.RecruitersPerformanceService.<locals>.<lambda>r|   c                   S   r  r   r  rs   rs   rs   rt   r    r  c                   S      t dd S )Nc                   S   r  r   r  rs   rs   rs   rt   r    r  z@RecruitersPerformanceService.<locals>.<lambda>.<locals>.<lambda>r	   rs   rs   rs   rt   r        )r  r  sourcedr  r   r   r  r  r%  )#r   r   r>  r   r#  r!  r$  r   r%  r&  r#   rh   ri   r9   r  r  r   r  r  r  r  r   r   r5   r8   r
   r)  r   r,  r   r7   r   r  r   r'  ),rm   r<  r   from_date_strto_date_strr  r  from_dtto_dtr  rZ  profile_idsprofile_recruiter_mappa_owner_qsr  uidr   source_user_mapresume_profile_mapprofile_job_mapcjob_idsjobdesc_map	resume_qsresume_sourced_maprecruiter_jd_sourced	resume_idsource_user_idr  recruiter_idsource_userrecruiter_namer  rF  recruiter_totalsrecruiter_jds	recruiterr  cntfinalrowsr  countsr-  rs   rs   rt   RecruitersPerformanceServiceP  s&  




		









	r  c              
   C   s   z:g }t jjddd}|D ](}tjj|j| jd }d}|r*|jdkr(dnd}|	|j|j
|j|d q|W S  tyG } z d }~ww )Nrz   r   r|   
jobboardidr   r{   )r   r|   rw  r~   )rF   rh   ri   r   rG   r   r   r   r~   r   r|   	logo_pathrl   )rS  
job_boardsactive_job_boards	job_boardjob_board_credentialsr~   rr   rs   rs   rt   getJobboards,  s&   
r  c              
   C   sf   z%t jj| d |d }|r|j|j|j|j|jdW S ddddddW S  t	y2 } z d }~ww )Nzjob-board-idr  )api_keyr   rL  r   r~   r   r{   )r  r   rL  r   
is_enabled)
rG   rh   ri   r   apikeyr   rL  r   r~   rl   )rm   r   r  rr   rs   rs   rt   jobBoardConfigServiceG  s&   	r  c              
   C   s   z:g }t jj| jdd}|D ])}tjj|jd}tjj| j|d|jd	 }|
|j|j|j|r2dndd q|W S  tyG } z d }~ww )Nrz   rj  r   )r   r  r~   r  r   r   )r   r|   rw  selected_jb)rG   rh   ri   r   rF   r>  r  rH   r   r   r   r|   r  rl   )rS  r*  r	  r  job_board_credentialr  jd_jobboardrr   rs   rs   rt   getJDJobboardsb  s"   

r  c               
      s  zt jj| dd}g }dddd}|D ]}|d  d7  < |d  |jr(|jnd7  < ||j qg d}tjj| ||d	 |d
< ddddd}tjj| dd }|d  |7  < t	jj| dd}|D ]*}	|d  d7  < |	j
d ur|	j
dkr|d  d7  < qg|	j
dkr|d  d7  < qgddddd}
t }tjj| |d}|D ]C}|jr||j |jdkr|jdkr|
d  d7  < q|jdkr|jdkr|
d  d7  < q|jdkr|jdkr|
d  d7  < qt||
d< tjj| |dd}i }|D ]}|j}|j}||vrt ||< || | qdd t jj|d D }d!d"g}i }| D ]\}}tjj|d#j|d$ }||d%|d&||< q-t fd'd(td)d*d*D }g d+  fd,d|D }tjj|  d-d( |D d.d( |D d/d( |D d0d1d2d3d4jtd5d6}|D ]$}|d3  d7|d2 d8d7|d1 d8}|d4 }|d9 pd|| |< q|||
||d:}|W S  t y } z d }~ww );Nrz   rj  r   )
active_jdstotal_positionscandidates_inprocessr  r   r  )r   r   r   )r   r  r  r  )
to_profileactive_profilesstrenght_above_50strenght_below_50r   r  r   r  2   r  r  )shortlistedr  r  r  r  r   r{   r  r   r  r  r  F)r   r  profileid__isnullc                 S   r  rs   r  )r]  r  rs   rs   rt   r    ru  z-getOverallDashboardCounts.<locals>.<dictcomp>r~  r@  E1r  )activitycode__inr   )jd_nameactivity_countc                    s   g | ]	} t |d  qS r  r  r  )r   rs   rt   r_    r  z-getOverallDashboardCounts.<locals>.<listcomp>r  r  )r@  rr  rt  ISrC  rA  rB  c                    s"   i | ]}| d dd  D qS )r=  c                 S   r]  )r   rs   )r]  r   rs   rs   rt   r    r^  z8getOverallDashboardCounts.<locals>.<dictcomp>.<dictcomp>r  r  )ACTIVITY_CODESrs   rt   r    s    c                 S      g | ]}|j qS rs   )r-  r  rs   rs   rt   r_    r^  c                 S   r&  rs   )r<  r  rs   rs   rt   r_    r^  c                 S   r&  rs   r  r  rs   rs   rt   r_     r^  )r   r!  year__in	month__inday__inr  r<  r-  r  r=  )total_profilesrO  r  r*  )	jobs_dataprofiles_datapipeline_datajd_activity_dataprofile_activity_daywise)!r   rh   ri   r  r   r   r   r  r7   r8   rN  r)  r    r&  r,  r   r~   r  r   r  r  r9   r   r>  r   r   r  rC   r  r  r;  r   rl   ) r   active_job_descrd  r+  	active_jdinprocess_statusesr,  pending_resumesr  active_profiler-  shortlisted_candidatesr   regjd_profile_candidatesjd_profile_mapcandr*  r  jd_name_mapEXCLUDED_ACTIVITY_CODESr.  r  r#  last_15_daysr/  analysis_qsr-  r  r   r  rr   rs   )r%  r   rt   getOverallDashboardCounts{  s   	






$r>  c                  C   s  t d | d }t }| d}| d}|r t|d n|jdd}|r0t|d n|}t d|d	| d
d tjj	|dD }t
tjj	|ddddd}t d| dd |D }	dd tjj	||	||fdD }
t d|
 dd |D }t d| dd tjj	||dD }t d| tjj	||	ddjtttdddt dtttd ddt dtttd!ddt dtttd"ddt dtttd#ddt dtttd$ddt dtttd%ddt dtttd&ddt dd'}d(d |D }t d)| tt
}tt
}|D ]}||d  |d  qtd*d+ }td,d+ }| D ]C\}}|
|}||}|sJq6||i }t|}|D ]}||d-}|| | d.  d7  < || d.  d7  < |r|| | d/  |d/d0| 7  < || | d1  |d1d0| 7  < || | d2  |d2d0| 7  < || | d3  |d3d0| 7  < || | d4  |d4d0| 7  < || | d5  |d5d0| 7  < || | d6  |d6d0| 7  < || | d7  |d7d0| 7  < qV|| d/  |d/d07  < || d1  |d1d07  < || d2  |d2d07  < || d3  |d3d07  < || d4  |d4d07  < || d5  |d5d07  < || d6  |d6d07  < || d7  |d7d07  < q6| D ]'\}}|| d8d9i| ||  D ]\}}|| d8|i| qqt d:t| t d; |d|dt|d<S )=Nz$===== SOURCE PERFORMANCE START =====r<  r  r  r=  r   r  zDATE RANGE:u   →c                 S   r  rs   )r   r   r  rs   rs   rt   r  6  ru  z,SourcePerformanceService.<locals>.<dictcomp>r   F)r   r  jobid__isnullr  r   zCANDIDATE QS:c                 S      h | ]}|d  qS rV  rs   r]  r  rs   rs   rt   r  D  r  z+SourcePerformanceService.<locals>.<setcomp>c                 S   r  rs   )r   r  r  rs   rs   rt   r  G  ru  )r   r  dateofcreation__rangeu   PROFILE → SOURCE MAP:c                 S   r@  r  rs   rA  rs   rs   rt   r  R  r  r  c                 S   s   i | ]	}|j |j qS rs   )r   r   r  r  rs   rs   rt   r  U  r  )r   r  zJOB TITLE MAP:)r   r  r@  r  r  rr  r  r  rC  r  rB  rD  )rI  r  r  r  rH  r  r   r  c                 S   rt  rV  rs   )r]  ars   rs   rt   r  m  r  zACTIVITY MAP:c                
   S      dddddddddd	S Nr   )	r  rI  r  r  rH  r  r  r   r  rs   rs   rs   rs   rt   r  z     z*SourcePerformanceService.<locals>.<lambda>c                   S   r  )Nc                
   S   rD  rE  rs   rs   rs   rs   rt   r    rF  z<SourcePerformanceService.<locals>.<lambda>.<locals>.<lambda>r	   rs   rs   rs   rt   r    r  z
Unknown JDr  rI  r   r  r  rH  r  r  r   r  r  r   zFINAL OUTPUT:z"===== SOURCE PERFORMANCE END =====r  )r   r   r   r>  r   r#  r!  r5   rh   ri   r  r   r  r8   r   r9   r  r   r  r  r  r
   r   r  r  rQ  r'  ) rm   r<  r   r  r  r  r  
source_mapcandidate_qscandidate_profile_idsprofile_source_mapr  job_title_mapactivity_qsr\  r  profile_jobsr  source_totalsjd_rowsr  jobs	source_idr  activity	job_countr  rF  r   totalsr  r  rs   rs   rt   SourcePerformanceService   s   









$$$$$$$$ 
rU  )rw  r   )ry   r
  r
  )NNNN)r  r   r   r~  rx  r  r   r  ffmpegr;  pandasr  django.db.modelsr   r   r   r   r   r   collectionsr
   django.utilsr   django.shortcutsr   urllib.parser   r   rest_framework.authtoken.modelsr   	xhtml2pdfr   openpyxlr   openpyxl.stylesr   r   	constantsr   hirelines.metadatar   hirelines.settingsr   mailingr   app_api.modelsr   r   r   r   r   r   r   r    r!   r"   r#   r$   r%   r&   r'   r(   r)   r*   r+   r,   r-   r.   r/   r0   r1   r2   r3   r4   r5   r6   r7   r8   r9   r:   r;   r<   r=   r>   r?   r@   rA   rB   rC   rD   rE   rF   rG   rH   app_api.functions.databaserI   rJ   rK   rL   rM   rN   app_api.functions.mailingrO   django.forms.modelsrP   rQ   rR   excel_mappingrS   r   rT   databaserU   jd_profile_matchingrV   voice_comparisionrW   rX   ru   r   r   r   r   r  r  r  r7  rD  rI  rV  rp  rv  r   r  r  r  r  r  r  r  r  r  r  rc  rj  r  r  r  r  r  r  r  r  r  r  r,  r5  rC  rH  rM  rW  rn  ro  ry  r  r  r  r  r
  r:  r   r?  rB  rF  r  r  r  r  r  r  r  r  r  r  r$  r:  r;  r^  ri  rm  rs  r  r  r  calendarr  r  r  r  r  r  r  r  r  r  r  r  r  r  r>  rU  rs   rs   rs   rt   <module>   s8    2 J	*U1%..
th56-   cgK=5 K BrY
== 	  %*8 )B 
V. 0 6
 6 
R!%h) 
|2 ] &