SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
Patch parallela per usare C# e Python contemporaneamente.

Questa patch NON modifica gli oggetti legacy usati dal C#.

Il C# continua a usare:
- dbo.XMag_ViewPackingList
- dbo.ViewPackingListRestante
- dbo.sp_xExePackingListPallet
- dbo.sp_xExePackingListPalletPrenota
- dbo.sp_ControllaPrenotazionePackingListPalletNew

Il Python usa invece:
- dbo.py_XMag_ViewPackingList
- dbo.py_ViewPackingListRestante
- dbo.py_sp_xExePackingListPallet
- dbo.py_sp_xExePackingListPalletPrenota
- dbo.py_sp_ControllaPrenotazionePackingListPalletNew
- dbo.PyPickingListReservation
*/

IF OBJECT_ID(N'dbo.PyPickingListReservation', N'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[PyPickingListReservation](
        [ID] [tinyint] NOT NULL,
        [Documento] [varchar](8) NULL,
        [IDOperatore] [int] NULL,
        [ModUtente] [varchar](50) NULL,
        [ModDataOra] [datetime] NULL,
        CONSTRAINT [PK_PyPickingListReservation] PRIMARY KEY CLUSTERED ([ID] ASC),
        CONSTRAINT [CK_PyPickingListReservation_Singleton] CHECK ([ID] = 1)
    ) ON [PRIMARY];
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.PyPickingListReservation WHERE ID = 1)
BEGIN
    INSERT INTO dbo.PyPickingListReservation (ID, Documento, IDOperatore, ModUtente, ModDataOra)
    VALUES (1, NULL, NULL, NULL, GETDATE());
END
GO

CREATE OR ALTER VIEW [dbo].[py_XMag_ViewPackingList]
AS
SELECT TOP 10000
    legacy.Pallet,
    legacy.Lotto,
    legacy.Articolo,
    legacy.Descrizione,
    legacy.Qta,
    legacy.Documento,
    legacy.CodNazione,
    legacy.NAZIONE,
    legacy.Stato,
    legacy.PalletCella,
    legacy.Magazzino,
    legacy.Area,
    legacy.Cella,
    legacy.Ordinamento,
    legacy.Ubicazione,
    legacy.DEST,
    CASE
        WHEN pr.Documento IS NOT NULL
         AND pr.Documento = CAST(legacy.Documento AS varchar(8))
        THEN 1
        ELSE 0
    END AS IDStato
FROM dbo.XMag_ViewPackingList legacy
LEFT JOIN dbo.PyPickingListReservation pr
    ON pr.ID = 1
   AND NULLIF(LTRIM(RTRIM(pr.Documento)), '') IS NOT NULL
ORDER BY
    CASE
        WHEN pr.Documento IS NOT NULL
         AND pr.Documento = CAST(legacy.Documento AS varchar(8))
        THEN 1
        ELSE 0
    END DESC,
    legacy.Documento,
    legacy.Ordinamento;
GO

CREATE OR ALTER VIEW [dbo].[py_ViewPackingListRestante]
AS
SELECT
    Pallet,
    Lotto,
    Articolo,
    Descrizione,
    Qta,
    Documento,
    CodNazione,
    NAZIONE,
    Stato,
    PalletCella,
    Magazzino,
    Area,
    Cella,
    Ordinamento,
    Ubicazione,
    DEST,
    IDStato
FROM dbo.py_XMag_ViewPackingList
WHERE Cella <> 9999;
GO

