Untitled
unknown
plain_text
6 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=865855Editor is loading...
Leave a Comment