1082 lines
78 KiB
Transact-SQL
1082 lines
78 KiB
Transact-SQL
USE [eDosLocal]
|
||
GO
|
||
/****** Object: StoredProcedure [syncLoc].[UpdateRemoteReturns] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
DROP PROCEDURE IF EXISTS [syncLoc].[UpdateRemoteReturns]
|
||
GO
|
||
/****** Object: StoredProcedure [syncLoc].[SyncStatusUpdate] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
DROP PROCEDURE IF EXISTS [syncLoc].[SyncStatusUpdate]
|
||
GO
|
||
/****** Object: StoredProcedure [syncLoc].[SyncStatusIsOK] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
DROP PROCEDURE IF EXISTS [syncLoc].[SyncStatusIsOK]
|
||
GO
|
||
/****** Object: StoredProcedure [export].[VisitsUpdate] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
DROP PROCEDURE IF EXISTS [export].[VisitsUpdate]
|
||
GO
|
||
/****** Object: StoredProcedure [export].[VisitsSelect] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
DROP PROCEDURE IF EXISTS [export].[VisitsSelect]
|
||
GO
|
||
/****** Object: StoredProcedure [export].[Visits] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
DROP PROCEDURE IF EXISTS [export].[Visits]
|
||
GO
|
||
/****** Object: StoredProcedure [export].[GetLocalVisit] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
DROP PROCEDURE IF EXISTS [export].[GetLocalVisit]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[VisitInitConfirm] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
DROP PROCEDURE IF EXISTS [dbo].[VisitInitConfirm]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[VisitInit] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
DROP PROCEDURE IF EXISTS [dbo].[VisitInit]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[VisitInfo] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
DROP PROCEDURE IF EXISTS [dbo].[VisitInfo]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[VisitIDLast] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
DROP PROCEDURE IF EXISTS [dbo].[VisitIDLast]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[VisitExit] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
DROP PROCEDURE IF EXISTS [dbo].[VisitExit]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[StationSettingsUpdate] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
DROP PROCEDURE IF EXISTS [dbo].[StationSettingsUpdate]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[StationSettingsSelect] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
DROP PROCEDURE IF EXISTS [dbo].[StationSettingsSelect]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[SetNewID_LocalStation] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
DROP PROCEDURE IF EXISTS [dbo].[SetNewID_LocalStation]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[GetTaskList] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
DROP PROCEDURE IF EXISTS [dbo].[GetTaskList]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[GetTaskInfo] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
DROP PROCEDURE IF EXISTS [dbo].[GetTaskInfo]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[GetPersonInfo] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
DROP PROCEDURE IF EXISTS [dbo].[GetPersonInfo]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[GetNewID_LocalStation] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
DROP PROCEDURE IF EXISTS [dbo].[GetNewID_LocalStation]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[GetInstallationsComments] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
DROP PROCEDURE IF EXISTS [dbo].[GetInstallationsComments]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[GetID_LocalStation] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
DROP PROCEDURE IF EXISTS [dbo].[GetID_LocalStation]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[CommentOnEventSelect] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
DROP PROCEDURE IF EXISTS [dbo].[CommentOnEventSelect]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[AfterRestore] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
DROP PROCEDURE IF EXISTS [dbo].[AfterRestore]
|
||
GO
|
||
/****** Object: Schema [syncLoc] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
DROP SCHEMA IF EXISTS [syncLoc]
|
||
GO
|
||
/****** Object: Schema [export] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
DROP SCHEMA IF EXISTS [export]
|
||
GO
|
||
/****** Object: Schema [export] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
CREATE SCHEMA [export]
|
||
GO
|
||
/****** Object: Schema [syncLoc] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
CREATE SCHEMA [syncLoc]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[AfterRestore] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
create procedure [dbo].[AfterRestore] as
|
||
EXEC sp_change_users_login 'Update_One', 'eDosStation', 'eDosStation'
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[CommentOnEventSelect] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
|
||
--*select * FROM visit ORDER BY id_visit DESC
|
||
--select * FROM person
|
||
|
||
CREATE PROCEDURE [dbo].[CommentOnEventSelect] AS
|
||
SELECT [ID_CommnentOnEvent]
|
||
,[TriggerTable]
|
||
,[TriggerField]
|
||
,[TriggerStatus]
|
||
,[Comment_FR]
|
||
,[Comment_NL]
|
||
,[Comment_EN]
|
||
FROM [dbo].[CommentOnEvent]
|
||
|
||
GO
|
||
GRANT EXECUTE ON [dbo].[CommentOnEventSelect] TO [eDosStation] AS [dbo]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[GetID_LocalStation] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE PROCEDURE [dbo].[GetID_LocalStation] AS
|
||
SELECT ID_LocalStation = [ID_Station] FROM[dbo].[StationSettings] WHERE[PCNr] = HOST_NAME();
|
||
|
||
GO
|
||
GRANT EXECUTE ON [dbo].[GetID_LocalStation] TO [eDosStation] AS [dbo]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[GetInstallationsComments] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE PROCEDURE [dbo].[GetInstallationsComments] @Inst_CODE1 INT = null, @Inst_CODE2 INT = null AS
|
||
--DECLARE @Inst_CODE1 INT = 1, @Inst_CODE2 INT = 11;
|
||
|
||
SELECT [c].[ID_CommentToInstallation],
|
||
[c].[Inst_CODE],
|
||
[i].[InstallationName],
|
||
[c].[Comment],
|
||
[CommentDate] = CONVERT(DATE, [c].[CommentDate])
|
||
FROM [dbo].[CommentToInstallation] [c]
|
||
INNER JOIN [dbo].[Installation] [i]
|
||
ON [i].[Inst_CODE] = [c].[Inst_CODE]
|
||
WHERE (
|
||
(
|
||
@Inst_CODE1 IS NOT NULL
|
||
AND [c].[Inst_CODE] = @Inst_CODE1
|
||
)
|
||
OR
|
||
(
|
||
@Inst_CODE2 IS NOT NULL
|
||
AND [c].[Inst_CODE] = @Inst_CODE2
|
||
)
|
||
)
|
||
AND GETDATE()
|
||
BETWEEN [c].[CommentDateSTART] AND [c].[CommentDateStop]
|
||
AND [c].[deleted] = 0 --AND LEN(ISNULL(RTRIM(comment),''))>0
|
||
ORDER BY [c].[Inst_CODE],
|
||
[c].[commentdate];
|
||
GO
|
||
GRANT EXECUTE ON [dbo].[GetInstallationsComments] TO [eDosStation] AS [dbo]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[GetNewID_LocalStation] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
create PROCEDURE [dbo].[GetNewID_LocalStation] AS
|
||
|
||
IF NOT EXISTS (SELECT * FROM[dbo].[StationSettings] WHERE[PCNr] = HOST_NAME())
|
||
INSERT [dbo].[StationSettings]([PCNr], [ComPort], [Inst_CODE1], [Inst_CODE2], [Inst_Name1], [Inst_Name2], [TestCSN], [Reader], [useTestButtons], [useBeep], [checkUser], [EPDTimeout])
|
||
VALUES(HOST_NAME(), -- PCNr - varchar(20)
|
||
NULL, -- ComPort - int
|
||
NULL, -- Inst_CODE1 - int
|
||
NULL, -- Inst_CODE2 - int
|
||
NULL, -- Inst_Name1 - varchar(50)
|
||
NULL, -- Inst_Name2 - varchar(50)
|
||
NULL, -- TestCSN - varchar(50)
|
||
NULL, -- Reader - varchar(70)
|
||
0, -- useTestButtons - bit
|
||
1, -- useBeep - tinyint
|
||
0, -- checkUser - bit
|
||
10000-- EPDTimeout - int
|
||
)
|
||
|
||
SELECT ID_LocalStation = [ID_Station] FROM[dbo].[StationSettings] WHERE[PCNr] = HOST_NAME();
|
||
|
||
GO
|
||
GRANT EXECUTE ON [dbo].[GetNewID_LocalStation] TO [eDosStation] AS [dbo]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[GetPersonInfo] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE PROCEDURE [dbo].[GetPersonInfo]
|
||
@CSN CHAR(14) = NULL,
|
||
@PersID INT = NULL,
|
||
@AllowMultipleEPD BIT = 0,
|
||
@CheckUser BIT = 1
|
||
AS
|
||
BEGIN
|
||
--DECLARE @PersID INT = 12103, @CSN CHAR(14) = null,@AllowMultipleEPD BIT = 0;
|
||
SET NOCOUNT ON;
|
||
|
||
DECLARE @lid_station INT,
|
||
@lid_visit INT;
|
||
|
||
IF @PersID IS NULL
|
||
SELECT @PersID = [PersID]
|
||
FROM [dbo].[PersonInfoSnapshot]
|
||
WHERE [CSN] = @CSN COLLATE SQL_Latin1_General_CP850_BIN;
|
||
|
||
SET NOCOUNT OFF;
|
||
|
||
SELECT TOP (1)
|
||
@lid_station = [ID_Station],
|
||
@lid_visit = [ID_Visit]
|
||
FROM [dbo].[Visit]
|
||
WHERE [PersID] = @PersID
|
||
AND [ReturnTime] IS NULL
|
||
AND [EntryTime] > DATEADD(YEAR, -1, GETDATE());
|
||
|
||
SELECT [a].[PersID],
|
||
[a].[Type],
|
||
[a].[CSN],
|
||
[a].[FirstName],
|
||
[a].[LastName],
|
||
[a].[NickName],
|
||
[AccessAllow] = CONVERT( BIT,
|
||
CASE
|
||
WHEN @AllowMultipleEPD = 1
|
||
AND [a].[hasEPD] = 1 THEN
|
||
0
|
||
ELSE
|
||
[a].[AccessAllow]
|
||
END
|
||
),
|
||
[a].[Language],
|
||
[a].[MarginHP10_M],
|
||
[a].[MarginHP10_Y],
|
||
[a].[Margin],
|
||
[a].[VisitIDLocked],
|
||
[a].[ID_CommentToPerson],
|
||
[a].[Comment],
|
||
[a].[Deleted],
|
||
[ReasonNoAccess] = CASE
|
||
WHEN @AllowMultipleEPD = 1
|
||
AND [a].[hasEPD] = 1 THEN
|
||
'EPD not returned'
|
||
WHEN ISNULL([VisitIDLocked], 0) > 0 THEN
|
||
'Locked because of alarm'
|
||
ELSE
|
||
[a].[ReasonNoAccess]
|
||
END,
|
||
[a].[Dose_52W_microSv],
|
||
[a].[hasEPD],
|
||
[activeStation] = @lid_station,
|
||
[activeVisit] = @lid_visit,
|
||
[a].[isAgent]
|
||
FROM
|
||
(
|
||
SELECT [p].[PersID],
|
||
[p].[Type],
|
||
[p].[CSN],
|
||
[p].[FirstName],
|
||
[p].[LastName],
|
||
[p].[NickName],
|
||
[AccessAllow] = CASE
|
||
WHEN @CheckUser = 0 THEN
|
||
1
|
||
ELSE
|
||
[p].[AccessAllow]
|
||
END,
|
||
[p].[Language],
|
||
[p].[MarginHP10_M],
|
||
[p].[MarginHP10_Y],
|
||
[p].[Margin],
|
||
[p].[VisitIDLocked],
|
||
[p].[ID_CommentToPerson],
|
||
[p].[Comment],
|
||
[p].[Deleted],
|
||
[p].[ReasonNoAccess],
|
||
[p].[Dose_52W_microSv],
|
||
[hasEPD] = CONVERT( BIT,
|
||
CASE
|
||
WHEN @lid_visit IS NOT NULL THEN
|
||
1
|
||
ELSE
|
||
0
|
||
END
|
||
),
|
||
[isAgent] = CONVERT( BIT,
|
||
CASE
|
||
WHEN [p].[isAgent] IS NULL THEN
|
||
0
|
||
ELSE
|
||
[p].[isAgent]
|
||
END
|
||
)
|
||
FROM [dbo].[PersonInfoSnapshot] [p]
|
||
WHERE [p].[PersID] = @PersID
|
||
) [a];
|
||
END;
|
||
GO
|
||
GRANT EXECUTE ON [dbo].[GetPersonInfo] TO [eDosStation] AS [dbo]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[GetTaskInfo] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
|
||
/****** Script for SelectTopNRows command from SSMS ******/
|
||
|
||
CREATE PROCEDURE [dbo].[GetTaskInfo] @ID_Task INT = NULL,
|
||
@WorkerDosisMargin FLOAT = NULL
|
||
AS
|
||
|
||
--DECLARE @ID_Task INT =5,@WorkerDosisMargin FLOAT=10
|
||
SELECT
|
||
[t].[ID_Task],
|
||
[t].[Inst_CODE],
|
||
[t].[TaskType_CODE],
|
||
[t].[Task_Name],
|
||
[t].[TaskDescription],
|
||
[t].[EPDRad],
|
||
[t].[ID_AlarmEPD],
|
||
[t].[TaskDateStart],
|
||
[t].[TaskDateEnd],
|
||
[t].[TaskEnable],
|
||
[t].[ID_FNGainANGain],
|
||
[t].[Hp10Dose1],
|
||
[t].[Hp10Dose2],
|
||
[t].[Hp07Dose],
|
||
[t].[HP10Rate1ON],
|
||
[t].[HP10Rate1OFF],
|
||
[t].[HP10Rate2ON],
|
||
[t].[HP10Rate2OFF],
|
||
[t].[HP07RateON],
|
||
[t].[HP07RateOFF],
|
||
[t].[FNGain_ANGainValue],
|
||
[t].[InstallationActivity],
|
||
[t].[Description],
|
||
[t].[Inst_CODE],
|
||
[t].[MinWorkerDosisMargin],
|
||
[MarginExceeded] = CONVERT(BIT, CASE WHEN @WorkerDosisMargin IS NULL
|
||
OR [t].[MinWorkerDosisMargin] > @WorkerDosisMargin THEN 1 ELSE 0 END),
|
||
[ChirpRate] = CONVERT(REAL,ISNULL(t.[ChirpRate],0.0))
|
||
FROM [dbo].[TaskInfoSnapshot] [t]
|
||
WHERE @ID_Task IS NULL
|
||
OR [t].[ID_Task] = @ID_Task;
|
||
GO
|
||
GRANT EXECUTE ON [dbo].[GetTaskInfo] TO [eDosStation] AS [dbo]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[GetTaskList] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
|
||
/****** Script for SelectTopNRows command from SSMS ******/
|
||
|
||
CREATE PROCEDURE [dbo].[GetTaskList]
|
||
AS
|
||
SELECT [t].[ID_Task],
|
||
[t].[Inst_CODE],
|
||
[t].[TaskType_CODE],
|
||
[t].[Task_Name],
|
||
[t].[TaskDescription] ,
|
||
[t].[EPDRad]
|
||
FROM [dbo].[TaskInfoSnapshot] [t]
|
||
WHERE [t].[TaskEnable] = 1;
|
||
GO
|
||
GRANT EXECUTE ON [dbo].[GetTaskList] TO [eDosStation] AS [dbo]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[SetNewID_LocalStation] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
create PROCEDURE [dbo].[SetNewID_LocalStation] @StationID int = NULL AS
|
||
|
||
IF NOT EXISTS (SELECT * FROM[dbo].[StationSettings] WHERE [ID_Station]=@stationid OR pcnr=HOST_NAME())
|
||
SET IDENTITY_INSERT [StationSettings] ON;
|
||
INSERT [dbo].[StationSettings]( [ID_Station], [PCNr], [ComPort], [Inst_CODE1], [Inst_CODE2], [Inst_Name1], [Inst_Name2], [TestCSN], [Reader], [useTestButtons], [useBeep], [checkUser], [EPDTimeout])
|
||
VALUES(@StationID,HOST_NAME(), -- PCNr - varchar(20)
|
||
NULL, -- ComPort - int
|
||
NULL, -- Inst_CODE1 - int
|
||
NULL, -- Inst_CODE2 - int
|
||
NULL, -- Inst_Name1 - varchar(50)
|
||
NULL, -- Inst_Name2 - varchar(50)
|
||
NULL, -- TestCSN - varchar(50)
|
||
NULL, -- Reader - varchar(70)
|
||
0, -- useTestButtons - bit
|
||
1, -- useBeep - tinyint
|
||
0, -- checkUser - bit
|
||
10000-- EPDTimeout - int
|
||
)
|
||
|
||
SELECT ID_LocalStation = [ID_Station] FROM[dbo].[StationSettings] WHERE[PCNr] = HOST_NAME();
|
||
|
||
GO
|
||
GRANT EXECUTE ON [dbo].[SetNewID_LocalStation] TO [eDosStation] AS [dbo]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[StationSettingsSelect] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE PROCEDURE [dbo].[StationSettingsSelect] AS
|
||
SELECT
|
||
[ID_Station],
|
||
[PCNr],
|
||
[ComPort],
|
||
[Inst_CODE1],
|
||
[Inst_CODE2],
|
||
[Inst_Name1],
|
||
[Inst_Name2],
|
||
[TestCSN],
|
||
[Reader],
|
||
[useTestButtons] = ISNULL([useTestButtons], 0),
|
||
[useBeep] = ISNULL([useBeep], 22),
|
||
[checkUser] = ISNULL([checkUser], 0),
|
||
[EPDTimeout] = ISNULL([EPDTimeout], 10000),
|
||
[EPDRetries] = ISNULL([EPDREtries], 1),
|
||
[RestartCom] = ISNULL([RestartCom], 0),
|
||
[RestartComFirst] = ISNULL([RestartComFirst], 0),
|
||
[OnlyMK2] = ISNULL([OnlyMK2], 0),
|
||
[AllowMultipleEPD] = ISNULL([AllowMultipleEPD], 0)
|
||
FROM [dbo].[StationSettings]
|
||
WHERE [PCNr] = HOST_NAME();
|
||
GO
|
||
GRANT EXECUTE ON [dbo].[StationSettingsSelect] TO [eDosStation] AS [dbo]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[StationSettingsUpdate] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE PROCEDURE [dbo].[StationSettingsUpdate] @ComPort INT = NULL,
|
||
@Inst_CODE1 INT = NULL,
|
||
@Inst_CODE2 INT = NULL,
|
||
@Inst_Name1 VARCHAR(50) = NULL,
|
||
@Inst_Name2 VARCHAR(50) = NULL,
|
||
@TestCSN VARCHAR(50) = NULL,
|
||
@Reader VARCHAR(70) = NULL,
|
||
@useTestButtons BIT = 0,
|
||
@useBeep TINYINT = 22,
|
||
@checkUser BIT = 0,
|
||
@EPDTimeout INT = 10000,
|
||
@EPDRetries INT = 1,
|
||
@RestartCom BIT = 0,
|
||
@RestartComFirst BIT = 0,
|
||
@OnlyMK2 BIT = 0,
|
||
@AllowMultipleEPD BIT = 0
|
||
AS
|
||
IF NOT EXISTS (SELECT * FROM [dbo].[StationSettings] WHERE [PCNr] = HOST_NAME())
|
||
INSERT [dbo].[StationSettings]([PCNr], [ComPort], [Inst_CODE1], [Inst_CODE2], [Inst_Name1], [Inst_Name2], [TestCSN], [Reader], [useTestButtons], [useBeep], [checkUser], [EPDREtries], [RestartCom], [RestartComFirst], [OnlyMK2], [AllowMultipleEPD])
|
||
VALUES(HOST_NAME(), @ComPort, @Inst_CODE1, @Inst_CODE2, @Inst_Name1, @Inst_Name2, @TestCSN, @Reader, @useTestButtons, @useBeep, @checkUser, @EPDRetries, @RestartCom, @RestartComFirst, @OnlyMK2, @AllowMultipleEPD);
|
||
ELSE
|
||
UPDATE
|
||
[dbo].[StationSettings]
|
||
SET
|
||
[ComPort] = @ComPort,
|
||
[Inst_CODE1] = @Inst_CODE1,
|
||
[Inst_CODE2] = @Inst_CODE2,
|
||
[Inst_Name1] = @Inst_Name1,
|
||
[Inst_Name2] = @Inst_Name2,
|
||
[TestCSN] = @TestCSN,
|
||
[Reader] = @Reader,
|
||
[useTestButtons] = @useTestButtons,
|
||
[useBeep] = @useBeep,
|
||
[checkUser] = @checkUser,
|
||
[EPDTimeout] = @EPDTimeout,
|
||
[EPDREtries] = @EPDRetries,
|
||
[RestartCom] = @RestartCom,
|
||
[RestartComFirst] = @RestartComFirst,
|
||
[OnlyMK2] = @OnlyMK2,
|
||
[AllowMultipleEPD]=@AllowMultipleEPD
|
||
WHERE [PCNr] = HOST_NAME();
|
||
GO
|
||
GRANT EXECUTE ON [dbo].[StationSettingsUpdate] TO [eDosStation] AS [dbo]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[VisitExit] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE PROCEDURE [dbo].[VisitExit] @EPDVisitID INT,
|
||
@HP07 FLOAT,
|
||
@HP10 FLOAT,
|
||
@HP07Peak FLOAT,
|
||
@HP10Peak FLOAT,
|
||
@HP07PeakTime DATETIME = NULL,
|
||
@HP10PeakTime DATETIME = NULL,
|
||
@HP07Total FLOAT,
|
||
@HP10Total FLOAT,
|
||
@K1 INT,
|
||
@K2 INT,
|
||
@K3 INT,
|
||
@K4 INT,
|
||
@K5 INT = NULL,
|
||
@K6 INT = NULL,
|
||
@SG INT,
|
||
@BC INT,
|
||
@FB INT,
|
||
@HG INT,
|
||
@QualityData INT,
|
||
@alarmStatus INT,
|
||
@otherAlarms INT,
|
||
@errorStatus INT,
|
||
@operatingStatus INT,
|
||
@ENTRY_EPD_CLOCK DECIMAL(10, 0) NULL,
|
||
@EXIT_EPD_CLOCK DECIMAL(10, 0) NULL,
|
||
@PersID INT = NULL,
|
||
@eDosBatteryWarning BIT = NULL,
|
||
@eDosDose07Warning BIT = NULL,
|
||
@eDosDose07Alarm BIT = NULL,
|
||
@eDosRate07Warning BIT = NULL,
|
||
@eDosRate07Alarm BIT = NULL,
|
||
@eDosDose10Warning BIT = NULL,
|
||
@eDosDose10Alarm BIT = NULL,
|
||
@eDosRate10Warning BIT = NULL,
|
||
@eDosRate10Alarm BIT = NULL,
|
||
@isAlarm BIT = NULL,
|
||
@isFault BIT = NULL
|
||
AS BEGIN
|
||
DECLARE
|
||
@ID_Visit INT,
|
||
@ID_Station INT,
|
||
@ID_Station_Return INT;
|
||
|
||
SET @ID_Station = @EPDVisitID % 100;
|
||
SET @ID_Visit = @EPDVisitID / 100;
|
||
|
||
IF @isAlarm IS NULL
|
||
SET @isAlarm = CASE WHEN --ISNULL(@alarmStatus, 0) <> 0
|
||
--OR
|
||
(ISNULL(@otherAlarms, 0) & 0x7f) <> 0 --bit 7=battery low
|
||
OR ISNULL(@eDosDose07Warning, 0) <> 0
|
||
OR ISNULL(@eDosRate07Warning, 0) <> 0
|
||
OR ISNULL(@eDosDose10Warning, 0) <> 0
|
||
OR ISNULL(@eDosRate10Warning, 0) <> 0
|
||
OR ISNULL(@eDosDose07Alarm, 0) <> 0
|
||
OR ISNULL(@eDosRate07Alarm, 0) <> 0
|
||
OR ISNULL(@eDosDose10Alarm, 0) <> 0
|
||
OR ISNULL(@eDosRate10Alarm, 0) <> 0 THEN 1 ELSE 0 END;
|
||
|
||
DECLARE @Now DATETIME = GETDATE();
|
||
|
||
IF @ID_Station_Return IS NULL
|
||
SELECT
|
||
@ID_Station_Return = [ID_Station]
|
||
FROM [dbo].[StationSettings]
|
||
WHERE [PCNr] = HOST_NAME();
|
||
|
||
DECLARE @isComplete BIT = 1;
|
||
|
||
IF NOT EXISTS (SELECT
|
||
*
|
||
FROM [dbo].[Visit]
|
||
WHERE [ID_Visit] = @ID_Visit
|
||
AND [ID_Station] = @ID_Station)BEGIN
|
||
SET @isComplete = 0;
|
||
|
||
INSERT [dbo].[Visit]([ID_Station], [ID_Visit], [Inst_CODE], [isManualExit], [isDeleted], [ID_Station_Return], [isComplete], [ENTRY_EPD_CLOCK], [PersID])
|
||
VALUES(@ID_Station, @ID_Visit, 0, 0, 0, @ID_Station_Return, 0, @ENTRY_EPD_CLOCK, @PersID);
|
||
END;
|
||
|
||
UPDATE
|
||
[dbo].[Visit]
|
||
SET
|
||
[Hp07Dose] = @HP07,
|
||
[Hp10Dose] = @HP10,
|
||
[isComplete] = @isComplete,
|
||
[ReturnTime] = @Now,
|
||
[Hp07Peak] = @HP07Peak,
|
||
[Hp10Peak] = @HP10Peak,
|
||
[Hp07Time] = @HP07PeakTime,
|
||
[Hp10Time] = @HP10PeakTime,
|
||
[Hp07Total] = @HP07Total,
|
||
[Hp10Total] = @HP10Total,
|
||
[K1] = @K1,
|
||
[K2] = @K2,
|
||
[K3] = @K3,
|
||
[K4] = @K4,
|
||
[K5] = @K5,
|
||
[K6] = @K6,
|
||
[SG] = @SG,
|
||
[BC] = @BC,
|
||
[FB] = @FB,
|
||
[HG] = @HG,
|
||
[QualityData] = @QualityData,
|
||
[alarmStatus] = @alarmStatus,
|
||
[otherAlarms] = @otherAlarms,
|
||
[errorStatus] = @errorStatus,
|
||
[operatingStatus] = @operatingStatus,
|
||
--[D1] = @D1, [D2] = @D2, [D3] = @D3, [D4] = @D4,
|
||
[isAlarm] = @isAlarm,
|
||
[isManualExit] = 0,
|
||
[ID_Station_Return] = @ID_Station_Return,
|
||
[ENTRY_EPD_CLOCK] = @ENTRY_EPD_CLOCK,
|
||
[EXIT_EPD_CLOCK] = @EXIT_EPD_CLOCK,
|
||
[PersID] = CASE WHEN [PersID] IS NULL THEN @PersID ELSE [PersID] END,
|
||
[eDosBatteryWarning] = @eDosBatteryWarning,
|
||
[eDosDose07Warning] = @eDosDose07Warning,
|
||
[eDosDose07Alarm] = @eDosDose07Alarm,
|
||
[eDosRate07Warning] = @eDosRate07Warning,
|
||
[eDosRate07Alarm] = @eDosRate07Alarm,
|
||
[eDosDose10Warning] = @eDosDose10Warning,
|
||
[eDosDose10Alarm] = @eDosDose10Alarm,
|
||
[eDosRate10Warning] = @eDosRate10Warning,
|
||
[eDosRate10Alarm] = @eDosRate10Alarm,
|
||
[isFault] = @isFault
|
||
WHERE [ID_Visit] = @ID_Visit
|
||
AND [ID_Station] = @ID_Station;
|
||
|
||
-- local- >PersonInfoSnapshot
|
||
IF @isAlarm = 1 BEGIN
|
||
IF @PersID IS NULL
|
||
SELECT
|
||
@PersID = [PersID]
|
||
FROM [dbo].[Visit]
|
||
WHERE [ID_Visit] = @ID_Visit
|
||
AND [ID_Station] = @ID_Station;
|
||
|
||
UPDATE
|
||
[dbo].[PersonInfoSnapshot]
|
||
SET
|
||
[VisitIDLocked] = @ID_Visit * 100 + @ID_Station
|
||
WHERE [PersID] = @PersID;
|
||
|
||
IF NOT EXISTS (SELECT
|
||
*
|
||
FROM [dbo].[PersonAlarms]
|
||
WHERE [PersID] = @PersID
|
||
AND [VisitTime] = @Now)
|
||
INSERT [dbo].[PersonAlarms]([PersID], [VisitTime], [ID_Station], [ID_Visit])
|
||
VALUES(@PersID, @Now, @ID_Station, @ID_Visit);
|
||
END;
|
||
END;
|
||
|
||
GO
|
||
GRANT EXECUTE ON [dbo].[VisitExit] TO [eDosStation] AS [dbo]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[VisitIDLast] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE PROCEDURE [dbo].[VisitIDLast] @ID_Station INT AS select VisitIDLast=ID_Visit*100+ID_Station FROM [dbo].[Visit] WHERE [ID_Station]=@ID_Station
|
||
GO
|
||
GRANT EXECUTE ON [dbo].[VisitIDLast] TO [eDosStation] AS [dbo]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[VisitInfo] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
|
||
CREATE PROCEDURE [dbo].[VisitInfo] @EPDVisitID INT = 51905,
|
||
@hasExited BIT = 0 OUT,
|
||
@isManualExit BIT = 0 OUT,
|
||
@name VARCHAR(100) = NULL OUT
|
||
AS
|
||
--DECLARE @EPDVisitID INT= 51905
|
||
BEGIN
|
||
SET NOCOUNT ON;
|
||
|
||
DECLARE
|
||
@ID_Visit INT,
|
||
@ID_Station INT,
|
||
@PersID INT;
|
||
|
||
SET @ID_Station = @EPDVisitID % 100;
|
||
SET @ID_Visit = @EPDVisitID / 100;
|
||
SET @hasExited = 0;
|
||
SET @isManualExit = 0;
|
||
|
||
SELECT
|
||
@hasExited = CONVERT(BIT, CASE WHEN [ReturnTime] IS NOT NULL OR [isManualExit] = 1 THEN 1 ELSE 0 END),
|
||
@isManualExit = ISNULL([isManualExit], 0),
|
||
@PersID = @PersID
|
||
FROM [dbo].[Visit]
|
||
WHERE [ID_Visit] = @ID_Visit
|
||
AND [ID_Station] = @ID_Station;
|
||
|
||
SET NOCOUNT OFF;
|
||
|
||
SELECT [hasExited] = @hasExited;
|
||
|
||
SELECT
|
||
@name = ISNULL([name] + ' ' + ISNULL([callingname], [firstname]), '?')
|
||
FROM [dbo].[PersonAll]
|
||
WHERE [persid] = @PersID;
|
||
|
||
RETURN CASE WHEN ISNULL(@hasExited, 0) = 1 THEN 1 ELSE 0 END + CASE WHEN ISNULL(@isManualExit, 0) = 1 THEN 2 ELSE 0 END;
|
||
END;
|
||
GO
|
||
GRANT EXECUTE ON [dbo].[VisitInfo] TO [eDosStation] AS [dbo]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[VisitInit] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
|
||
CREATE PROCEDURE [dbo].[VisitInit] @PersID INT,
|
||
@ID_Task INT,
|
||
@Inst_CODE INT,
|
||
@EPD_InternalID INT,
|
||
@isBG BIT,
|
||
@EPDVisitID INT = NULL OUTPUT,
|
||
@ID_Station INT NULL,
|
||
@ID_Visit INT,
|
||
@ENTRY_EPD_CLOCK DECIMAL(10, 0) NULL,
|
||
@epdType TINYINT = NULL
|
||
AS
|
||
--Local !!
|
||
DECLARE @ID_EPD INT;
|
||
|
||
IF @ID_Station IS NULL
|
||
SELECT
|
||
@ID_Station = [ID_Station]
|
||
FROM [dbo].[StationSettings]
|
||
WHERE [PCNr] = HOST_NAME();
|
||
|
||
SELECT
|
||
@ID_EPD = [ID_EPD]
|
||
FROM [dbo].[EPD]
|
||
WHERE [EPD_InternalID] = @EPD_InternalID;
|
||
|
||
IF @ID_EPD IS NULL BEGIN
|
||
INSERT [dbo].[EPD]([EPD_InternalID], [EPD_sn], [EPDType])
|
||
VALUES(@EPD_InternalID, CONVERT(VARCHAR(8), @EPD_InternalID), CASE WHEN @epdType IS NOT NULL THEN @epdType
|
||
WHEN @isBG = 0 THEN 2 ELSE 0 END);
|
||
|
||
SET @ID_EPD = SCOPE_IDENTITY();
|
||
END;
|
||
|
||
INSERT [dbo].[Visit]([ID_Station], [PersID], [Inst_CODE], [ID_Visit], [ID_Task], [EntryTime], [ID_EPD], [isBG], [isComplete], [isDeleted], [isAlarmCleared], [isAlarm], [isManualExit], [ENTRY_EPD_CLOCK], [EXIT_EPD_CLOCK], [epdType], [epdID])
|
||
VALUES(@ID_Station, @PersID, @Inst_CODE, @ID_Visit, @ID_Task, GETDATE(), @ID_EPD, @isBG, 0, 0, 0, 0, 0, @ENTRY_EPD_CLOCK, @ENTRY_EPD_CLOCK, @epdType, @EPD_InternalID);
|
||
|
||
SET @EPDVisitID = @ID_Visit * 100 + @ID_Station;
|
||
GO
|
||
GRANT EXECUTE ON [dbo].[VisitInit] TO [eDosStation] AS [dbo]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[VisitInitConfirm] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
create PROCEDURE [dbo].[VisitInitConfirm]
|
||
@ID_Station INT NULL,
|
||
@ID_Visit INT,
|
||
@InitError INT
|
||
as
|
||
UPDATE [dbo].[Visit] SET InitError=@InitError WHERE ID_Station=@ID_Station AND ID_Visit=@ID_Visit;
|
||
GO
|
||
GRANT EXECUTE ON [dbo].[VisitInitConfirm] TO [eDosStation] AS [dbo]
|
||
GO
|
||
/****** Object: StoredProcedure [export].[GetLocalVisit] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE PROCEDURE [export].[GetLocalVisit] @ID_LocalStation INT = NULL
|
||
AS
|
||
IF @ID_LocalStation IS NULL
|
||
SELECT @ID_LocalStation = [ID_Station]
|
||
FROM [dbo].[StationSettings]
|
||
WHERE [PCNr] = HOST_NAME ();
|
||
|
||
SELECT [ID_LocalStation] = @ID_LocalStation,
|
||
[ID_Station],
|
||
[ID_Visit],
|
||
[Inst_CODE],
|
||
[PersID],
|
||
[ID_Task],
|
||
[EntryTime],
|
||
[ReturnTime],
|
||
[Hp10Dose],
|
||
[Hp07Dose],
|
||
[Hp10Total],
|
||
[Hp07Total],
|
||
[Hp10Peak],
|
||
[Hp07Peak],
|
||
[Hp10Time],
|
||
[Hp07Time],
|
||
[ID_EPD],
|
||
[QualityData],
|
||
[K1],
|
||
[K2],
|
||
[K3],
|
||
[K4],
|
||
[K5],
|
||
[K6],
|
||
[SG],
|
||
[BC],
|
||
[FB],
|
||
[HG],
|
||
[alarmStatus],
|
||
[otherAlarms],
|
||
[errorStatus],
|
||
[operatingStatus],
|
||
[isBG],
|
||
[isComplete],
|
||
[isDeleted],
|
||
[isAlarm],
|
||
[isAlarmCleared],
|
||
[isManualExit],
|
||
[ID_Station_Return],
|
||
[ENTRY_EPD_CLOCK],
|
||
[EXIT_EPD_CLOCK]
|
||
FROM [dbo].[Visit];
|
||
GO
|
||
/****** Object: StoredProcedure [export].[Visits] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
/****** Script for SelectTopNRows command from SSMS ******/
|
||
|
||
create PROCEDURE [export].[Visits] AS
|
||
|
||
SELECT [ID_Station]
|
||
,[ID_Visit]
|
||
,[Inst_CODE]
|
||
,[PersID]
|
||
,[ID_Task]
|
||
,[EntryTime]
|
||
,[ReturnTime]
|
||
,[Hp10Dose]
|
||
,[Hp07Dose]
|
||
,[Hp10Total]
|
||
,[Hp07Total]
|
||
,[Hp10Peak]
|
||
,[Hp07Peak]
|
||
,[Hp10Time]
|
||
,[Hp07Time]
|
||
,[ID_EPD]
|
||
,[QualityData]
|
||
,[K1]
|
||
,[K2]
|
||
,[K3]
|
||
,[K4]
|
||
,[K5]
|
||
,[K6]
|
||
,[SG]
|
||
,[BC]
|
||
,[FB]
|
||
,[HG]
|
||
,[alarmStatus]
|
||
,[otherAlarms]
|
||
,[errorStatus]
|
||
,[operatingStatus]
|
||
,[isBG]
|
||
,[isComplete]
|
||
,[isDeleted]
|
||
,[isAlarm]
|
||
,[isAlarmCleared]
|
||
,[isManualExit]
|
||
,[ID_Station_Return]
|
||
,[ENTRY_EPD_CLOCK]
|
||
,[EXIT_EPD_CLOCK]
|
||
,[TIME_LOST]
|
||
,VisitStatus=
|
||
CASE
|
||
WHEN [EntryTime] is NOT NULL AND returntime IS NOT NULL THEN 3
|
||
WHEN [EntryTime] is NOT NULL THEN 1
|
||
WHEN returntime IS NOT NULL THEN 2
|
||
ELSE 0 END
|
||
,syncedEntry
|
||
, SyncedExit
|
||
FROM [eDosLocal].[dbo].[Visit] WHERE syncedEntry=0 OR SyncedExit=0
|
||
GO
|
||
/****** Object: StoredProcedure [export].[VisitsSelect] Script Date: 9/9/2024 1:55:12 PM ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE PROCEDURE [export].[VisitsSelect]
|
||
AS
|
||
SELECT
|
||
[ID_Station],
|
||
[ID_Visit],
|
||
[Inst_CODE],
|
||
[PersID],
|
||
[ID_Task],
|
||
[EntryTime],
|
||
[ReturnTime],
|
||
[Hp10Dose],
|
||
[Hp07Dose],
|
||
[Hp10Total],
|
||
[Hp07Total],
|
||
[Hp10Peak],
|
||
[Hp07Peak],
|
||
[Hp10Time],
|
||
[Hp07Time],
|
||
[ID_EPD],
|
||
[QualityData],
|
||
[K1],
|
||
[K2],
|
||
[K3],
|
||
[K4],
|
||
[K5],
|
||
[K6],
|
||
[SG],
|
||
[BC],
|
||
[FB],
|
||
[HG],
|
||
[alarmStatus],
|
||
[otherAlarms],
|
||
[errorStatus],
|
||
[operatingStatus],
|
||
[isBG],
|
||
[isComplete],
|
||
[isDeleted],
|
||
[isAlarm],
|
||
[isAlarmCleared],
|
||
[isManualExit],
|
||
[ID_Station_Return],
|
||
[ENTRY_EPD_CLOCK],
|
||
[EXIT_EPD_CLOCK],
|
||
[TIME_LOST],
|
||
[VisitStatus] = CONVERT(TINYINT, CASE WHEN [EntryTime] IS NOT NULL
|
||
AND [ReturnTime] IS NOT NULL THEN 3
|
||
WHEN [EntryTime] IS NOT NULL THEN 1
|
||
WHEN [ReturnTime] IS NOT NULL THEN 2 ELSE 0 END),
|
||
[syncedEntry],
|
||
[syncedExit],
|
||
[epdType],
|
||
[epdID],
|
||
[eDosBatteryWarning],
|
||
[eDosDose07Warning],
|
||
[eDosDose07Alarm],
|
||
[eDosRate07Warning],
|
||
[eDosRate07Alarm],
|
||
[eDosDose10Warning],
|
||
[eDosDose10Alarm],
|
||
[eDosRate10Warning],
|
||
[eDosRate10Alarm]
|
||
FROM [dbo].[Visit]
|
||
WHERE([syncedEntry] = 0 OR [syncedExit] = 0);
|
||
GO
|
||
GRANT EXECUTE ON [export].[VisitsSelect] TO [eDosStation] AS [dbo]
|
||
GO
|
||
/****** Object: StoredProcedure [export].[VisitsUpdate] Script Date: 9/9/2024 1:55:13 PM ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
|
||
/****** Script for SelectTopNRows command from SSMS ******/
|
||
|
||
CREATE PROCEDURE [export].[VisitsUpdate]
|
||
@ID_Station INT,
|
||
@ID_Visit INT,
|
||
@syncedEntry BIT = 0,
|
||
@syncedExit BIT = 0
|
||
AS
|
||
UPDATE [dbo].[Visit]
|
||
SET [syncedEntry] = @syncedEntry,
|
||
[syncedExit] = @syncedExit,
|
||
[syncedEntryTime] = CASE
|
||
WHEN [v].[syncedEntry] = 0 THEN
|
||
GETDATE ()
|
||
ELSE
|
||
[v].[syncedEntryTime]
|
||
END,
|
||
[syncedExitTime] = CASE
|
||
WHEN [v].[syncedExit] = 0 THEN
|
||
GETDATE ()
|
||
ELSE
|
||
[v].[syncedExitTime]
|
||
END
|
||
FROM [dbo].[Visit] [v]
|
||
WHERE [v].[ID_Station] = @ID_Station
|
||
AND [v].[ID_Visit] = @ID_Visit;
|
||
GO
|
||
GRANT EXECUTE ON [export].[VisitsUpdate] TO [eDosStation] AS [dbo]
|
||
GO
|
||
/****** Object: StoredProcedure [syncLoc].[SyncStatusIsOK] Script Date: 9/9/2024 1:55:13 PM ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
|
||
CREATE PROCEDURE [syncLoc].[SyncStatusIsOK] @ID_Station INT = NULL
|
||
AS
|
||
IF @ID_Station IS NULL
|
||
SELECT
|
||
@ID_Station = [ID_Station]
|
||
FROM [dbo].[StationSettings]
|
||
WHERE [PCNr] = HOST_NAME();
|
||
|
||
SELECT
|
||
isOk = CONVERT(BIT, CASE WHEN [TimeStamp] > DATEADD(MINUTE, -31, GETDATE()) THEN 1 ELSE 0 END)
|
||
FROM [syncLoc].[SyncStatus]
|
||
WHERE [ID_Station] = @ID_Station;
|
||
GO
|
||
GRANT EXECUTE ON [syncLoc].[SyncStatusIsOK] TO [eDosStation] AS [dbo]
|
||
GO
|
||
/****** Object: StoredProcedure [syncLoc].[SyncStatusUpdate] Script Date: 9/9/2024 1:55:13 PM ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
|
||
CREATE PROCEDURE [syncLoc].[SyncStatusUpdate] @ID_Station INT,
|
||
@TimeStamp DATETIME = NULL,
|
||
@New INT = NULL,
|
||
@Entries INT = NULL,
|
||
@Exits INT = NULL,
|
||
@Skipped INT = NULL,
|
||
@ErrorMessage VARCHAR(512) = NULL,
|
||
@ID_Visit_Min INT = NULL,
|
||
@ID_Visit_Max INT = NULL,
|
||
@Persons INT = NULL,
|
||
@Tasks INT = NULL
|
||
AS
|
||
IF NOT EXISTS (SELECT * FROM [syncLoc].[SyncStatus] WHERE [ID_Station] = @ID_Station)
|
||
INSERT [syncLoc].[SyncStatus]([ID_Station], [TimeStamp], [New], [Entries], [Exits], [Skipped], [ErrorMessage], [ID_Visit_Min], [ID_Visit_Max], [Persons], [Tasks])
|
||
VALUES(@ID_Station, @TimeStamp, @New, @Entries, @Exits, @Skipped, @ErrorMessage, @ID_Visit_Min, @ID_Visit_Max, @Persons, @Tasks);
|
||
ELSE
|
||
UPDATE
|
||
[syncLoc].[SyncStatus]
|
||
SET
|
||
[TimeStamp] = @TimeStamp,
|
||
[New] = @New,
|
||
[Entries] = @Entries,
|
||
[Exits] = @Exits,
|
||
[Skipped] = @Skipped,
|
||
[ErrorMessage] = @ErrorMessage,
|
||
[ID_Visit_Min] = @ID_Visit_Min,
|
||
[ID_Visit_Max] = @ID_Visit_Max,
|
||
[Persons] = @Persons,
|
||
[Tasks] = @Tasks
|
||
WHERE [ID_Station] = @ID_Station;
|
||
GO
|
||
GRANT EXECUTE ON [syncLoc].[SyncStatusUpdate] TO [eDosStation] AS [dbo]
|
||
GO
|
||
/****** Object: StoredProcedure [syncLoc].[UpdateRemoteReturns] Script Date: 9/9/2024 1:55:13 PM ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
CREATE PROCEDURE [syncLoc].[UpdateRemoteReturns] @ID_LocalStation INT=NULL, @pcnr VARCHAR(20) =NULL
|
||
AS BEGIN
|
||
-- Luc 2022-03-25
|
||
--DECLARE @ID_LocalStation INT = 9;
|
||
--DECLARE @pcnr VARCHAR(20);
|
||
IF @pcnr='EDOS-SCH' SET @pcnr='EDOS-WSCH';
|
||
ELSE IF @pcnr='DESKTOP-CCDL4A3' SET @pcnr='EDOS-LNK';
|
||
ELSE IF @pcnr='EDOS-ZZZ' SET @pcnr='EDOS-LSCH';
|
||
IF @ID_LocalStation IS NULL BEGIN
|
||
IF @pcnr IS NULL SET @pcnr=@@ServerName;
|
||
IF @pcnr='EDOS-SCH' SET @pcnr='EDOS-WSCH';
|
||
ELSE IF @pcnr='DESKTOP-CCDL4A3' SET @pcnr='EDOS-LNK';
|
||
ELSE IF @pcnr='EDOS-ZZZ' SET @pcnr='EDOS-LSCH';
|
||
SELECT @ID_LocalStation=[ID_Station]
|
||
FROM [dbo].[StationSettings]
|
||
WHERE [PCNr]=@pcnr;
|
||
END;
|
||
SELECT @ID_LocalStation, [PCNr]
|
||
FROM [dbo].[StationSettings]
|
||
WHERE [ID_Station]=@ID_LocalStation;
|
||
TRUNCATE TABLE [syncLoc].[compareVisit];
|
||
INSERT [syncLoc].[compareVisit]([ID_Station], [ID_Visit], [Inst_CODE], [PersID], [ID_Task], [EntryTime], [ReturnTime], [Hp10Dose], [Hp07Dose], [Hp10Total], [Hp07Total], [Hp10Peak], [Hp07Peak], [Hp10Time], [Hp07Time], [ID_EPD], [QualityData], [K1], [K2], [K3], [K4], [K5], [K6], [SG], [BC], [FB], [HG], [alarmStatus], [otherAlarms], [errorStatus], [operatingStatus], [isBG], [isComplete], [isDeleted], [isAlarm], [isAlarmCleared], [isManualExit], [ID_Station_Return], [ENTRY_EPD_CLOCK], [EXIT_EPD_CLOCK], [TIME_LOST], [epdID], [eDosBatteryWarning], [eDosDose07Warning], [eDosDose07Alarm], [eDosRate07Warning], [eDosRate07Alarm], [eDosDose10Warning], [eDosDose10Alarm], [eDosRate10Warning], [eDosRate10Alarm])
|
||
SELECT [v].[ID_Station], [v].[ID_Visit], [v].[Inst_CODE], [v].[PersID], [v].[ID_Task], [v].[EntryTime], [v].[ReturnTime], [v].[Hp10Dose], [v].[Hp07Dose], [v].[Hp10Total], [v].[Hp07Total], [v].[Hp10Peak], [v].[Hp07Peak], [v].[Hp10Time], [v].[Hp07Time], [v].[ID_EPD], [v].[QualityData], [v].[K1], [v].[K2], [v].[K3], [v].[K4], [v].[K5], [v].[K6], [v].[SG], [v].[BC], [v].[FB], [v].[HG], [v].[alarmStatus], [v].[otherAlarms], [v].[errorStatus], [v].[operatingStatus], [v].[isBG], [v].[isComplete], [v].[isDeleted], [v].[isAlarm], [v].[isAlarmCleared], [v].[isManualExit], [v].[ID_Station_Return], [v].[ENTRY_EPD_CLOCK], [v].[EXIT_EPD_CLOCK], [v].[TIME_LOST], [v].[epdID], [v].[eDosBatteryWarning], [v].[eDosDose07Warning], [v].[eDosDose07Alarm], [v].[eDosRate07Warning], [v].[eDosRate07Alarm], [v].[eDosDose10Warning], [v].[eDosDose10Alarm], [v].[eDosRate10Warning], [v].[eDosRate10Alarm]
|
||
FROM [EDOS].[edos].[dbo].[Visit] [v]
|
||
INNER JOIN(SELECT [ID_Visit]
|
||
FROM [dbo].[Visit]
|
||
WHERE [ID_Station]=@ID_LocalStation AND [ReturnTime] IS NULL) [i] ON [i].[ID_Visit]=[v].[ID_Visit]
|
||
WHERE [v].[ID_Station]=@ID_LocalStation AND [v].[ReturnTime] IS NOT NULL;
|
||
UPDATE [dbo].[Visit]
|
||
SET [ReturnTime]=[c].[ReturnTime], [Hp10Dose]=[c].[Hp10Dose], [Hp07Dose]=[c].[Hp07Dose], [Hp10Total]=[c].[Hp10Total], [Hp07Total]=[c].[Hp07Total], [Hp10Peak]=[c].[Hp10Peak], [Hp07Peak]=[c].[Hp07Peak], [Hp10Time]=[c].[Hp10Time], [Hp07Time]=[c].[Hp07Time], [QualityData]=[c].[QualityData], [K1]=[c].[K1], [K2]=[c].[K2], [K3]=[c].[K3], [K4]=[c].[K4], [K5]=[c].[K5], [K6]=[c].[K6], [SG]=[c].[SG], [BC]=[c].[BC], [FB]=[c].[FB], [HG]=[c].[HG], [alarmStatus]=[c].[alarmStatus], [otherAlarms]=[c].[otherAlarms], [errorStatus]=[c].[errorStatus], [operatingStatus]=[c].[operatingStatus], [isComplete]=[c].[isComplete], [isDeleted]=[c].[isDeleted], [isAlarm]=[c].[isAlarm], [isAlarmCleared]=[c].[isAlarmCleared], [isManualExit]=CASE WHEN [v].[isManualExit] IS NULL OR [c].[isManualExit]=1 THEN [c].[isManualExit] ELSE [v].[isManualExit] END, [ID_Station_Return]=ISNULL([c].[ID_Station_Return], [v].[ID_Station_Return]), [EXIT_EPD_CLOCK]=[c].[EXIT_EPD_CLOCK], [eDosBatteryWarning]=CASE WHEN [v].[eDosBatteryWarning] IS NULL OR [c].[eDosBatteryWarning]=1 THEN [c].[eDosBatteryWarning] ELSE [v].[eDosBatteryWarning] END, [eDosDose07Warning]=CASE WHEN [v].[eDosDose07Warning] IS NULL OR [c].[eDosDose07Warning]=1 THEN [c].[eDosDose07Warning] ELSE [v].[eDosDose07Warning] END, [eDosDose07Alarm]=CASE WHEN [v].[eDosDose07Alarm] IS NULL OR [c].[eDosDose07Alarm]=1 THEN [c].[eDosDose07Alarm] ELSE [v].[eDosDose07Alarm] END, [eDosRate07Warning]=CASE WHEN [v].[eDosRate07Warning] IS NULL OR [c].[eDosRate07Warning]=1 THEN [c].[eDosRate07Warning] ELSE [v].[eDosRate07Warning] END, [eDosRate07Alarm]=CASE WHEN [v].[eDosRate07Alarm] IS NULL OR [c].[eDosRate07Alarm]=1 THEN [c].[eDosRate07Alarm] ELSE [v].[eDosRate07Alarm] END, [eDosDose10Warning]=CASE WHEN [v].[eDosDose10Warning] IS NULL OR [c].[eDosDose10Warning]=1 THEN [c].[eDosDose10Warning] ELSE [v].[eDosDose10Warning] END, [eDosDose10Alarm]=CASE WHEN [v].[eDosDose10Alarm] IS NULL OR [c].[eDosDose10Alarm]=1 THEN [c].[eDosDose10Alarm] ELSE [v].[eDosDose10Alarm] END, [eDosRate10Warning]=CASE WHEN [v].[eDosRate10Warning] IS NULL OR [c].[eDosRate10Warning]=1 THEN [c].[eDosRate10Warning] ELSE [v].[eDosRate10Warning] END, [eDosRate10Alarm]=CASE WHEN [v].[eDosRate10Alarm] IS NULL OR [c].[eDosRate10Alarm]=1 THEN [c].[eDosRate10Alarm] ELSE [v].[eDosRate10Alarm] END
|
||
FROM [dbo].[Visit] [v]
|
||
INNER JOIN [syncLoc].[compareVisit] [c] ON [c].[ID_Station]=[v].[ID_Station] AND [c].[ID_Visit]=[v].[ID_Visit]
|
||
WHERE [v].[ReturnTime] IS NULL AND [v].[isComplete]=0;
|
||
END;
|
||
GO
|
||
GRANT EXECUTE ON [syncLoc].[UpdateRemoteReturns] TO [eDosStation] AS [dbo]
|
||
GO
|