[ADMIN SITE] Các bugs khi đi qua hệ thống

[lyquoclam]

• So sánh sai thời gian Khuyến mãi:
• Chọn hình ảnh 2 lần trong Thông Tin Minh Bạch

[lyquoclam]

Hi anh @LeDangIUH Fixed 07/10/2016 Note: Khi build xong nhớ chạy scripts bên dưới cho tất cả databases:

So sánh sai thời gian Khuyến mãi

ALTER PROCEDURE [dbo].[sp_GetAllProductsByType]
(
     @type int,
     @userid int
)
AS
BEGIN
    DECLARE @CRating float
    IF(@type = 0)
    BEGIN
        SELECT p.ProductID, ProductName, p.Price, p.CompanyID, c.CompanyName, ProductDetail, p.ViewNumber, i.ImageUrl, i.ImageName, p.IsWarning, p.WarningContent,
        CAST(STUFF(
            (
                    select 
                        CONCAT(',',COUNT(*))
                    from dbo.SubCommentHistory sub 
                    left join dbo.[CommentHistory] com on com.ProductID = p.ProductID
                    where 
                    sub.CommentHistoryID = com.CommentHistoryID
                    for xml path('')
                )
            ,1,1,''
        ) as bigint) as TotalSub,
        cast((
            select (CAST( CAST(SUM(pr.Score) as float) / CAST(COUNT(*)as float) as float) +0) from ProductRating pr
                    where pr.ProductID = p.ProductID for xml path('')
        )as float) AS AVGRating,
        (SELECT TOP 1 pr.Score FROM ProductRating pr WHERE pr.ProductID = p.ProductID AND pr.UserID = @userid) AS ScoreRating,
        (
            SELECT Price FROM [dbo].[ProductPromotion] WHERE StartDate <= GETDATE() AND EndDate >= CONVERT(date, getdate()) AND IsDelete = 0 AND ProductID = P.ProductID
        ) AS PromotionPrice
        FROM [dbo].[FavoriteProduct] fp
        INNER JOIN [dbo].[Product] p ON p.ProductID = fp.ProductID
        INNER JOIN [dbo].[ProductGroup] pg ON p.ProductGroupID = pg.ProductGroupID
        INNER JOIN [dbo].[Company] c ON c.CompanyID = p.CompanyID
        LEFT JOIN [dbo].[Image] i ON i.ImageID = p.ImageID
        WHERE p.IsActive = 1 AND fp.UserID = @userid AND pg.IsActive = 1  AND p.IsVerified = 1
        GROUP BY p.ProductID, ProductName, p.Price, p.CompanyID, c.CompanyName, ProductDetail, p.ViewNumber, i.ImageUrl, i.ImageName, p.IsWarning, p.WarningContent
    END
    ELSE IF(@type = 1)
    BEGIN
        SELECT p.ProductID, ProductName, p.Price, p.CompanyID, c.CompanyName, ProductDetail, p.ViewNumber, i.ImageUrl, i.ImageName, p.IsWarning, p.WarningContent,
        CAST(STUFF(
            (
                    select 
                        CONCAT(',',COUNT(*))
                    from dbo.SubCommentHistory sub 
                    left join dbo.[CommentHistory] com on com.ProductID = p.ProductID
                    where 
                    sub.CommentHistoryID = com.CommentHistoryID
                    for xml path('')
                )
            ,1,1,''
        ) as bigint) as TotalSub,
        cast((
            select (CAST( CAST(SUM(pr.Score) as float) / CAST(COUNT(*)as float) as float) +0) from ProductRating pr
                where pr.ProductID = p.ProductID for xml path('')
        )as float) AS AVGRating,
        (SELECT TOP 1 pr.Score FROM ProductRating pr WHERE pr.ProductID = p.ProductID AND pr.UserID = @userid) AS ScoreRating,
        (
            SELECT Price FROM [dbo].[ProductPromotion] WHERE StartDate <= GETDATE() AND EndDate >= CONVERT(date, getdate()) AND IsDelete = 0 AND ProductID = p.ProductID
        ) AS PromotionPrice
        FROM [dbo].[SearchedHistory] fp
        INNER JOIN [dbo].[Product] p ON p.ProductID = fp.ProductID
        INNER JOIN [dbo].[Company] c ON c.CompanyID = p.CompanyID
        INNER JOIN [dbo].[ProductGroup] pg ON p.ProductGroupID = pg.ProductGroupID
        LEFT JOIN [dbo].[Image] i ON i.ImageID = p.ImageID
        WHERE p.IsActive = 1 AND fp.UserID = @userid AND pg.IsActive = 1  AND p.IsVerified = 1
        GROUP BY p.ProductID, ProductName, p.Price, p.CompanyID, c.CompanyName, ProductDetail, p.ViewNumber, i.ImageUrl, i.ImageName, p.IsWarning, p.WarningContent
    END
