USE [SchoolAdvisor_Test]
GO

/****** Object:  StoredProcedure [dbo].[sp_GetCompany]    Script Date: 2020/06/26 13:28:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- sp_GetCompany 70
ALTER procedure [dbo].[sp_GetCompany]
				(@CompanyID int)
as
	Declare @Reviews int,
			@StarRating int,
			@NumberOfSchools int,
			@CompanyRating int,
			@ProductServiceCount int
	
	Set @Reviews = (select dbo.CalcAmountOfReviewsCompany(@CompanyID))
	Set @NumberOfSchools = (select dbo.CalcAmountOfSchoolsForCompany(@CompanyID))
	Set @CompanyRating = (select dbo.CalcCompanyRating(@CompanyID))
	Set @ProductServiceCount = (select dbo.CalcAmountOfProductsService(@CompanyID))

	Select CompanyID,
		   CompanyName,
     	   Logo,
		   ListingTypeID,
		   Telephone,
		   Website,
		   Email,
		   Description,
		   Video,
		   Link,
		   ISNULL(SubscriptionValid,0) SubscriptionValid,
		   ISNULL(@ProductServiceCount,0) as ProductServiceCount,
		   ISNULL(@Reviews,0) as NumberOfReviews,
		   ISNULL(@NumberOfSchools,0) as NumberOfSchools,
		   ISNULL(@CompanyRating,0) as Rating,
		    (SELECT count(BranchID) FROM tblBranchInfo where CompanyID = @CompanyID) BranchCount,
		   (Select top 1 Isnull(Latitude,0) from tblBranchInfo where CompanyID = @CompanyID) as Latitiude,
		   (Select top 1 Isnull(Longitude,0) from tblBranchInfo where CompanyID = @CompanyID) as Longitude,
		   Isnull(ListedByUserID, 0) as ListedByUserID,
		  isnull((Select distinct  
    substring(
        (
            Select Distinct ','+t.Description  AS [text()]
from
tblCompanies c
inner join tblProductServices p on c.CompanyID = p.CompanyID
inner join tblCategories cat on p.CategoryID = cat.CategorieID
inner join tblTagProductServiceLinks tpl on p.ProductServiceID = tpl.ProductServiceID
inner join tblTags t on t.TagID = tpl.TagID
where c.CompanyID = @CompanyID and t.Description <> ''

            For XML PATH ('')
        ), 2, 1000) + ',' +
substring(
        (
            Select Distinct ','+cat.Description  AS [text()]
from
tblCompanies c
inner join tblProductServices p on c.CompanyID = p.CompanyID
inner join tblCategories cat on p.CategoryID = cat.CategorieID
inner join tblTagProductServiceLinks tpl on p.ProductServiceID = tpl.ProductServiceID
inner join tblTags t on t.TagID = tpl.TagID
where c.CompanyID = @CompanyID and t.Description <> ''

            For XML PATH ('')
        ), 2, 1000) keywords
From dbo.tblCompanies ST2 where ST2.CompanyID = @CompanyID),'') keywords
	From tblCompanies
	Where CompanyID = @CompanyID

		INSERT INTO dbo.tblItemsHit
	        ( ItemType, ID, HitDate )
	VALUES  ( 1, -- ItemType - int
	          @CompanyID, -- ID - int
	          GETDATE()  -- HitDate - datetime
	          )



GO


