USE XXX
GO

/****** Object:  StoredProcedure Script Date: 8/17/2017 7:46:23 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [XXX].[XXX]
    @equipment_tag varchar(50),
    @postfix varchar(50),
    @time_from datetime2(0),
    @time_to datetime2(0)
AS
BEGIN
DECLARE @equipment_id smallint,
@equipment_level_tag varchar(100);

DECLARE @machineTable TABLE (Value varchar(50));
set @equipment_level_tag = (SELECT equipment_level_tag FROM [XXX].[XXX] where equipment_tag = @equipment_tag)
SET @equipment_id = (SELECT id FROM Equipment.Equipment WHERE tag = @equipment_tag);
If @equipment_level_tag = 'FACTORY'
    Begin
    INSERT INTO @machineTable select parent_tag
  FROM [XXX].[XXX] where equipment_tag like '%_packer' AND structure_tag = 'UTILITIES'
    End
Else If @equipment_level_tag  = 'DEPARTMENT'
    Begin
 INSERT INTO @machineTable SELECT parent_tag
  FROM  [XXX].[XXX] WHERE parent_id in (SELECT [equipment_id]
  FROM  [XXX].[XXX] where parent_id in ((SELECT [equipment_id]
  FROM  [XXX].[XXX] where parent_id = 2))) and equipment_tag like '%_packer' AND structure_tag = 'UTILITIES'
    End
Else If @equipment_level_tag  = 'CELL'
    Begin
  INSERT INTO @machineTable select parent_tag
  FROM [XXX].[XXX] WHERE parent_id in (SELECT [equipment_id]
  FROM [XXX].[XXX] where parent_id = @equipment_id) and equipment_tag like '%_packer' AND structure_tag = 'UTILITIES'
    End
Else If @equipment_level_tag  = 'WORK_CENTER'
    Begin
  INSERT INTO @machineTable select parent_tag
  FROM [XXX].[XXX] where parent_id = @equipment_id and equipment_tag like '%_packer' AND structure_tag = 'UTILITIES'
    End
Else If @equipment_level_tag  = 'EQUIPMENT'
    Begin
  INSERT INTO @machineTable select parent_tag
  FROM [XXX].[XXX] where equipment_id = @equipment_id;
END

  SELECT place_id,name,CONVERT(datetime2(0), SWITCHOFFSET(CONVERT(datetimeoffset,time_from), DATENAME(TzOffset, SYSDATETIMEOFFSET()))),data FROM
       mes_machines_statistics
        WHERE name = 'OEE_'+@postfix AND time_from >= @time_from AND time_from < @time_to
        and place_id in (SELECT idx FROM _machines WHERE name like '%_packer' and line_idx in (SELECT * FROM @machineTable))

If @postfix = 'HOUR'
BEGIN
SELECT DATEADD(hh, DATEDIFF(hh, 0, CONVERT(datetime2(0), SWITCHOFFSET(CONVERT(datetimeoffset,time_from), DATENAME(TzOffset, SYSDATETIMEOFFSET())))),0) AS time_consume,
        SUM(consumption*multiplicator) as consume
FROM [XXX].[XXX] where equipment_id = @equipment_id AND time_from >= @time_from AND time_from < @time_to
GROUP BY DATEADD(hh, DATEDIFF(hh, 0, CONVERT(datetime2(0), SWITCHOFFSET(CONVERT(datetimeoffset,time_from), DATENAME(TzOffset, SYSDATETIMEOFFSET())))),0)
        ORDER BY 1
        END
Else If @postfix = 'SHIFT'
BEGIN
 WITH Shift_Consumption_CTE (shift_id,shift_start,consume)
 AS (
 select t1.shift_id ,t2.shift_start, SUM(t1.consumption*t1.multiplicator)
from [XXX].[XXX].[XXX] t1
 JOIN [XXX].[XXX].[XXX] t2 on t2.id = t1.shift_id
where equipment_id = @equipment_id AND CAST(shift_start AS DATETIME2(0)) >= @time_from
  AND CAST(shift_end AS DATETIME2(0)) <= @time_to
    GROUP BY
  t1.shift_id,t2.shift_start
 )
SELECT CONVERT(datetime2(0), SWITCHOFFSET(CONVERT(datetimeoffset,shift_start), DATENAME(TzOffset, SYSDATETIMEOFFSET()))),consume from Shift_Consumption_CTE
END
Else If @postfix = 'DAY'
BEGIN
 WITH Daily_Consumption_CTE (prod_day_start,consume)
 AS (
select t2.prod_day_start,SUM(t1.consumption*t1.multiplicator)
from [XXX].[XXX].[XXX] t1
 join [XXX].[XXX].[XXX] t2 on t2.id = t1.shift_id
where equipment_id = @equipment_id AND prod_day >= CAST(@time_from AS DATE)
  AND prod_day <= CAST(@time_to AS DATE)
  GROUP BY
  t2.prod_day_start
 )
 SELECT CONVERT(datetime2(0), SWITCHOFFSET(CONVERT(datetimeoffset,prod_day_start), DATENAME(TzOffset, SYSDATETIMEOFFSET()))),consume from Daily_Consumption_CTE
 END
 END