-- ==========================================================
-- Create Stored Procedure sp_gps_device_configuration_deployment_reset_state
-- ==========================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Elysee Dagan
-- Create date: 31-08-2012
-- =============================================
IF OBJECT_ID('[dbo].[sp_gps_device_configuration_deployment_reset_state]') IS NOT NULL 
    BEGIN
        DROP PROCEDURE [dbo].[sp_gps_device_configuration_deployment_reset_state]
        IF OBJECT_ID('[dbo].[sp_gps_device_configuration_deployment_reset_state]') IS NOT NULL 
            PRINT '*** FAILED DROPPING PROCEDURE [dbo].[sp_gps_device_configuration_deployment_reset_state]***'
        ELSE 
            PRINT '*** DROPPED PROCEDURE [dbo].[sp_gps_device_configuration_deployment_reset_state]****'
    END
go
CREATE PROCEDURE [dbo].[sp_gps_device_configuration_deployment_reset_state] @imei VARCHAR(50) = NULL
AS 
    BEGIN
	 
        SET @imei = NULLIF(@imei, '')
	 
        IF @imei IS NOT NULL 
            BEGIN
                UPDATE  dbo.gps_device_configuration_deployment
                SET     [status] = 0 ,
                        number_of_try = ISNULL(number_of_try, 0) + 1 ,
                        last_try_date = GETDATE()
                WHERE   vehicle_id IN (
                        SELECT  vehicle_id
                        FROM    dbo.vehicle(NOLOCK) v 
                                JOIN dbo.gps_device(NOLOCK) g ON g.id = v.gps_device_id
                        WHERE   ( g.imei = @imei
                                  OR RIGHT(g.imei, 6) = @imei
                                ) )
                        AND [status] = 1
            END
        ELSE 
            BEGIN
                UPDATE  dbo.gps_device_configuration_deployment
                SET     [status] = 0
                WHERE   [status] = 1
            END
    END
  
 GO
GRANT EXEC ON [dbo].[sp_gps_device_configuration_deployment_reset_state] TO process_role     
 GO

IF OBJECT_ID('[dbo].[sp_gps_device_configuration_deployment_reset_state]') IS NOT NULL 
    PRINT '*** CREATED PROCEDURE [dbo].[sp_gps_device_configuration_deployment_reset_state] ***'
ELSE 
    PRINT '*** FAILED CREATING PROCEDURE [dbo].[sp_gps_device_configuration_deployment_reset_state] ***'
go