Samuele Locatelli
59 lines
1.8 KiB
Transact-SQL
59 lines
1.8 KiB
Transact-SQL
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
ALTER VIEW [dbo].[v_PrtReport]
|
|
AS
|
|
SELECT
|
|
ORD.OrderId,
|
|
ORD.CompanyId,
|
|
ORD.OrderExtCode,
|
|
ORD.DateIns,
|
|
ORD.UserIdIns,
|
|
ORD.DateMod,
|
|
ORD.UserIdMod,
|
|
ORD.Status,
|
|
ORD.OrderDescript,
|
|
COM.CompanyExtCode,
|
|
COM.CompanyName,
|
|
COM.Address,
|
|
COM.ZipCode,
|
|
COM.City,
|
|
COM.State,
|
|
COM.VAT,
|
|
DOR.DoorId,
|
|
ROW_NUMBER() OVER ( PARTITION BY DOR.DoorId ORDER BY DOR.DoorId) AS RowDoor, -- mi serve per i totoali sul report
|
|
DOR.DoorIdParent,
|
|
DOR.DoorExtCode,
|
|
DOR.DoorDescript,
|
|
DOR.Quantity,
|
|
DOR.UnitCost,
|
|
DOR.MeasureUnit,
|
|
CASE
|
|
WHEN DOP.ObjectId IN ('Profiles','Size','Swing') THEN 'INFO'
|
|
WHEN DOP.ObjectId IN ('Properties','Finishing') THEN 'OPTIONS'
|
|
--WHEN CHARINDEX('template', DOP.JsoncActVal) > 0 THEN 'HARDWARE'
|
|
ELSE 'HARDWARE' END AS ObjectType,
|
|
DOP.ObjectId as ObjectKey,
|
|
|
|
CASE
|
|
WHEN DOP.ObjectId = 'Properties' THEN JSON_VALUE(JsoncActVal,'$.Properties.Properties')
|
|
WHEN DOP.ObjectId = 'Finishing' THEN JSON_VALUE(JsoncActVal,'$.Finishing.Finishing')
|
|
WHEN DOP.ObjectId = 'Size' THEN JSON_QUERY(JsoncActVal,'$.Size')
|
|
WHEN DOP.ObjectId = 'Swing' THEN JSON_VALUE(JsoncActVal,'$.Swing.Swing')
|
|
--WHEN DOP.ObjectId = 'Profiles' THEN JSON_QUERY(JsoncActVal,'$.Profiles')
|
|
--WHEN CHARINDEX('template', DOP.JsoncActVal) > 0 THEN 'HARDWARE'
|
|
ELSE DOP.JsoncActVal END AS ObjectVal,
|
|
--DOP.JsoncActVal as ObjectVal,
|
|
1 as ObjectQty
|
|
FROM
|
|
[Order] AS ORD
|
|
INNER JOIN Company AS COM ON ORD.CompanyId = COM.CompanyId
|
|
INNER JOIN Door AS DOR ON ORD.OrderId = DOR.OrderId
|
|
INNER JOIN DoorOp AS DOP ON DOR.DoorId = DOP.DoorId
|
|
GO
|
|
|
|
|