Untitled

 avatar
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