Esempio n. 1
0
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)
Esempio n. 2
0
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)
Esempio n. 3
0
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)
Esempio n. 4
0
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)
Esempio n. 5
0
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)
Esempio n. 6
0
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)