385 lines
8.6 KiB
Transact-SQL
385 lines
8.6 KiB
Transact-SQL
-- 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
|
|
|