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