PRINT N'Creazione di [dbo].[stp_CKD_getByKey]...';


GO


-- =============================================
-- Author:		S.E.L.
-- Create date: 2020.08.25
-- Description:	Recupera CART da Key
-- =============================================
CREATE PROCEDURE [dbo].[stp_CKD_getByKey]
(
	@CartID		INT = 0
)
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	*
	FROM	v_CartKitDetailAll
	WHERE	CartID	= @CartID
	
END
GO
PRINT N'Creazione di [dbo].[stp_IL_invalidateByCart]...';


GO


-- =============================================
-- Author:		S.E.L.
-- Create date: 2020.08.25
-- Description:	Aggiorna l'OnCartDate a null per tutti gli item di un CART invalidando
-- =============================================
CREATE PROCEDURE [dbo].[stp_IL_invalidateByCart]
(
	@CartID		INT				= 0
	,@PlaceCod	NVARCHAR(50)	= ''
)
AS
BEGIN

    SET NOCOUNT ON;
    SET XACT_ABORT ON;

	BEGIN TRAN

		-- Faccio registrazione in EventList
		INSERT INTO ItemEvents(ItemID, StatusID, DateStart, DateEnd, placeCod)
		SELECT 		ItemID, StatusID, GETDATE(), GETDATE(), @PlaceCod
		FROM		ItemList IL
			INNER JOIN KitList KL ON IL.KitID = KL.KitID
		WHERE		KL.CartID	= @CartID
			AND		NOT (OnCartDate IS NULL)
			
		-- invalido su PackLog
		DELETE	
		FROM	PackLog
		WHERE	CartID	= @CartID
			
		-- aggiorno status
		UPDATE		IL 
		SET			OnCartDate	= NULL
		FROM		ItemList IL
			INNER JOIN KitList KL ON IL.KitID = KL.KitID
		WHERE		KL.CartID	= @CartID
			AND		NOT (OnCartDate IS NULL)


	COMMIT tran
END
GO
PRINT N'Creazione di [dbo].[stp_IL_invalidateByPartCart]...';


GO


-- =============================================
-- Author:		S.E.L.
-- Create date: 2020.08.25
-- Description:	Aggiorna l'OnCartDate a null per tutti gli item di un CART invalidando
-- =============================================
CREATE PROCEDURE [dbo].[stp_IL_invalidateByPartCart]
(
	@CartID			INT				= 0
	,@ItemExtCode	NVARCHAR(250)	= ''
	,@PlaceCod		NVARCHAR(50)	= ''
)
AS
BEGIN

    SET NOCOUNT ON;
    SET XACT_ABORT ON;

	BEGIN TRAN

		-- Faccio registrazione in EventList
		INSERT INTO ItemEvents(ItemID, StatusID, DateStart, DateEnd, placeCod)
		SELECT 		ItemID, StatusID, GETDATE(), GETDATE(), @PlaceCod
		FROM		ItemList IL
			INNER JOIN KitList KL ON IL.KitID = KL.KitID
		WHERE		KL.CartID	= @CartID
			AND		ItemExtCode	= @ItemExtCode
			AND		NOT (OnCartDate IS NULL)

		-- invalido su PackLog
		DELETE	
		FROM	PackLog
		WHERE	CartID	= @CartID
			AND	MRP_PART = @ItemExtCode

		-- aggiorno status
		UPDATE		IL 
		SET			OnCartDate	= NULL
		FROM		ItemList IL
			INNER JOIN KitList KL ON IL.KitID = KL.KitID
		WHERE		KL.CartID	= @CartID
			AND		ItemExtCode	= @ItemExtCode
			AND		NOT (OnCartDate IS NULL)


	COMMIT tran
END
GO
PRINT N'Creazione di [dbo].[stp_IRK_getByCart]...';


GO



-- =============================================
-- Author:		S.E.L.
-- Description:	Recupera TUTTI gli items RICHIESTi al KITTING dato un CART
-- Create date: 2020.08.21
--
-- =============================================
CREATE PROCEDURE [dbo].[stp_IRK_getByCart]
(
	@CartID INT = 0 -- 0 NON ESISTE	
)
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	*
	FROM	v_ItemRequestOnKit
	WHERE	CartID = @CartID

