/*
Patch online - form storiche Python

Contenuto:
- crea/aggiorna le viste Python-only usate dalla form "Storico Picking List"
- non modifica stored procedure legacy C#
- non modifica le viste legacy C#
- non crea oggetti per "Storico movimenti UDC", perche' quella form legge in sola lettura:
  dbo.MagazziniPallet, dbo.Celle, dbo.XMag_GiacenzaPalletPlistChiuse

Oggetti creati/aggiornati:
- dbo.py_vPreparaPackingListSAMA1
- dbo.py_vPreparaPackingList
- dbo.py_XMag_ViewPackingListStorico
*/

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

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_vPreparaPackingListSAMA1]
AS
SELECT
    SAMA1.dbo.LOTSER.NUMLOT,
    SAMA1.dbo.ARTICO.CODICE,
    SAMA1.dbo.FATRIG.DESCR,
    LEFT(SAMA1.dbo.LOTSER.NUMSER, 6) AS UDC,
    SUM(SAMA1.dbo.LOTSER.QTTGIAI) AS Qta,
    SAMA1.dbo.BAMTES.NUMDOC,
    SAMA1.dbo.BAMTES.DATDOC,
    SAMA1.dbo.BAMTES.ID,
    SAMA1.dbo.BAMTES.DESCRDEST,
    SAMA1.dbo.BAMTES.STATO AS StatoDocumento,
    SAMA1.dbo.NAZIONI.CODICE AS Expr1,
    SAMA1.dbo.MTRASP.CODICE + '  ' + SAMA1.dbo.NAZIONI.DESCR AS NAZIONE,
    SAMA1.dbo.BAMTES.IDMTRASP
FROM SAMA1.dbo.NAZIONI
INNER JOIN SAMA1.dbo.BAMTES
    ON SAMA1.dbo.NAZIONI.ID = SAMA1.dbo.BAMTES.IDNAZDEST
RIGHT OUTER JOIN SAMA1.dbo.LOTTIBF
LEFT OUTER JOIN SAMA1.dbo.LOTSER
    ON SAMA1.dbo.LOTTIBF.IDLOTSER = SAMA1.dbo.LOTSER.ID
LEFT OUTER JOIN SAMA1.dbo.ARTICO
    ON SAMA1.dbo.LOTSER.IDARTICO = SAMA1.dbo.ARTICO.ID
LEFT OUTER JOIN SAMA1.dbo.FATRIG
    ON SAMA1.dbo.LOTTIBF.IDFATRIG = SAMA1.dbo.FATRIG.ID
    ON SAMA1.dbo.BAMTES.ID = SAMA1.dbo.FATRIG.IDBAM
LEFT OUTER JOIN SAMA1.sam.EXTUC
    ON LEFT(SAMA1.dbo.LOTSER.NUMSER, 6) = SAMA1.sam.EXTUC.CODICE
LEFT OUTER JOIN SAMA1.dbo.MTRASP
    ON SAMA1.dbo.BAMTES.IDMTRASP = SAMA1.dbo.MTRASP.ID
WHERE
    SAMA1.dbo.BAMTES.ANNDOC >= YEAR(GETDATE())
    AND SAMA1.dbo.BAMTES.STATO IN ('P', 'D')
    AND SAMA1.dbo.LOTSER.NUMLOT <> '00000000000'
GROUP BY
    SAMA1.dbo.LOTSER.NUMLOT,
    SAMA1.dbo.ARTICO.CODICE,
    SAMA1.dbo.FATRIG.DESCR,
    LEFT(SAMA1.dbo.LOTSER.NUMSER, 6),
    SAMA1.dbo.BAMTES.NUMDOC,
    SAMA1.dbo.BAMTES.DATDOC,
    SAMA1.dbo.BAMTES.ID,
    SAMA1.dbo.BAMTES.DESCRDEST,
    SAMA1.dbo.BAMTES.STATO,
    SAMA1.dbo.NAZIONI.CODICE,
    SAMA1.dbo.NAZIONI.DESCR,
    SAMA1.dbo.BAMTES.IDMTRASP,
    SAMA1.dbo.MTRASP.CODICE;
GO

CREATE OR ALTER VIEW [dbo].[py_vPreparaPackingList]
AS
SELECT
    NUMLOT,
    CODICE,
    DESCR,
    UDC,
    Qta,
    NUMDOC,
    DATDOC,
    ID,
    DESCRDEST,
    StatoDocumento,
    Expr1,
    NAZIONE
FROM dbo.py_vPreparaPackingListSAMA1
WHERE NUMLOT <> '';
GO

CREATE OR ALTER VIEW [dbo].[py_XMag_ViewPackingListStorico]
AS
SELECT TOP (100000)
    prep.UDC AS Pallet,
    prep.NUMLOT AS Lotto,
    prep.CODICE AS Articolo,
    prep.DESCR AS Descrizione,
    prep.Qta,
    prep.NUMDOC AS Documento,
    prep.DATDOC AS DataDocumento,
    prep.StatoDocumento,
    prep.Expr1 AS CodNazione,
    prep.NAZIONE,
    CASE
        WHEN prep.Expr1 = 'DE' THEN 10
        WHEN prep.Expr1 = 'TH' THEN
            CASE WHEN SUBSTRING(prep.DESCRDEST, 1, 2) = 'NA' THEN 11 ELSE 13 END
        WHEN prep.Expr1 = 'MEX' THEN 12
        ELSE 4
    END AS Stato,
    ISNULL(g.NumeroPallet, 0) AS PalletCella,
    ISNULL(g.IDMagazzino, 1) AS Magazzino,
    ISNULL(g.IDArea, 5) AS Area,
    ISNULL(g.IDCella, 1000) AS Cella,
    ISNULL(c.Ordinamento, 99999) AS Ordinamento,
    ISNULL(c.Corsia + ' - ' + c.Colonna + ' - ' + c.Fila, 'Non scaff.') AS Ubicazione,
    SUBSTRING(prep.DESCRDEST, 1, 2) AS DEST,
    CASE
        WHEN MAX(CASE
            WHEN pr.Documento IS NOT NULL
             AND pr.Documento = CAST(prep.NUMDOC AS varchar(8))
            THEN 1 ELSE 0 END) = 1
        THEN 1
        ELSE 0
    END AS IDStato
FROM dbo.Celle c
INNER JOIN dbo.XMag_GiacenzaPallet g
    ON c.ID = g.IDCella
RIGHT OUTER JOIN dbo.py_vPreparaPackingList prep
    ON g.BarcodePallet COLLATE SQL_Latin1_General_CP1_CI_AS = prep.UDC
LEFT JOIN dbo.PyPickingListReservation pr
    ON pr.ID = 1
   AND NULLIF(LTRIM(RTRIM(pr.Documento)), '') IS NOT NULL
GROUP BY
    prep.Expr1,
    prep.NAZIONE,
    prep.UDC,
    prep.NUMDOC,
    prep.DATDOC,
    prep.StatoDocumento,
    prep.NUMLOT,
    prep.CODICE,
    prep.DESCR,
    prep.Qta,
    g.NumeroPallet,
    g.IDMagazzino,
    g.IDArea,
    g.IDCella,
    c.Ordinamento,
    c.Corsia,
    c.Colonna,
    c.Fila,
    prep.DESCRDEST;
GO

PRINT 'Patch form storiche Python applicata.';
PRINT 'Creato/aggiornato dbo.py_vPreparaPackingListSAMA1';
PRINT 'Creato/aggiornato dbo.py_vPreparaPackingList';
PRINT 'Creato/aggiornato dbo.py_XMag_ViewPackingListStorico';
GO