-- ==========================================================
-- Create Stored Procedure sp_gps_device_configuration_deployment_change_state
-- ==========================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Elysee Dagan
-- Create date: 13-10-2012
-- =============================================
IF OBJECT_ID('[dbo].[sp_gps_device_configuration_deployment_change_state]') IS NOT NULL 
    BEGIN
        DROP PROCEDURE [dbo].[sp_gps_device_configuration_deployment_change_state]
        IF OBJECT_ID('[dbo].[sp_gps_device_configuration_deployment_change_state]') IS NOT NULL 
            PRINT '*** FAILED DROPPING PROCEDURE [dbo].[sp_gps_device_configuration_deployment_change_state]***'
        ELSE 
            PRINT '*** DROPPED PROCEDURE [dbo].[sp_gps_device_configuration_deployment_change_state]****'
    END
go
CREATE PROCEDURE [dbo].[sp_gps_device_configuration_deployment_change_state]
    @id INT ,
    @status INT ,
    @device_response VARCHAR(250)
AS 
    BEGIN
        DECLARE @activation BIT
        DECLARE @geofence_assigned_area_identifier_id SMALLINT
        DECLARE @vehicleID INT
        DECLARE @config_type SMALLINT
		
        SELECT  @activation = [activation] ,
                @geofence_assigned_area_identifier_id = geofence_assigned_area_identifier_id ,
                @vehicleID = vehicle_id ,
                @config_type = gps_device_configuration_type
        FROM    dbo.gps_device_configuration_deployment
        WHERE   id = @id
		
       
        UPDATE  dbo.gps_device_configuration_deployment
        SET     [status] = @status ,
                deployed_date = GETDATE() ,
                device_response = @device_response,
                number_of_try = ISNULL(number_of_try,0) +1,
                last_try_date = GETDATE()
        WHERE   id = @id

        IF @activation = 0
            AND @geofence_assigned_area_identifier_id IS NOT NULL
            AND @config_type = 1 
            BEGIN
                UPDATE  dbo.gps_device_configuration_deployment
                SET     geofence_assigned_area_identifier_id = NULL
                WHERE   vehicle_id = @vehicleID
                        AND geofence_assigned_area_identifier_id = @geofence_assigned_area_identifier_id
            END

    END
  
 GO
GRANT EXEC ON [dbo].[sp_gps_device_configuration_deployment_change_state] TO process_role     
 GO

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