
    4zi+=                        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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S r\R&                  " S	S
5      R)                  S5       V s/ s H)  o R+                  5       (       d  M  U R+                  5       PM+     sn r\R&                  " SS
5      R)                  S5       V s/ s H)  o R+                  5       (       d  M  U R+                  5       PM+     sn r\\-   rSrSrSrSSS.r\" \R&                  " SS5      5      r\R>                  " 5       \" \S9-
  RA                  S5      r!S r"S r#S#S jr$S r%S r&S r'S r(S r)S r*S r+\,S :X  a	   \+" 5         ggs  sn f s  sn f ! \- a    \." S!5         g\/ a*  r0\." S"\0 35        S SK1r1\1Rd                  " 5          Sr0C0gSr0C0ff = f)$    N)datetime	timedeltawin32zutf-8)encoding)load_dotenvz../.envc            	          [         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     tendara/tendara_keywords.pyget_db_connectionr      sa    ??""YYy!9d+,YYy!=)9% #  r   KEYWORDS ,GERMAN_KEYWORDSz
tendera.atz*https://www.tendera.at/api/tenders/_searchd   zapplication/json)zContent-TypeAcceptTENDERA_DAYS   )days%Y-%m-%dc                     U (       d  g S H<  n [         R                  " U S S US [        U S S 5       5      R                  S5      s  $    g ! [         a     MN  f = f)N)%Y-%m-%dT%H:%M:%S%Y-%m-%d %H:%M:%Sr&   z%Y-%m-%dT%H:%M:%S%z   r&   )r   strptimelenstrftime	Exceptionvaluefmts     r   
parse_dater2   (   sh    \	$$U3BZ5Ec%*o1FGPPQ[\\ ]
   		s   7A
AAc                     U (       d  g S H-  n [         R                  " U S S U5      R                  S5      s  $    g ! [         a     M?  f = f)N)r(   r)   z%Y-%m-%dT%H:%Mr&   r*   r)   )r   r+   r-   r.   r/   s     r   parse_datetimer4   3   sV    W	$$U3BZ5>>?RSS X
   		s   (=
A
Ac                 h    U c  g [        U 5      R                  5       nU(       d  g U(       a  US U nU$ N)strstrip)r0   maxlenvs      r   sr;   >   s5    }E
AgvJHr   c                 Z    / SQnU H!  u  p#U R                  U5      S S/ 4;  d  M  Us  $    g )N))PROCEDURE_PT_OPENOpen)PROCEDURE_PT_RESTRICTED
Restricted)PROCEDURE_PT_DIRECTDirect)PROCEDURE_PT_WITH_PRIOR_NOTICEzWith Prior Notice)!PROCEDURE_PT_WITHOUT_PRIOR_NOTICEzWithout Prior Notice)$PROCEDURE_PT_COMPETITIVE_NEGOTIATIONzCompetitive Negotiationr   )get)srcmappingfieldlabels       r   derive_procurement_methodrK   I   s7    G  775>$B/L   r   c                 P   / n[        S5       H;  nU R                  SU S35      nU(       d  M!  UR                  [        U5      5        M=     U R                  S5      nU(       a)  [        U5      U;  a  UR                  [        U5      5        U(       a  SR	                  U5      $ S $ )N   ,OBJECT_CONTRACT_OBJECT_DESCR_CPV_ADDITIONAL_	_CPV_CODE4OBJECT_CONTRACT_OBJECT_DESCR_CPV_ADDITIONAL_CPV_CODEr   )rangerF   appendr7   join)rG   cpvsir:   s       r   derive_cpv_additionalrV   X   s    D1XGGB1#YOP1KKA  	FGASV4CF!388D>+t+r   c                 :   [         US/ SSSU SSSS.0SS	SS
SU SSSS.0/00S.0/S.0SSSS[        SS.00/S.0/S.0SSS00/S/S/ 0SSS./SSS00SSS	00S.S.n[        R                  " [        [
        USS9nUR                  5         UR                  5       $ )Nbool   query_string*FAND)querydefault_fieldallow_leading_wildcarddefault_operatornestedALL_CONTRACTORSmust)pathr]   )minimum_should_matchshouldrQ   SORTING_DATEnow)gtelte)rc   filterorderdescexcludes	date_time)rI   formatrd   ALL_CPVS)costs_per_cpvbiggest_bodies)sizefromr]   sortstored_fields_sourcedocvalue_fieldsaggsr$   )headersjsontimeout)	PAGE_SIZECUTOFF_DATErequestspostAPI_URLHEADERSraise_for_statusr|   )keywordfrom_offsetpayloadresps       r   
