USE [SchoolAdvisor_Test]
GO

/****** Object:  StoredProcedure [dbo].[sp_GetProduct]    Script Date: 2020/06/26 16:05:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- sp_GetProduct 106
ALTER procedure [dbo].[sp_GetProduct]
				(@ProductID int)
as
	Declare @Reviews int,
			@StarRating int,
			@NumberOfSchools int
    
	Declare @Value int,
			@Price int,
			@Service int

	
	Set @Reviews = (select dbo.CalcAmountOfReviews(@ProductID))
	Set @NumberOfSchools = (select dbo.CalcAmountOfSchools(@ProductID))
    Set @StarRating = (select dbo.CalcProductRating(@ProductID))

	Declare @Temp Table(
		Value int,
		Price int,
		Service int)
	Insert @Temp
	Select Value,
		   Price,
		   Service
    From tblReviews where ProductServiceID = @ProductID --AND Value > 0 AND Price > 0 AND Service > 0

	Select @Value = Avg(Value) From @Temp WHERE Value > 0
	Select	   @Price = Avg(Price) From @Temp WHERE Price > 0
	Select	   @Service = Avg(Service) From @Temp WHERE Service > 0

	
	Select	ProductServiceID,
			P.Description,
			CategoryID,
			Image,
			Code,
			ProductName,
			c.CompanyID,
			C.CompanyName,
			@Reviews as NumberOfReviews,
			@NumberOfSchools as SchoolCount,
			Isnull(@StarRating,0) as Rating,
			Isnull(@Value,0) as Value,
			Isnull(@Price,0) as Price,
			Isnull(@Service,0) as Service,
			(SELECT count(BranchID) FROM tblBranchInfo where CompanyID = c.CompanyID) BranchCount,
			(Select top(1) ImagePath from tblProductServiceImages where ProductServiceID = @ProductID) as ProductImage,
			c.Logo,
			c.Description as CompanyDescription,
			c.Telephone,
			c.Website,
			c.Email,
			(Select Top 1 Latitude From tblBranchInfo Where CompanyID = c.CompanyID) as Latitiude,
			(Select Top 1 Longitude From tblBranchInfo Where CompanyID = c.CompanyID) as Longitude,
			Isnull(c.ListedByUserID, 0) as ListedByUserID,
			p.Video,
			c.ListingTypeID,
		p.Overview,
		p.Specials,
		p.Pricing,
		p.DeliveryAndSupport,
		p.Link
	From tblProductServices P
	Left Join tblCompanies C
	on P.CompanyID = C.CompanyID
    Where ProductServiceID = @ProductID
	

GO