END

ALTER PROCEDURE [dbo].[SearchProductsByStr]
(
     @str nvarchar(max),
     @UserID bigint
)
AS
BEGIN
    SELECT p.ProductID, ProductName, p.Price, p.CompanyID, c.CompanyName, ProductDetail, p.ViewNumber, i.ImageUrl, i.ImageName, p.IsWarning, p.WarningContent,
        CAST(STUFF(
            (
                    select 
                        CONCAT(',',COUNT(*))
                    from dbo.SubCommentHistory sub 
                    left join dbo.[CommentHistory] com on com.ProductID = p.ProductID
                    where 
                    sub.CommentHistoryID = com.CommentHistoryID
                    for xml path('')
                )
            ,1,1,''
        ) as bigint) as TotalSub,
        Cast((
            select (CAST( CAST(SUM(pr.Score) as float) / CAST(COUNT(*)as float) as float) +0) from ProductRating pr
                where pr.ProductID = p.ProductID for xml path('')
        ) as float) as AVGRating,
        (SELECT TOP 1 pr.Score FROM ProductRating pr WHERE pr.ProductID = p.ProductID AND pr.UserID = @UserID) AS ScoreRating,
        (
            SELECT Price FROM [dbo].[ProductPromotion] WHERE StartDate <= GETDATE() AND EndDate >= CONVERT(date, getdate()) AND IsDelete = 0 AND ProductID = P.ProductID
        ) AS PromotionPrice
    FROM Product p
    INNER JOIN [dbo].[Company] c ON c.CompanyID = p.CompanyID
    INNER JOIN [dbo].[ProductGroup] pg ON pg.ProductGroupID = p.ProductGroupID
    LEFT JOIN [dbo].[Image] i ON i.ImageID = p.ImageID
    WHERE p.IsActive = 1 AND pg.IsActive = 1 AND p.IsVerified = 1 AND (ProductName like N'%'+@str+'%' OR Barcode like N'%'+@str+'%')

    GROUP BY p.ProductID, ProductName, p.Price, p.CompanyID, c.CompanyName, ProductDetail, p.ViewNumber, i.ImageUrl, i.ImageName, p.IsWarning, p.WarningContent
END

ALTER PROCEDURE [dbo].[sp_getDataFullProduct]
    @ListProductGroup nvarchar(max),
    @page int,
    @Leng int,
    @UserID bigint
