Asignacion de cargas

USE [KarghoUS]
GO
/** Object:  StoredProcedure [dbo].[ProcessTruckRecommendations]    Script Date: 7/8/2024 10:12:11 **/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ProcessTruckRecommendations]
AS
BEGIN
    DECLARE @IdTruck uniqueidentifier; 
	DECLARE @EquipmentCarrierId uniqueidentifier;
    DECLARE @EquipmentId BIGINT;

    DECLARE cur_trucks CURSOR FOR
    SELECT Id
    FROM Truck
    WHERE Status = 1
    ORDER BY NEWID();

    OPEN cur_trucks;
    FETCH NEXT FROM cur_trucks INTO @IdTruck;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Inicializar @EquipmentId como NULL para cada iteración del bucle
        SET @EquipmentId = NULL;
		SET @EquipmentCarrierId = NULL;

        -- Obtener el Id del equipo asociado al camión si existe
        SELECT @EquipmentId = Id,
			@EquipmentCarrierId = IdCarrier
        FROM Equipment
        WHERE IdTruck = @IdTruck;

        -- Verificar si se encontró un equipo asociado
        IF (@EquipmentId IS NOT NULL)
        BEGIN
            -- Lógica para cargar y verificar recomendaciones
            DECLARE @ExistAcceptedRec BIT = 0;
			DECLARE @ExistAssignedRec BIT = 0;
			DECLARE @ExistAssignedRecWaiting BIT = 0;
			DECLARE @NotSameNotRespondedCarrier BIT = 0;
			DECLARE @NotSameRejectCarrier BIT = 0;
			DECLARE @NotSameRejectedCarrierByShipper BIT = 0;
			
			DECLARE @NearLoadId uniqueidentifier;

			DECLARE @currentDateTime DATETIME = GETDATE();

            -- Variables para comparación de países
            DECLARE @equipmentCarrierUser BIGINT;
            DECLARE @loadCountry BIGINT;
            DECLARE @differentCountries BIT;

            -- Iterar sobre las cargas cercanas
            DECLARE cur_nearloads CURSOR FOR
            SELECT Id
            FROM Load WHERE IdStatus IS NULL;

            OPEN cur_nearloads;
            FETCH NEXT FROM cur_nearloads INTO @NearLoadId;

            WHILE @@FETCH_STATUS = 0
            BEGIN
				DECLARE	@return_value BIGINT;
				SET @return_value = NULL

                SELECT @ExistAcceptedRec = CASE WHEN EXISTS (
                    SELECT 1
                    FROM Recommendation
                    WHERE IdLoad = @NearLoadId AND Status = 3
                ) THEN 1 ELSE 0 END;

                IF (@ExistAcceptedRec = 1)
                BEGIN
				FETCH NEXT FROM cur_nearloads INTO @NearLoadId;
                    CONTINUE; 
                END
				--
				SELECT @ExistAssignedRec = CASE WHEN EXISTS (
                    SELECT 1
                    FROM Recommendation
                    WHERE IdLoad = @NearLoadId AND Status = 5
                ) THEN 1 ELSE 0 END;

                IF (@ExistAssignedRec = 1)
                BEGIN
				FETCH NEXT FROM cur_nearloads INTO @NearLoadId;
                    CONTINUE; 
                END
				--
				SELECT @ExistAssignedRecWaiting = CASE WHEN EXISTS (
                    SELECT 1
                    FROM Recommendation
                    WHERE IdLoad = @NearLoadId AND Status = 1
                ) THEN 1 ELSE 0 END;

                IF (@ExistAssignedRecWaiting = 1)
                BEGIN
					FETCH NEXT FROM cur_nearloads INTO @NearLoadId;
                    CONTINUE; 
                END
				-- carrier condiciones
				SELECT @NotSameNotRespondedCarrier = CASE WHEN EXISTS (
				    SELECT 1
					FROM Recommendation
					WHERE IdLoad = @NearLoadId
					AND Status = 1
					AND ExpiryDate <= @currentDateTime
					AND IdCarrier = @EquipmentCarrierId
				) THEN 1 ELSE 0 END;
				IF (@NotSameNotRespondedCarrier = 1)
					BEGIN
						FETCH NEXT FROM cur_nearloads INTO @NearLoadId;
						CONTINUE; 
					END
				--
				SELECT @NotSameRejectCarrier = CASE WHEN EXISTS (
				    SELECT 1
					FROM Recommendation
					WHERE IdLoad = @NearLoadId
					AND Status = 2
					AND IdCarrier = @EquipmentCarrierId
				) THEN 1 ELSE 0 END;
				IF (@NotSameRejectCarrier = 1)
					BEGIN
					FETCH NEXT FROM cur_nearloads INTO @NearLoadId;
					CONTINUE; 
				END
				--
				SELECT @NotSameRejectedCarrierByShipper = CASE WHEN EXISTS (
				    SELECT 1
					FROM Recommendation
					WHERE IdLoad = @NearLoadId
					AND Status = 4
					AND IdCarrier = @EquipmentCarrierId
				) THEN 1 ELSE 0 END;
				IF (@NotSameRejectedCarrierByShipper = 1)
					BEGIN
						FETCH NEXT FROM cur_nearloads INTO @NearLoadId;
						CONTINUE; 
				END
                --
				SELECT @equipmentCarrierUser = IdCountry
				FROM [User]
				WHERE IdCarrier = @EquipmentCarrierId;

				SELECT @loadCountry = IdCountry
				FROM [Load]
				WHERE Id = @NearLoadId;

				SET @differentCountries = CASE 
					WHEN @equipmentCarrierUser <> @loadCountry 
					THEN 1 
					ELSE 0 
				END;

				IF (@differentCountries = 1)
				BEGIN
					FETCH NEXT FROM cur_nearloads INTO @NearLoadId;
					CONTINUE; 
				END	
				--

				DECLARE @ExpiryDate DATETIME;
				SET @ExpiryDate = DATEADD(MINUTE, 5, GETDATE());

				DECLARE @Now DATETIME;
				SET @Now = GETDATE(); 

				INSERT INTO Recommendation(IdLoad, IdCarrier, IdEquipment, Status, ExpiryDate, created_at, updated_at)
				VALUES (@NearLoadId, @EquipmentCarrierId, @EquipmentId, 1, @ExpiryDate, @Now, @Now);
				SET @return_value = @EquipmentId;
				SELECT	'@return_value' = @return_value
                FETCH NEXT FROM cur_nearloads INTO @NearLoadId;
            END;

            CLOSE cur_nearloads;
            DEALLOCATE cur_nearloads;
        END

        FETCH NEXT FROM cur_trucks INTO @IdTruck;
    END;

    CLOSE cur_trucks;
    DEALLOCATE cur_trucks;
END;