CREATE OR ALTER PROCEDURE [dbo].[py_sp_xExePackingListPallet]
    @IDOperatore int,
    @Documento varchar(8),
    @Azione char(1) = 'P',
    @RC int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET @RC = 0;

    DECLARE @Nominativo varchar(50) = '';
    DECLARE @DocumentoAttivo varchar(8) = NULL;
    DECLARE @Description varchar(255) = '';
    DECLARE @Message varchar(255) = '';
    DECLARE @IDResult int = 0;
    DECLARE @ID int = 0;

    SELECT @Nominativo = [Login]
    FROM dbo.Operatori
    WHERE ID = @IDOperatore;

    IF @Nominativo IS NULL
        SET @Nominativo = 'SYSTEM';

    IF @Azione NOT IN ('P', 'S')
    BEGIN
        SET @RC = -10;
        RETURN;
    END;

    IF NOT EXISTS (
        SELECT 1
        FROM dbo.py_XMag_ViewPackingList
        WHERE CAST(Documento AS varchar(8)) = @Documento
    )
    BEGIN
        SET @RC = -20;
        RETURN;
    END;

    IF NOT EXISTS (SELECT 1 FROM dbo.PyPickingListReservation WHERE ID = 1)
    BEGIN
        INSERT INTO dbo.PyPickingListReservation (ID, Documento, IDOperatore, ModUtente, ModDataOra)
        VALUES (1, NULL, NULL, NULL, GETDATE());
    END;

    SELECT @DocumentoAttivo = NULLIF(LTRIM(RTRIM(Documento)), '')
    FROM dbo.PyPickingListReservation
    WHERE ID = 1;

    DECLARE @TargetCelle TABLE (
        IDCella int PRIMARY KEY
    );

    INSERT INTO @TargetCelle (IDCella)
    SELECT DISTINCT Cella
    FROM dbo.py_XMag_ViewPackingList
    WHERE CAST(Documento AS varchar(8)) = @Documento
      AND Cella IS NOT NULL;

    IF @Azione = 'P'
    BEGIN
        IF @DocumentoAttivo = @Documento
            RETURN;

        UPDATE c
        SET c.IDStato = 0,
            c.ModUtente = @Nominativo,
            c.ModDataOra = GETDATE()
        FROM dbo.Celle c
        WHERE ISNULL(c.IDStato, 0) <> 0;

        UPDATE c
        SET c.IDStato = 1,
            c.ModUtente = @Nominativo,
            c.ModDataOra = GETDATE()
        FROM dbo.Celle c
        INNER JOIN @TargetCelle t ON t.IDCella = c.ID;

        UPDATE dbo.PyPickingListReservation
        SET Documento = @Documento,
            IDOperatore = @IDOperatore,
            ModUtente = @Nominativo,
            ModDataOra = GETDATE()
        WHERE ID = 1;

        SELECT TOP 1 @Description = NAZIONE
        FROM dbo.py_XMag_ViewPackingList
        WHERE CAST(Documento AS varchar(8)) = @Documento;

        EXEC dbo.sp_LogPackingList
             @ID = @ID,
             @Code = @Documento,
             @Description = @Description,
             @Message = @Message OUTPUT,
             @IDResult = @IDResult OUTPUT;

        RETURN;
    END;

    IF @Azione = 'S'
    BEGIN
        IF ISNULL(@DocumentoAttivo, '') <> @Documento
            RETURN;

        UPDATE c
        SET c.IDStato = 0,
            c.ModUtente = @Nominativo,
            c.ModDataOra = GETDATE()
        FROM dbo.Celle c
        INNER JOIN @TargetCelle t ON t.IDCella = c.ID;

        UPDATE dbo.PyPickingListReservation
        SET Documento = NULL,
            IDOperatore = @IDOperatore,
            ModUtente = @Nominativo,
            ModDataOra = GETDATE()
        WHERE ID = 1;

        RETURN;
    END;
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[py_sp_xExePackingListPalletPrenota]
    @IDOperatore int,
    @Documento varchar(8),
    @RC int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET @RC = 0;

    DECLARE @Nominativo varchar(50) = '';

    SELECT @Nominativo = LOGIN
    FROM dbo.Operatori
    WHERE ID = @IDOperatore;

    IF @Nominativo IS NULL
        SET @Nominativo = 'SYSTEM';

    UPDATE c
    SET c.IDStato = 1,
        c.ModUtente = @Nominativo,
        c.ModDataOra = GETDATE()
    FROM dbo.Celle c
    WHERE c.ID IN (
        SELECT DISTINCT Cella
        FROM dbo.py_ViewPackingListRestante
        WHERE CAST(Documento AS varchar(8)) = @Documento
          AND Cella IS NOT NULL
    );
END;
GO

CREATE OR ALTER PROCEDURE [dbo].[py_sp_ControllaPrenotazionePackingListPalletNew]
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Documento varchar(8) = NULL;
    DECLARE @IDOperatore int = 0;
    DECLARE @Nominativo varchar(50) = 'SYSTEM';
    DECLARE @RC int = 0;

    SELECT
        @Documento = NULLIF(LTRIM(RTRIM(Documento)), ''),
        @IDOperatore = ISNULL(IDOperatore, 0),
        @Nominativo = ISNULL(NULLIF(LTRIM(RTRIM(ModUtente)), ''), 'SYSTEM')
    FROM dbo.PyPickingListReservation
    WHERE ID = 1;

    IF ISNULL(@Documento, '') = ''
        RETURN;

    IF NOT EXISTS (
        SELECT 1
        FROM dbo.py_ViewPackingListRestante
        WHERE CAST(Documento AS varchar(8)) = @Documento
    )
    BEGIN
        UPDATE dbo.Celle
        SET IDStato = 0,
            ModUtente = @Nominativo,
            ModDataOra = GETDATE()
        WHERE ISNULL(IDStato, 0) <> 0;

        UPDATE dbo.PyPickingListReservation
        SET Documento = NULL,
            IDOperatore = @IDOperatore,
            ModUtente = @Nominativo,
            ModDataOra = GETDATE()
        WHERE ID = 1;

        RETURN;
    END;

    UPDATE dbo.Celle
    SET IDStato = 0,
        ModUtente = @Nominativo,
        ModDataOra = GETDATE()
    WHERE ISNULL(IDStato, 0) <> 0;

    IF @IDOperatore <= 0
    BEGIN
        SELECT TOP 1 @IDOperatore = ID
        FROM dbo.Operatori
        WHERE LOGIN = @Nominativo;
    END;

    EXEC dbo.py_sp_xExePackingListPalletPrenota
         @IDOperatore = @IDOperatore,
         @Documento = @Documento,
         @RC = @RC OUTPUT;
END;
GO