
    7/9h3                        d dl mZ d dlmZmZmZmZ d dlmZm	Z	m
Z
 d dlmZ d dlZd dlmZ d dlmZ d dlmZ d dlZd dlZd d	lmZmZ d d
lmZ d dlmZ d dlZd dlmZ d dlmZ d dlmZ d dlmZ  e         e       ZejA                  e ejB                  d              ed      Z" ejB                  d       ejB                  d       ejB                  d       ejB                  d      dZ# e$e#       defdZ%de&de&dee'   dee'   de&f
dZ(ejS                  d e!      d ee%      fded"ee&   d#e*fd$       Z+ejY                  d%       ed       ed       ed       ed       ed      fdedee&   dee&   dee&   dee&   d&ee&   fd'       Z-d( Z.d) Z/ejS                  d*      d+d+d+d+d,d d- ee%      fded.e&d/e&d0e&d1e&d2e'd3e'd4e'd#e*fd5       Z0ejS                  d6e!      d+d+d+d+d+ ee%      fded.e&d/e&d7e&d0e&d1e&d#e*fd8       Z1ejS                  d9      d+d+d+d+ ee%      fd.e&d/e&d0e&d1e&d#e*f
d:       Z2ejS                  d;      d+d+d+d+ ee%      fd.e&d/e&d0e&d1e&d#e*f
d<       Z3ejS                  d=e!      defd>       Z4ejY                  d=       ed?       ed?      fded@e&dAe&fdB       Z5ejS                  dC      defdD       Z6y)E    )Optional)FastAPIFormRequestDepends)HTMLResponseRedirectResponseStreamingResponse)Jinja2TemplatesN)Error)StaticFiles)SessionMiddleware)datetime	timedelta)JSONResponse)load_dotenv)bcrypt)HTTPException)r   SESSION_SECRET)
secret_key	templates)	directoryHOSTUSERPASSWORDDATABASEhostuserpassworddatabaserequestc                 n    | j                   rd| j                   vrt        dd      | j                   d   S )Nr   .  zNot Authorized)status_codedetail)sessionr   r"   s    $/var/www/html/Resume-Scraper/main.pyrequire_loginr*   )   s1    ??fGOO;4DEE??6""    
job_titles	locations
after_yearbefore_year
query_textc                 F   	 t        j                  j                  di t        }|j	                         }d}|j                  || ||||f       |j                          |j                          |j                          y# t        $ r}t        d|        Y d }~yd }~ww xY w)Nz
            INSERT INTO resume_queries (job_titles, locations, after_year, before_year,query_text)
            VALUES (%s, %s, %s, %s, %s)
        Tu   ❌ MySQL Error: F )
mysql	connectorconnect	DB_CONFIGcursorexecutecommitcloser   print)	r,   r-   r.   r/   r0   
connectionr7   sqles	            r)   insert_queryr?   .   s    __,,9y9
""$ 	sZJJWX !!%&s   A<A? ?	B BB /)response_classmessager   c                 4    t         j                  d| |d      S )Nz	form.html)r"   rB   r   TemplateResponse)r"   rB   r   s      r)   	form_pagerF   ?   s    %%kwSZ3[\\r+   z/submitboolean_queryc                    |r|j                         rt        |      nd }|r|j                         rt        |      nd }|r!|j                         r|j                         }nd}	dj                  |j	                  d      D 
cg c]  }
d|
j                          d c}
      }dj                  |j	                  d      D cg c]  }d|j                          d c}      }|	d| dd| ddg}|r|j                  d|        |r|j                  d	|        d
j                  |      }t        |||||      }|rt        dd      S ddiS c c}
w c c}w )Nz (intitle:resume OR inurl:resume) OR ,"()z&-job -jobs -sample -samples -templateszafter:zbefore: z</?message=Data+added+successfully.+Scraping+will+start+soon.r$   urlr%   errorzFailed to insert into database.)isdigitintstripjoinsplitappendr?   r	   )r"   r,   r-   r.   r/   rG   after_year_intbefore_year_intr0   
base_querytitlejob_partloclocation_partquery_partssuccesss                   r)   submit_formra   C   sb    )3z7I7I7KS_QUN*5+:M:M:Oc+&UYO ,,."((*
7
;;*BRBRSVBWX!EKKM?! 4XY9??SVCW$XCqQ%7$XYzOa 4	
 '789(9:;XXk*
 :y./S]^G$bpstt677%  Y$Xs   EEc                  H    t        j                  j                  di t        S )Nr2   )r3   r4   r5   r6   r2   r+   r)   get_connectionrc   i   s    ??""/Y//r+   c                    | j                  d      D cg c]  }|j                          }}g }|D ]  }|j                  d      D cg c]  }|j                          }}g }|D ]:  }|j                  d| d       |j                  d|j                          d       < |j                  ddj	                  |       d        dd	j	                  |       dS c c}w c c}w )
