Untitled
unknown
plain_text
5 months ago
26 kB
3
Indexable
alter PROCEDURE [App].[usp_GetVehiclesBySiteId] ( @SiteId INT ,@UserId INT ,@searchstring NVARCHAR(MAX) = NULL ,@VehicleFilter [App].[VehicleFilter] READONLY ) AS /********************************************************************************************************************************************* ** STORED PROCEDURE : [dbo].[usp_GetVehiclesBySiteId] ** Author : DI Dev Team ** Date : 12/12/2017 ** Purpose : Get List of Vehicles by SiteId ** Arguments : None ** Returns : 0 ** Depends On : None ** Effects : None ** Invoked Via : Web Portal DECLARE @SiteId INT = 88887; DECLARE @UserId INT = 66254; DECLARE @searchstring NVARCHAR(MAX) = NULL; DECLARE @VehicleFilter [App].[VehicleFilter]; INSERT INTO @VehicleFilter VALUES (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 200, 0, NULL, NULL); EXEC [App].[usp_GetVehiclesBySiteId] @SiteId = @SiteId, @UserId = @UserId, @searchstring = @searchstring, @VehicleFilter = @VehicleFilter; **********************************************************************************************************************************************/ BEGIN EXECUTE [App].[usp_CheckUserEntityAccess] @UserId,@SiteId,'Site'; DECLARE @ManufacturerId AS INT ,@ManufacturerName AS NVARCHAR(100) ,@ManufacturerCode AS NVARCHAR(50) ,@TaskSelection INT ,@Checklist INT ,@AccessControlFeature BIT ,@LocationPlusFeature BIT ,@ImpactManagmentFeature BIT ,@StatusTextFilter NVARCHAR(100) = NULL SET @searchstring=REPLACE(@searchstring,'_','!_') DECLARE @query_columns NVARCHAR(MAX)=NULL,@query_columns2 NVARCHAR(MAX)='',@query_count NVARCHAR(MAX)=NULL,@query_tables1 NVARCHAR(MAX)=NULL,@query_tables2 NVARCHAR(MAX)=NULL,@query_orderby NVARCHAR(MAX)=NULL ,@Final_Select_Query1 NVARCHAR(MAX)=NULL,@Final_Select_Query2 NVARCHAR(MAX)=NULL,@query_total NVARCHAR(MAX)=NULL, @query_tables3 NVARCHAR(MAX)=NULL ,@query_columnsN1 NVARCHAR(MAX)= '', @query_tablesN3 NVARCHAR(MAX) = '' ,@IsPagination BIT = 0 ,@Status NVARCHAR(10) ,@VehicleName NVARCHAR(260) ,@CurrentOperator NVARCHAR(500) ,@CurrentOperatorAlias NVARCHAR(500) ,@FromDate DateTime ,@ToDate DateTime ,@VehicleGroupCsv NVARCHAR(MAX) ,@VehicleTypeName NVARCHAR(MAX) ,@VehicleModelNumber NVARCHAR(MAX) ,@VehicleManufacturerName NVARCHAR(MAX) ,@CertificationType NVARCHAR(MAX) ,@LockOutState NVARCHAR(MAX) ,@ByPassState NVARCHAR(MAX) ,@Top INT ,@Skip INT ,@SortColumn NVARCHAR(100) ,@SortOrder NVARCHAR(10) select @Status=case when [Status]='true' then 1 when [Status]='false' then 0 else NULL end ,@StatusTextFilter = case when [Status]='' then NULL else [status] end ,@VehicleName=[VehicleName] ,@CurrentOperator=[CurrentOperator] ,@CurrentOperatorAlias=[CurrentOperatorAlias] ,@FromDate=[FromDate] ,@ToDate=[ToDate] ,@VehicleGroupCsv=nullif([VehicleGroupCsv],'') ,@VehicleTypeName=nullif([VehicleTypeName],'') ,@VehicleModelNumber=nullif([VehicleModelNumber],'') ,@VehicleManufacturerName=nullif([VehicleManufacturerName],'') ,@CertificationType=nullif([CertificationType],'') ,@LockOutState=nullif([LockOutState],'') ,@ByPassState=nullif([ByPassState],'') ,@Top=isnull([Top],25) ,@Skip=ISNULL([Skip], 0) ,@SortColumn=ISNULL([SortColumn], 'VehicleName') ,@SortOrder=ISNULL([SortOrder], 'ASC') from @VehicleFilter set @VehicleGroupCsv= REPLACE(@VehicleGroupCsv,'@1#^',',') if @SortColumn = 'iPortEquippedDisplayText' set @SortColumn = 'IPortEquipped' if @SortColumn = 'noResponseEnabledDisplayText' set @SortColumn = 'NoResponseEnabled' if @SortColumn = 'extractEventsDisplayText' set @SortColumn = 'ExtractEvents' if @SortColumn = 'vehicleGroupName' set @SortColumn = 'VehicleGroupCsv' if @SortColumn = 'rtlsActiveEnabledDisplayText' set @SortColumn = 'RtlsActiveEnabled' --------override rtls active enabled column if @SortColumn = 'rtlsEnabledDisplayText' set @SortColumn = 'RtlsEnabled' --------override rtls enabled column if @SortColumn = 'currentConfigurationDisplayText' set @SortColumn = 'SyncStatus' if @SortColumn = 'objectSenseEnabledDisplayText' set @SortColumn = 'ObjectSenseEnabled' IF @SortColumn = 'gtsRtlsActiveEnabledDisplayText' SET @SortColumn = 'GtsRtlsActiveEnabled' IF @SortColumn = 'gtsRtlsEnabledDisplayText' SET @SortColumn = 'GtsRtlsEnabled' IF @SortColumn = 'gtsRtlsGroupNameDisplayText' SET @SortColumn = 'GtsRtlsEnabledGroupName' IF @SortColumn = 'integratedTetherEnabledDisplayText' SET @SortColumn = 'IntegratedTether' IF @SortColumn = 'pitToPedEnabledDisplayText' SET @SortColumn = 'PitToPedEnabled' IF @SortColumn = 'vdaEnabledDisplayText' SET @SortColumn = 'VdaEnabled' if @SortColumn = 'statusDisplayText' BEGIN set @SortColumn = '[Status]' if @SortOrder = 'ASC' begin set @SortOrder = 'DESC' end else begin set @SortOrder = 'ASC' end END /*if @top is not null and @skip is not null set @IsPagination=1 */ SET @searchstring=REPLACE(@searchstring,'''','''''') SET @searchstring=REPLACE(@searchstring,'[','![') set @query_total = 'select * from (' set @query_count = 'select count(1) as ROW_COUNT from (' EXECUTE [App].[usp_GetManufacturerBySiteId] @SiteId ,@ManufacturerId OUTPUT ,@ManufacturerName OUTPUT ,@UserId; SELECT @TaskSelection = TaskSelection ,@Checklist = Checklists ,@AccessControlFeature = AccessControl ,@LocationPlusFeature = LocationPlus ,@ImpactManagmentFeature = Impacts FROM [App].[udf_GetPricingTierFeatureBySiteId](@SiteId) SELECT @ManufacturerCode = ManufacturerCode FROM [App].[Manufacturer] WHERE [ManufacturerId] = @ManufacturerId; DROP TABLE IF EXISTS #Gen2SyncStatus CREATE TABLE #Gen2SyncStatus(VehicleId INT, SyncStatus INT) INSERT INTO #Gen2SyncStatus select A.VehicleId,CalculatedStatus from ( select V.VehicleId, CalculatedStatus,row_number() over(partition by V.VehicleId order by VCCS.ModifiedDate desc) as roworder from [App].[Vehicle] V LEFT JOIN [App].[VehicleConfigurationChangeStore] VCCS ON V.VEhicleId = VCCS.VehicleId WHERE V.SiteId = @SiteId ) A where roworder = 1 create nonclustered index idx_gen2syncstatus on #gen2syncstatus(vehicleid) include(SyncStatus) UPDATE A SET A.SyncStatus = 5 FROM #Gen2SyncStatus A INNER JOIN [App].[ConfigurationChangeStore] CCS ON A.VehicleId = CCS.VehicleId WHERE SyncStatus IS NULL AND CCS.[Status] = 0 AND CCS.SiteId = @SiteId DROP TABLE IF EXISTS #VehicleGroup CREATE TABLE #VehicleGroup ( VehicleId INT ,GroupNames NVARCHAR(max) ,GroupName NVARCHAR(max) ) INSERT INTO #VehicleGroup SELECT [VehicleId] ,STRING_AGG(CAST(G.GroupName AS NVARCHAR(MAX)), ',') GroupNames ,STRING_AGG(CAST((REPLACE(G.GroupName,',','@1#^')) AS NVARCHAR(MAX)),',') WITHIN GROUP(ORDER BY GroupName) GroupName FROM( SELECT DISTINCT [VehicleId] ,GP.GroupName FROM [App].[VehicleGroup] AS VGS INNER JOIN [App].[Group] AS GP ON VGS.[GroupId] = GP.[GroupId] WHERE VGS.SiteId = @SiteId AND GP.[Status] = 1 AND VGS.[IsActive] = 1 ) AS G GROUP BY [VehicleId] CREATE NONCLUSTERED INDEX [NIX_#VehicleGroup_VehicleId] ON #VehicleGroup (VehicleId) INCLUDE (GroupName) IF (@ManufacturerCode = 'T') BEGIN IF(@searchstring like '%Install %') BEGIN select @searchstring = replace(@searchstring,' ','') END END --TOYOTA BEGIN set @query_columns = 'select * from (SELECT VH.SiteId,VH.[VehicleId] ,VH.[VehicleName] ,VH.SAPVehicleTypeId ,VHT.[VehicleTypeName] ,VM.[ModelNumber] AS [VehicleModelNumber] ,ISNULL(VM.[ImageName], ''Default.jpg'') AS [VehicleImage] ,CASE WHEN VH.InstallReady = 1 THEN NULL ELSE ( SELECT ''Base Device''+ISNULL('',''+STRING_AGG(b.peripheral,'',''),'''') FROM [App].[VehicleHardwarePeripherals] b WHERE b.VehicleId = VH.VehicleId and b.connected = 1)END AS DetectedHardwareDetails ,SP.ShiftProfileName AS VehicleShift ,VHM.[VehicleManufacturerId] ,VHM.[VehicleManufacturerName] ,VH.[VehicleIdentificationNumber] AS [Vin] ,CASE WHEN (VH.InstallReady = 1 AND VH.[Status] = 1 ) THEN NULL else D.DeviceSerialNumber END AS DeviceId ,VG.GroupName AS [VehicleGroupCsv] ,VG.GroupNames AS [TempVehicleGroupCsv] ,(SELECT STRING_AGG(CAST(PTF.PricingTierFeatureDescription AS NVARCHAR(MAX)),'','') WITHIN GROUP(ORDER BY PricingTierFeatureDescription) FROM [App].[VehiclePricingTier] AS VPT INNER JOIN [App].[PricingTierFeature] AS PTF ON PTF.PricingTierFeatureId=VPT.PricingTierFeatureId WHERE VH.[VehicleId]=VPT.[VehicleId] AND VPT.IsActive=1 ) AS [PricingTierFeatureCsv] ,CASE WHEN VCC.VehicleConfigCurrent=''Yes'' THEN 1 ELSE 0 END AS [CurrentConfiguration] ,VNCA.[LastCommunicatedTime] AS [LastCommunication] ,TTD.[LastActivity] AS [LastActivity] ,CASE WHEN '+CAST(@Checklist AS NVARCHAR(1))+'=1 THEN CL.[ChecklistName] ELSE NULL END AS [AssignedChecklist] ,CASE WHEN '+CAST(@Checklist AS NVARCHAR(1))+'=1 THEN CLM.[ChecklistModeDescription] ELSE NULL END AS [ChecklistMode] ,CASE WHEN '+CAST(@Checklist AS NVARCHAR(1))+'=1 THEN CLF.[FrequencyHrs] ELSE NULL END AS [ChecklistFrequency] ,CASE WHEN '+CAST(@Checklist AS NVARCHAR(1))+'=1 THEN CLF.[FrequencyDescription] ELSE NULL END AS [FrequencyDescription] ,IPF.ImpactProfileName AS [AccelerometerGroup] ,substring(CONVERT(nvarchar,DATEADD(ms,VH.[InactiveTimeoutValue]*1000,0),108),4,5) AS [InactivityTimeoutDisplayText] ,[HibernationTimeoutValue] AS [HibernationTimeout] ,CASE WHEN VH.InstallReady <> 1 THEN ISNULL(LS.[LockState],''Not Available'') ELSE NULL END AS [LockoutState] ,CASE WHEN VH.InstallReady <> 1 THEN ISNULL(BS.[BypassStateDescription],''Not Available'') ELSE NULL END AS [BypassState] ,NULL AS [IMonitorSoftwareVersion] ,NULL AS [IMonitorOperatorSystemVersion] ,NULL AS [IPortEquipped] ,VH.[SerialNumber] ,VH.[NoResponseEnabled] ,VH.[ExtractEvents] ,TTD.[KeyHours] AS [KeyHours] ,TTD.[TravelHours] AS [TravelHours] ,CASE WHEN (VH.InstallReady = 1 AND VH.[Status] = 1 ) THEN NULL else VH.[InstallationDate] END AS [InstallationDate] ,TTD.[ActivityHours] AS [ActivityHours] ,TTD.[LiftHours] AS [LiftHourMeter] ,TTD.[OperatorPresenceHours] AS [OperatorPresenceHours] ,NULL AS [DeviceSerialNumber] ,NULL AS [CANBridgeSerialNumber] , D.DeviceModelNumber AS [TelemetryDeviceModel] ,VH.[YearOfManufacture] ,CONCAT_WS('' '', O.[FirstName], O.[LastName]) AS CurrentOperator ,O.[Alias] AS CurrentOperatorAlias ,VH.[HideFromReporting] ,VH.IsCanBusEnabled ,(SELECT STRING_AGG(CAST(Z.ZoneName AS NVARCHAR(MAX)),'','') WITHIN GROUP(ORDER BY ZoneName) FROM [App].[Zone] AS Z WHERE ZoneCoordinates.STIntersects(''POINT(''+CAST(CAST(TTD.Longitude AS DECIMAL(18,7)) AS NVARCHAR(20))+'' ''+CAST(CAST(TTD.Latitude AS DECIMAL(18,7)) AS NVARCHAR(20))+'')'')=1 AND IsActive=1 AND SiteId='+CAST(@SiteId AS NVARCHAR(6))+' ) AS [ZoneNameCsv] ,[JobCodeVersion] ,VH.[Status] ,VH.[VehicleConfigVersion] AS RevisionNumber ,NULL AS AppliedRevisionNumber ,CASE WHEN VH.MCVPVID IS NULL THEN D2C.VehicleConfigCurrentId ELSE SyncStatus END AS VehicleConfigCurrentId ,ACM.AccessControlModeDescription AS AccessControlModesText ,VH.[IsCheckListMandatory] AS IsCheckListMandatory ,D2C.LastOperatorOperated as LastOperatorName ,D2C.[LastLoginTime]' set @query_columns2=',D.DeviceType ,CASE WHEN VH.InstallReady = 1 THEN NULL ELSE D2C.BatteryStateOfCharge END AS BatteryStateOfCharge ,VH.InstallReady ,CASE WHEN (VH.InstallReady = 1 AND VH.[Status] = 1 ) THEN ''InstallReady'' WHEN (VH.InstallReady = 0 AND VH.[Status] = 1 ) THEN ''Connected'' WHEN (VH.InstallReady = 0 AND VH.[Status] = 0 ) THEN ''Deleted'' WHEN (VH.InstallReady = 1 AND VH.[Status] = 0 ) THEN ''Deleted'' WHEN (VH.InstallReady is null AND VH.[Status] = 0 ) THEN ''Deleted'' else '''' END AS statusDisplayTextToyota ,CASE WHEN D2C.BatteryVoltage IS NULL THEN ''N/A'' else Convert(NVARCHAR,STR(ROUND(D2C.BatteryVoltage / 1000.0 ,1,1),10,1)) END AS BatteryVoltage ,D.WifiMac2G AS WifiMac2G ,D.WifiMac5G AS WifiMac5G ,CASE WHEN TTD.[KHSource]=1 THEN ''AM'' WHEN TTD.[KHSource]=0 THEN ''VI'' ELSE NULL END AS KeyHourSource ,CASE WHEN TTD.[LHSource]=1 THEN ''AM'' WHEN TTD.[LHSource]=0 THEN ''VI'' ELSE NULL END AS LiftHourSource ,CASE WHEN TTD.[THSource]=1 THEN ''AM'' WHEN TTD.[THSource]=0 THEN ''VI'' ELSE NULL END AS TravellHourSource ,CASE WHEN TTD.[AHSource]=1 THEN ''AM'' WHEN TTD.[AHSource]=0 THEN ''VI'' ELSE NULL END AS ActivityHourSource ,CASE WHEN TTD.[OPSource]=1 THEN ''AM'' WHEN TTD.[OPSource]=0 THEN ''VI'' ELSE NULL END AS OperatorPresenceSource' set @query_tables1 = ' FROM [App].[Vehicle](NOLOCK) VH LEFT JOIN [App].[ShiftProfile] SP ON VH.ShiftProfileId=SP.ShiftProfileId LEFT JOIN [App].[VehicleManufacturer] VHM ON VH.[VehicleManufacturerId]=VHM.[VehicleManufacturerId] LEFT JOIN [App].[Vehicle_Toyota_D2C](NOLOCK) AS D2C ON D2C.VehicleId=VH.VehicleId LEFT JOIN [App].[VehicleConfigCurrent] VCC ON VCC.VehicleConfigCurrentId=D2C.VehicleConfigCurrentId LEFT JOIN [App].[VehicleModel] VM ON VH.[VehicleModelId]=VM.[VehicleModelId] LEFT JOIN [App].[VehicleType] VHT ON VH.[VehicleTypeId]=VHT.[VehicleTypeId] LEFT JOIN [App].[CheckList] CL ON VH.[CheckListId]=CL.[CheckListId] LEFT JOIN [App].[CheckListMode] CLM ON VH.[ChecklistModeId]=CLM.[ChecklistModeId] LEFT JOIN [App].[CheckListFrequency] CLF ON CLF.[CheckListFrequencyID]=VH.[CheckListFrequencyID] LEFT JOIN [App].[ImpactProfile] IPF ON IPF.ImpactProfileId=VH.ImpactProfileId LEFT JOIN [App].[LockoutState] LS ON D2C.[LockoutStateId]=LS.[LockoutStateId] LEFT JOIN [App].[BypassState] BS ON D2C.[BypassStateId]=BS.[BypassStateId] LEFT JOIN [App].[TruckTelemetryData_Toyota](NOLOCK) TTD ON VH.[VehicleId]=TTD.[VehicleId] AND TTD.SiteId=VH.SiteId AND TTD.[Status]=1 LEFT JOIN [App].[Operator] O ON O.[OperatorId]=TTD.[OperatorId] LEFT JOIN [App].[DeviceVehicle] DV ON DV.VehicleId=VH.VehicleId AND DV.IsActive=1 LEFT JOIN [App].[Device] D ON D.DeviceId=DV.DeviceId LEFT JOIN #VehicleGroup VG ON VG.VehicleId=VH.VehicleId LEFT JOIN [App].[AccessControlMode] ACM ON ACM.AccessControlModeId=VH.AccessControlModeId LEFT JOIN #Gen2SyncStatus VCCS ON VH.VehicleId=VCCS.VehicleId LEFT JOIN [App].[VehicleNotCommunicatingAlert] VNCA ON VH.VehicleId = VNCA.VehicleId WHERE VH.[SiteId] ='+CAST(@SiteId AS NVARCHAR(6))+' AND((VH.[Status] = 1 AND VH.[InstallReady] = 1 ) OR (VH.[Status] = 1 AND VH.[InstallReady] = 0 and (DV.Isactive = 0 or DV.Isactive is not null)) OR(VH.[Status] = 0 AND (VH.[InstallReady] = 0 OR VH.[InstallReady] is null)) OR (VH.[Status] = 0 AND VH.[InstallReady] = 1)))A UNION ALL' set @query_tables3 = ' (SELECT VH.SiteId,VH.[VehicleId] ,VH.[VehicleName] ,VH.SAPVehicleTypeId ,VHT.[VehicleTypeName] ,VM.[ModelNumber] AS [VehicleModelNumber] ,ISNULL(VM.[ImageName], ''Default.jpg'') AS [VehicleImage] ,NULL as [DetectedHardwareDetails] ,SP.ShiftProfileName AS VehicleShift ,VHM.[VehicleManufacturerId] ,VHM.[VehicleManufacturerName] ,VH.[VehicleIdentificationNumber] AS [Vin] ,NULL AS DeviceId ,NULL AS [VehicleGroupCsv] ,NULL AS [TempVehicleGroupCsv] ,NULL AS [PricingTierFeatureCsv] ,NULL AS [CurrentConfiguration] ,NULL AS [LastCommunication] ,NULL AS [LastActivity] ,NULL AS [AssignedChecklist] ,NULL AS [ChecklistMode] ,NULL AS [ChecklistFrequency] ,NULL AS [FrequencyDescription] ,NULL AS [AccelerometerGroup] ,NULL AS [InactivityTimeoutDisplayText] ,NULL AS [HibernationTimeout] ,''Not Available'' AS [LockoutState] ,''Not Available'' AS [BypassState] ,NULL AS [IMonitorSoftwareVersion] ,NULL AS [IMonitorOperatorSystemVersion] ,NULL AS [IPortEquipped] ,VH.[SerialNumber] ,NULL AS [NoResponseEnabled] ,NULL AS [ExtractEvents] ,NULL AS [KeyHours] ,NULL AS [TravelHours] ,NULL AS [InstallationDate] ,NULL AS [ActivityHours] ,NULL AS [LiftHourMeter] ,NULL AS [OperatorPresenceHours] ,NULL AS [DeviceSerialNumber] ,NULL AS [CANBridgeSerialNumber] ,NULL AS [TelemetryDeviceModel] ,VH.[YearOfManufacture] ,NULL AS CurrentOperator ,NULL AS CurrentOperatorAlias ,NULL AS [HideFromReporting] ,VH.IsCanBusEnabled ,NULL AS [ZoneNameCsv] ,NULL AS [JobCodeVersion] ,0 AS [Status] ,NULL AS RevisionNumber ,NULL AS AppliedRevisionNumber ,NULL AS VehicleConfigCurrentId ,NULL AS AccessControlModesText ,NULL AS IsCheckListMandatory ,NULL AS LastOperatorName ,NULL AS [LastLoginTime] ,NULL AS [DeviceType] ,NULL AS [BatteryStateOfCharge] , VH.InstallReady ,''Deleted'' as statusDisplayTextToyota ,NULL AS BatteryVoltage ,NULL As WifiMac2G ,NULL AS WifiMac5G ,NULL AS KeyHourSource ,NULL AS LiftHourSource ,NULL AS TravellHourSource ,NULL AS ActivityHourSource ,NULL AS OperatorPresenceSource FROM [App].[VehicleSiteHistory] VSH INNER JOIN App.Vehicle VH ON VSH.VehicleID=VH.VehicleId AND VH.SiteId <> '+CAST(@SiteId AS NVARCHAR(6))+' LEFT JOIN [App].[ShiftProfile] SP ON VH.ShiftProfileId=SP.ShiftProfileId INNER JOIN [App].[VehicleManufacturer] VHM ON VH.[VehicleManufacturerId]=VHM.[VehicleManufacturerId] INNER JOIN [App].[VehicleModel] VM ON VH.[VehicleModelId]=VM.[VehicleModelId] LEFT JOIN [App].[VehicleType] VHT ON VH.[VehicleTypeId]=VHT.[VehicleTypeId] WHERE VSH.[SiteId]='+CAST(@SiteId AS NVARCHAR(6))+'))t where t.[SiteId]='+CAST(@SiteId AS NVARCHAR(6))+' AND 1=1'+ case when @StatusTextFilter is not null then ' and [StatusDisplayTextToyota] IN (SELECT [Value] FROM STRING_SPLIT('''+@StatusTextFilter+''', '',''))' else '' end+ case when @VehicleName is not null then ' and VehicleName like ''%'+@VehicleName+'%''' else '' end+ case when @CurrentOperator is not null then ' and CurrentOperator like ''%'+@CurrentOperator+'%''' else '' end+ case when @CurrentOperatorAlias is not null then ' and CurrentOperatorAlias like ''%'+@CurrentOperatorAlias+'%''' else '' end+ case when @FromDate is not null and @ToDate is not null then ' and (LastLoginTime between '''+CONVERT(NVARCHAR,@FromDate,121)+''' AND '''+CONVERT(NVARCHAR,@ToDate,121)+''')' else '' end+ case when @VehicleGroupCsv is not null then ' and EXISTS (SELECT value FROM STRING_SPLIT('''+ @VehicleGroupCsv+''', '','') WHERE (TempVehicleGroupCsv like ''%''+ value+''%'' ) )'else '' end+ case when @VehicleTypeName is not null then ' and VehicleTypeName IN (SELECT [Value] FROM STRING_SPLIT('''+ @VehicleTypeName +''', '',''))' else '' end+ case when @VehicleModelNumber is not null then ' and VehicleModelNumber IN (SELECT [Value] FROM STRING_SPLIT('''+ @VehicleModelNumber +''', '',''))' else '' end+ case when @LockOutState is not null then ' and LS.LockoutStateDescription IN (SELECT [Value] FROM STRING_SPLIT('''+ @LockOutState +''', '',''))' else '' end+ case when @ByPassState is not null then ' and BS.ByPassStateDescription IN (SELECT [Value] FROM STRING_SPLIT('''+ @ByPassState +''', '',''))' else '' end+ case when @VehicleManufacturerName is not null then ' and VehicleManufacturerName IN (SELECT [Value] FROM STRING_SPLIT('''+ @VehicleManufacturerName +''', '',''))' else '' end set @query_tables2=''+case when @searchstring is not null then +' and (VehicleId LIKE ''%' + @searchstring + '%'' escape ''!'' or VehicleName LIKE ''%' + @searchstring + '%'' escape ''!'' or VehicleTypeName LIKE ''%' + @searchstring + '%'' escape ''!'' or VehicleModelNumber LIKE ''%' + @searchstring + '%'' escape ''!'' -- or VehicleImage LIKE ''%' + @searchstring + '%'' escape ''!'' or VehicleShift LIKE ''%' + @searchstring + '%'' escape ''!'' or VehicleManufacturerId LIKE ''%' + @searchstring + '%'' escape ''!'' or VehicleManufacturerName LIKE ''%' + @searchstring + '%'' escape ''!'' or Vin LIKE ''%' + @searchstring + '%'' escape ''!'' or DeviceId LIKE ''%' + @searchstring + '%'' escape ''!'' or VehicleGroupCsv LIKE ''%' + @searchstring + '%'' escape ''!'' or PricingTierFeatureCsv LIKE ''%' + @searchstring + '%'' escape ''!'' or CurrentConfiguration LIKE ''%' + @searchstring + '%'' escape ''!'' or LastCommunication LIKE ''%' + @searchstring + '%'' escape ''!'' or LastActivity LIKE ''%' + @searchstring + '%'' escape ''!'' or AssignedChecklist LIKE ''%' + @searchstring + '%'' escape ''!'' or ChecklistMode LIKE ''%' + @searchstring + '%'' escape ''!'' or ChecklistFrequency LIKE ''%' + @searchstring + '%'' escape ''!'' or FrequencyDescription LIKE ''%' + @searchstring + '%'' escape ''!'' or AccelerometerGroup LIKE ''%' + @searchstring + '%'' escape ''!'' or InactivityTimeoutDisplayText LIKE ''%' + @searchstring + '%'' escape ''!'' or HibernationTimeout LIKE ''%' + @searchstring + '%'' escape ''!'' or LockoutState LIKE ''%' + @searchstring + '%'' escape ''!'' or BypassState LIKE ''%' + @searchstring + '%'' escape ''!'' or IMonitorSoftwareVersion LIKE ''%' + @searchstring + '%'' escape ''!'' or IMonitorOperatorSystemVersion LIKE ''%' + @searchstring + '%'' escape ''!'' or IPortEquipped LIKE ''%' + @searchstring + '%'' escape ''!'' or SerialNumber LIKE ''%' + @searchstring + '%'' escape ''!'' or NoResponseEnabled LIKE ''%' + @searchstring + '%'' escape ''!'' or ExtractEvents LIKE ''%' + @searchstring + '%'' escape ''!'' or KeyHours LIKE ''%' + @searchstring + '%'' escape ''!'' or TravelHours LIKE ''%' + @searchstring + '%'' escape ''!'' or InstallationDate LIKE ''%' + @searchstring + '%'' escape ''!'' or ActivityHours LIKE ''%' + @searchstring + '%'' escape ''!'' or LiftHourMeter LIKE ''%' + @searchstring + '%'' escape ''!'' or OperatorPresenceHours LIKE ''%' + @searchstring + '%'' escape ''!'' or DeviceSerialNumber LIKE ''%' + @searchstring + '%'' escape ''!'' or CANBridgeSerialNumber LIKE ''%' + @searchstring + '%'' escape ''!'' or TelemetryDeviceModel LIKE ''%' + @searchstring + '%'' escape ''!'' or YearOfManufacture LIKE ''%' + @searchstring + '%'' escape ''!'' or CurrentOperator LIKE ''%' + @searchstring + '%'' escape ''!'' or CurrentOperatorAlias LIKE ''%' + @searchstring + '%'' escape ''!'' or LastLoginTime LIKE ''%' + @searchstring + '%'' escape ''!'' or HideFromReporting LIKE ''%' + @searchstring + '%'' escape ''!'' or IsCanBusEnabled LIKE ''%' + @searchstring + '%'' escape ''!'' or ZoneNameCsv LIKE ''%' + @searchstring + '%'' escape ''!'' or JobCodeVersion LIKE ''%' + @searchstring + '%'' escape ''!'' --or StatusText LIKE ''%' + @searchstring + '%'' escape ''!'' or RevisionNumber LIKE ''%' + @searchstring + '%'' escape ''!'' or AppliedRevisionNumber LIKE ''%' + @searchstring + '%'' escape ''!'' or VehicleConfigCurrentId LIKE ''%' + @searchstring + '%'' escape ''!'' or AccessControlModesText LIKE ''%' + @searchstring + '%'' escape ''!'' or IsCheckListMandatory LIKE ''%' + @searchstring + '%'' escape ''!'' or LastOperatorName LIKE ''%' + @searchstring + '%'' escape ''!'' or DeviceType LIKE ''%' + @searchstring + '%'' escape ''!'' or WifiMac2G LIKE ''%' + @searchstring + '%'' escape ''!'' or WifiMac5G LIKE ''%' + @searchstring + '%'' escape ''!'' or BatteryStateOfCharge LIKE ''%' + @searchstring + '%'' escape ''!'' Or statusDisplayTextToyota LIKE ''%' + @searchstring + '%'' escape ''!'')' else '' end + '' END set @query_orderby=' ORDER BY '+@SortColumn+' '+@SortOrder+' OFFSET '+CAST(@Skip AS NVARCHAR(6))+' ROWS FETCH NEXT '+CAST(@Top AS NVARCHAR(6))+' ROWS ONLY' IF (@SortColumn = 'BatteryStateOfCharge' AND @ManufacturerCode = 'T') set @query_orderby=' ORDER BY CASE WHEN BatteryStateOfCharge IS NULL THEN 1 ELSE 0 END, BatteryStateOfCharge ' + @SortOrder+' OFFSET '+CAST(@Skip AS NVARCHAR(6))+' ROWS FETCH NEXT '+CAST(@Top AS NVARCHAR(6))+' ROWS ONLY' SET @Final_Select_Query1 = @query_total+@query_columns+@query_columnsN1+@query_columns2+@query_tables1+@query_tablesN3+@query_tables3+@query_tables2+@query_orderby SET @Final_Select_Query2 = @query_count+@query_columns+@query_columnsN1+@query_columns2+@query_tables1+@query_tablesN3+@query_tables3+@query_tables2 EXEC SP_EXECUTESQL @Final_Select_Query1; EXEC SP_EXECUTESQL @Final_Select_Query2; END --select * from App.SubscriptionPackage --select * from App.Vehicle where subscriptionpackageid=1 --update App.Vehicle set SubscriptionPackageId =1 where VehicleId=865855
Editor is loading...
Leave a Comment