
    ihL                        S SK r S SKJr  S SKrS SKrS SKrS SKrS SKrS SKrS SK	J	r	  \R                  S:X  a  \R                  R                  SS9  S SKJr  \" \R                  R!                  \R                  R#                  \5      S5      5        S SKr\R                  R+                  S \R                  R!                  \R                  R#                  \5      S	5      5        S S
KJrJr  S r\R4                  " SS5      R7                  S5       V s/ s H)  o R9                  5       (       d  M  U R9                  5       PM+     sn r\R4                  " SS5      R7                  S5       V s/ s H)  o R9                  5       (       d  M  U R9                  5       PM+     sn r\\-   rS SK r \ RB                  " \R4                  " SS5      5      r"Sr#Sr$Sr%\&" \R4                  " SS5      5      r'Sr(SSSS.r)SSSSSSS.r*S  r+S! r,S" r-S+S# jr.S$ r/S% r0S+S& jr1S' r2\3S(:X  a	   \2" 5         ggs  sn f s  sn f ! \4 a    \5" S)5         g\6 a*  r7\5" S*\7 35        S SK8r8\8Rr                  " 5          Sr7C7gSr7C7ff = f),    N)BeautifulSoup)datetimewin32zutf-8)encoding)load_dotenvz../.envz..)is_keyword_donemark_keyword_donec            	          [         R                  R                  [        R                  " S5      [        [        R                  " SS5      5      [        R                  " S5      [        R                  " S5      [        R                  " S5      S9$ )NDB_HOSTDB_PORTi  DB_USERDB_PASSWORDDB_NAME)hostportuserpassworddatabase)mysql	connectorconnectosgetenvint     IC:\Users\Yogesh Aggrawal\Documents\GitHub\tenders\nepps\nepps_keywords.pyget_db_connectionr      sa    ??""YYy!9d+,YYy!=)9% #  r   KEYWORDS ,GREEK_KEYWORDS	CPV_CODESz{}zPhttps://nepps-search.eprocurement.gov.gr/actSearch/faces/active_search_main.jspxzLhttps://nepps-search.eprocurement.gov.gr/techSpecs/faces/techSpecs_data.jspxneppsNEPPS_MAX_PAGES   %   zoMozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/131.0.0.0 Safari/537.36z?text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8zel-GR,el;q=0.9,en;q=0.8)
User-AgentAcceptzAccept-Languagez0application/x-www-form-urlencoded; charset=UTF-8truez(https://nepps-search.eprocurement.gov.gr)r(   zContent-TypezAdf-Rich-Messager)   OriginRefererc                    U (       a  [        U 5      R                  5       (       d  g  [        U 5      R                  5       R                  S5      S   n[        R                  " US5      R                  5       $ ! [         a     g f = f)N r   z%d/%m/%Y)strstripsplitr   strptimedate	Exception)val	date_parts     r   safe_date_greekr7   ;   sk    c#hnn&&HNN$**3/2	  J7<<>> s   AA8 8
BBc                    U (       d  g[        5       nUR                  5       nSnSnU  GHR  nUS   n[        UR                  S5      5      n[        UR                  S5      5      n	[        UR                  S5      5      n
 UR	                  S[
        U45        UR                  5       nU(       a`  US   nUR	                  SUS	   US
   US   US   X45        UR	                  SUS
   US   XUXS   US   US   US   US   U45        US-  nM  UR	                  S[
        XvS	   XvS   US
   US   X4	5        UR                  nUR	                  SXUUS
   US   XUXS   US   US   US   US   45        US-  nGMU     UR                  5         UR                  5         UR                  5         XE4$ ! [         a  n[        SU 35         S nAGM  S nAff = f)Nr   r   r   procedure_idsubmission_endpublication_datesubmission_startzCSELECT id FROM tenders WHERE source = %s AND source_id = %s LIMIT 1zUPDATE tenders SET title = %s, organization = %s, url = %s,
                       status = %s, closing_date = %s, updated_at = NOW() WHERE id = %stitle	authorityurlstatusa  UPDATE tender_details SET
                           contracting_authority_name = %s, budget = %s,
                           deadline = %s, submission_start = %s, submission_end = %s,
                           publication_date = %s, cpv_code = %s, cpv_description = %s,
                           delivery_place = %s, place_of_performance = %s,
                           award_amount = %s, updated_at = NOW()
                       WHERE tender_id = %sbudgetcpv_codecpv_descriptiondelivery_placeaward_amount   a  INSERT INTO tenders
                           (source, source_id, title, reference_number, url, organization,
                            status, closing_date, keyword, detail, created_at, updated_at)
                       VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, 0, NOW(), NOW())a  INSERT INTO tender_details
                           (tender_id, procedure_id, reference_number,
                            contracting_authority_name, budget,
                            deadline, submission_start, submission_end,
                            publication_date, cpv_code, cpv_description,
                            delivery_place, place_of_performance,
                            award_amount, created_at, updated_at)
                       VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW(), NOW())z  DB Error: )r   cursorr7   getexecuteSOURCEfetchone	lastrowidr4   printcommitclose)tenderskeywordconnrH   insertedupdatedtr:   closing_datepub_date	sub_startrow	tender_ides                 r   insert_tendersr]   E   s0   D[[]FHG(&quu-='>?&quu-?'@A&quu-?'@A	;	NNU& //#CF	[wZ;51X;`
 / {^Qx[!l}a0A.B'(!,<*=~&		3 1W \W:|uX{^Qx[,I #,,	h l{^Qx[!l}a0A.B'(!,<*=~&(  A{ F 	KKM
LLNJJL  	L$%	s    BF3A+F33
G=GGc                    U R                  [        [        SS9nUR                  5         UR                  nS n[
        R                  " SU5      nU(       a  UR                  S5      nS n[
        R                  " SU5      nU(       a  UR                  S5      nSSR                  [        R                  " [        R                  [        R                  -   SS	95      -   n0 S
U=(       d    S_SS_SU_SS_SS_SS_SS_SS_SS_SS_SS_SS_SS_SS_S S_S!S_n[        U(       a  S"U-   OS-   nU R                  X[        SS#9nUR                  5         UR                  nS$U;   a  [
        R                  " SU5      nU(       a  UR                  S5      n[
        R                  " SU5      nU(       a  UR                  S5      n[        S"-   U-   nU=(       d    S%US
'   U R                  X[        SS#9nUR                  5         UR                  nS n	S& H5  n
[
        R                  " X5      nU(       d  M"  UR                  S5      n	  X4$    X4$ )'N   )headerstimeoutz'_afrLoop',\s*'(\d+)'rG   z;jsessionid=([^'\"]+)'wr    
   k_afrLoop1_afrWindowMode0Adf-Window-Id_afrFS16_afrMTscreen_afrMFW1920_afrMFH1080_afrMFDW_afrMFDH_afrMFC24_afrMFCI_afrMFM_afrMFR96_afrMFG_afrMFS_afrMFOz;jsessionid=)paramsr`   ra   zAdfLoopbackUtils.runLoopback2)z0name="javax\.faces\.ViewState"\s+value="([^"]+)"z-javax\.faces\.ViewState["\s>]*value="([^"]+)"z)ViewState.*?(?:value|CDATA)\W+([^\s"<>]+))rI   URLHEADERSraise_for_statustextresearchgroupjoinrandomchoicesstringascii_lowercasedigits)sessionrespr   afr_loopm
jsessionid	window_idloopback_paramsloopback_url
view_statepatterns              r   get_initial_stater      s   ;;sGR;8D99DH
		*D1A771:J
		+T2AWWQZ
bggfnnV-C-Cfmm-SWYZ[[I
HO
#
 	
 	$	
 !(	

 	6

 %f
 	F
 '
 	4
 $S
 	3
 "4
 	3
 "3
 )23
O .:5LL;;|WVX;YD99D%-II.5wwqzHII/6J/*<L&.o#
#{{<Z\{]yyJ
 IIg$1J     r   c                     0 SS_SS_SS_SS_S	U_S
S_SS_SS_SS_SS_SS_SS_SS_SS_SU_SS_SS_SUSUSSSSSS.	En[          SU S3nU R                  Xe[        SS 9nUR                  $ )!NsocLangri   qryId1:oper0=qryId1:val00rg   qryId1:oper1CONTAINSqryId1:val10qryId1:oper2qryId1:val20r    qryId1:oper3qryId1:val30qryId1:oper4qryId1:val40qryId1:oper5qryId1:val50qryId1:oper6qryId1:val60qryId1:oper7qryId1:val70f1z8{pc1:t1={viewportSize=26,rows=50},pc1:_clmCxt={_shown=}}zpc1:t1,qryId1z<m xmlns="http://oracle.com/richClient/comm"><k v="operation"><s>RESULT_COMPONENT_DECODE</s></k><k v="type"><s>queryInternal</s></k></m>z^<m xmlns="http://oracle.com/richClient/comm"><k v="clearAll"/><k v="type"><s>query</s></k></m>)	&org.apache.myfaces.trinidad.faces.FORMrj   Adf-Page-Idjavax.faces.ViewStateoracle.adf.view.rich.DELTASeventevent.pc1:t1zevent.qryId1oracle.adf.view.rich.PROCESS?Adf-Window-Id=&Adf-Page-Id=0<   datar`   ra   )r   postAJAX_HEADERSr   )r   r   r   rR   rC   	form_datapost_urlr   s           r   post_searchr      s'   3$2C 	
 %3G 	
	 %3B	
 	
 %3B 	 %3B 	 %3B 	
 %3H 	
 %3B 37"!+'a  cx(7%I( oi[?H<<,PR<SD99r   c                    SU SU SU S3n0 SS_SS_S	S
_SS_SS_SS_SS_SS_SS_SS_SS_SS_SS_SS_SS_SS_SS_SUSUSU SU S[          S3S US S!.En[         S"U S#3nU R                  X[        S$S%9n	U	R                  $ )&Nz:<m xmlns="http://oracle.com/richClient/comm"><k v="id"><n>z3</n></k><k v="subtype"><n>2</n></k><k v="index"><n>z</n></k><k v="clientKey"><s>zq</s></k><k v="renderOnly"><b>1</b></k><k v="suppressMessageClear"><s>true</s></k><k v="type"><s>fetch</s></k></m>r   ri   r   r   r   rg   r   r   r    r   r   r   r   r   r   r   r   r   r   r   r   r   r   z{pc1:t1={first=z,scrollTopRowKey|p=z,viewportSize=z}}zpc1:t1)r   rj   r   r   r   r   r   r   r   r   r   r   )VIEWPORT_SIZEr   r   r   r   )
r   r   r   firstrow_keyfetch_idfetch_eventr   r   r   s
             r   
post_fetchr      sj    z " ' "&i (		 3-s 	  .r 		  .r	
 	
  .r 	  .r 	  .r 	
 %3B 	
 %3B 37"!+):5'ATU\T]]klykzz~'#(0#I& oi[?H<<,PR<SD99r   c                    / nS nSnSn/ n[         R                  " SU 5      nU(       a  UR                  S5      n[         R                  " SU [         R                  5      nU(       d  XX4U4$ UR                  S5      n[         R                  " SU5      n	U	(       a  [	        U	R                  S5      5      n[         R                  " SU5      n
U
(       a  [	        U
R                  S5      5      n[        US5      nUR                  SS	S
0S9nU GHN  nUR                  S5      nUc  M  UR                  U5        UR                  SSS9n[        U5      S:  a  ML  US   R                  S5      nU(       a  UR                  SS9OUS   R                  SS9nU(       a  UR                  5       (       d  M  US   R                  SS9nUS   R                  S[         R                  " S5      S9nU(       a  UR                  SS9OUS   R                  SS9nUS   R                  SS9nUS   R                  SS9nUS   R                  SS9nUS   R                  SS9nUS   R                  SS9nUS   R                  SS9n[        U5      S:  a  US   R                  SS9OSn[        U5      S :  a  US    R                  SS9OSn[        U5      S:  a'  US   R                  S[         R                  " S5      S9OS nU(       a  UR                  SS9O"[        U5      S:  a  US   R                  SS9OSnS!U 3nUR                  UUUUUUUUUUUUUS".5        GMQ     XX4U4$ )#Nr   zC<update id="javax\.faces\.ViewState"><!\[CDATA\[(.*?)\]\]></update>rG   z2<update id="pc1:t1"><!\[CDATA\[(.*?)\]\]></update>z_rowCount="(\d+)"z_startRow="(\d+)"zhtml.parsertrrolerZ   )attrs_afrrktdgridcell)r      aT)r0      spanz
::content$)id            	         r    rc   z]https://nepps-search.eprocurement.gov.gr/actSearch/faces/active_search_main.jspx?procedureId=)r:   r@   r>   rB   r<   r=   r;   rC   rD   r?   rE   rA   rF   )r   r   r   DOTALLr   r   find_allrI   appendlenfindget_textr0   compile) xml_textrQ   new_view_state	row_count	start_rowrow_keysvs_matchtable_match
table_htmlrcsrsouprowsrZ   afrrkcells	proc_linkr:   r>   budget_spanrB   r<   r=   r;   rC   r?   rA   rD   rE   
award_spanrF   r@   s                                    r   parse_adf_responser     s   GNIIHyyNH !*))="))K 	hFF""1%J	'	4B	$		'	4B	$	]3D==fe_=5D!=T
3u:?!HMM#&	9By)))5aHYHY`dHYHe<#5#5#7#7a!!!-AhmmFrzz-/HmI5@%%D%1eAhFWFW^bFWFc 8,,4,8 8,,4,8 8,,4,8 8,,4,8 8,,4,8	 9--D-9<?JN58,,4,8PR=@Z"_59--D-9RTSVW\S]`bSb59>>&RZZ5N>Ohl
>H:..T.:qtuzq{  A  rAuUWyOaOahlOaOm  GImnzm{|(u2B 0N _"nl
 	= N I(BBr   c           	         U (       a  SU  S3OSU 3n[        SU 35        [        R                  " 5       n[        SSS9  [        U5      u  pEU(       d  [        S5        g	[        S
5        [        SSS9  [	        X4XPUS9n[        U5      u  pxpnU(       a  UnU(       d  [        S5        g	[        S[        U5       SU	 35        [        5       nSnSnSnU =(       d    UnU Vs/ s H  nUS   U;  d  M  UPM     nnU H  nUR                  US   5        M     [        UU5      u  nnUU-  nUU-  n[        SU SU SU 35        U
[        U5      -   nSnU(       a  [        US   5      U
-
  OSnSnUU	:  Gax  U[        :  Gam  US-  nUU-   n[        SU SU S3SS9   [        X4UUUUS9nUS-  n[        U5      u  pxnnnU(       a  UnUS:  a  Un	U(       a  US:  a  [        US   5      U-
  nU Vs/ s H  nUS   U;  d  M  UPM     nnU H  nUR                  US   5        M     U(       d<  US-  n[        SU S35        US:  a  OU[         -  n[        R                  " S5        M  Sn[        UU5      u  nnUU-  nUU-  n[        S[        U5       SU SU 35        US:  a  U[        U5      -   OU[        U5      -   n[        R                  " S5        UU	:  a  U[        :  a  GMm  U[        :  a  [        S [         S!35        [        S"U SU 35        X4$ s  snf ! [         aL  n[        SU 35        US-  nUS:  a  [        S5         S nAMj  [        R                  " S5         S nAGM  S nAff = fs  snf )#Nz	keyword=""zCPV=z
  z    Getting session...r.   endzERROR: Could not get ViewStater9   OKz    Posting search...)rR   rC   zNo results.zGot z rows | Total on server: r   rG   r:   z
    [Page z] Inserted: z, Updated: r&   z] Fetching from row z...)r   r   r   zRequest failed: r   z    Too many errors, stopping.zNo new tenders (attempt z).z | Inserted: z    Reached max pages (u   ) — stopping.z    => New: )rN   requestsSessionr   r   r   r   setaddr]   r   	MAX_PAGESr   r4   timesleepr   )rR   rC   labelr   r   r   	resp_textrQ   new_vsr   r   r   seen_idstotal_insertedtotal_updatedpage_numkw_labelrV   new_tendersrT   rU   current_firstr   
key_offsetconsecutive_errorsnext_row_keyr\   r   r   rks                                 r   scrape_keywordr  ^  s   (/j	$tH:5FE	D. G	
",-g6J./	$K	
!s+GV^_I6H6S3GY8
m	DW7	{
CDuHNMH"(H%Kg>):()J1gKKQ~&'  '{H=HghNgM	JxjXJk'
KLG,MH19Xa[!I-qJ
)
#9(<A$z1
8*$8sKQTU	"7	)6+7,46I MH '9&C#RJ6I"q&RUbJ")O'QQ~->h-Nq'OALL>*+  !#,-?,@CD!Q&]*MJJqM*;A'("'!S%&mH:[	RS-/!VS\)W9U

1a )
#9(<d 9'	{/BC	L(M?
CD((I L2  	$QC()!#!Q&67JJqM	" Ps6   $L#5L#!L( 6NN(
M>2$M9M99M>c            	      ~   [        S5        [        S5        [        S5        [        S[        [        5       35        [        S[        [        5       35        [        S[         35        [        S5        [        5       n SnSn[        [        S5       H  u  p4[        SU S	[        [        5       S
3SS9  [        X[        5      (       a  [        SU S35        MI  [        US9u  pVX-  nX&-  n[        X[        5        [        SU S35        [        R                  " S5        M     [        [        R                  5       S5       H  u  nu  px[        SU S	[        [        5       S
3SS9  SU 3n	[        X	[        5      (       a  [        SU 35        MP  [        US9u  pVX-  nX&-  n[        X	[        5        [        SU SU 35        [        R                  " S5        M     U R                  5         [        S5        [        S5        [        S5        [        SUS 35        [        SUS 35        [        S5        g )NzF======================================================================z&NEPPS (Greece) - Keyword + CPV ScraperzKEYWORDS      : zCPV_CODES     : zMAX_PAGES     : r   rG   z
[KW /]r    r   z  [SKIP] Already ran today: "r   )rR   z  [SAVED] Marked done: "r&   z
[CPV zcpv:z  [SKIP] Already ran today: )rC   z  [SAVED] Marked done: u    — zG
======================================================================zScraping Complete!z  New     : r!   z  Updated : )rN   r   ALL_KEYWORDSr#   r   r   	enumerater   rK   r  r	   r   r   itemsrP   )
tracker_conngrand_insertedgrand_updatedirR   rT   rU   rC   	cpv_labeltracker_keys
             r   mainr    s   	(O	
23	(O	S./
01	S^,
-.	YK
()	(O$&LNM  a0
qc3|,-Q/R8<&9927)2>?*7;"!,8)'"56

1 1 %.ioo.?$C  Hs!C	N+1-26XJ'<f==0
;<*H="!,V<'zykBC

1 %D 	/	
	(O	L*
+,	Lq)
*+	(Or   __main__z

Interrupted.z
Fatal Error: )r    r    ):r   bs4r   r   r   sysr   r   r   r   platformstdoutreconfiguredotenvr   pathr   dirname__file__mysql.connectorr   insertkeyword_trackerr   r	   r   r   r1   r0   r   r"   r  jsonloadsr#   r   URL_TECHrK   r   r   r   r   r   r7   r]   r   r   r   r   r  r  __name__KeyboardInterruptrN   r4   r\   	traceback	print_excrd   s   0r   <module>r-     s4     	  
 	   <<7JJG,  BGGLL2I> ?  277<< 94@ A > &(YYz2%>%D%DS%IW%IWWY)!'')%IW%'YY/?%D%J%J3%O]%OSZSZS\)!'')%O]N* JJryyd34	b^BII/34	 DO0 DFO8aOd:!z4!HICXd)N/d z s X]v  " ! s#$s6   H'1H',H,H,H1 1I0I0 I++I0