
    MiU                        S SK r S SKJr  S SKrS SKJr  S SKrS SKrS SK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S
rSrSSSS.rS rS"S jrS rS rS rS rS#S jr\S:X  ae  S SKr\R@                  " SS9r!\!RE                  S\#SSS9  \!RE                  S\#S SS9  \!RI                  5       r% \" \%RL                  \%RN                  S9  gg! \( a    \)" S 5         g\* a*  r+\)" S!\+ 35        S SK,r,\,RZ                  " 5          Sr+C+gSr+C+ff = f)$    N)BeautifulSoup)datetime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     \C:\Users\Yogesh Aggrawal\Documents\GitHub\tenders\canadabuys.canada.ca\canadabuys_details.pyget_db_connectionr      sa    ??""YYy!9d+,YYy!=)9% #  r   
canadabuyszhttps://canadabuys.canada.cazoMozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36zJtext/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8zen-US,en;q=0.9)z
User-AgentAcceptzAccept-Languagec                      U (       ag  U R                  5       (       aR  [        R                  " SU 5      nU(       a4  [        R                  " UR                  S5      S5      R                  5       $ g!    g= f)z Parse date string to DATE format(\d{4}/\d{2}/\d{2})   z%Y/%m/%dN)stripresearchr   strptimegroupdate)date_str
date_matchs     r   
parse_dater*   %   sc    ((#98DJ(()9)9!)<jINNPP s   A-A1 1A5c                 >    U(       aP  U R                  U5      nU(       a6  U(       a   UR                  US5      R                  5       $ UR                  SS9$  gU (       a6  U(       a   U R                  US5      R                  5       $ U R                  SS9$  g!    g= f)zSafely get text from element Tr"   N)
select_onegetr"   get_text)elementselector	attributeelems       r   get_text_safer5   0   s    %%h/D88Ir288::}}4}00   {{9b17799##$#//  s   AB B -B B Bc                 h     U R                  S5      nU(       a  UR                  S5      $  g!    g= f)zExtract node ID from pagez[data-history-node-id]zdata-history-node-idN)r.   r/   )soupr4   s     r   extract_node_idr8   A   s@    7888233  s   (- 1c                 D   [        U S5      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SSSSSSSSSS/ S.En[        U5      US'   UR                  S5      nU(       a  UR                  SS9US'   UR                  S5      nU(       a  UR                  SS9US'   UR                  S5      nU(       a  [	        UR                  SS5      5      US'   UR                  S5      nU(       a  UR                  SS9US'   UR                  S5      nU(       aO  UR                  SSS9nXS	'   [        R                  " S U5      n	U	(       a  [	        U	R                  S!5      5      US
'   UR                  S"5      n
U
(       a!  U
R                  SS9nXS'   US	   (       d  XS	'   UR                  S#5      nU(       a  UR                  SS9US'   UR                  S$5      nU(       a  UR                  SS9US'   UR                  S%5      nU(       aU  UR                  SS9n[        R                  " S&U5      nU(       a(  UR                  S!5      US'   UR                  S'5      US'   / nUR                  S(5      nU H,  nUR                  SS9nU(       d  M  UR                  U5        M.     U(       a  S)R                  U5      US'   UR                  S*5      n/ nU H,  nUR                  SS9nU(       d  M  UR                  U5        M.     U(       a  S)R                  U5      US'   UR                  S+5      nU(       a  UR                  SS9US'   UR                  S,5      nU(       a  UR                  SS9US'   UR                  S-5      nU(       a  UR                  SS9nUR                  S.5       Vs/ s H  nUR                  5       PM     nn[        U5      S!:  a  US/   US'   [        U5      S':  a  US!   US0'   [        U5      S1:  a  US'   US2'   UR                  S35      nU(       a  UR                  SS9US4'   UR                  S55      nU(       a  / nUR                  S,5      nU(       a  UR                  UR                  SS95        UR                  S-5      nU(       a  UR                  UR                  SS95        UR                  S35      n U (       a  UR                  U R                  SS95        U(       a  S6R                  U5      US7'   UR                  S85      n!U!(       a  U!R                  SS9US9'   UR                  S:5      n"U"(       a  U"R                  SS9US;'   UR                  S<5      n#U#(       a  U#R                  SS9US='   UR                  S>5      n$U$(       a=  U$R                  S?5      n%U%(       a  U%R                  SS9US@'   OU$R                  SS9US@'   UR                  SA5      n&U&(       a  U&R                  SS9USB'   UR                  SC5      n'U'(       a  U'R                  SS9USD'   UR                  SE5      n(U((       a=  U(R                  S?5      n)U)(       a  U)R                  SS9USF'   OU(R                  SS9USF'   UR                  SG5      n*U* GH  n+ 0 n,U+R                  SH5      n-U-(       aY  U-R                  SS9U,SI'   U-R                  SJS5      U,SK'   U,SK   (       a(  U,SK   R!                  SL5      (       d  ["        U,SK   -   U,SK'   U+R                  SM5      n.U.(       a  U.R                  SS9U,SN'   U+R                  SO5      n/U/(       ab  / n0U/R                  SP5       H!  n-U0R                  U-R                  SS95        M#     U0(       a  SQR                  U05      OU/R                  SS9U,SR'   U+R                  SS5      n1U1(       a   [%        U1R                  SS95      U,ST'   U+R                  SU5      n2U2(       a  [	        U2R                  SS95      U,SV'   U,R                  SI5      (       a  USW   R                  U,5        GM  GM     U$ s  snf !   S/U,ST'    Nu= f! [&         a  n3[)        SXU3 35         Sn3A3GM  Sn3A3ff = f)Yz-Extract all detail data from tender page HTMLzhtml.parsernode_idNsolicitation_numberclosing_timeamendment_datedescriptioncontract_durationcontract_duration_fullcontract_start_datenotice_typeprocurement_methodunspsc_codeunspsc_descriptiontrade_agreementslimited_tendering_reasonorganizationcontracting_org_addresscontracting_org_city)contracting_org_provincecontracting_org_postalcontracting_org_countrycontracting_authority_namecontracting_authority_phonecontracting_authority_emailregions_of_delivery	languagesregion_of_opportunityselection_criteriacontract_authority_address	documentsz:.field--name-field-tender-solicitation-number .field--itemTr-   z.closing-date-field .timeclassz-.field--name-field-tender-amendment-date timer   r,   z+.field--name-body.tender-detail-descriptionz'.field--name-field-tender-contract-date )	separatorr"   r    r!   z:.views-field-field-tender-contract-duration .field-contentzA.views-field-field-tender-notice-type .field-content .field--itemzH.views-field-field-tender-procurement-method .field-content .field--itemz1.views-field-field-tender-unspsc .unspsc_link_clrz(\d+)\s+(.+)   zD.field--name-field-tender-trade-agreements .field--item .field--itemz | z7.field--name-field-tender-ltr .field--item .field--itemz).field--name-field-tender-contact-orgnamez(.field--name-field-tender-contact-a-linez(.field--name-field-tender-contact-a-city,r   rK      rL   z+.field--name-field-tender-contact-a-countryrM   zdl.tender-contractingz, rU   z:.field--name-field-tender-contact-contactname .field--itemrN   z<.field--name-field-tender-contact-phone .phone-number-formatrO   z4.field--name-field-tender-contact-email .field--itemrP   z9.views-field-field-tender-delivery-regions .field-contentz.field--itemrQ   z9.views-field-field-tender-notice-languages .field-contentrR   z<.views-field-field-tender-opportunity-regions .field-contentrS   z;.views-field-field-tender-selection-criteria .field-contentrT   z .tender-documents-table tbody trz.field-document_link atitlehrefurlhttpz.field-amendment_numberamendment_numberz.field-languageaz and languagez.field-download_countdownload_countz.field-date_added
date_addedrV   z)    Warning: Error parsing document row: )r   r8   r.   r0   r*   r/   r#   r$   r&   matchselectappendjoinsplitr"   len
startswithBASE_URLr   	Exceptionprint)4htmlr7   datasol_num	time_span
amend_datedescduration	full_textstart_date_matchduration_summaryduration_textrB   proc_methodunspsc_linkre   rF   trade_itemsitemtext	ltr_itemsltr_reasonsorg_nameaddress_line	city_elem	city_textppartscountrycontracting_blockaddr2city2country2	auth_name
auth_phone
auth_emailregions_elemregion_item	lang_elemopp_region_elemselection_elemselection_itemdoc_rowsrowdoclinkamend	lang_celllangsdl_count	date_elemes4                                                       r   extract_detail_datar   K   s>	   }-D4t 	 	$	
 	t 	T 	!$ 	t 	t 	d 	t 	d 	D 	#D 	  	"4!" 	#$ %)"&#'&*'+'+#!%"&*;DB &d+DO ooZ[G&-&6&6T&6&B"#  @AI(111=^ !PQJ!+JNN:r,J!K ??HID"mm$m7] HIH%%4%@	)2%& 99%;YG*45E5K5KA5N*OD&' 'cd(111=$1 !,--:)* //"efK)222>] //"lmK%0%9%9%9%E!" //"UVK((t(4	)4"'++a.D).QD%& ++deK}}4}(4##D)  #(::.>#?  UVIK}}4}(4t$  +0::k+B'( JKH'00t0<^ ??#MNL*6*?*?d*?*K&'  JKI&&T&2	$-OOC$89$8q$89u:?+08D'(u:?/4QxD+,u:?-21XD)* ooKLG*1*:*:*:*F&' (?@!,,-WXLLd34!,,-WXLLd34$//0]^LL***671551AD-.  \]I-6-?-?d-?-K)*!_`J.8.A.A.A.M*+!WXJ.8.A.A.A.M*+ ??#^_L"--n=*5*>*>T*>*JD&'*6*?*?d*?*KD&'  [\I%..T.:[ oo&deO(7(@(@t(@(L$% __%bcN'22>B)7)@)@t)@)LD%&)7)@)@t)@)LD%& {{=>H)	C >>":;D#}}4}8G!XXfb1E
u:c%j&;&;F&C&C!)CJ!6CJ NN#<=E*/..t.*D&' '89I%,,S1DLLT!:; 29>',,u"5IDVDV]aDVDbJ ~~&=>H.,/0A0A0A0M,NC()
 ':;I$.y/A/A/A/M$NL!www[!((-  M X Km :P.,-C()  	=aSAB	s8   a*8D/a;(a/Aa;/a86a;;
