1605 lines
No EOL
94 KiB
Transact-SQL
1605 lines
No EOL
94 KiB
Transact-SQL
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 |