
    0iP                        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  \R                  R                  S \R                  R                  \R                  R                  \5      S5      5        S SKJ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 \ RC                  S\"SSS9  \ RC                  S\"S SS9  \ RG                  5       r$ \" \$RJ                  \$RL                  S9  gg! \' a    \(" S5         g\) a*  r*\(" S \* 35        S SK+r+\+RX                  " 5          Sr*C*gSr*C*ff = f)#    N)BeautifulSoup)datetimewin32zutf-8)encodingz..)get_db_connection_new
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     _C:\Users\Yogesh Aggrawal\Documents\GitHub\tenders\canadabuys.canada.ca\scrape_tender_details.py
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_safer!   &   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   )soupr    s     r   extract_node_idr$   7   s@    7888233  s   (- 1c                    [        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.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  UR                  SS9US6'   UR                  S75      nU(       a  UR                  SS9US8'   UR                  S95      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                  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SA'   UR                  SB5      n$U$(       a=  U$R                  S<5      n%U%(       a  U%R                  SS9USC'   OU$R                  SS9USC'   UR                  SD5      n&U& GH  n' 0 n(U'R                  SE5      n)U)(       aY  U)R                  SS9U(SF'   U)R                  SGS5      U(SH'   U(SH   (       a(  U(SH   R!                  SI5      (       d  ["        U(SH   -   U(SH'   U'R                  SJ5      n*U*(       a  U*R                  SS9U(SK'   U'R                  SL5      n+U+(       ab  / n,U+R                  SM5       H!  n)U,R                  U)R                  SS95        M#     U,(       a  SNR                  U,5      OU+R                  SS9U(SO'   U'R                  SP5      n-U-(       a   [%        U-R                  SS95      U(SQ'   U'R                  SR5      n.U.(       a  [	        U.R                  SS95      U(SS'   U(R                  SF5      (       a  UST   R                  U(5        GM  GM     U$ s  snf !   S/U(SQ'    Nu= f! [&         a  n/[)        SUU/ 35         Sn/A/GM  Sn/A/ff = f)Vz-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	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   r7      r8   z+.field--name-field-tender-contact-a-countryr9   z:.field--name-field-tender-contact-contactname .field--itemr:   z<.field--name-field-tender-contact-phone .phone-number-formatr;   z4.field--name-field-tender-contact-email .field--itemr<   z9.views-field-field-tender-delivery-regions .field-contentz.field--itemr=   z9.views-field-field-tender-notice-languages .field-contentr>   z<.views-field-field-tender-opportunity-regions .field-contentr?   z;.views-field-field-tender-selection-criteria .field-contentr@   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_addedrA   z)    Warning: Error parsing document row: )r   r$   r   r   r   r   r   r   r   matchselectappendjoinsplitr   len
startswithBASE_URLint	Exceptionprint)0htmlr#   datasol_num	time_span
amend_datedescduration	full_textstart_date_matchduration_summaryduration_textr.   proc_methodunspsc_linkrP   r2   trade_itemsitemtext	ltr_itemsltr_reasonsorg_nameaddress_line	city_elem	city_textppartscountry	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es0                                                   r   extract_detail_datar   A   s   }-D4t 	 	$	
 	t 	T 	!$ 	t 	t 	d 	t 	d 	D 	#D 	  	"4!" 	#$ %)"&#'&*'+'+#!%"9D@ &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&'  \]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   ^ D/^#^+A^#^ ^##
_-__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 pagerJ      )headerstimeout   u       ✗ HTTP Nu       ✗ Request error: )	rV   rW   requestsr   HEADERSstatus_coderj   rZ   rY   )rI   full_urlresponser   s       r   fetch_tender_detailr   /  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   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)/zSave detail data to databaseziUPDATE tenders SET description=%s, organization=%s, detail=1, updated_at=NOW()
               WHERE id=%sr*   r4   z8SELECT id FROM tender_details WHERE tender_id=%s LIMIT 1r'   r(   r)   r+   r,   r-   r.   r/   r0   r1   r2   r3   r5   r6   r7   r8   r9   r:   r;   r<   r=   r>   r?   r@   r&   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, 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,
                    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,NOW(),NOW())r   rA   zNSELECT id FROM tender_documents WHERE tender_id=%s AND document_url=%s LIMIT 1rI   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)rG   rK   rM   rN   rO   r   Tu       ✗ Database error: N)Fr   )	get_db_connectioncursorexecutefetchoner   commitcloserY   rZ   )		tender_iddetail_dataconnr   det_rowfields	doc_countr   r   s	            r   save_tender_detailsr   =  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	"
" NN) %!& NNy v%$ 	{+CNN`GGEN+   NN4 GGG,cggen+,cggj.A)1-sww|/DF NI! ,$ 	

Y (,-s   G#G& &
H0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: rE   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   idrI   rG   z
[/z] z	  Title: N<   z...z  URL: r4   r   r:   r;   r<   r5   z  Org: z	 | Auth: z
 | Phone: z
 | Email: z	 | Addr: u     ✓ Parsed: rA   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: )rZ   r   r   r   SOURCEr   rR   fetchallr   rU   	enumerater   timesleepr   r   r   rY   )
batch_size
start_fromr   r   r   queryparamsrecordssuccess_counterror_count
total_docsirecordr   rI   rG   r[   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   sysosplatformstdoutreconfigurepathinsertrS   dirname__file__	db_configr   r   r   rW   r   r   r!   r$   r   r   r   r   __name__argparseArgumentParserparseradd_argumentrX   
parse_argsargsr   r   KeyboardInterruptrZ   rY   r   	traceback	print_exc     r   <module>r      ss     	   
 	 <<7JJG, 277<< 94@ A @	 * DZ'	"l\fP`D z$$1TUF
S$Ecd
S!BXYDT__U   -+, #A3'(s   D. .E->E- E((E-