Untitled

CREATE PROCEDURE [App].[usp_GetSiteProvisioningByManufacturerId_Old_temp] @ManufacturerId INT
	,@UserId INT
AS
/*********************************************************************************************************************************************
** STORED PROCEDURE           : [App].[usp_GetSiteProvisioningByManufacturerId]
** Author                     : Dev 2
** Date                       : 08/07/2018
** Purpose                    : Get Site Provisioning details
** Arguments                  : @ManufacturerId
** Returns                    : 0
** Depends On                 : None
** Effects                    : None
** Invoked Via                : Web Portal

EXEC [App].[usp_GetSiteProvisioningByManufacturerId] 1,71113
**********************************************************************************************************************************************/
BEGIN
	EXEC [App].[usp_CheckUserEntityAccess] @UserId
		,@ManufacturerId
		,'Manufacturer'

	CREATE TABLE #NumberOfVehicles (
		SiteId INT
		,[NumberOfVehicles] INT
		)

	CREATE TABLE #NumberOfOperators (
		SiteId INT
		,[NumberOfOperators] INT
		)

	INSERT INTO #NumberOfVehicles (
		SiteId
		,[NumberOfVehicles]
		)
	SELECT V.[SiteId]
		,COUNT(V.[VehicleId]) AS [NumberOfVehicles]
	FROM [App].[Vehicle] V
		INNER JOIN [App].[DeviceVehicle] DV ON V.[VehicleId] = DV.[VehicleId]
	WHERE V.[Status] = 1
		AND DV.[IsActive] = 1
	GROUP BY V.[SiteId]

	INSERT INTO #NumberOfOperators (
		SiteId
		,[NumberOfOperators]
		)
	SELECT EntityValueId AS [SiteId]
		,COUNT(O.[OperatorId]) AS [NumberOfOperators]
	FROM [App].[OperatorSite] OS
		INNER JOIN App.Operator O ON O.[OperatorId] = OS.[OperatorId]
		INNER JOIN [Identity].[Entity] E ON E.EntityValueId = OS.[SiteId] AND E.EntityTypeId = 7 --Site
	WHERE IsResetter = 0
	GROUP BY E.EntityValueId

	CREATE NONCLUSTERED INDEX [NIX_#NumberOfVehicles] ON #NumberOfVehicles (SiteId)

	CREATE NONCLUSTERED INDEX [NIX_#NumberOfOperators] ON #NumberOfOperators (SiteId)

	CREATE TABLE #ServiceLevels (
		SiteId INT,
		ServiceLevels NVARCHAR(MAX)
	)

	INSERT INTO #ServiceLevels(
		SiteId,
		ServiceLevels
	)

	SELECT S.SiteId,STRING_AGG(CAST(PTF.PricingTierFeatureDescription AS NVARCHAR(MAX)), ',') ServiceLevels
	FROM App.[Site] S 
		INNER JOIN App.MvwSiteHierarchy SH ON SH.SiteId = S.SiteId
		INNER JOIN [App].[SitePricingTier] SPT ON S.SiteId = SPT.SiteId
		INNER JOIN [App].[PricingTierFeature] AS PTF ON PTF.PricingTierFeatureId = SPT.PricingTierFeatureId 
	WHERE 
		SPT.IsActive = 1
	GROUP BY S.SiteId

	CREATE NONCLUSTERED INDEX [NIX_#ServiceLevels] ON #ServiceLevels (SiteId)

	SELECT ProvisioningStatus
		,S.SiteId
		,S.SiteName
		,S.SiteCode AS PartnerId
		,S.StoreNumber
		,CASE 
			WHEN AD.AddressLine2 IS NULL
				THEN ''
			ELSE AD.AddressLine2
			END + CASE 
			WHEN AD.Street IS NULL
				THEN ''
			ELSE AD.Street
			END + CASE 
			WHEN AD.City IS NULL
				THEN ''
			ELSE ', ' + AD.City
			END + CASE 
			WHEN AD.STATE IS NULL
				THEN ''
			ELSE ', ' + AD.STATE
			END + ISNULL(' ' + AD.ZipCode, '') AS [Address]
		,C.CompanyName AS ParentCompany
		,CASE 
			WHEN (
					SH.ServicingBranchId IS NULL
					OR SH.ServicingBranchId = - 1
					)
				THEN ADE.BranchName
			ELSE AB.BranchName
			END AS ServicingDealerName
		,CASE 
			WHEN (
					SH.ServicingBranchId IS NULL
					OR SH.ServicingBranchId = - 1
					)
				THEN ADE.BranchCode
			ELSE AB.BranchCode
			END AS ServicingDealerPartnerId
		,CASE 
			WHEN (
					SH.ServicingBranchId IS NULL
					OR SH.ServicingBranchId = - 1
					)
				THEN (
						CASE 
							WHEN SDA.AddressLine2 IS NULL
								THEN ''
							ELSE SDA.AddressLine2
							END + CASE 
							WHEN SDA.Street IS NULL
								THEN ''
							ELSE SDA.Street
							END + CASE 
							WHEN SDA.City IS NULL
								THEN ''
							ELSE ', ' + SDA.City
							END + CASE 
							WHEN SDA.STATE IS NULL
								THEN ''
							ELSE ', ' + SDA.STATE
							END + ISNULL(' ' + SDA.ZipCode, '')
						)
			ELSE (
					CASE 
						WHEN SAD.AddressLine2 IS NULL
							THEN ''
						ELSE SAD.AddressLine2
						END + CASE 
						WHEN SAD.Street IS NULL
							THEN ''
						ELSE SAD.Street
						END + CASE 
						WHEN SAD.City IS NULL
							THEN ''
						ELSE ', ' + SAD.City
						END + CASE 
						WHEN SAD.STATE IS NULL
							THEN ''
						ELSE ', ' + SAD.STATE
						END + ISNULL(' ' + SAD.ZipCode, '')
					)
			END AS [ServicingDealerAddress]
		,ADE.BranchName AS SellingDealerName
		,ADE.BranchCode AS SellingDealerPartnerId
		,CASE 
			WHEN SDA.AddressLine2 IS NULL
				THEN ''
			ELSE SDA.AddressLine2
			END + CASE 
			WHEN SDA.Street IS NULL
				THEN ''
			ELSE SDA.Street
			END + CASE 
			WHEN SDA.City IS NULL
				THEN ''
			ELSE ', ' + SDA.City
			END + CASE 
			WHEN SDA.STATE IS NULL
				THEN ''
			ELSE ', ' + SDA.STATE
			END + ISNULL(' ' + SDA.ZipCode, '') AS [SellingDealerAddress]
		,OS.[NumberOfOperators]
		,VH.[NumberOfVehicles]
		,isnull(C.IsTemporary,0) as IsTemporary
        ,SL.ServiceLevels
        ,isnull(S.IsRentalSite,0) as IsRentalSite
	FROM App.Site S
		INNER JOIN App.[ProvisioningStatus] PS ON PS.ProvisioningStatusId = S.ProvisioningStatusId
		LEFT JOIN App.MvwSiteHierarchy SH ON SH.SiteId = S.SiteId
		INNER JOIN App.Company C ON C.CompanyId = S.CompanyId
		LEFT JOIN App.[Address] AD ON AD.AddressId = S.AddressId
		LEFT JOIN App.Branch AB ON AB.BranchId = SH.ServicingBranchId --ServicingDealer (We take Branch but name it as Dealer)
		LEFT JOIN App.[Address] SAD ON SAD.AddressId = AB.AddressId
		LEFT JOIN App.Branch ADE ON ADE.BranchId = SH.BranchId --SellingDealer
		LEFT JOIN App.[Address] SDA ON SDA.AddressId = ADE.AddressId
		LEFT JOIN #NumberOfOperators OS ON OS.SiteId = S.SiteId
		LEFT JOIN #NumberOfVehicles VH ON VH.SiteId = S.SiteId
		LEFT JOIN #ServiceLevels SL ON SL.SiteId = S.SiteId
END