def getSearchGameCnt(genre=[], company=[], platform=[], keyword=""):
db = get_db()
sqlCommand = ""
if len(company) > 0:
sqlCommand += """
SELECT gameId FROM GameDetail WHERE company IN (%s)
INTERSECT """ % (','.join([("'" + c + "'") for c in company]))
if len(platform) > 0:
sqlCommand += """
SELECT gameId FROM GameDetail WHERE platform IN (%s)
INTERSECT """ % (','.join([("'" + p + "'") for p in platform]))
if keyword != "":
sqlCommand += """
SELECT gameId FROM searchGameTable WHERE title MATCH ('%s')
OR genre MATCH ('%s')
OR description MATCH ('%s')
INTERSECT """ % (keyword, keyword, keyword)
sqlCommand += """SELECT d.gameId FROM
GameDetail AS d INNER JOIN GenreStat AS s WHERE
d.gameId = s.gameId """
for i in genre:
sqlCommand += (" AND s.[%s] = 1 " % (i))
sqlCommand = '(' + sqlCommand + ')'
sqlCommand = "SELECT COUNT(*) FROM GameDetail WHERE gameId IN " + sqlCommand
#if keyword != "":
# sqlCommand += " AND title LIKE '%" + keyword + "%' "
#print(sqlCommand)
result = db.execute(sqlCommand)
result = result.fetchone()['count(*)']
#pdb.set_trace()
return int(result)
def getGenreLst():
db = get_db()
result = db.execute('PRAGMA table_info(GenreStat)')
allGenreLst = []
for i in result:
if i['name'] != "gameId" and i['name'] != "title":
allGenreLst.append(i['name'])
return allGenreLst
def getGameImageName(idArr):
db = get_db()
idStrArr = list(map(str, idArr))
result = db.execute(
'SELECT * FROM GameImages WHERE gameId IN (%s)' %
','.join('?' * len(idStrArr)), idStrArr)
result = result.fetchall()
retVal = [dict(x) for x in result]
return retVal
def getPlatformLst():
db = get_db()
result = db.execute('SELECT platform FROM GameDetail')
allPlatformLst = []
tempSet = set()
for i in result:
tempSet.add(i['platform'])
for j in tempSet:
allPlatformLst.append(j)
return allPlatformLst
def getCompanyLst():
db = get_db()
result = db.execute('SELECT company FROM GameDetail')
allCompanyLst = []
tempSet = set()
for i in result:
tempSet.add(i['company'])
for j in tempSet:
allCompanyLst.append(j)
return allCompanyLst
def getGameInfo(idArr):
db = get_db()
idStrArr = list(map(str, idArr))
result = db.execute(
'SELECT * FROM GameDetail WHERE gameId IN (%s)' %
','.join('?' * len(idStrArr)), idStrArr)
result = result.fetchall()
retVal = list()
for i in result:
retVal.append(dict(i))
return retVal
def getSearchGameList(page, genre=[], company=[], platform=[], keyword=""):
db = get_db()
#Select * From Account Limit 9 Offset 10;
sqlCommand = ""
if len(company) > 0:
sqlCommand += """
SELECT gameId FROM GameDetail WHERE company IN (%s)
INTERSECT """ % (','.join([("'" + c + "'") for c in company]))
if len(platform) > 0:
sqlCommand += """
SELECT gameId FROM GameDetail WHERE platform IN (%s)
INTERSECT """ % (','.join([("'" + p + "'") for p in platform]))
if keyword != "":
sqlCommand += """
SELECT gameId FROM searchGameTable WHERE title MATCH ('%s')
OR genre MATCH ('%s')
OR description MATCH ('%s')
INTERSECT """ % (keyword, keyword, keyword)
sqlCommand += """SELECT d.gameId FROM
GameDetail AS d INNER JOIN GenreStat AS s WHERE
d.gameId = s.gameId """
for i in genre:
sqlCommand += (" AND s.[%s] = 1 " % (i))
sqlCommand = '(' + sqlCommand + ')'
sqlCommand = "SELECT * FROM GameDetail WHERE gameId IN " + sqlCommand
# if keyword != "":
# sqlCommand += " AND title LIKE '%" + keyword + "%' "
sqlCommand += " ORDER BY score DESC "
sqlCommand += " LIMIT " + str(ITEM_ONE_PAGE) + " OFFSET " + str(
ITEM_ONE_PAGE * page)
#print(sqlCommand)
result = db.execute(sqlCommand)
result = result.fetchall()
#d = {x:x*10 for x in range(3)}
retVal = list()
for x in result:
retVal.append(dict(x))
return retVal
def recGameLst():
if request.method == 'POST':
#import pdb
#pdb.set_trace();
import json
info = json.loads(request.form['questionnaireJsonInfo'])
#print(info)
from recommendAlgo import CustomizedRecommendation as customRec
recGameJson = customRec.CustomizedRecommendation().getRecommendation(
get_db(), info['selPlatformSet'], info['selGenreSet'], 16)
recGameLst = json.loads(recGameJson)
recGameImageNameLst = gameDataAccess.getGameImageName(
[x['gameId'] for x in recGameLst])
recGameLst = gameDataAccess.mergeRecGameLstAndImgInfo(
recGameLst, recGameImageNameLst)
selectionStr = ', '.join(info['selPlatformSet']) + ', ' + ', '.join(
info['selGenreSet'])
return render_template("recGameLst.html",
recGameLst=recGameLst,
selectionStr=selectionStr)
#return str(recGameLst);
else:
abort(404, "Error")
def index():
cookiesGameArr = []
for k in request.cookies:
if "Game_" in k:
gameId = k.split("_")[1]
cookiesGameArr.append((int(request.cookies[k]), int(gameId)))
cookiesGameArr.sort(reverse=True)
for i in range(0, len(cookiesGameArr)):
cookiesGameArr[i] = cookiesGameArr[i][1]
print(cookiesGameArr)
recommendGame = fr.FrontPageRecommendation().getRecommendation(
get_db(), cookiesGameArr, INDEX_REC_GAME_NUM,
"./recommendAlgo/Model/tfidf_model.txt",
"./recommendAlgo/Model/cv_model.txt")
recGameLst = json.loads(recommendGame)
recGameImageNameLst = gameDataAccess.getGameImageName(
[x['gameId'] for x in recGameLst])
recGameLst = gameDataAccess.mergeRecGameLstAndImgInfo(
recGameLst, recGameImageNameLst)
return render_template("index.html", recGameLst=recGameLst)
def gameDetail(gameId):
gameDetail = gameDataAccess.getGameInfo([gameId])
gameImageInfo = gameDataAccess.getGameImageName([gameId])
if len(gameDetail) == 0 or len(gameImageInfo) == 0:
abort(404, "game id {0} doesn't exist.".format(gameId))
gameDetail = gameDetail[0]
gameImageInfo = gameImageInfo[0]
#import pdb; pdb.set_trace()
reGames = cr.ContentRecommendation().getRecommendation(
get_db(),
gameId,
8,
"./recommendAlgo/Model/tfidf_model.txt", #tfidf_path
"./recommendAlgo/Model/cv_model.txt") #cv_path
recGameLst = json.loads(reGames)
recGameImageNameLst = gameDataAccess.getGameImageName(
[x['gameId'] for x in recGameLst])
recGameLst = gameDataAccess.mergeRecGameLstAndImgInfo(
recGameLst, recGameImageNameLst)
return render_template("gameDetail.html",
gameDetail=gameDetail,
gameImageInfo=gameImageInfo,
recGameLst=recGameLst)