AS
BEGIN
    declare @Skip int = @page -1;
    declare @Take int = @Leng;
    --declare @BarCode nvarchar(Max) = '456546213'
    select 
        p.ProductID,
        p.ProductName,
        p.Price,
        p.CompanyID,
        p.ProductDetail,
        p.ViewNumber,
        p.Barcode,
        p.WarningContent,
        p.IsWarning,
        ip.ImageUrl,
        ip.ImageName,
        (
                SELECT Price FROM [dbo].[ProductPromotion] pp WHERE pp.StartDate <= GETDATE() AND pp.EndDate >= CONVERT(date, getdate()) AND pp.IsDelete = 0 AND pp.ProductID = P.ProductID
            ) AS PromotionPrice,

        Cast((
            select (CAST( CAST(SUM(pr.Score) as float) / CAST(COUNT(*)as float) as float) +0) from ProductRating pr
                where pr.ProductID = p.ProductID for xml path('')
        ) as float) as TotalRating,
        (SELECT TOP 1 pr.Score FROM ProductRating pr WHERE pr.ProductID = p.ProductID AND pr.UserID = @UserID) AS ScoreRating,
        Cast((
            select COUNT(*) from FavoriteProduct fp
                where fp.ProductID = p.ProductID for xml path('')
        ) as float) as TotalWishlist,
        Cast((
            select COUNT(*) from CommentHistory ch
                where ch.ProductID = p.ProductID for xml path('')
        ) as float) as TotalComment,

        pg.ProductGroupName,

        c.CompanyName,
        c.Address,
        c.EmailAddress,
        c.PhoneNumber,
        c.LinkWebSite,
        ic.ImageUrl ImageUrlCompany,
        ic.ImageName ImageNameCompany,
        (
            select 
                trans.TransparentTitle,
                (
                    select 
                        sub.SubTransparentTitle,
                        ips.ImageUrl,
                        ips.ImageName,
                        ptrans.Text
                    from SubTransparent sub 
                        left join ProductTransparent ptrans on ptrans.SubTransparentID = sub.SubTransparentID
                        left join Image ips on ips.ImageID = ptrans.ImageID
                    where sub.TransparentID = trans.TransparentID and sub.IsActive = 1 and ptrans.ProductId = p.ProductID
                    for xml path('SubTransparents'), root('rb')
                ) as ListSub
            from Transparent trans 
            where trans.TransparentGroupID = p.ProductGroupID and trans.IsActive = 1
            for xml path('Transparents'), root('root')
        ) as ListTransparent,

        (
            select 
                ct.CertidicateName,
                icc.ImageUrl,
                icc.ImageName
            from Certificate cer 
                left join CertificateType ct on ct.CertificateTypeID = cer.CetificateTypeID
                left join Image icc on icc.ImageId = ct.ImageID
            where cer.ProductID = p.ProductID and cer.CertificateType = 0 and ct.IsActive = 1
            for xml path('Certificates'), root('root')
        ) as ListCertificateOfProduct,

        (
            select 
                ct.CertidicateName,
                icc.ImageUrl,
                icc.ImageName
            from Certificate cer 
                left join CertificateType ct on ct.CertificateTypeID = cer.CetificateTypeID
                left join Image icc on icc.ImageId = ct.ImageID
            where cer.CompanyID = p.CompanyID and cer.CertificateType = 1 and ct.IsActive = 1
            for xml path('Certificates'), root('root')
        ) as ListCertificateOfCompany,
        case when cast((select COUNT(*) from FavoriteProduct fp where fp.UserID = @UserID and fp.ProductId = p.ProductID) as int) > 0 then 1 else 0 end as IsWishlished

    from Product p
        left join ProductGroup pg on pg.ProductGroupID = p.ProductGroupID
        left join Company  c on c.CompanyID = p.CompanyID
        left join Image ip on ip.ImageID = p.ImageID
        left join Image ic on ic.ImageID = c.ImageID
    --  left join FavoriteProduct fp on fp.ProductID = p.ProductID
    where (@ListProductGroup is null or len(@ListProductGroup) = 0 or p.ProductGroupID in (select item from dbo.Split(@ListProductGroup,','))) 
    and p.IsActive = 1 and p.IsVerified = 1 and pg.IsActive = 1 
    --and (@UserID is null or @UserID <=0 or fp.UserID = @UserID)
    ORDER BY p.DateCreated desc
    OFFSET (@Skip) ROWS FETCH NEXT (@Take) ROWS ONLY
END

Chọn hình ảnh 2 lần trong Thông Tin Minh Bạch(Fixed trong code)

[lyquoclam]

Hi anh @LeDangIUH Note: Nhớ chạy script này for all databases nha anh Dang

Fixed Promotion for mobile

ALTER PROCEDURE [dbo].[sp_api_mobile_getbarcode]
    @BarCode nvarchar(max),
    @UserID bigint
