def getInventory(self, queryTerms):
keys = queryTerms.keys()
isbnSelect = ""
kindSelect = ""
statusSelect = ""
titleSelect = ""
authorSelect = ""
categorySelect = ""
clauseTables = []
if "kind" in keys: # joins suck, avoid if possible
kind_map = {}
for k in [(x.kindName, x.id) for x in list(Kind.select())]:
kind_map[k[0]] = k[1]
try:
kind_id = kind_map[queryTerms['kind']]
kindSelect = Book.sqlrepr(
AND(
Field("book", "title_id") == Field("title", "id"),
Field("title", "kind_id") == kind_id))
except:
pass
if 'status' in keys:
statusSelect = Book.sqlrepr(
Field("book", "status") == queryTerms["status"])
if ('title' in keys) or ('authorName' in keys) or ('kind' in keys) or (
'categoryName' in keys) or ('isbn' in keys):
clauseTables.append('title')
#we are going to need to do a join
if 'title' in keys:
titleSelect = Book.sqlrepr(
AND(
Field("book", "title_id") == Field("title", "id"),
RLIKE(Field("title", "booktitle"),
queryTerms["title"])))
if 'isbn' in keys:
titleSelect = Book.sqlrepr(
AND(
Field("book", "title_id") == Field("title", "id"),
Field("title", "isbn") == queryTerms["isbn"]))
if 'authorName' in keys:
#~ authorSelect="""book.title_id = title.id AND author.title_id=title.id AND author.author_name RLIKE %s""" % (Book.sqlrepr(queryTerms["authorName"]))
authorSelect = Book.sqlrepr(
AND(
Field("book", "title_id") == Field("title", "id"),
Field("author", "id") == Field("author_title",
"author_id"),
Field("title", "id") == Field("author_title",
"title_id"),
RLIKE(Field("author", "author_name"),
queryTerms["authorName"])))
clauseTables.append('author')
clauseTables.append('author_title')
if 'categoryName' in keys:
#~ categorySelect="""book.title_id = title.id AND category.title_id=title.id AND category.category_name RLIKE %s""" % (Book.sqlrepr(queryTerms["categoryName"]))
categorySelect = Book.sqlrepr(
AND(
Field("book", "title_id") == Field("title", "id"),
Field("category", "title_id") == Field("title", "id"),
RLIKE(Field("category", "category_name"),
queryTerms["categoryName"])))
clauseTables.append('category')
# At this time, ubuntu install sqlobject 0.6.1 if apt-get install python2.4-sqlobject,
# which make the search crash, since the distinct attribute is defined somewhere after 0.6.1
try:
books = Book.select(string.join([
term for term in [
statusSelect, titleSelect, authorSelect, kindSelect,
categorySelect
] if term != ""
], " AND "),
clauseTables=clauseTables,
distinct=True)
except TypeError:
books = Book.select(string.join([
term for term in [
statusSelect, titleSelect, authorSelect, kindSelect,
categorySelect
] if term != ""
], " AND "),
clauseTables=clauseTables)
results = {}
i = 1
for b in books:
theTitle = b.title.booktitle.decode("unicode_escape")
if b.notes == None:
b.notes = ""
authorString = string.join([
a.author_name.decode("unicode_escape") for a in b.title.author
], ",")
results[i] = (string.capitalize(theTitle), authorString,
b.listprice,
b.title.publisher.decode("unicode_escape"),
b.status.decode("unicode_escape"), b.title.isbn,
b.distributor.decode("unicode_escape"),
b.notes.decode("unicode_escape"), b.id,
b.title.kind and b.title.kind.kindName or '')
i = i + 1
return results
def getInventory(self,queryTerms):
#print queryTerms
keys=queryTerms.keys()
isbnSelect=""
kindSelect=""
statusSelect=""
titleSelect=""
authorSelect=""
categorySelect=""
clauseTables=[]
if "kind" in keys: # joins suck, avoid if possible
kind_map={}
for k in [(x.kindName,x.id) for x in list(Kind.select())]:
kind_map[k[0]]=k[1]
try:
kind_id=kind_map[queryTerms['kind']]
kindSelect=Book.sqlrepr(AND(Field("book","title_id")==Field("title","id"), Field("title","kind_id")==kind_id))
except:
pass
if 'status' in keys:
statusSelect=Book.sqlrepr(Field("book","status")==queryTerms["status"])
if ('title' in keys) or ('authorName' in keys) or ('kind' in keys) or ('categoryName' in keys) or ('isbn' in keys):
clauseTables.append('title')
#we are going to need to do a join
if 'title' in keys:
titleSelect=Book.sqlrepr(AND(Field("book","title_id")==Field("title","id"), RLIKE(Field("title","booktitle"), queryTerms["title"])))
if 'isbn' in keys:
titleSelect=Book.sqlrepr(AND(Field("book","title_id")==Field("title","id"), Field("title","isbn")==queryTerms["isbn"]))
if 'authorName' in keys:
#authorSelect="""book.title_id = title.id AND author.title_id=title.id AND author.author_name RLIKE %s""" % (Book.sqlrepr(queryTerms["authorName"]))
authorSelect=Book.sqlrepr(AND(Field("book","title_id")==Field("title","id"), Field("author","id")==Field("author_title","author_id"), Field("title","id")==Field("author_title","title_id"), RLIKE(Field("author","author_name"), queryTerms["authorName"])))
clauseTables.append('author')
clauseTables.append('author_title')
if 'categoryName' in keys:
categorySelect="""book.title_id = title.id AND category.title_id=title.id AND category.category_name RLIKE %s""" % (Book.sqlrepr(queryTerms["categoryName"]))
#categorySelect=Book.sqlrepr(AND(Field("book","title_id")==Field("title","id"), Field("category","title_id")==Field("title","id"), RLIKE(Field("category","category_name"), queryTerms["categoryName"])))
clauseTables.append('category')
# At this time, ubuntu install sqlobject 0.6.1 if apt-get install python2.4-sqlobject,
# which make the search crash, since the distinct attribute is defined somewhere after 0.6.1
try:
books=Book.select(
string.join([term for term in [statusSelect,titleSelect,authorSelect,kindSelect,categorySelect] if term !=""]," AND "),
clauseTables=clauseTables,
distinct=True )
except TypeError:
books=Book.select(
string.join([term for term in [statusSelect,titleSelect,authorSelect,kindSelect,categorySelect] if term !=""]," AND "),
clauseTables=clauseTables )
results={}
i=1
for b in books:
theTitle=b.title.booktitle
authorString=string.join([a.authorName for a in b.title.author],",")
categoryString=string.join([c.categoryName for c in b.title.categorys],",")
results[i]=(string.capitalize(theTitle),
authorString,
b.listprice if b.listprice is not None else '',
b.title.publisher if b.title.publisher is not None else '',
b.status if b.status is not None else'',
b.title.isbn,
b.distributor if b.distributor is not None else '',
b.location.locationName if b.location is not None else '',
b.notes if b.notes is not None else '',
b.id,
b.title.kind and b.title.kind.kindName if b.title.kind is not None else '',
categoryString,
b.title.type if b.title.type is not None else '')
#~ for b in books:
#~ theTitle=b.title.booktitle.format()
#~ authorString=string.join([a.authorName.format() for a in b.title.author],",")
#~ categoryString=string.join([c.categoryName.format() for c in b.title.categorys],",")
#~ results[i]=(string.capitalize(theTitle),
#~ authorString,
#~ b.listprice if b.listprice is not None else '',
#~ b.title.publisher.format() if b.title.publisher is not None else '',
#~ b.status.format() if b.status is not None else'',
#~ b.title.isbn,
#~ b.distributor.format() if b.distributor is not None else '',
#~ b.location.locationName.format() if b.location is not None else '',
#~ b.notes.format() if b.notes is not None else '',
#~ b.id,
#~ b.title.kind and b.title.kind.kindName if b.title.kind is not None else '',
#~ categoryString,
#~ b.title.type if b.title.type is not None else '')
i=i+1
#print "results are ", results
return results
def getInventory(queryTerms):
print(queryTerms, file=sys.stderr)
keys = list(queryTerms)
print("keys are ", keys)
for k in keys:
if type(queryTerms[k]) == bytes:
queryTerms[k] = queryTerms[k].decode("utf-8")
isbnSelect = ""
kindSelect = ""
statusSelect = ""
titleSelect = ""
authorSelect = ""
categorySelect = ""
clauseTables = []
if "kind" in keys: # joins suck, avoid if possible
kind_map = {}
for k in [(x.kindName, x.id) for x in list(Kind.select())]:
kind_map[k[0]] = k[1]
try:
kind_id = kind_map[queryTerms["kind"]]
kindSelect = Book.sqlrepr(
AND(
Field("book", "title_id") == Field("title", "id"),
Field("title", "kind_id") == kind_id,
)
)
except:
pass
if "status" in keys:
statusSelect = Book.sqlrepr(Field("book", "status") == queryTerms["status"])
if (
("title" in keys)
or ("authorName" in keys)
or ("kind" in keys)
or ("categoryName" in keys)
or ("isbn" in keys)
):
clauseTables.append("title")
# we are going to need to do a join
if "title" in keys:
titleSelect = Book.sqlrepr(
AND(
Field("book", "title_id") == Field("title", "id"),
RLIKE(Field("title", "booktitle"), queryTerms["title"]),
)
)
if "isbn" in keys:
isbn, price = _process_isbn(queryTerms["isbn"])
print("isbn and price are ", isbn, price)
titleSelect = Book.sqlrepr(
AND(
Field("book", "title_id") == Field("title", "id"),
Field("title", "isbn") == isbn,
)
)
if "authorName" in keys:
# authorSelect="""book.title_id = title.id AND author.title_id=title.id AND author.author_name RLIKE %s""" % (Book.sqlrepr(queryTerms["authorName"]))
authorSelect = Book.sqlrepr(
AND(
Field("book", "title_id") == Field("title", "id"),
Field("author", "id") == Field("author_title", "author_id"),
Field("title", "id") == Field("author_title", "title_id"),
RLIKE(Field("author", "author_name"), queryTerms["authorName"]),
)
)
clauseTables.append("author")
clauseTables.append("author_title")
if "categoryName" in keys:
categorySelect = (
"""book.title_id = title.id AND category.title_id=title.id AND category.category_name RLIKE %s"""
% (Book.sqlrepr(queryTerms["categoryName"]))
)
clauseTables.append("category")
try:
books = Book.select(
" AND ".join(
[
term
for term in [
statusSelect,
titleSelect,
authorSelect,
kindSelect,
categorySelect,
]
if term != ""
]
),
clauseTables=clauseTables,
distinct=True,
)
except TypeError:
books = Book.select(
" AND ".join(
[
term
for term in [
statusSelect,
titleSelect,
authorSelect,
kindSelect,
categorySelect,
]
if term != ""
]
),
clauseTables=clauseTables,
)
results = {}
i = 1
for book_for_info in books:
theTitle = book_for_info.title.booktitle
authorString = ", ".join([a.authorName for a in book_for_info.title.author])
categoryString = ", ".join(
[c.categoryName for c in book_for_info.title.categorys]
)
results[i] = (
theTitle.capitalize(),
authorString,
book_for_info.listprice if book_for_info.listprice is not None else "",
book_for_info.title.publisher
if book_for_info.title.publisher is not None
else "",
book_for_info.status if book_for_info.status is not None else "",
book_for_info.title.isbn,
book_for_info.distributor if book_for_info.distributor is not None else "",
book_for_info.location.locationName
if book_for_info.location is not None
else "",
book_for_info.notes if book_for_info.notes is not None else "",
book_for_info.id,
book_for_info.title.kind and book_for_info.title.kind.kindName
if book_for_info.title.kind is not None
else "",
categoryString,
book_for_info.title.type if book_for_info.title.type is not None else "",
)
return results