Untitled
unknown
plain_text
6 months ago
15 kB
5
Indexable
--CREATE PROCEDURE [App].[usp_GetSiteProvisioningByManufacturerId]
declare @ManufacturerId INT =1
,@UserId INT =66643
,@searchstring NVARCHAR(30) = '0000000002'
,@SiteProvisioning [App].[SiteFilter]
insert into @SiteProvisioning values (null,null,null,null,null,null,null,null,null,null,null,null,NULL,25,0,
null,null)
--AS
BEGIN
--EXEC [App].[usp_CheckUserEntityAccess] @UserId
-- ,@ManufacturerId
-- ,'Manufacturer'
DECLARE @Query_total NVARCHAR(max)=null,@query_columns NVARCHAR(MAX)=NULL,@query_count NVARCHAR(MAX)=NULL,@query_tables NVARCHAR(MAX)=NULL,@query_orderby NVARCHAR(MAX)=NULL
,@Final_Select_Query1 NVARCHAR(MAX)=NULL,@Final_Select_Query2 NVARCHAR(MAX)=NULL
,@IsPagination BIT = 0
,@CompanyCode nvarchar(max)
,@ServicingDealerPartnerId nvarchar(max)
,@SellingDealerPartnerId nvarchar(max)
,@ProvisioningStatus nvarchar(max)
,@ServiceLevels nvarchar(max)
,@IsRentalSite bit
,@Top INT
,@Skip INT
,@SortColumn NVARCHAR(30)
,@SortOrder NVARCHAR(10)
,@DesiredFirmwareVersion NVARCHAR(MAX) = NULL
,@TelematicsDataCollectionStatus NVARCHAR(MAX) = NULL
,@PrivacyModeStatus NVARCHAR(MAX) = NULL
,@SiteGroups NVARCHAR(MAX) = NULL
,@StateList NVARCHAR(MAX) = NULL
,@CountryList NVARCHAR(MAX) = NULL
SELECT @CompanyCode = nullif([PartnerId],'')
,@ServicingDealerPartnerId = nullif([ServicingDealerPartnerId],'')
,@SellingDealerPartnerId = nullif([SellingDealerPartnerId],'')
,@ProvisioningStatus = nullif([ProvisioningStatus],'')
,@ServiceLevels = nullif([ServiceLevels],'')
,@IsRentalSite =case when [IsRentalSite]='true' then 1 when [IsRentalSite]='false' then 0 else NULL end
,@Skip = ISNULL([Skip], 0)
,@Top = [Top]
,@SortColumn = ISNULL([SortColumn], 'SiteId')
,@SortOrder = ISNULL([SortOrder], 'ASC')
,@DesiredFirmwareVersion = NULLIF(DesiredFirmwareVersion, '')
,@TelematicsDataCollectionStatus = nullif([TelematicsDataCollectionStatus],'')
,@PrivacyModeStatus = nullif([PrivacyModeStatus], '')
,@SiteGroups = nullif([SiteGroups], '')
,@StateList = nullif([StateList], '')
,@CountryList = nullif(LTRIM([CountryList]), '')
FROM @SiteProvisioning
IF (@SortColumn = 'PrivacyModeStatus')
BEGIN
SET @SortColumn = 'PrivacyMode'
END
DECLARE @StateXML AS XML
DECLARE @CountryXML AS XML
SET @StateXML = CAST(('<X>'+REPLACE(@StateList,',' ,'</X><X>')+'</X>') AS XML)
SET @CountryXML = CAST(('<X>'+REPLACE(@CountryList,',' ,'</X><X>')+'</X>') AS XML)
DECLARE @StateCountryList TABLE (ID INT IDENTITY,StateCountryList NVARCHAR(50))
DECLARE @Statetemp TABLE(ID INT IDENTITY,StateList NVARCHAR(50))
DECLARE @Countrytemp TABLE (ID INT IDENTITY,CountryList NVARCHAR(50))
INSERT INTO @Statetemp
SELECT N.value('.', 'NVARCHAR(50)') AS StateList FROM @StateXML.nodes('X') AS T(N)
INSERT INTO @Countrytemp
SELECT N.value('.', 'NVARCHAR(50)') AS CountryList FROM @CountryXML.nodes('X') AS T(N)
INSERT INTO @StateCountryList
SELECT StateList+','+CountryList As StateCountryList FROM @Statetemp s join @Countrytemp c on s.id=c.id
Declare @StateCountryLists NVARCHAR(MAX) = (select STRING_AGG(CAST(StateCountryList AS NVARCHAR(MAX)), '&') list from @StateCountryList)
SET @searchstring=REPLACE (@searchstring,'''','''''')
SET @searchstring=REPLACE(@searchstring,'[','![')
DECLARE @vFeatureToggle INT = ISNULL((SELECT TOP 1 1 FROM [App].[FeatureAssignedToggle] WHERE [FeatureName]='T_85871_UNDERSCORE_WILDCARD_MANF_DLR_BRN' AND [FeatureAssignedToggle]=1 ),0)
IF @vFeatureToggle = 1
BEGIN
SET @searchstring=REPLACE(@searchstring,'_','!_')
END
if @Skip is not null and @Top is not null
begin
set @IsPagination = 1
set @query_count = 'select count(1) as ROW_COUNT from ('
set @query_orderby = ' ORDER BY ' + @SortColumn + ' ' + @SortOrder
end
drop table if exists #ServiceLevels
CREATE TABLE #ServiceLevels (
SiteId INT,
ServiceLevels NVARCHAR(MAX)
)
INSERT INTO #ServiceLevels(
SiteId,
ServiceLevels
)
SELECT S.SiteId,STRING_AGG(CAST(PTF.PricingTierFeatureDescription AS NVARCHAR(MAX)), ',') WITHIN GROUP(ORDER BY PricingTierFeatureDescription) AS ServiceLevels
FROM App.[Site] S
-- INNER JOIN App.[MvwSiteHierarchy_Toy] 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
DROP TABLE IF EXISTS #SiteGroups , #LevelAddress
SELECT SG.SiteId
,STRING_AGG(CAST(SGD.GroupName AS NVARCHAR(MAX)), ',') WITHIN GROUP(ORDER BY GroupName) GroupName
INTO #SiteGroups
FROM [App].[SiteGroup] AS SG
INNER JOIN [App].[SiteGroupDetail] AS SGD ON SG.[GroupId] = SGD.[GroupId]
INNER JOIN [App].[Site] S ON S.SiteId = SG.SiteId
WHERE SGD.[Status] = 1
AND SG.[Status] = 1
GROUP BY SG.SiteId
CREATE NONCLUSTERED INDEX [NIX_#SiteGroups] ON #SiteGroups (SiteId)
CREATE NONCLUSTERED INDEX [NIX_#ServiceLevels] ON #ServiceLevels (SiteId)
SELECT S.siteid, CASE WHEN (S.ServicingBranchId IS NULL OR S.ServicingBranchId = - 1)
THEN B.BranchName ELSE ABE.BranchName END AS ServicingDealerName
,CASE WHEN (S.ServicingBranchId IS NULL OR S.ServicingBranchId = - 1)
THEN B.BranchCode ELSE ABE.BranchCode END AS ServicingDealerPartnerId
,CASE WHEN (S.ServicingBranchId IS NULL OR S.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]
,B.BranchName AS SellingDealerName,B.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]
INTO #LevelAddress
FROM App.Site S
INNER JOIN App.Company C ON C.CompanyId = S.CompanyId
INNER JOIN App.Branch B ON B.BranchId=C.SellingBranchId
LEFT JOIN App.[Address] SDA ON SDA.AddressId = B.AddressId
LEFT JOIN App.Branch ABE ON ABE.BranchId = S.ServicingBranchId
LEFT JOIN App.[Address] SAD ON SAD.AddressId = ABE.AddressId
INNER JOIN App.Dealer D ON D.DealerId = B.DealerId
INNER JOIN App.Manufacturer M ON M.ManufacturerId = D.ManufacturerId
WHERE C.IsTemporary = 0
CREATE NONCLUSTERED INDEX [NIX_#LevelAddress] ON #LevelAddress (SiteId)
set @Query_total = 'select * from ('
set @query_columns = 'SELECT ProvisioningStatus,S.SiteId,S.SiteName,S.TelematicsDataCollectionStatus,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
,L.ServicingDealerName
,L.ServicingDealerPartnerId
,L.[ServicingDealerAddress]
,L.SellingDealerName
,L.SellingDealerPartnerId
,L.[SellingDealerAddress]
,(select count(o.operatorid) from app.operatorsite os
inner join app.operator o on o.OperatorId = os.OperatorId
where o.isresetter=0 and os.status=1 and os.siteid=s.siteid) as NumberOfOperators
,(select count(v.vehicleid) from app.vehicle v
inner join app.devicevehicle dv on dv.vehicleid = v.vehicleid
where dv.isactive =1 and v.status = 1
and v.siteid = s.siteid) as NumberOfVehicles
,isnull(C.IsTemporary,0) as IsTemporary,
SL.ServiceLevels,
isnull(S.IsRentalSite,0) as IsRentalSite,
F.FirmwareFileId as DeviceFirmwareFileId,
F.CurrentFirmwareVersion as DesiredFirmwareVersion,
SG.GroupName AS SiteGroups,
S.PrivacyModeStatus As PrivacyMode,
AD.[State] AS [State],
AD.[Country] AS [Country]
'
set @query_tables = '
FROM App.Site S
INNER JOIN App.[ProvisioningStatus] PS ON PS.ProvisioningStatusId = S.ProvisioningStatusId
LEFT JOIN App.[MvwSiteHierarchy_Toy] 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
inner join #LevelAddress L ON L.SiteId = S.SiteId
LEFT JOIN #ServiceLevels SL ON SL.SiteId = S.SiteId
INNER JOIN [App].[SiteSettings] SS ON SS.SiteId=S.SiteId
LEFT JOIN [App].[FirmwareFile] F ON F.FirmwareFileId= SS.DesiredFirmwareVersionId
LEFT JOIN #SiteGroups SG ON SG.SiteId = S.SiteId
WHERE 1=1 ' +
case when @CompanyCode is not null then ' and C.CompanyCode IN (SELECT [Value] FROM STRING_SPLIT( '''+ @CompanyCode +''', '',''))' else '' end+
case when @SellingDealerPartnerId is not null then ' and ADE.BranchCode IN (SELECT [Value] FROM STRING_SPLIT( '''+ @SellingDealerPartnerId +''', '',''))' else '' end+
case when @ServiceLevels is not null then ' and exists (SELECT 1 FROM STRING_SPLIT(SL.ServiceLevels, '','') WHERE '', ''+LTRIM(@ServiceLevels)+'','' LIKE ''%, ''+LTRIM([Value])+'',%'')' else '' end+
case when @TelematicsDataCollectionStatus is not null then ' and TelematicsDataCollectionStatus IN (SELECT [Value] FROM STRING_SPLIT( '''+ @TelematicsDataCollectionStatus +''', '',''))' else '' end+
case when @ProvisioningStatus is not null then ' and ProvisioningStatus IN (SELECT [Value] FROM STRING_SPLIT( '''+ @ProvisioningStatus +''', '',''))' else '' end+') t'+
case when @ServicingDealerPartnerId is not null then ' where ServicingDealerPartnerId in (SELECT [Value] FROM STRING_SPLIT( '''+@ServicingDealerPartnerId+''', '',''))' else ' where 1=1' end+
case when @IsRentalSite is not null then ' and IsRentalSite = '+ CAST(@IsRentalSite as NVARCHAR(1)) else '' END +
CASE WHEN @PrivacyModeStatus IS NOT NULL THEN ' and PrivacyMode IN (SELECT [Value] FROM STRING_SPLIT( ''' + @PrivacyModeStatus + ''', '',''))' ELSE '' END +
CASE WHEN @StateCountryLists IS NOT NULL THEN ' AND ([State] +'',''+[Country]) IN (SELECT LTRIM([Value]) FROM STRING_SPLIT( '''+ @StateCountryLists +''', ''&'')) ' ELSE '' END +
case when @DesiredFirmwareVersion is not null then ' and DeviceFirmwareFileId IN (SELECT [Value] FROM STRING_SPLIT( '''+ @DesiredFirmwareVersion +''', '','')) ' else '' end +
case when @SiteGroups is not null then ' and exists (SELECT 1 FROM STRING_SPLIT(SiteGroups, '','') WHERE '', ''+LTRIM(@SiteGroups)+'','' LIKE ''%, ''+LTRIM([Value])+'',%'')' else '' end+
+case when @searchstring is not null then +'
and (ProvisioningStatus LIKE ''%' + @searchstring + '%'' escape ''!''
or SiteId LIKE ''%' + @searchstring + '%'' escape ''!''
or SiteName LIKE ''%' + @searchstring + '%'' escape ''!''
or PartnerId LIKE ''%' + @searchstring + '%'' escape ''!''
--or StoreNumber LIKE ''%' + @searchstring + '%'' escape ''!''
or Address LIKE ''%' + @searchstring + '%'' escape ''!''
or ParentCompany LIKE ''%' + @searchstring + '%'' escape ''!''
or ServicingDealerName LIKE ''%' + @searchstring + '%'' escape ''!''
or ServicingDealerPartnerId LIKE ''%' + @searchstring + '%'' escape ''!''
or ServicingDealerAddress LIKE ''%' + @searchstring + '%'' escape ''!''
or SellingDealerName LIKE ''%' + @searchstring + '%'' escape ''!''
or SellingDealerPartnerId LIKE ''%' + @searchstring + '%'' escape ''!''
or SellingDealerAddress LIKE ''%' + @searchstring + '%'' escape ''!''
or NumberOfOperators LIKE ''%' + @searchstring + '%'' escape ''!''
or NumberOfVehicles LIKE ''%' + @searchstring + '%'' escape ''!''
or IsTemporary LIKE ''%' + @searchstring + '%'' escape ''!''
or ServiceLevels LIKE ''%' + @searchstring + '%'' escape ''!''
or SiteGroups LIKE ''%' + @searchstring + '%'' escape ''!''
or IsRentalSite LIKE ''%' + @searchstring + '%'' escape ''!'')' else '' end + ''
if (@IsPagination = 1)
begin
set @query_orderby += ' OFFSET '+CAST(@Skip AS NVARCHAR(6))+' ROWS'+CASE WHEN @Top IS NOT NULL THEN+' FETCH NEXT '+CAST(@Top AS NVARCHAR(6))+' ROWS ONLY' ELSE '' END
set @Final_Select_Query1 = @Query_total+@query_columns+@query_tables+@query_orderby
set @Final_Select_Query2 = @query_count+@query_columns+@query_tables
EXEC SP_EXECUTESQL @Final_Select_Query1,N'@ServiceLevels nvarchar(max),@SiteGroups nvarchar(max)',@ServiceLevels ,@SiteGroups
EXEC SP_EXECUTESQL @Final_Select_Query2,N'@ServiceLevels nvarchar(max),@SiteGroups nvarchar(max)',@ServiceLevels ,@SiteGroups
--print @Final_Select_Query1
--print @Final_Select_Query2
end
else
begin
set @Final_Select_Query1 = @Query_total+@query_columns+@query_tables
EXEC SP_EXECUTESQL @Final_Select_Query1,N'@ServiceLevels nvarchar(max),@SiteGroups nvarchar(max)',@ServiceLevels ,@SiteGroups
--print @Final_Select_Query1
end
ENDEditor is loading...
Leave a Comment