AS
BEGIN
    --declare @BarCode nvarchar(Max) = '456546213'
    --exec sp_api_mobile_getbarcode '8935067702050',-1
    select 
        distinct
        p.ProductID,
        p.ProductName,
        Cast( case when p.Price is null then 0 else p.Price end as Int) Price,
        p.CompanyID,
        p.ProductDetail,
        p.ViewNumber,
        p.Barcode,
        p.WarningContent,
        p.IsWarning,
        ip.ImageUrl,
        ip.ImageName,
        (
                SELECT Price FROM [dbo].[ProductPromotion] pp WHERE pp.StartDate <= GETDATE() AND pp.EndDate >= CONVERT(date, getdate()) AND pp.IsDelete = 0 AND pp.ProductID = P.ProductID
            ) AS PromotionPrice,

        Cast((
            select (CAST( CAST(SUM(pr.Score) as float) / CAST(COUNT(*)as float) as float) +0) from ProductRating pr
                where pr.ProductID = p.ProductID for xml path('')
        ) as float) as TotalRating,
        (SELECT TOP 1 pr.Score FROM ProductRating pr WHERE pr.ProductID = p.ProductID AND pr.UserID = @UserID) AS ScoreRating,
        Cast((
            select COUNT(*) from FavoriteProduct fp
                where fp.ProductID = p.ProductID for xml path('')
        ) as int) as TotalWishlist,
        Cast((
            select COUNT(*) from CommentHistory ch
            left join SubCommentHistory  sub on sub.CommentHistoryID = ch.CommentHistoryID
                where ch.ProductID = p.ProductID for xml path('')
        ) as int) as TotalComment,

        pg.ProductGroupName,

        c.CompanyName,
        c.Address,
        c.EmailAddress,
        c.PhoneNumber,
        c.LinkWebSite,
        ic.ImageUrl ImageUrlCompany,
        ic.ImageName ImageNameCompany,
        (
            select 
                trans.TransparentTitle,
                (
                    select 
                        sub.SubTransparentTitle,
                        ips.ImageUrl,
                        ips.ImageName,
                        ptrans.Text
                    from SubTransparent sub 
                        left join ProductTransparent ptrans on ptrans.SubTransparentID = sub.SubTransparentID
                        left join Image ips on ips.ImageID = ptrans.ImageID
                    where sub.TransparentID = trans.TransparentID and sub.IsActive = 1 and ptrans.ProductId = p.ProductID
                    for xml path('SubTransparents'), root('rb')
                ) as ListSub
            from Transparent trans 
            where trans.TransparentGroupID = p.ProductGroupID and trans.IsActive = 1
            for xml path('Transparents'), root('root')
        ) as ListTransparent,

        (
            select 
                ct.CertidicateName,
                icc.ImageUrl,
                icc.ImageName
            from Certificate cer 
                left join CertificateType ct on ct.CertificateTypeID = cer.CetificateTypeID
                left join Image icc on icc.ImageId = ct.ImageID
            where cer.ProductID = p.ProductID and cer.CertificateType = 0 and ct.IsActive = 1
            for xml path('Certificates'), root('root')
        ) as ListCertificateOfProduct,

        (
            select 
                ct.CertidicateName,
                icc.ImageUrl,
                icc.ImageName
            from Certificate cer 
                left join CertificateType ct on ct.CertificateTypeID = cer.CetificateTypeID
                left join Image icc on icc.ImageId = ct.ImageID
            where cer.CompanyID = p.CompanyID and cer.CertificateType = 1 and ct.IsActive = 1
            for xml path('Certificates'), root('root')
        ) as ListCertificateOfCompany,
        case when cast((select COUNT(*) from FavoriteProduct fp where fp.UserID = @UserID and fp.ProductId = p.ProductID) as int) > 0 then 1 else 0 end as IsWishlished,
        case when pting.Score is null then 0 else pting.Score end myScore

    from dbo.Split( @BarCode ,',') tbl 
        left join Product p on p.Barcode = tbl.item
        left join ProductGroup pg on pg.ProductGroupID = p.ProductGroupID
        left join Company  c on c.CompanyID = p.CompanyID
        left join Image ip on ip.ImageID = p.ImageID
        left join Image ic on ic.ImageID = c.ImageID
        left join ProductRating pting on pting.UserID = @UserId and pting.ProductId = p.ProductId
        --left join FavoriteProduct fp on fp.ProductID = p.ProductID
    where 
        --(
        --  @BarCode is not null 
        --  or  LEN(@BarCode) = 0 
        --  or  p.Barcode in(select item from dbo.Split( @BarCode ,','))
        --)
        --and 
        p.IsActive = 1 and p.IsVerified = 1 
    --and (@UserID is null or @UserID <=0 or fp.UserID = @UserID)
END