
    /i`M                     H   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
r
\R                  S:X  a  \R                  R                  SS9  \R                  R                  S \R                  R!                  \R                  R#                  \5      S5      5        S SKJrJr  SrS	S
SS.rS	SSS
SSS.rSrS rS rS rS rS rS rS r S r!\"S:X  a	   \!" 5         gg! \# a    \$" S5         g\% a*  r&\$" S\& 35        S SK'r'\'RP                  " 5          Sr&C&gSr&C&ff = f)    N)BeautifulSoupwin32zutf-8)encoding..)	DB_CONFIGget_db_connectionzPhttps://nepps-search.eprocurement.gov.gr/actSearch/faces/active_search_main.jspxzoMozilla/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Referer%   c                     [         R                  R                  [        S   [        S   [        S   S9n U R	                  5       nUR                  S[        S    35        UR                  5         U R                  5         [        5       n U R	                  5       nUR                  S5        U R                  5         UR                  5         U R                  5         [        S5        g )	Nhostuserpassword)r   r   r   zCREATE DATABASE IF NOT EXISTS databasea>  
        CREATE TABLE IF NOT EXISTS nepps (
            id INT AUTO_INCREMENT PRIMARY KEY,
            procedure_id VARCHAR(100) UNIQUE,
            url VARCHAR(500),
            title TEXT,
            budget VARCHAR(255),
            publication_date VARCHAR(255),
            submission_start VARCHAR(255),
            submission_end VARCHAR(255),
            cpv_code VARCHAR(255),
            cpv_description TEXT,
            authority TEXT,
            delivery_place TEXT,
            status VARCHAR(255),
            award_amount VARCHAR(255),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_procedure_id (procedure_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    zTable 'nepps' ready)
mysql	connectorconnectr   cursorexecutecloser   commitprint)connr   s     NC:\Users\Yogesh Aggrawal\Documents\GitHub\tenders\nepps\scrape_tenders_ajax.pycreate_tabler   %   s    ??""vv:& # D
 [[]F
NN3Ij4I3JKL
LLNJJLD[[]F
NN  	, 	KKM
LLNJJL	
     c                    U (       d  g[        5       nUR                  5       nSnSnSnU  Hf  n UR                  UUS   US   US   US   US   US	   US
   US   US   US   US   US   US   45        UR                  S:X  a  US-  nMa  US-  nMh     UR                  5         UR                  5         UR                  5         XE4$ ! [         a  n[        SU 35         S nAM  S nAff = f)N)r   r   a;  
    INSERT INTO nepps (procedure_id, url, title, budget, publication_date,
                       submission_start, submission_end, cpv_code, cpv_description,
                       authority, delivery_place, status, award_amount)
    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
    ON DUPLICATE KEY UPDATE
        url = VALUES(url),
        title = VALUES(title),
        budget = VALUES(budget),
        publication_date = VALUES(publication_date),
        submission_start = VALUES(submission_start),
        submission_end = VALUES(submission_end),
        cpv_code = VALUES(cpv_code),
        cpv_description = VALUES(cpv_description),
        authority = VALUES(authority),
        delivery_place = VALUES(delivery_place),
        status = VALUES(status),
        award_amount = VALUES(award_amount)
    r   procedure_idurltitlebudgetpublication_datesubmission_startsubmission_endcpv_codecpv_description	authoritydelivery_placestatusaward_amount   z  DB Error: )r   r   r   rowcount	Exceptionr   r   r   )tendersr   r   insert_queryinsertedupdatedtes           r   insert_tendersr7   N   s!   D[[]FL( HG	NN<.!%'
($%$%"#*#$+"#(.!*  !#A1) 2 	KKM
LLNJJL  	L$%	s   ACC
C+C&&C+c                      [        5       n U R                  5       nUR                  S5        UR                  5       S   nUR	                  5         U R	                  5         U$ )NzSELECT COUNT(*) FROM neppsr   )r   r   r   fetchoner   )r   r   counts      r   get_current_countr;      sJ    D[[]F
NN/0OOa E
LLNJJLLr   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[        nU(       a  [        S#-   U-   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	[
        R                  " S'U5      nU(       a  UR                  S5      n	U	(       d/  [
        R                  " S(U5      nU(       a  UR                  S5      n	U	(       d/  [
        R                  " S)U5      nU(       a  UR                  S5      n	[
        R                  " S*U5      n
U
(       a  U
R                  S5      OS+n[        S,U 35        [        S-U	 35        [        S.U 35        [        S/[        U5       S035        S1U;  a  U	c  [        S25        [        USS3 5        X4$ )4z?Handle ADF loopback flow and extract ViewState + Adf-Window-Id.   )headerstimeoutNz'_afrLoop',\s*'(\d+)'r.   z;jsessionid=([^'\"]+)'w 
   )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>   r?   zAdfLoopbackUtils.runLoopback2z0name="javax\.faces\.ViewState"\s+value="([^"]+)"z-javax\.faces\.ViewState["\s>]*value="([^"]+)"z)ViewState.*?(?:value|CDATA)\W+([^\s"<>]+)z<form[^>]+id="([^"]+)"f1z  Window-Id: z  ViewState: z  Form: z  Page length: z chars	ViewStateu?     WARNING: Page does not contain ViewState — dumping snippet:i  )getURLHEADERSraise_for_statustextresearchgroupjoinrandomchoicesstringascii_lowercasedigitsr   len)sessionresprd   afr_loopm
jsessionid	window_idloopback_paramsloopback_url
view_state
form_match	form_names               r   get_initial_staterz      sN    ;;sGR;8D99D H
		*D1A771:J
		+T2AWWQZ
 bggfnnV-C-Cfmm-SWYZ[[IHO# 	 	$	
 	( 	6 	6 	F 	F 	4 	C 	3 	4 	3 	3  	3!O& L^+j8;;|WVX;YD99D &-II.5wwqzHII/6J/*<L&.o#
#{{<Z\{]yy J
		EtLAWWQZ
IIFMJIIBDIJ 4d;J'1
  #tI	M)
%&	M*
&'	HYK
 !	OCI;f
-.$:#5OPd5Dk  r   c                     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SSSSS.	En[          SU S3nU R                  XC[        SS 9nUR                  $ )!z>POST the search form to trigger query and get initial results.socLangrG   qryId1:oper0=qryId1:val00rE   qryId1:oper1qryId1:val10rA   qryId1:oper2qryId1:val20qryId1:oper3qryId1:val30qryId1:oper4qryId1:val40qryId1:oper5qryId1:val50qryId1:oper6CONTAINSqryId1:val60qryId1:oper7qryId1:val70r^   z8{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.FORMrH   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>   r?   )ra   postAJAX_HEADERSrd   )ro   rw   rt   	form_datapost_urlrp   s         r   post_searchr      s)   3 	 		
 	 	 	 	 	 	 	 	 	 	
 	  	
!" 	#$ 37"!+'a  cx(75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                  $ )&a  POST a fetch event to paginate the ADF table.

Args:
    first: row offset for the viewport (0-indexed position in result set)
    row_key: _afrRK value of the row at `first` position
    fetch_id: incrementing request id (starts at 2 after the initial search)
z:<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|   rG   r}   r~   r   rE   r   r   rA   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   rH   r   r   r   r   r   r   r   r   r   r   )VIEWPORT_SIZEra   r   r   rd   )
ro   rw   rt   firstrow_keyfetch_idfetch_eventr   r   rp   s
             r   
post_fetchr     sl    z " ' "&i (		 3 	 		
 	 	 	 	 	 	 	 	 	 	
 	  	
!" 	#$ 37"!+):5'ATU\T]]klykzz~'#(03I8 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HW  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U(       a  S"U 3OS nUR                  UUUUUUUUUUUUUS#.5        GMZ     XX4U4$ )$zParse ADF partial-response XML to extract table rows + metadata.

Returns: (tenders, new_view_state, row_count, start_row, row_keys)
    row_keys: list of _afrRK values (strings) for pagination
Nr   zC<update id="javax\.faces\.ViewState"><!\[CDATA\[(.*?)\]\]></update>r.   z2<update id="pc1:t1"><!\[CDATA\[(.*?)\]\]></update>z_rowCount="(\d+)"z_startRow="(\d+)"zhtml.parsertrrolerow)attrs_afrrktdgridcell)r      aT)strip   spanz
::content$)id            	         rA   rB   z]https://nepps-search.eprocurement.gov.gr/actSearch/faces/active_search_main.jspx?procedureId=)r!   r"   r#   r$   r%   r&   r'   r(   r)   r*   r+   r,   r-   )re   rf   rg   DOTALLintr   find_allr`   appendrn   findget_textr   compile) xml_textr1   new_view_state	row_count	start_rowrow_keysvs_matchtable_match
table_htmlrcsrsouprowsr   afrrkcells	proc_linkr!   r#   budget_spanr$   r%   r&   r'   r(   r*   r,   r)   r+   
award_spanr-   r"   s                                    r   parse_adf_responser   M  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q***68$$4$0!H%%D%1	r##$#/;>u:>%(++$+7r;>u:?r++$+7PRMPQVZZ\_U2Y^^Frzz-/H^Ibf
:Dz***6mpqvmwz|m|5QS9K]K]dhK]Ki  CE AMmnzm{|  SU( 0 0, .",(
 	k J I(BBr   c                  l   [        S5        [        S5        [        S5        [        S[         35        [        S5        [        5         [        R                  " 5       n [        S5        [        U 5      u  pU(       d  [        S5        g [        S5        [        XU5      n[        U5      u  pEpgnU(       a  UnSn	Sn
Sn[        5       nSnU(       d  [        S	5        [        S
US S  35        g U Vs/ s H  oS   U;  d  M  UPM     nnU H  nUR                  US   5        M     [        U5      u  nnU	[        U5      -  n	U
U-  n
UU-  n[        SU S[        U5       SU SU S3	5        [        U(       a  SU SU SUS    SUS    3OS5        U[        U5      -   nSnU(       a  [        US   5      U-
  nOSnSnUU:  Ga  US-  nUU-   n[        SU SU SU S3SS9   [        XUUUUS9nUS-  n[        U5      u  pEn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  oS   U;  d  M  UPM     nnU H  nUR                  US   5        M     U(       dG  US-  n[        S"U S#35        US :  a  [        S$5        OU["        -  n[        R                   " S5        M  Sn[        U5      u  nnU	[        U5      -  n	U
U-  n
UU-  n[        S%[        U5       SU SU S&[        U5       S'U 3
5        US:  a  U[        U5      -   OU[        U5      -   n[        R                   " S5        UU:  a  GM  [        S(5        [        S)5        [        S5        [        S*U 35        [        S+U	S, 35        [        S-U
S, 35        [        S.US, 35        [        S/[%        5       S, 35        [        S5        g s  snf ! [         aL  n[        SU 35        US-  nUS :  a  [        S!5         S nAM  [        R                   " S 5         S nAGMh  S nAff = fs  snf )0NzF======================================================================z$NEPPS (ESIDIS) Greece - AJAX ScraperzSource: z"
[1] Getting initial page state...z,ERROR: Could not extract ViewState from pagez
[2] Posting search query...r   r.   z$ERROR: No tenders in search responsezResponse preview: i  r!   z
[Page z] Got z tenders (Inserted: z, Updated: )z  Total rows on server: z | Start row: z | Row keys: r   rA      z] Fetching from row z (key=z)... )end)r   r   r   zRequest failed: r   zToo many errors, stopping.zNo new tenders (attempt z).z$Stopping after repeated empty pages.zGot z) | Total: /zG
======================================================================zScraping Complete!z	  Pages: z  Fetched: ,z  New: z  Updated: z  DB total: )r   ra   r   requestsSessionrz   r   r   setaddr7   rn   r   r   r0   timesleepr   r;   )ro   rw   rt   	resp_textr1   new_vsr   r   r   total_fetchedtotal_insertedtotal_updatedseen_idspage_numr5   new_tendersr3   r4   current_firstr   
key_offsetconsecutive_errorsnext_row_keyr6   r   r   rks                              r   scrape_tendersr     s   	(O	
01	(O	HSE
	(ON G 

/0-g6J<= 

)*G;I6H6S3GY8
MNMuHH45"9Tc?"345 &Kg>):()J1gKKQ~&'  '{3HgS%%MhNWM	HXJfS%5$66J8*T_`g_hhi
jk	w$YK~i[V^_`VaUbbdemnpeqdr
s  FH  I G,MH
 !%	1


)
#A %z1
"6}oVL>Y]^dgh	"7	)6+7,46I MH '9&C#RJ6I "q&RUbJ")O'Q~->h-Nq'OALL>*+  !#,-?,@CD!Q&<=]*MJJqM*;7'[))(" S%&&:8*KPWyXcdghpdqcrrst}s~  	A-/!VS\)W9U

1w )
#z 
/	
	(O	IhZ
 !	Ka(
)*	GN1%
&'	Ka(
)*	L*,Q/
01	(OA LR  	$QC()!#!Q&23JJqM	( Ps6   2OO'O <P1P1
P."$P)P))P.__main__z

Interrupted.z
Fatal Error: ))r   bs4r   mysql.connectorr   re   r   sysosri   rk   platformstdoutreconfigurepathinsertrh   dirname__file__	db_configr   r   ra   rb   r   r   r   r7   r;   rz   r   r   r   r   __name__KeyboardInterruptr   r0   r6   	traceback	print_exc r   r   <module>r     s>      	  
 	  <<7JJG, 277<< 94@ A 2X DO0 DFO8a &!R:z\!~!H2jqChHV z   # " s#$s   C" "D!2D!7 DD!