def viewProductByCategory(request, categoryName):
isLogin = request.POST.get('isLogin')
isForgotPass = request.POST.get('isForgotPassword')
if request.method == 'POST' and isLogin == "1":
loginAccount(request)
elif request.method == 'POST' and isForgotPass == "1":
forgotPassword(request)
productList = None
sort = request.GET.get('sort', 'rate')
if sort == 'rate':
productList = Product.objects.order_by('-avgScore').select_related(
'categoryId',
'brandId').filter(categoryId__categoryName=categoryName)
else:
productList = Product.objects.order_by('-dtm_crt').select_related(
'categoryId',
'brandId').filter(categoryId__categoryName=categoryName)
getProductListByPage = None
if productList:
paginator = Paginator(productList, 12)
page_number = request.GET.get('page', 1)
getProductListByPage = paginator.get_page(page_number)
if getProductListByPage.has_next():
next_url = getProductListByPage.next_page_number()
else:
next_url = ''
if getProductListByPage.has_previous():
prev_url = getProductListByPage.previous_page_number()
else:
prev_url = ''
else:
getProductListByPage = Product.objects.none()
next_url = ''
prev_url = ''
try:
Category.objects.get(categoryName=categoryName)
except:
context = {
'message':
"There is no category \"" + categoryName + "\" available"
}
return render(request, 'error.html', context)
context = {
'productList': getProductListByPage,
'categoryName': categoryName,
'userPending': countUserPending(request),
'reportUser': countReport(request),
'next_page_url': next_url,
'prev_page_url': prev_url
}
return render(request, 'productListByCategory.html', context)
def advanceSearch(request):
isLogin = request.POST.get('isLogin')
isForgotPass = request.POST.get('isForgotPassword')
# isFilter = request.POST.get('IsFilter')
resultFilter = None
new_var = None
if request.method == 'POST' and isLogin == "1":
loginAccount(request)
elif request.method == 'POST' and isForgotPass == "1":
forgotPassword(request)
new_var = productFilters(request.GET, Product.objects.all())
productName = None
if 'productName' in request.GET:
productName = request.GET['productName']
else:
productName = ''
if new_var:
paginator = Paginator(new_var.qs, 8)
page_number = request.GET.get('page', 1)
getProductListByPage = paginator.get_page(page_number)
if getProductListByPage.has_next():
next_url = getProductListByPage.next_page_number()
else:
next_url = ''
if getProductListByPage.has_previous():
prev_url = getProductListByPage.previous_page_number()
else:
prev_url = ''
else:
getProductListByPage = Product.objects.none()
next_url = ''
prev_url = ''
context = {
'productName': productName,
'filter': new_var,
'productList': getProductListByPage,
'userPending': countUserPending(request),
'reportUser': countReport(request),
'next_page_url': next_url,
'prev_page_url': prev_url
}
return render(request, 'productList.html', context)
def hotItems(request):
isLogin = request.POST.get('isLogin')
isForgotPass = request.POST.get('isForgotPassword')
if request.method == 'POST' and isLogin == "1":
loginAccount(request)
elif request.method == 'POST' and isForgotPass == "1":
forgotPassword(request)
productList = Product.objects.select_related('brandId').order_by(
'-visitCount')[:8]
getProductListByPage = None
if productList:
paginator = Paginator(productList, 12)
page_number = request.GET.get('page', 1)
getProductListByPage = paginator.get_page(page_number)
if getProductListByPage.has_next():
next_url = f'?page={getProductListByPage.next_page_number()}'
else:
next_url = ''
if getProductListByPage.has_previous():
prev_url = f'?page={getProductListByPage.previous_page_number()}'
else:
prev_url = ''
else:
getProductListByPage = Product.objects.none()
next_url = ''
prev_url = ''
context = {
'productList': getProductListByPage,
'userPending': countUserPending(request),
'reportUser': countReport(request),
'next_page_url': next_url,
'prev_page_url': prev_url
}
return render(request, 'hotProducts.html', context)
def brandPage(request, brandName, sort):
isLogin = request.POST.get('isLogin')
isForgotPass = request.POST.get('isForgotPassword')
if request.method == 'POST' and isLogin == "1":
loginAccount(request)
elif request.method == 'POST' and isForgotPass == "1":
forgotPassword(request)
error = 0
try:
getBrand = Brand.objects.get(brandName=brandName)
brandStatsData = []
error = 1
with connection.cursor() as cursor:
raw_sql = """
with
stats as(
select
(
select count(*) from product_product as p
join product_brand as b on p.brandId_id = b.brandId
where b.brandId = """ + str(
getBrand.brandId) + """ and p.avgScore <= 10 and p.avgScore >=8
) as positive,
(
select count(*) from product_product as p
join product_brand as b on p.brandId_id = b.brandId
where b.brandId = """ + str(
getBrand.brandId
) + """ and p.avgScore < 8 and p.avgScore >=5
) as mixed,
(
select count(*) from product_product as p
join product_brand as b on p.brandId_id = b.brandId
where b.brandId = """ + str(
getBrand.brandId
) + """ and p.avgScore < 5 and p.avgScore > 0
) as negative
, b.brandName
from product_product as p
join product_brand as b on p.brandId_id = b.brandId
where b.brandId = """ + str(getBrand.brandId) + """
),
maxMin as(
select (select max(p.avgScore) from product_product as p
join product_brand as b on p.brandId_id = b.brandId
where b.brandId = """ + str(
getBrand.brandId) + """
having max(p.avgScore) > 0
order by p.dtm_upd desc) as highRate,
(
select p.productName from product_product as p
join product_brand as b on p.brandId_id = b.brandId
where p.avgScore = (
select max(p.avgScore) from product_product as p
join product_brand as b on p.brandId_id = b.brandId
where b.brandId = """ + str(getBrand.brandId) + """
having max(p.avgScore) > 0
) and b.brandId = """ + str(
getBrand.brandId) + """
order by p.dtm_upd desc
limit 1
) as highRateName,
(select min(p.avgScore) from product_product as p
join product_brand as b on p.brandId_id = b.brandId
where b.brandId = """ + str(
getBrand.brandId) + """
having min(p.avgScore) > 0
order by p.dtm_upd desc
) as minRate,
(
select p.productName from product_product as p
join product_brand as b on p.brandId_id = b.brandId
where p.avgScore = (
select min(p.avgScore) from product_product as p
join product_brand as b on p.brandId_id = b.brandId
where b.brandId = """ + str(getBrand.brandId) + """
having min(p.avgScore) > 0
) and b.brandId = """ + str(
getBrand.brandId) + """
order by p.dtm_upd desc
limit 1
) as minRateName,
b.brandName as brandName
from product_product as p
join product_brand as b on p.brandId_id = b.brandId
where b.brandId = """ + str(getBrand.brandId) + """
)
select distinct (positive), mixed, negative, highRate, highRateName, minRate, minRateName
from stats join maxMin using (brandName)
"""
cursor.execute(raw_sql)
for qux in cursor.fetchall():
brandStatsData.append({
"positive": qux[0],
"mixed": qux[1],
"negative": qux[2],
"highestRate": numIndicator(str(qux[3])),
"highestRateName": qux[4],
"lowestRate": numIndicator(str(qux[5])),
"lowestRateName": qux[6]
})
categoryList = []
categoryList.append({"category": "By Date"})
categoryList.append({"category": "By Rate"})
error = 1
with connection.cursor() as cursor:
raw_sql = """
select distinct(c.categoryName) from product_product as p
join product_brand as b on p.brandId_id = b.brandId
join product_category as c on p.categoryId_id = c.categoryId
where b.brandId = """ + str(getBrand.brandId) + """
"""
cursor.execute(raw_sql)
for qux in cursor.fetchall():
categoryList.append({"category": qux[0]})
getProductFromSort = None
if sort == "By Date":
getProductFromSort = Product.objects.order_by('-dtm_crt').filter(
brandId=getBrand)
elif sort == "By Rate":
getProductFromSort = Product.objects.order_by('-avgScore').filter(
brandId=getBrand)
else:
getCategory = Category.objects.get(categoryName=sort)
getProductFromSort = Product.objects.order_by(
'productName').filter(brandId=getBrand, categoryId=getCategory)
getProductByPage = None
if getProductFromSort:
paginator = Paginator(getProductFromSort, 5)
page_number = request.GET.get('page', 1)
getProductByPage = paginator.get_page(page_number)
if getProductByPage.has_next():
next_url = f'?page={getProductByPage.next_page_number()}'
else:
next_url = ''
if getProductByPage.has_previous():
prev_url = f'?page={getProductByPage.previous_page_number()}'
else:
prev_url = ''
else:
getProductByPage = Product.objects.none()
next_url = ''
prev_url = ''
context = {
'brandData': brandStatsData,
'brand': getBrand,
'userPending': countUserPending(request),
'reportUser': countReport(request),
'sortObj': getProductByPage,
'categoryList': categoryList,
'sort': sort,
'next_page_url': next_url,
'prev_page_url': prev_url
}
return render(request, 'brandPage.html', context)
except Exception as e:
print(e)
context = None
if error == 0:
context = {'message': "Brand " + brandName + " Not Found"}
else:
context = {'message': 'error'}
return render(request, 'error.html', context)
def showMoreReview(request, productName, brand, showMore):
isLogin = request.POST.get('isLogin')
isForgotPass = request.POST.get('isForgotPassword')
if request.method == 'POST' and isLogin == "1":
loginAccount(request)
elif request.method == 'POST' and isForgotPass == "1":
forgotPassword(request)
try:
obj = Product.objects.select_related('brandId').get(
productName=productName, brandId__brandName=brand)
ratingUserScore = []
ratingMusicStore = []
userAvg = None
musStoreAvg = None
with connection.cursor() as cursor:
raw_sql = """
with
statsUser as (
select
FORMAT((
(select count(*) from review_review as r
join register_user as u on r.userID_id = u.userID
where u.roleId like "%Reg_User%" and productId_id = """ + str(
obj.productId) + """ and r.rating <= 10 and r.rating >=8)
/(count(u.userID))
*100
),0) as positive_user,
FORMAT((
(select count(*) from review_review as r
join register_user as u on r.userID_id = u.userID
where u.roleId like "%Reg_User%" and productId_id = """ + str(
obj.productId) + """ and r.rating < 8 and r.rating >=5)
/(count(u.userID))
*100
),0) as mixed_user,
FORMAT((
(select count(*) from review_review as r
join register_user as u on r.userID_id = u.userID
where u.roleId like "%Reg_User%" and productId_id = """ + str(
obj.productId) + """ and r.rating < 5 and r.rating >=0)
/(count(u.userID))
*100
),0) as negative_user,
p.productId as productId
from review_review as r
join register_user as u on r.userID_id = u.userID
join product_product as p on r.productId_id = p.productId
where u.roleId like "%Reg_User%" and p.productId = """ + str(
obj.productId) + """
)
select positive_user, mixed_user, negative_user
from statsUser
"""
cursor.execute(raw_sql)
for qux in cursor.fetchall():
ratingUserScore.append({
"positive_user": qux[0],
"mixed_user": qux[1],
"negative_user": qux[2]
})
with connection.cursor() as cursor:
raw_sql = """
with
statsMusStore as (
select
FORMAT((
(select count(*) from review_review as r
join register_user as u on r.userID_id = u.userID
where u.roleId like "%Mus_Store%" and productId_id = """ + str(
obj.productId) + """ and r.rating <= 10 and r.rating >=8)
/(count(u.userID))
*100
),0) as positive_ms,
FORMAT((
(select count(*) from review_review as r
join register_user as u on r.userID_id = u.userID
where u.roleId like "%Mus_Store%" and productId_id = """ + str(
obj.productId) + """ and r.rating < 8 and r.rating >=5)
/(count(u.userID))
*100
),0) as mixed_ms,
FORMAT((
(select count(*) from review_review as r
join register_user as u on r.userID_id = u.userID
where u.roleId like "%Mus_Store%" and productId_id = """ + str(
obj.productId) + """ and r.rating < 5 and r.rating >=0)
/(count(u.userID))
*100
),0) as negative_ms,
p.productId as productId
from review_review as r
join register_user as u on r.userID_id = u.userID
join product_product as p on r.productId_id = p.productId
where u.roleId like "%Mus_Store%" and p.productId = """ + str(
obj.productId) + """
)
select positive_ms, mixed_ms, negative_ms
from statsMusStore
"""
cursor.execute(raw_sql)
for qux in cursor.fetchall():
ratingMusicStore.append({
"positive_music": qux[0],
"mixed_music": qux[1],
"negative_music": qux[2]
})
with connection.cursor() as cursor:
raw_sql = """
select
FORMAT (avg(r.rating), 1) as user_avg
from review_review as r
join register_user as u on r.userID_id = u.userID
join product_product as p on r.productId_id = p.productId
where u.roleId like "%Reg_User%" and p.productId=""" + str(
obj.productId) + """
"""
cursor.execute(raw_sql)
for qux in cursor.fetchall():
userAvg = numIndicator(qux[0])
with connection.cursor() as cursor:
raw_sql = """
select
FORMAT (avg(r.rating), 1) as Mus_store_avg
from review_review as r
join register_user as u on r.userID_id = u.userID
join product_product as p on r.productId_id = p.productId
where u.roleId like "%Mus_Store%" and p.productId=""" + str(
obj.productId) + """
"""
cursor.execute(raw_sql)
for qux in cursor.fetchall():
musStoreAvg = numIndicator(qux[0])
username = None
review_available = None
messages = None
getUser = request.user.username
if getUser != '':
username = request.user.username
adminRoleCheck = request.user.groups.filter(name='Admin').exists()
if adminRoleCheck == False:
obj.visitCount = obj.visitCount + 1
obj.save()
checkReview = Review.objects.select_related(
'userID',
'productId').filter(userID__userName=username,
productId__productName=productName)
if not checkReview:
review_available = "enabled"
else:
review_available = "disabled"
messages = "You cannot submit another review again"
else:
review_available = "disabled"
messages = "You Need To Login First"
otherProduct = Product.objects.order_by('-avgScore')[:6]
reviewList = None
if showMore == 'Music Store':
reviewList = Review.objects.select_related(
'userID',
'productId').filter(userID__roleId="Mus_Store",
productId__productName=productName)
else:
reviewList = Review.objects.select_related(
'userID',
'productId').filter(userID__roleId="Reg_User",
productId__productName=productName)
if reviewList:
paginator = Paginator(reviewList, 6)
page_number = request.GET.get('page', 1)
getReviewListByPage = paginator.get_page(page_number)
if getReviewListByPage.has_next():
next_url = f'?page={getReviewListByPage.next_page_number()}'
else:
next_url = ''
if getReviewListByPage.has_previous():
prev_url = f'?page={getReviewListByPage.previous_page_number()}'
else:
prev_url = ''
else:
getReviewListByPage = Review.objects.none()
next_url = ''
prev_url = ''
context = {
'obj': obj,
'review': getReviewListByPage,
'ratingUserScore': ratingUserScore,
'ratingMusicStore': ratingMusicStore,
'userAvg': userAvg,
'musStoreAvg': musStoreAvg,
'reviewStatus': review_available,
'messageModal': messages,
'userPending': countUserPending(request),
'otherProduct': otherProduct,
'showMore': showMore,
'next_page_url': next_url,
'prev_page_url': prev_url
}
return render(request, 'rating.html', context)
except Exception as e:
print(e)
context = None
context = {
'message':
"Product \"" + productName + "\" from brand \"" + brand +
"\" Not Found"
}
return render(request, 'error.html', context)
def profilePage(request, userName):
isLogin = request.POST.get('isLogin')
isForgotPass = request.POST.get('isForgotPassword')
if request.method == 'POST' and isLogin == "1":
loginAccount(request)
elif request.method == 'POST' and isForgotPass == "1":
forgotPassword(request)
error = 0
try:
getUser = User.objects.get(userName=userName)
reviewList = Review.objects.select_related(
'productId', 'userID').order_by('-dtm_crt').filter(
userID__userName=getUser.userName)
error = 1
userStatsData = []
with connection.cursor() as cursor:
raw_sql = """
with
stats as(
select
(select count(*) from review_review as r
join register_user as u on u.userID = r.userID_id
where u.userID = """ + str(
getUser.userID) + """ and r.rating <= 10 and r.rating >=8
) as positive,
(
select count(*) from review_review as r
join register_user as u on u.userID = r.userID_id
where u.userID = """ + str(
getUser.userID) + """ and r.rating < 8 and r.rating >=5
) as mixed,
(
select count(*) from review_review as r
join register_user as u on u.userID = r.userID_id
where u.userID = """ + str(
getUser.userID) + """ and r.rating < 5 and r.rating >=0
) as negative
, u.userID as UserID
from review_review as r
join register_user as u on r.userID_id = u.userID
where u.userID = """ + str(
getUser.userID) + """
),
maxMin as(
select (select max(r.rating) from review_review as r
join register_user as u on u.userID = r.userID_id
join product_product as p on p.productId = r.productId_id
where u.userID = """ + str(
getUser.userID) + """
order by r.dtm_crt desc) as highRate,
(
select b.brandName from review_review as r
join register_user as u on u.userID = r.userID_id
join product_product as p on p.productId = r.productId_id
join product_brand as b on p.brandId_id = b.brandId
where r.rating = (
select max(r.rating) from review_review as r
join register_user as u on u.userID = r.userID_id
join product_product as p on p.productId = r.productId_id
where u.userID = """ + str(
getUser.userID) + """
) and u.userID = """ + str(
getUser.userID) + """
order by r.dtm_crt desc
limit 1
) as highRateBrand,
(
select p.productName from review_review as r
join register_user as u on u.userID = r.userID_id
join product_product as p on p.productId = r.productId_id
where r.rating = (
select max(r.rating) from review_review as r
join register_user as u on u.userID = r.userID_id
join product_product as p on p.productId = r.productId_id
where u.userID = """ + str(
getUser.userID) + """
) and u.userID = """ + str(
getUser.userID) + """
order by r.dtm_crt desc
limit 1
) as highRateName,
(
select min(r.rating) from review_review as r
join register_user as u on u.userID = r.userID_id
join product_product as p on p.productId = r.productId_id
where u.userID = """ + str(
getUser.userID) + """
order by r.dtm_crt desc) as minRate,
(
select b.brandName from review_review as r
join register_user as u on u.userID = r.userID_id
join product_product as p on p.productId = r.productId_id
join product_brand as b on p.brandId_id = b.brandId
where r.rating = (
select min(r.rating) from review_review as r
join register_user as u on u.userID = r.userID_id
join product_product as p on p.productId = r.productId_id
where u.userID = """ + str(
getUser.userID) + """
) and u.userID = """ + str(
getUser.userID
) + """
order by r.dtm_crt desc
limit 1
) as minRateBrand,
(
select p.productName from review_review as r
join register_user as u on u.userID = r.userID_id
join product_product as p on p.productId = r.productId_id
where r.rating = (
select min(r.rating) from review_review as r
join register_user as u on u.userID = r.userID_id
join product_product as p on p.productId = r.productId_id
where u.userID = """ + str(
getUser.userID
) + """
) and u.userID = """ + str(
getUser.userID
) + """
order by r.dtm_crt desc
limit 1
) as minRateName,
u.userID as UserID
from review_review as r
join register_user as u on r.userID_id = u.userID
where u.userID = """ + str(getUser.userID
) + """
)
select distinct (positive), mixed, negative, highRate, highRateBrand, highRateName, minRate, minRateBrand, minRateName
from stats join maxMin using (UserID)
"""
cursor.execute(raw_sql)
for qux in cursor.fetchall():
userStatsData.append({
"positive": qux[0],
"mixed": qux[1],
"negative": qux[2],
"highestRate": numIndicator(qux[3]),
"highRateBrand": qux[4],
"highestRateName": qux[5],
"lowestRate": numIndicator(qux[6]),
"lowestRateBrand": qux[7],
"lowestRateName": qux[8]
})
getReviewListByPage = None
if reviewList:
paginator = Paginator(reviewList, 4)
page_number = request.GET.get('page', 1)
getReviewListByPage = paginator.get_page(page_number)
if getReviewListByPage.has_next():
next_url = f'?page={getReviewListByPage.next_page_number()}'
else:
next_url = ''
if getReviewListByPage.has_previous():
prev_url = f'?page={getReviewListByPage.previous_page_number()}'
else:
prev_url = ''
else:
getReviewListByPage = Review.objects.none()
next_url = ''
prev_url = ''
if getUser.roleId == 'Mus_Store':
getUser = MusicStoreData.objects.select_related('userID').get(
userID__userName=userName)
context = {
'userData': userStatsData,
'obj': getReviewListByPage,
'User': getUser,
'userPending': countUserPending(request),
'reportUser': countReport(request),
'next_page_url': next_url,
'prev_page_url': prev_url
}
return render(request, 'profile.html', context)
except Exception as e:
print(e)
context = None
if error == 0:
context = {'message': "User " + userName + " Not Found"}
else:
context = {'message': 'error'}
return render(request, 'error.html', context)