Untitled
unknown
plain_text
8 days ago
6.0 kB
1
Indexable
Never
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
Leave a Comment