import sys import os import json import requests import time from datetime import datetime if sys.platform == 'win32': sys.stdout.reconfigure(encoding='utf-8') from dotenv import load_dotenv load_dotenv(os.path.join(os.path.dirname(__file__), '../.env')) import mysql.connector sys.path.insert(0, os.path.join(os.path.dirname(__file__), '..')) from keyword_tracker import is_keyword_done, mark_keyword_done def get_db_connection(): return mysql.connector.connect( host=os.getenv('DB_HOST'), port=int(os.getenv('DB_PORT', 3306)), user=os.getenv('DB_USER'), password=os.getenv('DB_PASSWORD'), database=os.getenv('DB_NAME'), ) # KEYWORDS = [k.strip() for k in os.getenv('KEYWORDS', '').split(',') if k.strip()] # GERMAN_KEYWORDS = [k.strip() for k in os.getenv('GERMAN_KEYWORDS', '').split(',') if k.strip()] # ALL_KEYWORDS = KEYWORDS + GERMAN_KEYWORDS def load_keywords_from_db(): """Fetch keywords from managed_keywords table for 'en' and 'de' languages.""" result = {'en': [], 'de': []} try: conn = get_db_connection() cursor = conn.cursor() cursor.execute( "SELECT language_code, keyword FROM managed_keywords WHERE language_code IN ('en', 'de')" ) for lang_code, keyword_cell in cursor.fetchall(): for kw in keyword_cell.split(','): kw = kw.strip() if kw: result[lang_code].append(kw) cursor.close() conn.close() except Exception as e: print(f" Warning: Could not load keywords from DB: {e}") return result _kw_by_lang = load_keywords_from_db() KEYWORDS = _kw_by_lang['en'] GERMAN_KEYWORDS = _kw_by_lang['de'] ALL_KEYWORDS = KEYWORDS + GERMAN_KEYWORDS print(f"EN keywords ({len(KEYWORDS)}): {KEYWORDS}") print(f"DE keywords ({len(GERMAN_KEYWORDS)}): {GERMAN_KEYWORDS}") print(f"Total keywords : {len(ALL_KEYWORDS)}") # exit() SOURCE = 'tendera.at' API_URL = 'https://www.tendera.at/api/tenders/_search' PAGE_SIZE = 100 HEADERS = {'Content-Type': 'application/json', 'Accept': 'application/json'} MAX_PAGES = int(os.getenv('TENDERA_MAX_PAGES', 3)) # --------------------------------------------------------------------------- # Helpers # --------------------------------------------------------------------------- def parse_date(value): if not value: return None for fmt in ('%Y-%m-%dT%H:%M:%S', '%Y-%m-%d %H:%M:%S', '%Y-%m-%d', '%Y-%m-%dT%H:%M:%S%z'): try: return datetime.strptime(value[:19], fmt[:len(value[:19])]).strftime('%Y-%m-%d') except Exception: continue return None def parse_datetime(value): if not value: return None for fmt in ('%Y-%m-%dT%H:%M:%S', '%Y-%m-%d %H:%M:%S', '%Y-%m-%dT%H:%M', '%Y-%m-%d'): try: return datetime.strptime(value[:19], fmt).strftime('%Y-%m-%d %H:%M:%S') except Exception: continue return None def s(value, maxlen=None): if value is None: return None v = str(value).strip() if not v: return None if maxlen: v = v[:maxlen] return v def derive_procurement_method(src): mapping = [ ('PROCEDURE_PT_OPEN', 'Open'), ('PROCEDURE_PT_RESTRICTED', 'Restricted'), ('PROCEDURE_PT_DIRECT', 'Direct'), ('PROCEDURE_PT_WITH_PRIOR_NOTICE', 'With Prior Notice'), ('PROCEDURE_PT_WITHOUT_PRIOR_NOTICE', 'Without Prior Notice'), ('PROCEDURE_PT_COMPETITIVE_NEGOTIATION', 'Competitive Negotiation'), ] for field, label in mapping: if src.get(field) not in (None, '', []): return label return None def derive_cpv_additional(src): cpvs = [] for i in range(5): v = src.get(f'OBJECT_CONTRACT_OBJECT_DESCR_CPV_ADDITIONAL_{i}_CPV_CODE') if v: cpvs.append(str(v)) v = src.get('OBJECT_CONTRACT_OBJECT_DESCR_CPV_ADDITIONAL_CPV_CODE') if v and str(v) not in cpvs: cpvs.append(str(v)) return ','.join(cpvs) if cpvs else None NUTS_COUNTRY_MAP = { 'AT': 'Austria', 'BE': 'Belgium', 'BG': 'Bulgaria', 'CY': 'Cyprus', 'CZ': 'Czech Republic', 'DE': 'Germany', 'DK': 'Denmark', 'EE': 'Estonia', 'EL': 'Greece', 'ES': 'Spain', 'FI': 'Finland', 'FR': 'France', 'HR': 'Croatia', 'HU': 'Hungary', 'IE': 'Ireland', 'IT': 'Italy', 'LT': 'Lithuania', 'LU': 'Luxembourg', 'LV': 'Latvia', 'MT': 'Malta', 'NL': 'Netherlands', 'PL': 'Poland', 'PT': 'Portugal', 'RO': 'Romania', 'SE': 'Sweden', 'SI': 'Slovenia', 'SK': 'Slovakia', 'NO': 'Norway', 'CH': 'Switzerland', 'UK': 'United Kingdom', } def derive_country_from_nuts(nuts): if not nuts: return None return NUTS_COUNTRY_MAP.get(str(nuts).strip()[:2].upper()) def extract_timezone(value): """Extract timezone offset string (e.g. '+0200') from a datetime string.""" import re if not value: return None m = re.search(r'([+-]\d{2}:?\d{2})$', str(value)) return m.group(1) if m else None # --------------------------------------------------------------------------- # API fetch # --------------------------------------------------------------------------- def fetch_page(keyword, from_offset): payload = { 'size': PAGE_SIZE, 'from': from_offset, 'query': { 'bool': { 'must': [], 'filter': [ { 'bool': { 'minimum_should_match': 1, 'should': [ { 'query_string': { 'query': keyword, 'default_field': '*', 'allow_leading_wildcard': False, 'default_operator': 'AND', } }, { 'nested': { 'path': 'ALL_CONTRACTORS', 'query': { 'bool': { 'must': [ { 'query_string': { 'query': keyword, 'default_field': '*', 'allow_leading_wildcard': False, 'default_operator': 'AND', } } ] } } } } ] } }, ] } }, 'sort': [{'SORTING_DATE': {'order': 'desc'}}], 'stored_fields': ['*'], '_source': {'excludes': []}, 'docvalue_fields': [{'field': 'SORTING_DATE', 'format': 'date_time'}], 'aggs': { 'costs_per_cpv': {'nested': {'path': 'ALL_CPVS'}}, 'biggest_bodies': {'nested': {'path': 'ALL_CONTRACTORS'}}, }, } resp = requests.post(API_URL, headers=HEADERS, json=payload, timeout=30) resp.raise_for_status() return resp.json() # --------------------------------------------------------------------------- # DB insert # --------------------------------------------------------------------------- AWARD_TYPES = {'8_2_Z1', '8_2_Z3', '8_1_Z4', '8_1_Z5', '7_2_Z1'} def derive_status(notice_type, closing_date): """Return (status, status_text) based on notice type and deadline.""" if notice_type in AWARD_TYPES: return 'Awarded', 'Award Notice' if closing_date: deadline_dt = datetime.strptime(closing_date, '%Y-%m-%d') if deadline_dt >= datetime.now(): return 'Open', 'Open for Submissions' return 'Closed', 'Deadline Passed' return 'Closed', 'Deadline Passed' def get_or_insert_tender(cursor, src, es_id, keyword): cursor.execute( "SELECT id FROM tenders WHERE source = %s AND source_id = %s", (SOURCE, es_id) ) row = cursor.fetchone() if row: return row[0], False title = s(src.get('OBJECT_CONTRACT_TITLE')) ref = s(src.get('OBJECT_CONTRACT_REFERENCE_NUMBER')) description = s(src.get('OBJECT_CONTRACT_SHORT_DESCR')) organization = s(src.get('CONTRACTING_BODY_ADDRESS_CONTRACTING_BODY_OFFICIALNAME')) url = f'https://www.tendera.at/details/{es_id}' pub_date = parse_datetime(s(src.get('ADDITIONAL_CORE_DATA_DATE_FIRST_PUBLICATION'))) closing_date = parse_date(s(src.get('PROCEDURE_DATETIME_RECEIPT_TENDERS'))) notice_type = s(src.get('TYPE')) status, status_text = derive_status(notice_type, closing_date) cursor.execute(""" INSERT INTO tenders (source, source_id, title, reference_number, description, organization, url, publication_type, closing_date, keyword, status, status_text, detail, created_at, updated_at) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP) """, (SOURCE, es_id, title, ref, description, organization, url, pub_date, closing_date, keyword, status, status_text)) return cursor.lastrowid, True def insert_tender_detail(cursor, tender_id, src): all_contractors = src.get('ALL_CONTRACTORS') all_cpvs = src.get('ALL_CPVS') cursor.execute(""" 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, deadline_timezone, contract_duration, contract_duration_full, nuts_place, place_of_performance, contracting_org_country, 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, budget, 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, languages, 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, %s, %s, %s, %s, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP ) """, ( tender_id, s(src.get('OBJECT_CONTRACT_REFERENCE_NUMBER'), 255), s(src.get('TYPE'), 255), s(src.get('OBJECT_CONTRACT_TYPE_CONTRACT_TYPE'), 255), parse_datetime(s(src.get('ADDITIONAL_CORE_DATA_DATE_FIRST_PUBLICATION'))), parse_datetime(s(src.get('PROCEDURE_DATETIME_RECEIPT_TENDERS'))), parse_date(s(src.get('OBJECT_CONTRACT_OBJECT_DESCR_DATE_END'))), parse_date(s(src.get('OBJECT_CONTRACT_OBJECT_DESCR_DATE_START'))), parse_date(s(src.get('COMPLEMENTARY_INFO_DATE_DISPATCH_NOTICE'))), parse_datetime(s(src.get('IMPORT_DATE'))), parse_datetime(s(src.get('ADDITIONAL_CORE_DATA_DATETIME_LAST_CHANGE'))), extract_timezone(src.get('IMPORT_DATE')), s(src.get('OBJECT_CONTRACT_OBJECT_DESCR_DURATION'), 255), s(src.get('OBJECT_CONTRACT_OBJECT_DESCR_DURATION_TYPE'), 255), s(src.get('OBJECT_CONTRACT_OBJECT_DESCR_NUTS'), 255), s(src.get('OBJECT_CONTRACT_OBJECT_DESCR_MAIN_SITE')), derive_country_from_nuts(src.get('OBJECT_CONTRACT_OBJECT_DESCR_NUTS')) or 'Austria', s(src.get('OBJECT_CONTRACT_CPV_MAIN_CPV_CODE'), 255), derive_cpv_additional(src), s(src.get('OBJECT_CONTRACT_SHORT_DESCR')), s(src.get('CONTRACTING_BODY_ADDRESS_CONTRACTING_BODY_OFFICIALNAME')), s(src.get('CONTRACTING_BODY_ADDRESS_CONTRACTING_BODY_E_MAIL'), 255), s(src.get('CONTRACTING_BODY_ADDRESS_CONTRACTING_BODY_CONTACT'), 255), s(src.get('CONTRACTING_BODY_ADDRESS_CONTRACTING_BODY_PHONE'), 100), s(src.get('CONTRACTING_BODY_ADDRESS_CONTRACTING_BODY_E_MAIL'), 255), s(src.get('CONTRACTING_BODY_ADDRESS_CONTRACTING_BODY_NATIONALID'), 100), s(src.get('CONTRACTING_BODY_ADDRESS_CONTRACTING_BODY_DOMAIN'), 100), s(src.get('CONTRACTING_BODY_ADDRESS_CONTRACTING_BODY_ADDITIONAL_OFFICIALNAME')), s(src.get('CONTRACTING_BODY_ADDRESS_CONTRACTING_BODY_ADDITIONAL_CONTACT')), s(src.get('CONTRACTING_BODY_ADDRESS_CONTRACTING_BODY_ADDITIONAL_NATIONALID'), 100), s(src.get('CONTRACTING_BODY_ADDRESS_CONTRACTING_BODY_ADDITIONAL_PHONE'), 100), s(src.get('CONTRACTING_BODY_ADDRESS_CONTRACTING_BODY_ADDITIONAL_E_MAIL'), 255), s(src.get('CONTRACTING_BODY_URL_DOCUMENT')), s(src.get('CONTRACTING_BODY_URL_PARTICIPATION')), s(src.get('AWARD_CONTRACT_AWARDED_CONTRACT_VAL_TOTAL'), 255), s(src.get('AWARD_CONTRACT_AWARDED_CONTRACT_VAL_TOTAL'), 255), s(src.get('AWARD_CONTRACT_AWARDED_CONTRACT_VAL_TOTAL_CURRENCY'), 50), s(src.get('AWARD_CONTRACT_AWARDED_CONTRACT_CONTRACTOR_ADDRESS_CONTRACTOR_OFFICIALNAME')), s(src.get('AWARD_CONTRACT_AWARDED_CONTRACT_CONTRACTOR_ADDRESS_CONTRACTOR_NATIONALID'), 100), parse_date(s(src.get('AWARD_CONTRACT_AWARDED_CONTRACT_DATE_CONCLUSION_CONTRACT'))), src.get('AWARD_CONTRACT_AWARDED_CONTRACT_NB_TENDERS_RECEIVED') or None, src.get('AWARD_CONTRACT_AWARDED_CONTRACT_NB_SME_TENDER') or None, src.get('AWARD_CONTRACT_AWARDED_CONTRACT_NB_SME_CONTRACTOR') or None, json.dumps(all_contractors, ensure_ascii=False) if all_contractors else None, json.dumps(all_cpvs, ensure_ascii=False) if all_cpvs else None, s(src.get('ADDITIONAL_CORE_DATA_ABOVETHRESHOLD'), 10), s(src.get('ADDITIONAL_CORE_DATA_BELOWTHRESHOLD'), 10), derive_procurement_method(src), s(src.get('ADDITIONAL_CORE_DATA_D_JUSTIFICATION')), s(src.get('DATA_SOURCE'), 255), s(src.get('SOURCE_URL')), '', 'DE', )) # --------------------------------------------------------------------------- # Per-keyword scrape # --------------------------------------------------------------------------- def scrape_keyword(keyword): from_offset = 0 total_new = 0 total_skipped = 0 page = 1 while True: print(f" [Page {page}] offset={from_offset} ...", end=' ') try: data = fetch_page(keyword, from_offset) except Exception as e: print(f"API error: {e}") break hits = data.get('hits', {}) results = hits.get('hits', []) if not results: print("No more results.") break total_hits = hits.get('total', {}).get('value', '?') page_new = 0 page_skip = 0 conn = get_db_connection() cursor = conn.cursor() for record in results: es_id = record.get('_id') src = record.get('_source', {}) tender_id, is_new = get_or_insert_tender(cursor, src, es_id, keyword) if not is_new: page_skip += 1 continue insert_tender_detail(cursor, tender_id, src) page_new += 1 conn.commit() cursor.close() conn.close() total_new += page_new total_skipped += page_skip print(f"Got {len(results)} | New: {page_new} | Skipped: {page_skip} | Total: {total_hits}") if page >= MAX_PAGES: print(f" Reached max pages ({MAX_PAGES}) — stopping.") break if len(results) < PAGE_SIZE: break from_offset += PAGE_SIZE page += 1 time.sleep(1) print(f" => New: {total_new}, Skipped: {total_skipped}") return total_new, total_skipped # --------------------------------------------------------------------------- # Main # --------------------------------------------------------------------------- def main(): print("=" * 70) print("TENDERA.AT - Keyword Scraper") print("=" * 70) print(f"KEYWORDS : {len(KEYWORDS)}") print(f"GERMAN_KEYWORDS: {len(GERMAN_KEYWORDS)}") print(f"Total : {len(ALL_KEYWORDS)}") if not ALL_KEYWORDS: print('ERROR: No keywords found in managed_keywords table for language_code in ("en", "de")') return grand_new = 0 grand_skipped = 0 tracker_conn = get_db_connection() # Fix existing records that don't have the correct detail URL fix_cursor = tracker_conn.cursor() fix_cursor.execute(""" UPDATE tenders SET url = CONCAT('https://www.tendera.at/details/', source_id), updated_at = CURRENT_TIMESTAMP WHERE source = %s AND url NOT LIKE 'https://www.tendera.at/details/%%' """, (SOURCE,)) fixed = fix_cursor.rowcount tracker_conn.commit() fix_cursor.close() if fixed: print(f"Fixed {fixed} existing record(s) with correct detail URL.") for i, keyword in enumerate(ALL_KEYWORDS, 1): print(f"\n[{i}/{len(ALL_KEYWORDS)}] Keyword: \"{keyword}\"") if is_keyword_done(tracker_conn, keyword, SOURCE): print(f" [SKIP] Already ran today: \"{keyword}\"") continue new, skipped = scrape_keyword(keyword) grand_new += new grand_skipped += skipped mark_keyword_done(tracker_conn, keyword, SOURCE) print(f" [SAVED] Marked keyword done: \"{keyword}\"") time.sleep(1) tracker_conn.close() print("\n" + "=" * 70) print("Scraping Complete!") print("=" * 70) print(f" Keywords searched : {len(ALL_KEYWORDS)}") print(f" New : {grand_new:,}") print(f" Skipped : {grand_skipped:,}") print("=" * 70) if __name__ == '__main__': try: main() except KeyboardInterrupt: print("\n\nInterrupted.") except Exception as e: print(f"\nFatal Error: {e}") import traceback traceback.print_exc()