Untitled
unknown
plain_text
6 months ago
10 kB
2
Indexable
CREATE PROCEDURE [App].[usp_GetSiteProvisioningByManufacturerId]
AS
BEGIN
DECLARE @ManufacturerId INT = 1,
@UserId INT = 66643,
@searchstring NVARCHAR(30) = '0000000002',
@SiteProvisioning [App].[SiteFilter],
@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,
@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;
-- Initialize @SiteProvisioning here as required.
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;
-- Adjust sort column
IF (@SortColumn = 'PrivacyModeStatus')
SET @SortColumn = 'PrivacyMode';
-- Convert state and country lists to XML for easier parsing
DECLARE @StateXML AS XML = CAST(('<X>' + REPLACE(@StateList, ',', '</X><X>') + '</X>') AS XML);
DECLARE @CountryXML AS XML = 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);
-- Sanitize the search string
SET @searchstring = REPLACE(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
SET @searchstring = REPLACE(@searchstring, '_', '!_');
-- Pagination parameters
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].[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 #LevelAddress;
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
(ISNULL(SDA.AddressLine2, '') + ISNULL(SDA.Street, '') +
ISNULL(', ' + SDA.City, '') + ISNULL(', ' + SDA.STATE, '') +
ISNULL(' ' + SDA.ZipCode, ''))
ELSE
(ISNULL(SAD.AddressLine2, '') + ISNULL(SAD.Street, '') +
ISNULL(', ' + SAD.City, '') + ISNULL(', ' + SAD.STATE, '') +
ISNULL(' ' + SAD.ZipCode, ''))
END AS [ServicingDealerAddress],
B.BranchName AS SellingDealerName,
B.BranchCode AS SellingDealerPartnerId,
ISNULL(SDA.AddressLine2, '') + ISNULL(SDA.Street, '') +
ISNULL(', ' + SDA.City, '') + ISNULL(', ' + SDA.STATE, '') +
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;
-- Initialize @query_tables with the tables used in the SELECT statement
SET @query_tables = 'FROM App.[Site] S
LEFT JOIN #ServiceLevels SL ON SL.SiteId = S.SiteId
LEFT JOIN App.Manufacturer M ON M.ManufacturerId = S.ManufacturerId
LEFT JOIN App.Dealer D ON D.DealerId = S.DealerAssignedBranchId
LEFT JOIN #LevelAddress AS A ON A.SiteId = S.SiteId';
SET @query_total = 'SELECT * FROM (';
SET @query_columns = 'SELECT S.SiteId,
S.SiteName,
S.VIN,
S.ChassisNo,
S.CreationDateTime,
S.SellDateTime,
S.FirmwareVersion,
S.FirmwareVersionDateTime,
S.BOMVersion,
S.CreatedByUserId,
S.UpdatedByUserId,
S.UpdatedDateTime,
S.DealerAssignedBranchId,
SL.ServiceLevels,
S.TelematicsDataCollectionStatus,
S.PrivacyModeStatus,
@ServiceLevels AS ServiceLevels,
CASE
WHEN S.PrivacyModeStatus = 1 THEN 1
ELSE 0
END AS PrivacyMode,
@StateCountryLists AS StateCountryList,
@DesiredFirmwareVersion AS DesiredFirmwareVersion,
CASE WHEN D.DealerId IS NOT NULL THEN 1 ELSE 0 END AS HasDealer,
CASE WHEN D.DealerId IS NOT NULL THEN D.DealerId ELSE NULL END AS DealerId,
CASE WHEN D.DealerId IS NOT NULL THEN D.DealerName ELSE NULL END AS DealerName,
CASE WHEN S.SiteId IS NOT NULL THEN 1 ELSE 0 END AS IsValidSite,
A.ServicingDealerName,
A.ServicingDealerPartnerId,
A.SellingDealerName,
A.SellingDealerPartnerId,
A.SellingDealerAddress,
A.ServicingDealerAddress
' + @query_tables + ' WHERE (S.CompanyId = @ManufacturerId)
AND (S.VIN LIKE ''%'' + @searchstring + ''%'' OR
S.ChassisNo LIKE ''%'' + @searchstring + ''%'' OR
S.SiteName LIKE ''%'' + @searchstring + ''%'' OR
S.SiteId LIKE ''%'' + @searchstring + ''%'')
AND (M.ManufacturerId IS NULL OR M.ManufacturerId = @ManufacturerId)
AND (S.DealerAssignedBranchId IS NULL OR S.DealerAssignedBranchId IN (
SELECT DealerId
FROM App.Dealer
WHERE SellingBranchId = @UserId
))';
-- Pagination and ordering
IF @IsPagination = 1
BEGIN
SET @query_total += @query_count + @query_orderby + ') AS TotalCount;'
EXEC sp_executesql @query_total;
END
-- Final result
SET @query_total += @query_columns + ') AS Result;'
EXEC sp_executesql @query_total;
END
Editor is loading...
Leave a Comment