-- SCRIPT update DB: VIEWS

PRINT N'Modifica di [dbo].[v_OtherItemKitDetail]...';


GO


ALTER VIEW [dbo].[v_OtherItemKitDetail]
AS

-- 2020.07.21 aggiunta Where status >=2 (Packing APPROVATE)

SELECT 
	 Kit.BatchID
	,Kit.CartID
	,Kit.OrdID
	,Kit.KitID
	,K2o.KitExtCode
	,OL.OrderExtCode
	,K2o.OtherItemID
	,OIL.OtherItemDesc
	,OIL.OtherItemDtmx
	,OIL.OtherItemExtCode
	,K2o.OnCartDate
	,K2o.QtyReq
	,K2o.QtyCur
    --,pack.PackListID
FROM dbo.Kit2OtherItem AS k2o
INNER JOIN dbo.OtherItemList AS OIL ON k2o.OtherItemID = OIL.OtherItemID
INNER JOIN dbo.KitList AS Kit ON k2o.KitExtCode = kit.KitExtCode
INNER JOIN dbo.OrderList AS OL ON Kit.OrdID = OL.OrdID
INNER JOIN dbo.PackList AS Pack ON k2o.PackListID = pack.PackListID
WHERE pack.Status >= 2   -- solo OtherItem di packing approvate
GO
PRINT N'Aggiornamento in corso di [dbo].[v_OtherItemKitSum]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[v_OtherItemKitSum]';


GO
PRINT N'Aggiornamento in corso di [dbo].[v_CartKitDetail]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[v_CartKitDetail]';


GO
PRINT N'Aggiornamento in corso di [dbo].[v_CartKitDetailAll]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[v_CartKitDetailAll]';


GO
PRINT N'Modifica di [dbo].[v_Pack2Cart]...';


GO

ALTER VIEW [dbo].[v_Pack2Cart]
AS
WITH cteP2L
AS (
	SELECT DISTINCT PL.PackListID
		,KL.CartID
	FROM dbo.PackList PL
	INNER JOIN Pack2Kit PK ON PL.PackListID = PK.PackListID
	INNER JOIN KitList KL ON KL.KitExtCode = PK.KitExtCode
	WHERE CartID > 1
	)

SELECT DISTINCT
	cteP2L.*
	,CL.CartIndex
	,CL.CartDtmx
	,OL.OrderExtCode
	,OL.FamilyCode
	,CASE 
		WHEN KittingStart IS NULL
			THEN 0
		ELSE 1
		END AS Started
	,CASE 
		WHEN KittingEnd IS NULL
			THEN 0
		ELSE 1
		END AS Completed
FROM cteP2L
INNER JOIN Carts CL		ON cteP2L.CartID	= CL.CartID
INNER JOIN KitList KL	ON KL.CartID		= CL.CartID
INNER JOIN OrderList OL ON KL.OrdID			= OL.OrdID
GO
PRINT N'Aggiornamento in corso di [dbo].[v_PackListStatus]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[v_PackListStatus]';


GO
PRINT N'Modifica di [dbo].[v_PackListItemDet]...';


GO




ALTER VIEW [dbo].[v_PackListItemDet]
AS

SELECT PackListID
	,COUNT(DISTINCT MRP_PART) AS NumParts
	,SUM(QTY) AS TotQty
FROM dbo.PackListDet
GROUP BY PackListID
GO
PRINT N'Modifica di [dbo].[v_PackList]...';


GO





ALTER VIEW [dbo].[v_PackList]
AS

WITH cteLog AS
(
	SELECT	PackListID, COUNT(Dtmx) AS NumPz
	FROM	PackLog
	GROUP BY	PackListID
)
,cteDati AS
(
	SELECT PL.PackListID
		,PL.BatchName
		,PL.ImportDate
		,PL.STATUS AS StatusID
		,PL.SchedPrior AS SchedPrior
		,COUNT(DISTINCT KL.CartID) AS NumCart
		,COUNT(DISTINCT P2K.KitExtCode) AS NumKit
		,ISNULL(PLID.NumParts,0) AS NumParts
		,ISNULL(PLID.TotQty,0) AS TotQty
	FROM dbo.Pack2Kit P2K
	INNER JOIN KitList KL ON P2K.KitExtCode = KL.KitExtCode
	RIGHT OUTER JOIN dbo.PackList PL ON P2K.PackListID = PL.PackListID
	LEFT OUTER JOIN dbo.v_PackListItemDet PLID ON PL.PackListID = PLID.PackListID
	where kl.CartID > 1
	GROUP BY PL.PackListID
		,PL.BatchName
		,PL.ImportDate
		,PL.STATUS
		,PL.SchedPrior
		,PLID.NumParts
		,PLID.TotQty
)

