luc/eDosStationFull
1
0
Fork 0
Code Issues Pull requests Projects Releases Packages Wiki Activity Actions
eDosStationFull/scripts/edosstation.sql

1605 lines
94 KiB
MySQL
Raw Permalink Normal View History

first
2026-08-18 12:15:26 +02:00
<EFBFBD><EFBFBD>USE [eDosLocal]
GO
CREATE USER [eDosStation] FOR LOGIN [eDosStation] WITH DEFAULT_SCHEMA=[dbo]
GO
CREATE SCHEMA [export]
GO
CREATE SCHEMA [syncLoc]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AddLimitPerson](
[ID_ChangeLimit] [int] IDENTITY(1,1) NOT NULL,
[PersID] [int] NULL,
[PersID_Resp] [int] NULL,
[DateStart] [date] NULL,
[IncreaseYearToDateLimit] [float] NULL,
[IncreaseQuarterLimit] [float] NULL,
[IncreaseMonthlyLimit] [float] NULL,
[JustificationIncrease] [varchar](5000) NULL,
[DateEnd] [date] NULL,
[deleted] [bit] NULL,
CONSTRAINT [PK_AddLimitPerson] PRIMARY KEY CLUSTERED
(
[ID_ChangeLimit] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AlarmEPD](
[ID_AlarmEPD] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[AlarmEPDName] [varchar](40) NULL,
[EPDRad] [varchar](2) NULL,
[Hp10Dose1] [float] NULL,
[Hp10Dose2] [float] NULL,
[Hp07Dose_bis] [float] NULL,
[HP10Rate1ON] [float] NULL,
[HP10Rate2ON] [float] NULL,
[HP07RateON] [float] NULL,
[HP10Rate1OFF] [float] NULL,
[HP10Rate2OFF] [float] NULL,
[HP07RateOFF] [float] NULL,
[MinWorkerDosisMargin] [float] NULL,
CONSTRAINT [PK_AlarmEPD] PRIMARY KEY CLUSTERED
(
[ID_AlarmEPD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
GRANT ALTER ,DELETE ,INSERT ,SELECT,VIEW DEFINITION ON [dbo].[AlarmEPD] TO [eDosStation] AS [dbo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PersonAlarms](
[PersID] [int] NOT NULL,
[VisitTime] [datetime] NOT NULL,
[ID_Station] [int] NOT NULL,
[ID_Visit] [int] NOT NULL,
PRIMARY KEY NONCLUSTERED
(
[PersID] ASC,
[VisitTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
GRANT ALTER ,DELETE ,INSERT ,SELECT,VIEW DEFINITION ON [dbo].[PersonAlarms] TO [eDosStation] AS [dbo]
go
CREATE TABLE [dbo].[CommentOnEvent](
[ID_CommnentOnEvent] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[ID_HPHResponsible] [int] NULL,
[TriggerTable] [varchar](100) NULL,
[TriggerField] [varchar](100) NULL,
[TriggerStatus] [bit] NULL,
[CommentDate] [date] NULL,
[CommentApplyFrom] [date] NULL,
[Comment_FR] [varchar](5000) NULL,
[Comment_NL] [varchar](5000) NULL,
[Comment_EN] [varchar](5000) NULL,
[Explaination] [varchar](5000) NULL,
CONSTRAINT [PK_CommentOnEvent] PRIMARY KEY CLUSTERED
(
[ID_CommnentOnEvent] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
GRANT ALTER ,DELETE ,INSERT ,SELECT,VIEW DEFINITION ON [dbo].[CommentOnEvent] TO [eDosStation] AS [dbo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CommentToInstallation](
[ID_CommentToInstallation] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[PersID] [int] NULL,
[Inst_CODE] [int] NOT NULL,
[Comment] [varchar](500) NOT NULL,
[CommentDate] [datetime2](7) NULL,
[CommentDateSTART] [datetime2](7) NOT NULL,
[CommentDateSTOP] [datetime2](7) NOT NULL,
[deleted] bit NOT NULL,
CONSTRAINT [PK_CommentToInstallation] PRIMARY KEY CLUSTERED
(
[ID_CommentToInstallation] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CommentToInstallation] ADD CONSTRAINT [DF_CommentToInstallation_deleted] DEFAULT ((0)) FOR [deleted]
go
GRANT ALTER ,DELETE ,INSERT ,SELECT,VIEW DEFINITION ON [dbo].[CommentToInstallation] TO [eDosStation] AS [dbo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CommentToPerson](
[ID_CommentToPerson] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[PersID] [int] NOT NULL,
[PersID_AgentSC] [int] NULL,
[PersID_HPHResp] [int] NULL,
[Comment_FR] [varchar](1000) NULL,
[Comment_NL] [varchar](1000) NULL,
[Comment_EN] [varchar](1000) NULL,
[CommentDate] [date] NULL,
[CommentDateApply] [date] NOT NULL,
[NumberOfTimeToDisplay] [int] NOT NULL,
[TimesDisplayed] [int] NOT NULL,
[LastDisplayed] [datetime] NULL,
[deleted] [bit] NOT NULL,
CONSTRAINT [PK_CommentToPerson] PRIMARY KEY CLUSTERED
(
[ID_CommentToPerson] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
GRANT ALTER ,DELETE ,INSERT ,SELECT,VIEW DEFINITION ON [dbo].[CommentToPerson] TO [eDosStation] AS [dbo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Dose_DocD](
[ID_DoseDocD] [int] IDENTITY(1,1) NOT NULL,
[PersID] [int] NOT NULL,
[Date_Dose] [date] NULL,
[Dose_52W_microSv] [float] NULL,
[PersID_AgentSC] [int] NULL,
[deleted] [bit] NULL,
CONSTRAINT [PK_Dose_DocD] PRIMARY KEY CLUSTERED
(
[ID_DoseDocD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EPD](
[ID_EPD] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[EPDType] [int] NOT NULL,
[TypeAanduidingToestellen] [int] NULL,
[EPD_InternalID] [int] NULL,
[EPD_sn] [varchar](8) NULL,
[EPDstatus] [varchar](50) NULL,
[EPDNextCalibrationDate] [date] NULL,
[LokalToestellen] [varchar](50) NULL,
[LastInstallation] [int] NULL,
[LastUsedEntry] [date] NULL,
[LastUsedExit] [date] NULL,
CONSTRAINT [PK_EPD] PRIMARY KEY CLUSTERED
(
[ID_EPD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
GRANT ALTER ON [dbo].[EPD] TO [eDosStation] AS [dbo]
GO
GRANT DELETE ON [dbo].[EPD] TO [eDosStation] AS [dbo]
GO
GRANT INSERT ON [dbo].[EPD] TO [eDosStation] AS [dbo]
GO
GRANT SELECT ON [dbo].[EPD] TO [eDosStation] AS [dbo]
GO
GRANT VIEW DEFINITION ON [dbo].[EPD] TO [eDosStation] AS [dbo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EPDType](
[EPDType] [int] NOT NULL,
[EPDType_old] [int] NULL,
[EPDTypeDescription] [varchar](20) NULL,
[TelerakDeviceID] [int] NULL,
[EPDRad] [varchar](2) NULL,
[TypeAanduidingToestellen] [int] NULL,
CONSTRAINT [PK_EPDType] PRIMARY KEY CLUSTERED
(
[EPDType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
GRANT ALTER ,DELETE,INSERT,SELECT,VIEW DEFINITION ON [dbo].[EPDType] TO [eDosStation] AS [dbo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Installation](
[Inst_CODE] [int] NOT NULL,
[InstallationName] [varchar](50) NULL,
CONSTRAINT [PK_Installation] PRIMARY KEY CLUSTERED
(
[Inst_CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
GRANT ALTER ,DELETE,INSERT,SELECT,VIEW DEFINITION ON [dbo].[Installation] TO [eDosStation] AS [dbo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LimitPerson](
[ConCode] [varchar](10) NOT NULL,
[YearToDateLimit] [float] NOT NULL,
[QuarterLimit] [float] NOT NULL,
[MonthlyLimit] [float] NOT NULL,
[AccessAllow] [bit] NOT NULL,
CONSTRAINT [PK_LimitPerson] PRIMARY KEY CLUSTERED
(
[ConCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Person1](
[PersID] [int] NOT NULL,
[Type] [varchar](50) NULL,
[CSN] [varchar](20) NOT NULL,
[Badge] [varchar](10) NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[NickName] [varchar](50) NULL,
[conCode] [varchar](10) NULL,
[AccessAllow] [bit] NULL,
[Language] [char](1) NULL,
[StationVisitIDLocked] [int] NULL,
[Deleted] [bit] NOT NULL,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[PersID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PersonAll](
[persid] [int] NOT NULL,
[name] [varchar](50) NULL,
[firstname] [varchar](50) NULL,
[callingname] [varchar](50) NULL,
[persGroup] [char](1) NULL,
[sagEnd] [date] NULL,
[CSN] [varchar](14) NULL,
PRIMARY KEY CLUSTERED
(
[persid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PersonInfoSnapshot](
[PersID] [int] NOT NULL,
[Type] [varchar](50) NULL,
[CSN] [char](14) NOT NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[NickName] [varchar](50) NULL,
[AccessAllow] [bit] NULL,
[Language] [char](1) NOT NULL,
[MarginHP10_M] [decimal](10, 2) NULL,
[MarginHP10_Y] [decimal](10, 2) NULL,
[Margin] [decimal](10, 2) NULL,
[VisitIDLocked] [int] NOT NULL,
[ID_CommentToPerson] [int] NULL,
[Comment] [varchar](1000) NULL,
[Deleted] [bit] NOT NULL,
[ReasonNoAccess] [varchar](5000) NOT NULL,
[Dose_52W_microSv] [float] NULL,
CONSTRAINT [PK_PersonInfoSnapshot] PRIMARY KEY CLUSTERED
(
[PersID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
GRANT ALTER ,DELETE,INSERT,SELECT,VIEW DEFINITION ON [dbo].[PersonInfoSnapshot] TO [eDosStation] AS [dbo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StationSettings](
[ID_Station] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[PCNr] [varchar](20) NOT 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) NULL,
[useTestButtons] [bit] NULL,
[useBeep] [tinyint] NULL,
[checkUser] [bit] NOT NULL,
[EPDTimeout] [int] NULL,
[EPDREtries] [int] NULL,
[RestartCom] [bit] NULL,
[RestartComFirst] [bit] NULL,
[OnlyMK2] [bit] NULL,
[AllowMultipleEPD] [bit] NULL,
CONSTRAINT [PK_StationSettings] PRIMARY KEY CLUSTERED
(
[PCNr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[StationSettings] ADD CONSTRAINT [DF_StationSettings_checkUser] DEFAULT ((0)) FOR [checkUser]
GO
GRANT ALTER ,DELETE,INSERT,SELECT,VIEW DEFINITION ON [dbo].[StationSettings] TO [eDosStation] AS [dbo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Task](
[ID_Task] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Inst_CODE] [int] NULL,
[TaskType_CODE] [int] NULL,
[Task_Name] [varchar](max) NULL,
[TaskDescription] [varchar](50) NULL,
[EPDRad] [varchar](2) NOT NULL,
[ID_AlarmEPD] [int] NULL,
[TaskDateStart] [datetime] NULL,
[TaskDateEnd] [datetime] NULL,
[TaskEnable] [bit] NULL,
[ID_FNGainANGain] [int] NULL,
CONSTRAINT [PK_Task_1] PRIMARY KEY CLUSTERED
(
[ID_Task] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TaskInfoSnapshot](
[ID_Task] [int] NOT NULL,
[Inst_CODE] [int] NULL,
[TaskType_CODE] [int] NULL,
[Task_Name] [varchar](max) NULL,
[TaskDescription] [varchar](50) NOT NULL,
[EPDRad] [varchar](2) NOT NULL,
[ID_AlarmEPD] [int] NULL,
[TaskDateStart] [datetime] NULL,
[TaskDateEnd] [datetime] NULL,
[TaskEnable] [bit] NULL,
[ID_FNGainANGain] [int] NULL,
[Hp10Dose1] [float] NULL,
[Hp10Dose2] [float] NULL,
[Hp07Dose] [float] NULL,
[HP10Rate1ON] [float] NULL,
[HP10Rate1OFF] [float] NULL,
[HP10Rate2ON] [float] NULL,
[HP10Rate2OFF] [float] NULL,
[HP07RateON] [float] NULL,
[HP07RateOFF] [float] NULL,
[FNGain_ANGainValue] [float] NULL,
[InstallationActivity] [varchar](100) NULL,
[Description] [varchar](500) NULL,
[MinWorkerDosisMargin] [float] NULL,
CONSTRAINT [PK_TaskInfoSnapshot] PRIMARY KEY CLUSTERED
(
[ID_Task] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
GRANT ALTER ,DELETE,INSERT,SELECT,VIEW DEFINITION ON [dbo].[TaskInfoSnapshot] TO [eDosStation] AS [dbo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Visit](
[ID_Station] [int] NOT NULL,
[ID_Visit] [int] NOT NULL,
[Inst_CODE] [int] NOT NULL,
[PersID] [int] NULL,
[ID_Task] [int] NULL,
[EntryTime] [datetime] NULL,
[ReturnTime] [datetime] NULL,
[Hp10Dose] [float] NULL,
[Hp07Dose] [float] NULL,
[Hp10Total] [float] NULL,
[Hp07Total] [float] NULL,
[Hp10Peak] [float] NULL,
[Hp07Peak] [float] NULL,
[Hp10Time] [datetime] NULL,
[Hp07Time] [datetime] NULL,
[ID_EPD] [int] NULL,
[QualityData] [int] NULL,
[K1] [int] NULL,
[K2] [int] NULL,
[K3] [int] NULL,
[K4] [int] NULL,
[K5] [int] NULL,
[K6] [int] NULL,
[SG] [int] NULL,
[BC] [int] NULL,
[FB] [int] NULL,
[HG] [int] NULL,
[alarmStatus] [int] NULL,
[otherAlarms] [int] NULL,
[errorStatus] [int] NULL,
[operatingStatus] [int] NULL,
[isBG] [bit] NULL,
[isComplete] [bit] NOT NULL,
[isDeleted] [bit] NOT NULL,
[isAlarm] [bit] NOT NULL,
[isAlarmCleared] [bit] NOT NULL,
[isManualExit] [bit] NOT NULL,
[ID_Station_Return] [int] NULL,
[ENTRY_EPD_CLOCK] [numeric](10, 0) NULL,
[EXIT_EPD_CLOCK] [numeric](10, 0) NULL,
[TIME_LOST] [int] NULL,
[syncedEntry] [bit] NOT NULL,
[syncedExit] [bit] NOT NULL,
[syncedEntryTime] [datetime] NULL,
[syncedExitTime] [datetime] NULL,
[epdType] [tinyint] NULL,
[epdID] [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,
[isFault] [bit] NULL,
CONSTRAINT [PK_Visit_1] PRIMARY KEY CLUSTERED
(
[ID_Station] ASC,
[ID_Visit] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Visit] ADD CONSTRAINT [DF_Visit_isDeleted] DEFAULT ((0)) FOR [isDeleted]
ALTER TABLE [dbo].[Visit] ADD CONSTRAINT [DF_Visit_isAlarm] DEFAULT ((0)) FOR [isAlarm]
ALTER TABLE [dbo].[Visit] ADD CONSTRAINT [DF_Visit_isAlarmCleared] DEFAULT ((0)) FOR [isAlarmCleared]
ALTER TABLE [dbo].[Visit] ADD CONSTRAINT [DF_Visit_isManualExit] DEFAULT ((0)) FOR [isManualExit]
ALTER TABLE [dbo].[Visit] ADD CONSTRAINT [DF_Visit_syncedEntry] DEFAULT ((0)) FOR [syncedEntry]
ALTER TABLE [dbo].[Visit] ADD CONSTRAINT [DF_Visit_syncedExit] DEFAULT ((0)) FOR [syncedExit]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [syncLoc].[SyncStatus](
[ID_Station] [int] NOT NULL,
[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,
CONSTRAINT [PK_SyncStatus] PRIMARY KEY CLUSTERED
(
[ID_Station] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[StationSettings] ADD CONSTRAINT [DF_StationSettings_checkUser] DEFAULT ((0)) FOR [checkUser]
GO
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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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
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
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
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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetPersonInfo] @CSN CHAR(14) =NULL, @PersID INT = NULL AS
--DECLARE @PersID INT = null, @CSN CHAR(14) = 'TP10';
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;
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] = CASE WHEN 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 a.[hasEPD] = 1 THEN 'EPD not returned' ELSE a.[ReasonNoAccess] END,
[a].[Dose_52W_microSv],
[a].[hasEPD],
activeStation = @lid_station,
activeVisit = @lid_visit
FROM(SELECT
[p].[PersID],
[p].[Type],
[p].[CSN],
[p].[FirstName],
[p].[LastName],
[p].[NickName],
[p].[AccessAllow],
[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)
FROM [dbo].[PersonInfoSnapshot] [p]
WHERE [p].[PersID] = @PersID) a
GO
GRANT EXECUTE ON [dbo].[GetPersonInfo] TO [eDosStation] AS [dbo]
GO
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 [t].[MinWorkerDosisMargin] > @WorkerDosisMargin THEN
1
ELSE
0
END
)
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
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
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
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
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
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
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
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 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
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
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
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
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
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
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
USE [eDosLocal]
GO
/****** Object: Sequence [dbo].[VisitSequence] Script Date: 7/02/2022 9:50:56 ******/
IF OBJECT_ID('dbo.VisitSequence') IS NULL
begin
CREATE SEQUENCE [dbo].[VisitSequence]
AS [INT]
START WITH 100
INCREMENT BY 1
MINVALUE 0
MAXVALUE 2147483647
CACHE
GRANT UPDATE ON [dbo].[VisitSequence] TO [eDosStation]
end
go
---- Luc 2022-03-25
IF OBJECT_ID('[syncLoc].[compareVisit]') IS NULL
begin
CREATE TABLE [syncLoc].[compareVisit](
[ID_Station] [INT] NOT NULL,
[ID_Visit] [INT] NOT NULL,
[Inst_CODE] [INT] NOT NULL,
[PersID] [INT] NULL,
[ID_Task] [INT] NULL,
[EntryTime] [DATETIME] NULL,
[ReturnTime] [DATETIME] NULL,
[Hp10Dose] [FLOAT] NULL,
[Hp07Dose] [FLOAT] NULL,
[Hp10Total] [FLOAT] NULL,
[Hp07Total] [FLOAT] NULL,
[Hp10Peak] [FLOAT] NULL,
[Hp07Peak] [FLOAT] NULL,
[Hp10Time] [DATETIME] NULL,
[Hp07Time] [DATETIME] NULL,
[ID_EPD] [INT] NULL,
[QualityData] [INT] NULL,
[K1] [INT] NULL,
[K2] [INT] NULL,
[K3] [INT] NULL,
[K4] [INT] NULL,
[K5] [INT] NULL,
[K6] [INT] NULL,
[SG] [INT] NULL,
[BC] [INT] NULL,
[FB] [INT] NULL,
[HG] [INT] NULL,
[alarmStatus] [INT] NULL,
[otherAlarms] [INT] NULL,
[errorStatus] [INT] NULL,
[operatingStatus] [INT] NULL,
[isBG] [BIT] NULL,
[isComplete] [BIT] NOT NULL,
[isDeleted] [BIT] NOT NULL,
[isAlarm] [BIT] NOT NULL,
[isAlarmCleared] [BIT] NOT NULL,
[isManualExit] [BIT] NOT NULL,
[ID_Station_Return] [INT] NULL,
[ENTRY_EPD_CLOCK] [NUMERIC](10, 0) NULL,
[EXIT_EPD_CLOCK] [NUMERIC](10, 0) NULL,
[TIME_LOST] [INT] NULL,
[epdID] [INT] NULL,
CONSTRAINT [PK_compareVisit] PRIMARY KEY CLUSTERED
(
[ID_Station] ASC,
[ID_Visit] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];
GRANT ALTER,INSERT,UPDATE,DELETE,select ON [syncLoc].[compareVisit] TO edosStation
END
GO
CREATE OR ALTER PROCEDURE [syncLoc].[UpdateRemoteReturns] @ID_LocalStation INT AS
-- Luc 2022-03-25
--DECLARE @ID_LocalStation INT = 9;
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])
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]
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 [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] = ISNULL([c].[isManualExit], [v].[isManualExit]),
[ID_Station_Return] = ISNULL([c].[ID_Station_Return], [v].[ID_Station_Return]),
[EXIT_EPD_CLOCK] = [c].[EXIT_EPD_CLOCK]
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;
GO
GRANT EXECUTE ON [syncLoc].[UpdateRemoteReturns] TO [eDosStation]
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]
go
go
-- nu edosStationName
Reference in a new issue Copy permalink