zv
    Parse the input filter for AND/OR logic and build SQL parts
    for the column (job_titles, locations, etc.)
    ANDORzLOWER(z	) LIKE %s%rL   rI   rM    AND )rV   rT   rW   lowerrU   )	filter_textcolumn_nameparamspartfilter_partsfilter_filtersor_partor_parts
or_filterss	            r)   parse_filterrs   l   s    
 .9->->u-EFTDJJLFLFN 
>37::d3CDGMMODD
 	2G{m9=>MMAgmmo.a01	2
 	&++j"9!:!<=
> w||N+,A.. G
 Es   CC!z/api/results    
   	job_titlelocation
start_dateend_datedrawstartlengthc	                 "   t               }	|	j                  d      }
t        d|       t        d|       g }g }|r|j                  t	        |d|             |r|j                  t	        |d|             |r"|j                  d       |j                  |       |rTt        j                  |d      t        d	
      z   }|j                  d       |j                  |j                  d             |rddj                  |       nd}|
j                  d| dt        |             |
j                         d   }|dk(  r2d| d}|
j                  |t        |             |
j                         }n-|
j                  d| dg |||       |
j                         }|D ]9  }t        |j                  d      t
              s#|d   j                  d      |d<   ; |
j!                          |	j!                          t#        ||||||||dd      S )NT
dictionaryz
Job Title:z	Location:zrq.job_titleszrq.locationsrq.created_at >= %s%Y-%m-%dru   daysrq.created_at < %sWHERE rh   rt   z
        SELECT COUNT(*) as total
        FROM google_search_results gr
        JOIN resume_queries rq ON gr.resume_query_id = rq.id
        z
    totala  
            SELECT gr.id, gr.url, gr.email, gr.page_number, gr.status, gr.retry, gr.resume_query_id,
                   rq.job_titles, rq.locations, rq.created_at
            FROM google_search_results gr
            JOIN resume_queries rq ON gr.resume_query_id = rq.id
            z6
            ORDER BY gr.resume_query_id DESC
        zU
            ORDER BY gr.resume_query_id DESC
            LIMIT %s OFFSET %s
        
created_at)rw   rx   ry   rz   )r{   recordsTotalrecordsFiltereddatafilters)rc   r7   r;   rW   rs   r   strptimer   strftimerU   r8   tuplefetchonefetchall
isinstancegetr:   r   )r"   rw   rx   ry   rz   r{   r|   r}   r   connr7   r   rl   end_datetimewhere_clauser   queryrowsrows                      r)   get_results_apir      s0   D[[D[)F	,	"	+x GF |IGH |HnfEF ,-j!((:>PQARR+,l++J787>VGLL123BL NN 	 
 	
 
v OOg&E |
 N 	 	ueFm,  
 N 	 &v%v%u%	'    Gcggl+X6 #L 1 : :: FCG LLNJJL " $ 	
  r+   z/resultsresume_query_idc                    t               }|j                  d      }g }	g }
|r&|	j                  d       |
j                  d| d       |r&|	j                  d       |
j                  d| d       |r"|	j                  d       |
j                  |       |r"|	j                  d       |
j                  |       |r"|	j                  d       |
j                  |       |	rd	d
j                  |	       nd}d| d}|j	                  |t        |
             |j                         }|j                          |j                          t        j                  d| ||||||dd      S )NTr   rq.job_titles LIKE %srg   rq.locations LIKE %szgr.resume_query_id = %sr   rq.created_at <= %sr   rh   rt   a  
        SELECT gr.id, gr.url, gr.email, gr.page_number, gr.status, gr.retry, gr.resume_query_id,
               rq.job_titles, rq.locations, rq.created_at
        FROM google_search_results gr
        JOIN resume_queries rq ON gr.resume_query_id = rq.id
        zA
        ORDER BY gr.resume_query_id DESC
        LIMIT 1000
    zresults.html)rw   rx   r   ry   rz   )r"   resultsr   )
rc   r7   rW   rU   r8   r   r   r:   r   rE   )r"   rw   rx   r   ry   rz   r   r   r7   r   rl   r   r   r   s                 r)   show_resultsr      sY   D[[D[)FGF./)A&'-.(1o&01o&,-j!,-h7>VGLL123BL	
 
 E NN5%-(ooG
LLNJJL%%n" .$ 

7 
 
r+   z/export/allc                    t         j                  j                  t        j                  d      t        j                  d      t        j                  d      t        j                  d            }|j                         }d}g }g }	| r&|j                  d       |	j                  d|  d       |r&|j                  d	       |	j                  d| d       |r"|j                  d
       |	j                  |       |r"|j                  d       |	j                  |       |r|ddj                  |      z   z  }|j                  |t        |	             |j                         }