END
GO
PRINT N'Creazione di [dbo].[stp_IRKSum_getByCart]...';


GO
-- =============================================
-- Author:		S.E.L.
-- Description:	Recupera TUTTI gli items RICHIESTi al KITTING dato un CART in modalità SUMMED
-- Create date: 2020.08.21
--
-- =============================================
CREATE PROCEDURE [dbo].[stp_IRKSum_getByCart]
(
	@CartID INT = 0 -- 0 NON ESISTE	
)
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	
			OrderExtCode
			,CartDtmx
			,ItemExtCode
			,ItemDesc
			,ItemNote
			,SUM(QtyReq) AS TotReq
			,SUM(QtyCur) AS TotCur
			,MatExtCode
			,MatDesc
	FROM	v_ItemRequestOnKit
	WHERE	CartID = @CartID
	GROUP BY
			OrderExtCode
			,CartDtmx
			,ItemExtCode
			,ItemDesc
			,ItemNote
			,MatExtCode
			,MatDesc
		
END
GO
PRINT N'Creazione di [dbo].[stp_ISD_getMissingByBatch]...';


GO



-- =============================================
-- Author:		S.E.L.
-- Description:	Recupera Items Missing In Action del BATCH
-- Create date: 2020.08.25
--
-- =============================================
CREATE PROCEDURE [dbo].[stp_ISD_getMissingByBatch]
(
	@BatchID INT = 0 -- 0 NON ESISTE	
)
AS
BEGIN
	SET NOCOUNT ON;

	SELECT	IL.*
	FROM	v_ItemSearchDetail IL
		INNER JOIN KitList KL	ON IL.KitID		= KL.KitID
		INNER JOIN Nesting NE	ON NE.ItemID	= IL.ItemID
		INNER JOIN SheetList SL	ON SL.SheetID	= NE.SheetID
	WHERE	KL.BatchID		= @BatchID	-- mi interessa solo il batch
		AND	IL.StatusID		= 1			-- status 1 = lavorati ma NON scaricati su BIN/CART o in fasi di SCRAP/offline
		AND NOT (SL.UnlEnd IS NULL)		-- fogli già scaricati

END
GO
PRINT N'Creazione di [prt].[stp_prt_IRK]...';


GO


-- =============================================
-- Author:		S.E.L.
-- Description:	Recupera TUTTI gli items RICHIESTI al KITTING dato un CART (per report)

-- Create date: 2020.08.24
--
-- =============================================
CREATE PROCEDURE [prt].[stp_prt_IRK] 
(
     @CartID         AS INT              = 0     -- 0 NON ESISTE	
    ,@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 OrderExtCode
		--,CartID
		,CartDtmx
		--,KitID
		,KitExtCode
		--,ItemID
		--,StatusID
		,StatusDesc
		,ItemDtmx
		,ItemExtCode
		,ItemDesc
		,ItemNote
		,QtyReq
		,QtyCur
		,MatExtCode
		,MatDesc
		,OnCartDate
        ,@QRCodeBaseUrl AS QRCodeBaseUrl
	FROM v_ItemRequestOnKit
	WHERE CartID = @CartID
END
GO
PRINT N'Creazione di [prt].[stp_prt_IRK_Sum]...';


GO

-- =============================================
-- Author:		S.E.L.
-- Description:	Recupera TUTTI gli items RICHIESTi al KITTING dato un CART in modalità SUMMED
--
-- Create date: 2020.08.24

-- =============================================
CREATE PROCEDURE [prt].[stp_prt_IRK_Sum]
(
     @CartID         AS INT              = 0     -- 0 NON ESISTE	
    ,@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	
			OrderExtCode
			,CartDtmx
			,ItemExtCode
			,ItemDesc
			--,StatusDesc
			,ItemNote
			,SUM(QtyReq) AS TotReq
			,SUM(QtyCur) AS TotCur
			,MatExtCode
			,MatDesc
            ,@QRCodeBaseUrl AS QRCodeBaseUrl
	FROM dbo.v_ItemRequestOnKit
	WHERE CartID = @CartID
	GROUP BY
			OrderExtCode
			,CartDtmx
			,ItemExtCode
			,ItemDesc
			--,StatusDesc
			,ItemNote
			,MatExtCode
			,MatDesc

END
GO
PRINT N'Aggiornamento completato.';