USE [COMES3_cModeller]
GO
/****** Object:  StoredProcedure [data].[GanttSaveShiftData]    Script Date: 15. 5. 2016 21:50:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [data].[GanttSaveShiftData]
@prodShiftId int,
@startTs datetime2,
@endTs datetime2,
@deviceStructId int
WITH RECOMPILE 
AS BEGIN
UPDATE SHF_ProdShift
SET
  BeginTime = @startTs
  ,DMT_DeviceStructId = @deviceStructId
  ,EndTime = @endTs
WHERE p_id = @prodShiftId

SELECT @@ROWCOUNT
END



USE [COMES3_cModeller]
GO
/****** Object:  StoredProcedure [data].[GanttDetailShiftData]    Script Date: 15. 5. 2016 21:50:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [data].[GanttDetailShiftData]
@id int
WITH RECOMPILE 
AS BEGIN
PRINT 'ProductionShift'
SELECT
  SHF_ProdShift.p_id AS [SHF_ProdShift.p_id]
  ,SHF_ProdShift.BeginTime AS [SHF_ProdShift.BeginTime]
  ,SHF_ProdShift.EndTime AS [SHF_ProdShift.EndTime]
  
  ,DMT_DeviceStruct.p_id AS [SHF_ProdShift.DMT_DeviceStructId.p_id]
  ,DMT_DeviceStruct.Code AS [SHF_ProdShift.DMT_DeviceStructId.Code]
  ,DMT_DeviceStruct.Description_csCZ AS [SHF_ProdShift.DMT_DeviceStructId.Description]
  ,DMT_DeviceStruct.Name_csCZ AS [SHF_ProdShift.DMT_DeviceStructId.Name]
  
  ,SHF_Shift.p_id AS [SHF_ProdShift.SHF_ShiftId.p_id]
  ,SHF_Shift.Code AS [SHF_ProdShift.SHF_ShiftId.Code]
  ,SHF_Shift.ColorBg AS [SHF_ProdShift.SHF_ShiftId.ColorBg]
  ,SHF_Shift.ColorFg AS [SHF_ProdShift.SHF_ShiftId.ColorFg]
  ,SHF_Shift.Description_csCZ AS [SHF_ProdShift.SHF_ShiftId.Description]
  ,SHF_Shift.Name_csCZ AS [SHF_ProdShift.SHF_ShiftId.Name]
FROM data.SHF_ProdShift
  INNER JOIN data.DMT_DeviceStruct AS DMT_DeviceStruct ON DMT_DeviceStruct.p_id = SHF_ProdShift.DMT_DeviceStructId
  INNER JOIN data.SHF_Shift AS SHF_Shift ON SHF_Shift.p_id = SHF_ProdShift.SHF_ShiftId
WHERE @id = SHF_ProdShift.p_id
END



USE [COMES3_cModeller]
GO
/****** Object:  StoredProcedure [data].[GanttLoadShiftData]    Script Date: 15. 5. 2016 21:49:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [data].[GanttLoadShiftData]
@startMs datetime2,
@endMs datetime2
WITH RECOMPILE 
AS BEGIN
PRINT 'ProductionShift'
SELECT
  SHF_ProdShift.p_id AS [SHF_ProdShift.p_id]
  ,SHF_ProdShift.BeginTime AS [SHF_ProdShift.BeginTime]
  ,SHF_ProdShift.EndTime AS [SHF_ProdShift.EndTime]
  
  ,DMT_DeviceStruct.Name_csCZ AS [SHF_ProdShift.DMT_DeviceStructId.Name]   
  ,SHF_Shift.Code AS [SHF_ProdShift.SHF_ShiftId.Code]
  ,SHF_Shift.Name_csCZ AS [SHF_ProdShift.SHF_ShiftId.Name]
  
  ,DMT_DeviceStruct.p_id AS [SHF_ProdShift.DMT_DeviceStructId.p_id]
  ,DMT_DeviceStruct.Code AS [SHF_ProdShift.DMT_DeviceStructId.Code]
  ,DMT_DeviceStruct.Description_csCZ AS [SHF_ProdShift.DMT_DeviceStructId.Description]
  ,SHF_Shift.p_id AS [SHF_ProdShift.SHF_ShiftId.p_id]

  ,SHF_Shift.ColorBg AS [SHF_ProdShift.SHF_ShiftId.ColorBg]
  ,SHF_Shift.ColorFg AS [SHF_ProdShift.SHF_ShiftId.ColorFg]
  ,SHF_Shift.Description_csCZ AS [SHF_ProdShift.SHF_ShiftId.Description]

FROM SHF_ProdShift
  INNER JOIN DMT_DeviceStruct AS DMT_DeviceStruct ON DMT_DeviceStruct.p_id = SHF_ProdShift.DMT_DeviceStructId
  INNER JOIN SHF_Shift AS SHF_Shift ON SHF_Shift.p_id = SHF_ProdShift.SHF_ShiftId
WHERE (SHF_ProdShift.BeginTime BETWEEN @startMs AND @endMs OR SHF_ProdShift.EndTime BETWEEN @startMs AND @endMs)  
      OR ((SHF_ProdShift.BeginTime <= @startMs OR @startMs IS NULL) AND (SHF_ProdShift.EndTime >= @endMs OR @endMs IS NULL))
ORDER BY DMT_DeviceStruct.Code, SHF_ProdShift.BeginTime
END