|j                  D cg c]  }|d   	 }}t        j                         }t        j                  |      }|j!                  |       |j#                  |
       |j%                  d       |j'                          |j'                          t)        |dddi      S c c}w )Nr   r   r   r   r   z
        SELECT gr.url, gr.email, rq.job_titles, rq.locations, rq.created_at
        FROM google_search_results gr
        JOIN resume_queries rq ON gr.resume_query_id = rq.id
    r   rg   r   r   r   z WHERE rh   r   text/csvContent-Dispositionz#attachment; filename=export_all.csv
media_typeheaders)r3   r4   r5   osgetenvr7   rW   rU   r8   r   r   descriptionioStringIOcsvwriterwriterow	writerowsseekr:   r
   )rw   rx   ry   rz   r   r   r7   r   r   rl   r   ir   outputr   s                  r)   
export_allr     s   ??""YYvYYv:&:&	 # D [[]FE
 GF./)A&'-.(1o&,-j!,-hW\\'222
NN5%-(??D#//0qt0G0[[]FZZF
OOG
T
KKN
LLNJJLV
EZ  ]B  EC  D  D 1s   0H
z/export/emailsc                 2   t         j                  j                  t        j                  d      t        j                  d      t        j                  d      t        j                  d            }|j                         }d}g }g }	| r&|j                  d       |	j                  d|  d       |r&|j                  d	       |	j                  d| d       |r"|j                  d
       |	j                  |       |rTt        j                  |d      t        d      z   }
|j                  d       |	j                  |
j                  d             |r|ddj                  |      z   z  }t        |       |j                  |t        |	             |j                         D cg c]  }|d   	 }}t!        j"                  dj                  |            }|j%                  d       |j'                          |j'                          t)        |dddi      S c c}w )Nr   r   r   r   r   z
        SELECT gr.email
        FROM google_search_results gr
        JOIN resume_queries rq ON gr.resume_query_id = rq.id
        WHERE gr.email IS NOT NULL AND gr.email != ''
    r   rg   r   r   r   ru   r   r   rh   r   
r   r   zattachment; filename=emails.csvr   )r3   r4   r5   r   r   r7   rW   r   r   r   r   rU   r;   r8   r   r   r   r   r   r:   r
   )rw   rx   ry   rz   r   r   r7   r   r   rl   r   r   emailsr   s                 r)   export_emailsr   ?  s   ??""YYvYYv:&:&	 # D [[]FE GF./)A&'-.(1o&,-j!((:>PQARR+,l++J78 7<<000	%L
NN5%-( & 12c!f2F2 [[6*+F
KKN
LLNJJLV
EZ\}D~ 3s   !H/loginc                 2    t         j                  dd| i      S )N
login.htmlr"   rD   r(   s    r)   
login_formr   u  s    %%lY4HIIr+   .usernamer    c                 r   t               }|j                  d      }|j                  d|f       |j                         }|j	                          |j	                          |r>t        j                  ||d         r%|d   |d   d| j                  d<   t        d	d
      S t        j                  d| dd      S )NTr   z'SELECT * FROM users WHERE username = %spassword_hashidr   )r   r   r   r@   r$   rO   r   zInvalid credentials)r"   rQ   )rc   r7   r8   r   r:   r   verifyr'   r	   r   rE   )r"   r   r    r   r7   r   s         r)   loginr   y  s    D[[D[)F
NN<xkJ??D
LLNJJLh_(=>)-djAQ"RCS99%%lRg4hiir+   z/logoutc                 P    | j                   j                          t        dd      S )Nr   r$   rO   )r'   clearr	   r(   s    r)   logoutr     s    OOc::r+   )7typingr   fastapir   r   r   r   fastapi.responsesr   r	   r
   fastapi.templatingr   mysql.connectorr3   r   fastapi.staticfilesr   starlette.middleware.sessionsr   r   r   r   r   r   dotenvr   r   passlib.hashr   r   appadd_middlewarer   r   r6   r;   r*   strrS   r?   r   dictrF   postra   rc   rs   r   r   r   r   r   r   r   r2   r+   r)   <module>r      s    2 2 N N .  ! + ; 
 	 ( *  	  ! ;   i   $;K1L  Mk2	 BIIfBIIf		*%		*%		 i #7 #
S S hsm ZbcfZg ux " \*9=GTaLb ]w ]# ]T ] +] ) !%T
#Dz $T
!%d#':"8"8"8 }"8 	"8
 #"8 C="8 "8J0/, 792acuw  FG  VW  gi  x  @M  xN PW P PS P[^ Por P  @C P  PS P  ad P  qu P Pd L146cey{  NP  _f  gt  _u 3' 3c 3# 3]` 3sv 3  HK 3  X\ 3 23p  "B"^`ov  xE  pF .D# .Dc .DC .DX[ .Dhl .D .D` 	#%rRacry  {H  sI 2@S 2@ 2@s 2@[^ 2@ko 2@ 2@j ,/J J 0J (,0ItCy j7 jc j j j ;G ; ;r+   