fetch_pager   h   s>    45 %3189<BG<A	5&!" %-0A,2068FELMPV[PU	I:56	925.2*/&!"'!!F 45!(>;W\;]*^ _'!G+.0
b !7F"345#&4LM&(<='&2C)DE
q<Gz =='LD99;r   c                 R   U R                  S[        U45        U R                  5       nU(       a  US   S4$ [        UR	                  S5      5      n[        UR	                  S5      5      n[        UR	                  S5      5      n[        UR	                  S5      5      n[        UR	                  S5      5      n	[        [        UR	                  S	5      5      5      n
[        [        UR	                  S
5      5      5      nU R                  S[        X%XgXXU4
5        U R                  S4$ )Nz;SELECT id FROM tenders WHERE source = %s AND source_id = %sr   FOBJECT_CONTRACT_TITLE OBJECT_CONTRACT_REFERENCE_NUMBEROBJECT_CONTRACT_SHORT_DESCR6CONTRACTING_BODY_ADDRESS_CONTRACTING_BODY_OFFICIALNAMECONTRACTING_BODY_URL_DOCUMENT+ADDITIONAL_CORE_DATA_DATE_FIRST_PUBLICATION"PROCEDURE_DATETIME_RECEIPT_TENDERSaN  
        INSERT INTO tenders
            (source, source_id, title, reference_number, description,
             organization, url, publication_type, closing_date, keyword,
             detail, created_at, updated_at)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 1,
                CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
    T)executeSOURCEfetchoner;   rF   r4   r2   	lastrowid)cursorrG   es_idr   rowtitlerefdescriptionorganizationurlpub_dateclosing_dates               r   get_or_insert_tenderr      s   