SELECT	cte.*
		,ISNULL(cteLog.NumPz,0) AS TotConf
		,ISNULL(CSV.OutValue,'ND') AS StatusDesc
FROM	cteDati cte
	LEFT OUTER JOIN cteLog ON cte.PackListID = cteLog.PackListID
	LEFT OUTER JOIN dbo.ConfSetValues CSV ON cte.StatusID = CSV.IntVal
WHERE	CSV.SetName = 'PackListStatus'
GO
PRINT N'Modifica di [prt].[stp_prt_Bin]...';


GO





-- =============================================
--
-- Author:		Steamware
-- Description:	Recupera dati per report Bin (ceste)
--              
--
-- EXEC [prt].[stp_prt_Bin] 615, 0, 'C:\Test\'
--
-- Mod. date: 2020.07.28
-- Mod. date: 2020.07.29 agg. @QRCodeBaseUrl
--
-- =============================================
ALTER PROCEDURE [prt].[stp_prt_Bin]
( 
    @BinID          AS INT = 0,
    @flgPrePost     AS BIT = 0,               -- 0 -> dtmx pre (campo BinDtmx)   1 -> dtmx post (campo BinDtmxProc)
    @PathImage      AS NVARCHAR(4000) = '',   -- Path per il recupero delle immagini
    @QRCodeBaseUrl  AS NVARCHAR(400)  = ''    -- URL per la creazione QR code
)
AS
BEGIN

	SET NOCOUNT ON;

    -- se non passati imposto da config
    IF ISNULL( @QRCodeBaseUrl,'' ) = ''
        SELECT @QRCodeBaseUrl = Valore FROM dbo.Config WHERE chiave = 'prt_QRCodeBaseUrl'

     -- SET @QRCodeBaseUrl = 'https://qrcode.steamware.net/HOME/QR_site/JSON?val={''baseUrl'':''{0}'',''parameters'':[''QRCODEVALUE'']}'

    IF ISNULL( @PathImage,'' ) = ''
        SELECT @PathImage = Valore FROM dbo.Config WHERE chiave = 'prt_PathImage'


    -- Ogni BIN contiene pz di un solo articolo (metto TOP per sicurezza)
    SELECT TOP 1
         @flgPrePost AS flgPreOrPost
	    ,B.BinID
	    ,B.BinIndex
        ,'B' + CAST(B.BinIndex AS VARCHAR(50))  AS BinIndexChar  -- quello che uso nel report
	    ,ISNULL(IL.ItemExtCode,'')              AS ItemExtCode   -- usato anche nell' immagine
        ,IL.ItemDesc
        ,CASE WHEN @flgPrePost = 0 THEN BinDtmx ELSE BinDtmxProc END AS BinDtmxRich     -- Datamatrix del BIN richiesto (pre o post)
	    ,B.BinDtmx
	    ,B.BinDtmxProc
	    ,ISNULL(COUNT(IL.ItemID), 0)    AS TotItem
        ,ISNULL(@PathImage,'')          AS PathImageFull
        , @QRCodeBaseUrl                AS QRCodeBaseUrl
	    --,B.CreationDate
        --,ISNULL(I2B.BatchID, 0)     AS BatchID
	    --,ISNULL(SUM(I2B.NumItemLoad), 0) AS TotItemLoad
	    --,B.PaintDate
	    --,B.KittingStart
	    --,B.KittingEnd
		,BA.DueDate
    FROM dbo.Bins AS B
    LEFT JOIN dbo.BinList AS BL ON B.BinID = BL.BinID
    LEFT JOIN dbo.ItemList AS IL ON BL.ItemID = IL.ItemID 
    LEFT JOIN dbo.KitList AS Kit ON IL.KitID = Kit.KitID
    LEFT JOIN dbo.BatchList AS BA ON KIt.BatchID = BA.BatchID
    WHERE B.BinID = @BinID 
    GROUP BY 
	         B.BinID
	        ,B.BinIndex
	        ,IL.ItemExtCode
            ,IL.ItemDesc
	        ,B.BinDtmx
	        ,B.BinDtmxProc
	        --,B.CreationDate
            --,I2B.BatchID
	        --,B.PaintDate
	        --,B.KittingStart
	        --,B.KittingEnd
			,BA.DueDate
