USE [SUPFD_TEST]
GO
/****** Object:  StoredProcedure [dbo].[usp_RemoveEquipmentOperation]    Script Date: 2/23/2017 11:40:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      mgonzale
-- Create date: 2016-25-10
-- Description: Removes Equipment Operations
--              cramirez 02.21.2017 When there's records on collect_schemas_operations_equipments and algo on schemas_operations_equipments it only updates the records to status= 'INA',
--                                  When there's not any record on collect that's when it deletes all assignments and all relations between the operation and the equipment
--              cramirez 02.21.2017 added parameter for user name when a record is set to ina
-- =============================================

EXEC dbo.usp_RemoveEquipmentOperation 'L24B', '055', 'P32', 'cramirez'

ALTER PROCEDURE [dbo].[usp_RemoveEquipmentOperation]
    @Equipment NVARCHAR(15)
   ,@AddedOperation NVARCHAR(500)
   ,@siteKey NVARCHAR(10)
   ,@USER NVARCHAR(50)
AS

DECLARE @DATE SMALLDATETIME = GETDATE()

BEGIN
    IF EXISTS (SELECT TOP 1 1 FROM dbo.collect_schemas_operations_equipments csoe WHERE csoe.scoe_equi_equipment_key = @Equipment AND csoe.opre_operation_key = @AddedOperation AND csoe.scoe_site_key = @siteKey AND csoe.record_status = 'ACT')
    BEGIN
        IF EXISTS (SELECT 1 FROM operations_equipments WHERE equi_equipment_key=@Equipment AND oper_operation_key  = @AddedOperation AND equi_site_key=@siteKey AND [STATUS] = 'ACT')
            IF EXISTS (SELECT 1 FROM dbo.schemas_operations_equipments soe WHERE soe.opeq_equi_equipment_key = @Equipment AND soe.opeq_operation_key = @AddedOperation AND soe.record_status = 'ACT')
            BEGIN

                UPDATE dbo.schemas_operations_equipments
                SET record_status = 'INA', modified_by = @USER, modified_on = @DATE
                WHERE opeq_operation_key = @AddedOperation AND opeq_equi_equipment_key = @Equipment AND opeq_site_key = @siteKey AND opeq_equi_site_key = @siteKey AND whst_site_key = @siteKey

                UPDATE  operations_Equipments
                SET [STATUS] = 'INA', modified_by = @USER, modified_on = @DATE
                WHERE
                equi_equipment_key=@Equipment AND oper_operation_key  = @AddedOperation AND oper_site_key = @siteKey AND equi_site_key = @siteKey
            END
            ELSE
            BEGIN

                /**DELETE RATES**/
                DELETE FROM dbo.operations_equipments_wheel_styles_rates
                WHERE opeq_equi_equipment_key = @equipment AND opeq_operation_key = @AddedOperation AND opeq_equi_site_key = @siteKey

                DELETE FROM dbo.operations_equipments WHERE
                equi_equipment_key = @Equipment AND oper_operation_key = @AddedOperation AND oper_site_key = @siteKey AND equi_site_key = @siteKey
            END
    END
    ELSE
    BEGIN
        IF EXISTS (SELECT 1 FROM operations_equipments WHERE equi_equipment_key=@Equipment AND oper_operation_key  = @AddedOperation AND equi_site_key=@siteKey AND [STATUS] = 'ACT')
        BEGIN
            IF EXISTS (SELECT 1 FROM dbo.schemas_operations_equipments soe WHERE soe.opeq_equi_equipment_key = @Equipment AND soe.opeq_operation_key = @AddedOperation AND soe.record_status = 'ACT')
            BEGIN
                DELETE FROM dbo.schemas_operations_equipments
                WHERE opeq_operation_key = @AddedOperation AND opeq_equi_equipment_key = @Equipment AND opeq_site_key = @siteKey AND opeq_equi_site_key = @siteKey AND whst_site_key = @siteKey

                /**DELETE RATES**/
                DELETE FROM dbo.operations_equipments_wheel_styles_rates
                WHERE opeq_equi_equipment_key = @equipment AND opeq_operation_key = @AddedOperation AND opeq_equi_site_key = @siteKey

                DELETE FROM operations_Equipments
                WHERE equi_equipment_key=@Equipment AND oper_operation_key  = @AddedOperation AND oper_site_key = @siteKey AND equi_site_key = @siteKey
            END
            ELSE
            BEGIN
                DELETE FROM operations_Equipments
                WHERE equi_equipment_key=@Equipment AND oper_operation_key  = @AddedOperation AND oper_site_key = @siteKey AND equi_site_key = @siteKey
            END
        END
    END
END

SELECT * FROM dbo.operations_equipments