NNE	 //
C
1vu}SWW456ESWW?@ACSWW:;<KSWWUVWLSWW<=>C!!CGG,Y$Z"[\Ha(L MNOL
NN  %,
'
+, T!!r   c                 
   UR                  S5      nUR                  S5      nU R                  S/ UP[        UR                  S5      S5      P[        UR                  S5      S5      P[        UR                  S5      S5      P[        [        UR                  S5      5      5      P[        [        UR                  S	5      5      5      P[	        [        UR                  S
5      5      5      P[	        [        UR                  S5      5      5      P[	        [        UR                  S5      5      5      P[        [        UR                  S5      5      5      P[        [        UR                  S5      5      5      P[        UR                  S5      S5      P[        UR                  S5      S5      P[        UR                  S5      S5      P[        UR                  S5      5      P[        UR                  S5      S5      P[        U5      P[        UR                  S5      5      P[        UR                  S5      5      P[        UR                  S5      S5      P[        UR                  S5      S5      P[        UR                  S5      S5      P[        UR                  S5      S5      P[        UR                  S5      S5      P[        UR                  S5      S5      P[        UR                  S5      5      P[        UR                  S5      5      P[        UR                  S5      S5      P[        UR                  S5      S5      P[        UR                  S 5      S5      P[        UR                  S!5      5      P[        UR                  S"5      5      P[        UR                  S#5      S5      P[        UR                  S$5      S%5      P[        UR                  S&5      5      P[        UR                  S'5      S5      P[	        [        UR                  S(5      5      5      PUR                  S)5      =(       d    S PUR                  S*5      =(       d    S PUR                  S+5      =(       d    S PU(       a  [        R                  " US,S-9OS PU(       a  [        R                  " US,S-9OS P[        UR                  S.5      S/5      P[        UR                  S05      S/5      P[        U5      P[        UR                  S15      5      P[        UR                  S25      S5      P[        UR                  S35      5      PS4P75        g )5Nrb   rq   a  
        INSERT INTO tender_details (
            tender_id,
            reference_number, notice_type, category,
            publication_date, deadline, end_date, contract_start_date,
            bulletin_date, import_date, last_modified,
            contract_duration, contract_duration_full,
            nuts_place, place_of_performance,
            cpv_code, cpv_additional,
            summary,
            contracting_authority_name, contracting_authority_email,
            contact_name, contact_phone, contact_email,
            buyer_national_id, buyer_domain,
            additional_buyer_name, additional_buyer_contact,
            additional_buyer_national_id,
            additional_buyer_phone, additional_buyer_email,
            documents, participation_url,
            award_amount, currency,
            contractor_name, contractor_national_id, contract_date,
            nb_tenders_received, nb_sme_tenders, nb_sme_contractors,
            all_contractors, all_cpvs,
            above_threshold, below_threshold,
            procurement_method, limited_tendering_reason,
            data_source, source_url,
            financier_type,
            created_at, updated_at
        ) VALUES (
            %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, %s, %s,
            %s, %s, %s,
            %s, %s,
            %s, %s,
            %s, %s,
            %s, %s,
            %s,
            CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
        )
    r      TYPE"OBJECT_CONTRACT_TYPE_CONTRACT_TYPEr   r   %OBJECT_CONTRACT_OBJECT_DESCR_DATE_END'OBJECT_CONTRACT_OBJECT_DESCR_DATE_START'COMPLEMENTARY_INFO_DATE_DISPATCH_NOTICEIMPORT_DATE)ADDITIONAL_CORE_DATA_DATETIME_LAST_CHANGE%OBJECT_CONTRACT_OBJECT_DESCR_DURATION*OBJECT_CONTRACT_OBJECT_DESCR_DURATION_TYPE!OBJECT_CONTRACT_OBJECT_DESCR_NUTS&OBJECT_CONTRACT_OBJECT_DESCR_MAIN_SITE!OBJECT_CONTRACT_CPV_MAIN_CPV_CODEr   r   0CONTRACTING_BODY_ADDRESS_CONTRACTING_BODY_E_MAIL1CONTRACTING_BODY_ADDRESS_CONTRACTING_BODY_CONTACT/CONTRACTING_BODY_ADDRESS_CONTRACTING_BODY_PHONEr!   4CONTRACTING_BODY_ADDRESS_CONTRACTING_BODY_NATIONALID0CONTRACTING_BODY_ADDRESS_CONTRACTING_BODY_DOMAINACONTRACTING_BODY_ADDRESS_CONTRACTING_BODY_ADDITIONAL_OFFICIALNAME<CONTRACTING_BODY_ADDRESS_CONTRACTING_BODY_ADDITIONAL_CONTACT?CONTRACTING_BODY_ADDRESS_CONTRACTING_BODY_ADDITIONAL_NATIONALID:CONTRACTING_BODY_ADDRESS_CONTRACTING_BODY_ADDITIONAL_PHONE;CONTRACTING_BODY_ADDRESS_CONTRACTING_BODY_ADDITIONAL_E_MAILr   "CONTRACTING_BODY_URL_PARTICIPATION)AWARD_CONTRACT_AWARDED_CONTRACT_VAL_TOTAL2AWARD_CONTRACT_AWARDED_CONTRACT_VAL_TOTAL_CURRENCY2   JAWARD_CONTRACT_AWARDED_CONTRACT_CONTRACTOR_ADDRESS_CONTRACTOR_OFFICIALNAMEHAWARD_CONTRACT_AWARDED_CONTRACT_CONTRACTOR_ADDRESS_CONTRACTOR_NATIONALID8AWARD_CONTRACT_AWARDED_CONTRACT_DATE_CONCLUSION_CONTRACT3AWARD_CONTRACT_AWARDED_CONTRACT_NB_TENDERS_RECEIVED-AWARD_CONTRACT_AWARDED_CONTRACT_NB_SME_TENDER1AWARD_CONTRACT_AWARDED_CONTRACT_NB_SME_CONTRACTORF)ensure_ascii#ADDITIONAL_CORE_DATA_ABOVETHRESHOLD
   #ADDITIONAL_CORE_DATA_BELOWTHRESHOLD$ADDITIONAL_CORE_DATA_D_JUSTIFICATIONDATA_SOURCE
SOURCE_URLr   )	rF   r   r;   r4   r2   rV   r|   dumpsrK   )r   	tender_idrG   all_contractorsall_cpvss        r   insert_tender_detailr      sD   gg/0Oggj)H
NN 4h2
2
	#''4
5s;2
 	
#''&/32
 	
#''6
7=	2

 	q!NOPQ2
 	q!EFGH2
 	1SWWDEFG2
 	1SWWFGHI2
 	1SWWFGHI2
 	q/012
 	q!LMNO2
 	
#''9
:C@2
 	
#''>
?E2
 	
#''5
6<2
 	
#'':
;<2
  	
#''5
6<!2
" 	c"#2
$ 	
#''/
01%2
& 	
#''J
KL'2
( 	
#''D
EsK)2
* 	
#''E
FL+2
, 	
#''C
DcJ-2
. 	
#''D
EsK/2
0 	
#''H
I3O12
2 	
#''D
EsK32
4 	
#''U
VW52
6 	
#''P
QR72
8 	
#''S
TVYZ92
: 	
#''N
OQTU;2
< 	
#''O
PRUV=2
> 	
#''1
23?2
@ 	
#''6
78A2
B 	
#''=
>DC2
D 	
#''F
GLE2
F 	
#''^
_`G2
H 	
#''\
]_bcI2
J 	1SWWWXYZK2
L 	EFN$M2
N 	?@HDO2
P 	CDLQ2
R <K

?7PTS2
T 5=

8%0$U2
V 	
#''7
8"=W2
X 	
#''7
8"=Y2
Z 	"#&[2
\ 	
#''8
9:]2
^ 	
#''-
 #&_2
` 	
#'',
 a2
b 	c2
ifr   c           
         SnSnSnSn [        SU SU S3SS9   [        X5      nUR                  S	0 5      nUR                  S	/ 5      nU(       d  [        S
5        GO/UR                  S0 5      R                  SS5      n	Sn
Sn[	        5       nUR                  5       nU HV  nUR                  S5      nUR                  S0 5      n[        UUX5      u  nnU(       d  US-  nMD  [        UUU5        U
S-  n
MX     UR                  5         UR                  5         UR                  5         X*-  nX;-  n[        S[        U5       SU
 SU SU	 35        [        U5      [        :  a  O'U[        -  nUS-  n[        R                  " S5        GM  [        SU SU 35        X#4$ ! [         a  n[        SU 35         S nAM2  S nAff = f)Nr   rY   z
    [Page z	] offset=z ... )endzAPI error: hitszNo more results.totalr0   ?_idrx   zGot z | New: z | Skipped: z
 | Total: z    => New: z, Skipped: )printr   r.   rF   r   r   r   r   commitcloser,   r~   timesleep)r   r   	total_newtotal_skippedpagedataer   results
total_hitspage_new	page_skipconnr   recordr   rG   r   is_news                      r   scrape_keywordr   >  s   KIMD

4&	+d;E	g3D
 ((62&((62&$%XXgr*..w<
	"$FJJu%EJJy"-C 4VS% QIvQ	 C8MH  	

!	"S\N(8*L:V`Uabcw<)#y 	

1_ b 
L;}o
>?##]  	Ks#$	s   F" "
G,G  Gc            
         [        S5        [        S5        [        S5        [        S[        [        5       35        [        S[        [        5       35        [        S[        [        5       35        Sn Sn[        [        S5       HR  u  p#[        SU S	[        [        5       S
U S35        [        U5      u  pEX-  n X-  n[        R                  " S5        MT     [        S5        [        S5        [        S5        [        S[        [        5       35        [        SU S 35        [        SUS 35        [        S5        g )NzF======================================================================zTENDERA.AT - Keyword ScraperzKEYWORDS       : zGERMAN_KEYWORDS: zTotal          : r   rY   z
[/z] Keyword: ""zG
======================================================================zScraping Complete!z  Keywords searched : z  New               : r   z  Skipped           : )	r   r,   r   r    ALL_KEYWORDS	enumerater   r   r   )	grand_newgrand_skippedrU   r   newskippeds         r   mainr   }  s   	(O	
()	(O	c(m_
-.	c/23
45	c,/0
12IMa0
A3aL)*-yCD%g.	 

1 1 
/	
	(O	"3|#4"5
67	"9Q-
01	"="3
45	(Or   __main__z

Interrupted.z
Fatal Error: r6   )3sysr   r|   r   r   r   r   platformstdoutreconfiguredotenvr   rd   rS   dirname__file__mysql.connectorr   r   r   splitr8   r   r    r   r   r   r~   r   r   CUTOFF_DAYSrh   r-   r   r2   r4   r;   rK   rV   r   r   r   r   r   __name__KeyboardInterruptr   r.   r   	traceback	print_exc)ks   0r   <module>r     s   
 	    (<<7JJG,  BGGLL2I> ?  ')ii
B&?&E&Ec&JX&Jggi91779&JX&(ii0A2&F&L&LS&Q_&QU\U\U^91779&Q__,;	 2>PQ299^R01!<<FFzR	, @N"<jb8$~6 z A Y_D  " ! s#$s6   2F<F<	G%G3G HH H  H