def advanced():
search_form = AdvancedSearchForm()
if search_form.validate_on_submit():
if search_form.title:
cursor = g.conn.execute(text("""
WITH FullTable AS
(SELECT P.purl, P.title, P.model, P.number_of_citations, R.programming_language, K.keyword, A.first_name,
A.last_name, I.type, I.name, I.country, I.city, I.street, I.street_number, I.zip, R.rdate_published, P.date_published
FROM Papers P
LEFT OUTER JOIN Published_On PO ON P.purl = PO.purl
LEFT OUTER JOIN Repositories R ON PO.url = R.url
LEFT OUTER JOIN Is_Related_To IRT ON PO.purl = IRT.purl
LEFT OUTER JOIN Keywords K ON IRT.keyword = K.keyword
LEFT OUTER JOIN Published_By PB ON P.purl = PB.purl
LEFT OUTER JOIN Authors A ON PB.aid = A.aid
LEFT OUTER JOIN Works_At WA ON WA.aid = A.aid
LEFT OUTER JOIN Institutions I ON I.iid = WA.iid)
SELECT DISTINCT FT.title, FT.purl, FT.programming_language, FT.rdate_published
FROM FullTable FT
WHERE
upper(FT.title) LIKE '%%' || :title || '%%' AND
upper(FT.model) LIKE '%%' || :model || '%%' AND
FT.date_published >= :pdate AND
FT.number_of_citations >= :citations AND
upper(FT.first_name) LIKE '%%' || :first || '%%' AND
upper(FT.last_name) LIKE '%%' || :last || '%%' AND
upper(FT.name) LIKE '%%' || :institution || '%%' AND
FT.type IN :insttype AND
upper(FT.country) LIKE '%%' || :instcountry || '%%' AND
upper(FT.city) LIKE '%%' || :instcity || '%%' AND
upper(FT.zip) LIKE '%%' || :instzip || '%%' AND
upper(FT.street) LIKE '%%' || :inststreet || '%%' AND
upper(FT.street_number) LIKE '%%' || :instno || '%%';
"""), title=search_form.title.data.upper(), model=search_form.model.data.upper(),
pdate=str(
search_form.published_year.data if search_form.published_year.data else 1900) + '01' + '01',
citations=search_form.minimum_citations.data if search_form.minimum_citations.data else 0,
prog=search_form.repo_programming_language.data.upper(),
rdate=str(
search_form.repo_published_year.data if search_form.repo_published_year.data else 1900) + '01' + '01',
first=search_form.author_first_name.data.upper(),
last=search_form.author_last_name.data.upper(), institution=search_form.inst_name.data.upper(),
insttype=tuple(search_form.inst_type.data.split(' ')),
instcountry=search_form.inst_country.data.upper(), instcity=search_form.inst_city.data.upper(),
instzip=search_form.inst_zip.data.upper(),
inststreet=search_form.inst_street.data.upper(), instno=search_form.inst_street_no.data.upper())
results = []
for r in cursor:
if (search_form.repo_programming_language.data != '' and
(not r.programming_language or
r.programming_language.upper() != search_form.repo_programming_language.data.upper())):
continue
if (search_form.repo_published_year.data is not None and
(not r.rdate_published or
r.rdate_published.year < search_form.repo_published_year.data)):
continue
results.append({'title': r.title, 'purl': utils.encode_url(r.purl)})
return render_template('advancedsearch.html', results=results)
return render_template('advanced.html', form=search_form)
