USE [WGD]
GO
CREATE NONCLUSTERED INDEX [idxImageId] ON [dbo].[BackupFilePath]
(
    [ImageId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [idxStatus] ON [dbo].[Image]
(
    [Status] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX [idxSeriesId] ON [dbo].[Image]
(
    [SeriesId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX [idxStudyId] ON [dbo].[Series]
(
    [StudyId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX [idxPlanStartDate] ON [dbo].[Study]
(
    [PlanStartDate] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX [idxStudyStatus] ON [dbo].[Study]
(
    [StudyStatus] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX [idxPatientId] ON [dbo].[Study]
(
    [PatientId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX [idxDataSource] ON [dbo].[Patient]
(
    [DataSource] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX [idxSurname] ON [dbo].[Patient]
(
    [Surname] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

USE WGD
GO
BEGIN TRANSACTION
delete from BackupFilePath
delete from ImageMultiframeParams
delete from PacsMovedImages
delete from UnsendedImage
delete from StudyApr
DELETE FROM ImageOriginalParameters
DELETE FROM [Image]
DELETE FROM ImageOpticalParameters
delete from Series
DELETE FROM DeletedImages
Delete from Study
DELETE FROM EventLog
Delete from ModifiedPatient
DELETE FROM ReportsSavedFiles
Delete from ModifiedPatientHistory
DELETE FROM Patient
DELETE FROM PatientDetailed
GO

USE [WGD]
GO
/****** Object:  StoredProcedure [dbo].[sp_Validate_Study_Status]    Script Date: Сб 18.03.17 1:40:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_Validate_Study_Status]
(
    @ToCompletedTimeout int,
    @ToClosedTimeout int    
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    

--Обновляем статус исследования
--Если текуща дата больше даты последнего изменения + время таймаута,указанного в настройках
    UPDATE Study
    SET StudyStatus =
  ( CASE
   -- Статус сохранено(3) переходит в статус завершено (6)
    WHEN StudyStatus = 3 AND GETDATE()> DATEADD(HOUR,@ToCompletedTimeout, LastStudyUpdateDate) THEN 6
    --Статус завершено(6) переходит в статус закрыто(7)
    WHEN StudyStatus = 6  AND GETDATE()> DATEADD(HOUR,@ToClosedTimeout, LastStudyUpdateDate) THEN 7
    ELSE StudyStatus
     END
  ),
  LastStudyUpdateDate =
  ( CASE
   -- Статус сохранено(3) переходит в статус завершено (6)
    WHEN StudyStatus = 3 AND GETDATE()> DATEADD(HOUR,@ToCompletedTimeout, LastStudyUpdateDate) THEN GETDATE()
    --Статус завершено(6) переходит в статус закрыто(7)
    WHEN StudyStatus = 6  AND GETDATE()> DATEADD(HOUR,@ToClosedTimeout, LastStudyUpdateDate) THEN GETDATE()
    ELSE LastStudyUpdateDate
     END
  )
END