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)