END
GO
PRINT N'Modifica di [prt].[stp_prt_Cart]...';


GO



-- =============================================
--
-- Author:		Steamware
-- Description:	Recupera dati per report CART (carrello)
-- Create date: 2020.07.24
--
-- Mod. date: 2020.07.29 agg. @QRCodeBaseUrl
--
-- =============================================
ALTER PROCEDURE [prt].[stp_prt_Cart]
( 
      @CartID INT = 0
     ,@QRCodeBaseUrl  AS NVARCHAR(400)  = ''        -- URL per la creazione QR code
)
AS
BEGIN

	SET NOCOUNT ON;

    -- se non passati imposto da config
    IF ISNULL( @QRCodeBaseUrl,'' ) = ''
        SELECT @QRCodeBaseUrl = Valore FROM dbo.Config WHERE chiave = 'prt_QRCodeBaseUrl'


    SELECT ISNULL(kit.BatchID,0) AS BatchID
	    ,C.CartID
	    ,C.CartIndex
	    ,C.CartDtmx
        ,OL.OrderExtCode
        ,OL.FamilyCode
        ,COUNT(Kit.KitID) AS TotKit
        ,@QRCodeBaseUrl   AS QRCodeBaseUrl
		,BL.DueDate

    FROM dbo.Carts AS C
    LEFT JOIN dbo.KitList AS Kit ON C.CartID = Kit.CartID
    LEFT JOIN dbo.OrderList AS OL ON KIt.OrdID = OL.OrdID
    LEFT JOIN dbo.BatchList AS BL ON KIt.BatchID = BL.BatchID
    WHERE C.CartID = @CartID
    GROUP BY 
        ISNULL(kit.BatchID,0)
	        ,C.CartID
	        ,C.CartIndex
	        ,C.CartDtmx
            ,OL.OrderExtCode
            ,OL.FamilyCode
            ,BL.DueDate

END
GO
PRINT N'Modifica di [dbo].[ItemList].[StatusID].[MS_Description]...';


GO
EXECUTE sp_updateextendedproperty @name = N'MS_Description', @value = N'Gestione stati ITEM: 0= CAM, 1 = MACHINED, 2 = SecOp, 3=CART, 4 = BIN, 5=KIT, 50 = OtherItem, 100 = park,  990=SCRAP, 991 = Ordered after scrap, 992 = CNC created after SCRAP', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ItemList', @level2type = N'COLUMN', @level2name = N'StatusID';


GO
PRINT N'Aggiornamento in corso di [dbo].[stp_P2C_getByPackListId]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[stp_P2C_getByPackListId]';


GO
PRINT N'Aggiornamento in corso di [dbo].[stp_PL_closeCart]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[stp_PL_closeCart]';


GO
PRINT N'Aggiornamento in corso di [dbo].[stp_PL_getActive]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[stp_PL_getActive]';


GO
PRINT N'Aggiornamento in corso di [dbo].[stp_PL_getRunning]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[stp_PL_getRunning]';


GO
PRINT N'Aggiornamento in corso di [dbo].[stp_PL_getToSchedule]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[stp_PL_getToSchedule]';


GO
PRINT N'Aggiornamento in corso di [dbo].[stp_PL_updStatus]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[stp_PL_updStatus]';


GO
PRINT N'Aggiornamento in corso di [dbo].[stp_PLD_getRunByOrdExtCode]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[stp_PLD_getRunByOrdExtCode]';


GO
PRINT N'Aggiornamento completato.';


GO