def getBookByTitle():
parser.add_argument('title', type=str)
args = parser.parse_args()
title = "%" + args['title'].strip() + "%"
book = db.session.execute("SELECT * FROM Book WHERE title LIKE :title",
{'title': title})
book = dict(book.first())
bibnum = book['bibnum']
availableCountQuery = db.session.execute(
"Select ItemCount from Inventory where bibnum=:bibnum and entrydate = '2018-01-02'",
{'bibnum': bibnum})
checkoutCountQuery = db.session.execute(
"Select count(*) from Checkout where bibnum=:bibnum and checkoutDay >=01 and checkoutmonth=9 and checkoutyear=2019;",
{'bibnum': bibnum})
similairBooks = db.session.execute(
"select * from SubjectBook join Book on SubjectBook.bibnum = Book.BibNum where subjectid in (select Subject.subjectid from Subject join SubjectBook on Subject.subjectid = SubjectBook.subjectid where SubjectBook.bibnum = :bibnum)",
{'bibnum': bibnum})
similairBooks = similairBooks.fetchall()
availableCount = availableCountQuery.first()[0]
checkoutCount = checkoutCountQuery.first()[0]
if (availableCount - checkoutCount) > 0:
book['count'] = availableCount - checkoutCount
else:
book['count'] = 0
data = result_to_dict(similairBooks)
if data:
del data[0] #delete the first row from list i.e. searched book title
return {'book': book, 'suggestions': data}
def findBooksToPurchase():
query = db.session.execute(
"select c.bibnum,b.title,count(c.bibnum) as count FROM Checkout c inner join Book b on c.bibnum=b.bibnum where checkoutyear=2019 and checkoutmonth=9 and checkoutday=30 group by c.bibnum,b.title order by count desc LIMIT 10"
)
result = result_to_dict(query.fetchall())
return {'response': result}
def findBooksToRetire():
query = db.session.execute(
"SELECT distinct i.bibnum,b.title, i.itemcount FROM Inventory i left outer join Checkout c on i.bibnum = c.bibnum inner join Book b on i.bibnum=b.bibnum where i.entrydate = '2018-01-02' and i.ItemCount > 50 and c.checkoutid is NULL order by i.itemcount desc"
)
result = result_to_dict(query.fetchall())
return {'response': result}
def fetch_match_history():
conn = db.connect()
result = conn.execute('SELECT * FROM matchHistory LIMIT 20')
conn.close()
keys, items = utils.result_to_dict(result, match_history_pks)
return keys, items
def getCheckoutByItemType():
parser.add_argument('authorname', type=str)
args = parser.parse_args()
author = "%" + args['authorname'].strip() + "%"
query = db.session.execute(
"select AuthorName,Checkout.bibnum,itemtype,count(*) as count from Book,Checkout where Book.bibnum = Checkout.bibnum and AuthorName like :author group by AuthorName,Checkout.bibnum,itemtype order by count desc LIMIT 1;",
{'author': author})
result = result_to_dict(query.fetchall())
return {'response': result}
def search(data):
table = data['table']
table = utils.hyphen_to_camel(table)
keyword = data['keyword']
keys = data['keys']
searches = utils.generate_searches(keys, keyword)
conn = db.connect()
query = f'SELECT * FROM {table} WHERE {searches};'
utils.debug_log(query)
result = conn.execute(query)
conn.close()
pk = []
if table == 'matchHistory':
pk = match_history_pks
elif table == 'champions':
pk = champions_pks
k, i = utils.result_to_dict(result, pk)
return k, i
def makeSubjectsArray():
query = db.session.execute(
"Select Book.bibnum,Subject.subjectname from Book JOIN SubjectBook JOIN Subject where Book.bibnum = SubjectBook.bibnum and SubjectBook.subjectid = Subject.subjectid group by Book.bibnum,Subject.subjectname order by Book.bibnum;"
)
result = result_to_dict(query.fetchall())
hashmap = {}
i = 0
for row in result:
key = row['bibnum']
value = row['subjectname']
if key not in hashmap:
hashmap[key] = []
hashmap[key].append(value)
else:
hashmap[key].append(value)
i += 1
if i > 1000:
break
with open('Sampledata/SubjectList.json', 'w') as outfile:
json.dump(hashmap, outfile)
return {'response': json.dumps(hashmap)}
def checkserviceworking():
subjectsquery = db.session.execute(
"Select Book.bibnum,Subject.subjectname from Book JOIN SubjectBook JOIN Subject where Book.bibnum = SubjectBook.bibnum and SubjectBook.subjectid = Subject.subjectid group by Book.bibnum,Subject.subjectname order by Book.bibnum;"
)
subjectsresult = result_to_dict(subjectsquery.fetchall())
hashmap = {}
i = 0
for rowEle in subjectsresult:
key = rowEle['bibnum']
value = rowEle['subjectname']
if key not in hashmap:
hashmap[key] = []
hashmap[key].append(value)
else:
hashmap[key].append(value)
i += 1
key = value = None
result = db.session.query(Book).all()
non_students_dicts = (student.make_dict() for student in result)
i = 0
responseArray = []
for row in non_students_dicts:
if row['bibnum'] in hashmap:
row['subjectslist'] = hashmap[row['bibnum']]
responseArray.append(row)
i += 1
jsonStr = json.dumps(responseArray)
with open('OutputData/FinalAggregrate.json', 'w') as outfile:
json.dump(responseArray, outfile)
return {'response': json.dumps(responseArray)}