bbbc                 .    U R                  S5      (       d	  [        U -   OU n[        R                  " U[        SS9nUR
                  S:X  a  UR                  $ [        SUR
                   35        g! [         a  n[        SU 35         SnAgSnAff = f)zFetch tender detail pager_      )headerstimeout   u       ✗ HTTP Nu       ✗ Request error: )	rk   rl   requestsr/   HEADERSstatus_coder~   rn   rm   )r^   full_urlresponser   s       r   fetch_tender_detailr   J  s    
),)?)?8c>S<<'2F3&== M("6"6!789 's+,s   AA2 A2 2
B<BBc                     [        5       nUR                  5       nUR                  SUS   US   U 45        UR                  SU 45        UR                  5       n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   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nU(       a  UR                  SXP4-   5        OUR                  S U 4U-   5        S!nUS"    H  nUR                  S#XR	                  S$5      45        UR                  5       (       a  M<  UR                  S%XR	                  S&5      UR	                  S$5      UR	                  S'5      UR	                  S(5      UR	                  S)S!5      UR	                  S*5      45        US+-  nM     UR                  5         UR                  5         UR                  5         S,U4$ ! [         a  n[        S-U 35         S.nAg/S.nAff = f)0zSave detail data to databaseziUPDATE tenders SET description=%s, organization=%s, detail=1, updated_at=NOW()
               WHERE id=%sr>   rH   z8SELECT id FROM tender_details WHERE tender_id=%s LIMIT 1r;   r<   r=   r?   r@   rA   rB   rC   rD   rE   rF   rG   rI   rJ   rK   rL   rM   rN   rO   rP   rQ   rR   rS   rT   r:   rU   a  UPDATE tender_details SET
                   solicitation_number=%s, closing_time=%s, amendment_date=%s,
                   contract_duration=%s, contract_duration_full=%s, contract_start_date=%s,
                   notice_type=%s, procurement_method=%s,
                   unspsc_code=%s, unspsc_description=%s,
                   trade_agreements=%s, limited_tendering_reason=%s,
                   contracting_org_address=%s, contracting_org_city=%s,
                   contracting_org_province=%s, contracting_org_postal=%s,
                   contracting_org_country=%s,
                   contracting_authority_name=%s, contact_phone=%s,
                   contracting_authority_email=%s,
                   regions_of_delivery=%s, languages=%s,
                   region_of_opportunity=%s, selection_criteria=%s,
                   node_id=%s, contract_authority_address=%s, updated_at=NOW()
                   WHERE tender_id=%sa  INSERT INTO tender_details
                   (tender_id, solicitation_number, closing_time, amendment_date,
                    contract_duration, contract_duration_full, contract_start_date,
                    notice_type, procurement_method,
                    unspsc_code, unspsc_description,
                    trade_agreements, limited_tendering_reason,
                    contracting_org_address, contracting_org_city,
                    contracting_org_province, contracting_org_postal, contracting_org_country,
                    contracting_authority_name, contact_phone, contracting_authority_email,
                    regions_of_delivery, languages,
                    region_of_opportunity, selection_criteria, node_id, contract_authority_address,
                    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,NOW(),NOW())r   rV   zNSELECT id FROM tender_documents WHERE tender_id=%s AND document_url=%s LIMIT 1r^   zINSERT INTO tender_documents
                   (tender_id, document_name, document_url,
                    amendment_number, language, download_count, date_added)
                   VALUES (%s,%s,%s,%s,%s,%s,%s)r\   r`   rb   rc   rd   r!   Tu       ✗ Database error: N)Fr   )	r   cursorexecutefetchoner/   commitcloserm   rn   )		tender_iddetail_dataconnr   det_rowfields	doc_countr   r   s	            r   save_tender_detailsr   X  s   d " 	'^)DiP	
 	FL	
 //# -.N0K();7J+K01;?T3U&4H(I&4H(I*+[9S-T12K@V4W23[AY5Z1245{C`7a56-.K0H/0+>R2S	"K0L$M
" NN) %!& NN| v%$ 	{+CNN`GGEN+   NN4 GGG,cggen+,cggj.A)1-sww|/DF NI! ,$ 	

Y (,-s   G'G* *
H4HHc                 ^   [        S5        [        S5        [        S5        [        5       nUR                  SS9nUR                  S[        45        UR                  5       S   n[        SUS 35        S	n[        /nUS
:  a  US-  nUR                  U5        US-  nU (       a  US-  nUR                  U 5        UR                  XV5        UR                  5       nUR                  5         UR                  5         [        S[        U5       S35        [        S5        S
nS
n	S
n
[        US5       GH  u  pUS   nUS   nUS   n[        SU S[        U5       SU 35        [        SUSS  S35        [        SU 35        [        U5      nU(       d  U	S-  n	[        R                  " S5        M   [        U5      nUR                  S5      =(       d    SnUR                  S5      =(       d    SnUR                  S5      =(       d    SnUR                  S 5      =(       d    SnUR                  S!5      =(       d    Sn[        S"U S#U S$U S%U S&U 3
5        [        S'[        US(   5       S)35        [!        UU5      u  nnU(       a  US-  nU
U-  n
[        S*U S)35        OU	S-  n	 US,-  S
:X  aH  [        S-S. 35        [        S/U S[        U5       S0U S1U	 35        [        S2U
 35        [        S. 5        [        R                  " S35        GM     [        S45        [        S55        [        S5        [        S6[        U5       35        [        S7U 35        [        S8U	 35        [        S9U
 35        [        S5        g! ["         a  n[        S+U 35        U	S-  n	 SnANSnAff = f):z&Main function to scrape tender detailszP================================================================================z#Canada Buys - Tender Detail ScraperT)
dictionaryzoSELECT COUNT(*) as total FROM tenders WHERE source=%s AND detail=0 AND url LIKE '%%/en/tender-opportunities/%%'totalzTotal records to process: rZ   zSELECT id, source_id AS tender_id, url, title FROM tenders WHERE source=%s AND detail=0 AND url LIKE '%/en/tender-opportunities/%'r   z AND id > %sz ORDER BY idz	 LIMIT %szProcessing z records...r!   idr^   r\   z
[/z] z	  Title: N<   z...z  URL: rH   r,   rN   rO   rP   rI   z  Org: z	 | Auth: z
 | Phone: z
 | Email: z	 | Addr: u     ✓ Parsed: rV   z
 documentsu     ✓ Saved: u     ✗ Parse error: 
   
u   ────────────────────────────────────────────────────────────────────────────────z
Progress: z | Success: z | Errors: zTotal documents: g      ?zQ
================================================================================zScraping Complete!z  Total processed: z  Successful: z
  Errors: z  Documents saved: )rn   r   r   r   SOURCEr   rg   fetchallr   rj   	enumerater   timesleepr   r/   r   rm   )
batch_size
start_fromr   r   r   queryparamsrecordssuccess_counterror_count
total_docsirecordr   r^   r\   ro   r   orgauthphoneemailaddrsavedr   r   s                             r   scrape_tender_detailsr     s   	(O	
/0	(OD[[D[)F
NNy		 OOg&E	&uQi
01 QEXFA~j!	^Ej!
NN5!ooG
LLNJJL	KG~[
12	(OMKJw*	4L	UmwA3aG~R	{34	%*S)*uo #3'1KJJqM	-d3K//.17RC??#?@FBDOO$ABHbEOO$ABHbE??#<=CDGC5	$z%
5'QZ[_Z`abN3{;'?#@"ALM  39kJE9"i'
i[
;<q  r6Q;Bzl#$Jqc3w<.]O;WbVcde%j\23ZL" 	

3a +f 
/	
	(O	G~
./	N=/
*+	J{m
$%	
|
,-	(O-  	's+,1K	s   3C1N%N
N,N''N,__main__z!Scrape Canada Buys tender details)r>   z--batch-sizezNumber of records to process)typedefaulthelpz--start-fromzStart from record ID)r   r   u   

✗ Interrupted by useru   
✗ Fatal Error: )NN)Nr   ).r   bs4r   r#   r   r   sysr   platformstdoutreconfiguredotenvr   pathrh   dirname__file__mysql.connectorr   r   r   rl   r   r*   r5   r8   r   r   r   r   __name__argparseArgumentParserparseradd_argumentr   
parse_argsargsr   r   KeyboardInterruptrn   rm   r   	traceback	print_excr   r   r   <module>r      ss     	   
 	 <<7JJG,  BGGLL2I> ?  
 * DZ'	"}~fP`D z$$1TUF
S$Ecd
S!BXYDT__U   -+, #A3'(s   D! !E 1E 6 EE 