def search_contracts(gsins=[], keywords=[]):
"""Search for matching contracts."""
# Make the SQL condition fragments (ugly, again)
conditions = []
conditions.append(" or ".join(["gsin like '{}%'".format(esc(gsin)) for gsin in gsins if gsin]))
conditions.append(
" or ".join(
[
"contract in (select contract from ContractSearch where match(lemma) against('{}'))".format(
esc(keyword)
)
for keyword in keywords
if keyword
]
)
)
condition_fragment = " and ".join(["({})".format(condition) for condition in conditions if condition])
if not condition_fragment:
condition_fragment = "true"
# Make the SQL query string
query = CONTRACT_QUERY_TEMPLATE.format(conditions=condition_fragment)
# Post-processing for each result row
def post_contract(cursor, row):
data = sql_row_to_dict(cursor, row)
data["gsins"] = [data["gsin"]]
del data["gsin"]
id = re.sub(r"[^A-Z0-9-]", "-", data["contract"])
data["url_en"] = "https://buyandsell.gc.ca/procurement-data/contract-history/{}".format(id)
data["url_fr"] = "https://achatsetventes.gc.ca/donnees-sur-l-approvisionnement/contrats-octroyes/{}".format(id)
return data
# Execute the query
connection = bas.connect(config)
with connection.cursor() as cursor:
cursor.execute(query)
result = cursor.fetchall()
return [post_contract(cursor, row) for row in result]
def search_tenders(gsins=[], delivery=[], opportunity=[], keywords=[]):
"""Search for matching tenders."""
# Make the SQL condition fragments (a bit of ugly here)
conditions = []
conditions.append(" or ".join(["gsin like '{}%'".format(esc(gsin)) for gsin in gsins if gsin]))
conditions.append(" or ".join(["region_delivery='{}'".format(esc(region)) for region in delivery if region]))
conditions.append(" or ".join(["region_opportunity='{}'".format(esc(region)) for region in opportunity if region]))
conditions.append(
" or ".join(
[
"tender in (select tender from TenderSearch where match(lemma) against('{}'))".format(esc(keyword))
for keyword in keywords
if keyword
]
)
)
condition_fragment = " and ".join(["({})".format(condition) for condition in conditions if condition])
if not condition_fragment:
condition_fragment = "true"
# Make the SQL query string
query = TENDER_QUERY_TEMPLATE.format(conditions=condition_fragment)
# Post-processing for each result row
def post_tender(cursor, row):
data = sql_row_to_dict(cursor, row)
# Generate URLs to BAS
id = re.sub(r"[^A-Z0-9-]", "", data["tender"])
data["url_en"] = "https://buyandsell.gc.ca/procurement-data/tender-notice/{}".format(id)
data["url_fr"] = "https://achatsetventes.gc.ca/donnees-sur-l-approvisionnement/appels-d-offres/{}".format(id)
return data
# Execute the query
connection = bas.connect(config)
with connection.cursor() as cursor:
cursor.execute(query)
result = cursor.fetchall()
return [post_tender(cursor, row) for row in result]
'buyer_en = values(buyer_en),',
'buyer_fr = values(buyer_fr),',
'gsin = values(gsin)'
))
INSERT_FULLTEXT_QUERY = ' '.join((
'insert into ContractSearch(contract, lemma, lang)',
'values (%s, %s, %s)'
))
#
# Load the contracts
#
with open(sys.argv[1], 'r', encoding='utf-8-sig') as input:
contracts = ContractList(input)
connection = bas.connect(config)
with connection.cursor() as cursor:
today = str(datetime.date.today())
cursor.execute('delete from Contracts')
cursor.execute('delete from ContractSearch')
for counter, contract in enumerate(contracts):
if (counter + 1) % 5000 == 0:
print("{}...".format(counter+1))
if contract['date-expires'] > today:
result = cursor.execute(INSERT_CONTRACT_QUERY, (
contract['contract'],
contract['title_en'],
contract['title_fr'],
contract['date-awarded'],
contract['date-expires'],
contract['value'],
def esc(s):
"""SQL escape a string."""
return bas.connect(config).escape_string(s)
