-- HydroTel V6.2020.3 Upgrade Script. Last updated 17/08/2020 -- Update HydroTel Database Compliance version UPDATE dbo.SystemDefaults SET Setting = '6.2020.3' WHERE OptionId = -1 GO ----------------------------------------------------------------------- -- Update installation / upgrade date/time -- NOTE! THIS COMMON TIME STAMPING BLOCK SHOULD BE IN EVERY UPGRADE SCRIPT IF NOT EXISTS (SELECT OptionId FROM dbo.SystemDefaults WHERE OptionId = -2) BEGIN INSERT INTO dbo.SystemDefaults (OptionId,Name,Setting) VALUES (-2,'Installation Date',CONVERT(VARCHAR(33), CURRENT_TIMESTAMP,126)) END ELSE BEGIN UPDATE dbo.SystemDefaults SET Setting = CONVERT(VARCHAR(33), CURRENT_TIMESTAMP, 126) WHERE OptionId = -2 END GO ----------------------------------------------------------------------- -- Request all iRIS 270 and iRIS Under Cover configurations to ensure -- always correctly aligned in the configuration image. -- NOTE! THIS SYNCHRONISING REQUEST SHOULD BE IN EVERY UPGRADE SCRIPT UPDATE dbo.Loggers SET ConfigRequested = 1 where LoggerType in (23,24) GO ----------------------------------------------------------------------- ALTER PROCEDURE [dbo].[sp_HydroTel_Sys_UpdateLoggerInfo] -- ========================================================== -- Author HyQuest Solutions (NZ) Ltd -- Updated on 31/08/2016 -- Description Updates logger info from unsolicited call-in. -- ========================================================== @Logger int = NULL, @Flags int = NULL, @Firmware float = NULL, @Software float = NULL, @SerialNum varchar(8) = '', @IMEI varchar(20) = NULL, @IMSI varchar(20) = NULL AS -- Update firmware and software versions IF (@Firmware is not NULL) BEGIN update dbo.Loggers set FirmwareVersion=@Firmware,SoftwareVersion=@Software,Flags=@Flags where Logger=@Logger END -- Check and update logger serial number if required declare @HydroTelSerialNum varchar(8) declare @LoggerName varchar(30) declare @ParentLogger integer set @LoggerName = (select Name from Loggers where Logger=@Logger) -- Logger serial number not applicable if this logger is a child as it is the parent's. set @ParentLogger = (select ParentLogger from dbo.Loggers where Logger=@Logger) IF (@ParentLogger is null) BEGIN set @HydroTelSerialNum = (select SerialNo from dbo.Loggers where Logger=@Logger) -- Serial number not available in database yet. IF(@HydroTelSerialNum is null) BEGIN update dbo.Loggers set SerialNo=@SerialNum where Logger=@Logger insert into dbo.EventLog (DT,EventType,LogText) values(CURRENT_TIMESTAMP,6,@LoggerName + '. Serial Number [' + @SerialNum + '] obtained from device call-in.') insert into dbo.EventLog (DT,EventType,LogText) values(CURRENT_TIMESTAMP,3,'LOGGER[' + @LoggerName + ']. Serial Number [' + @SerialNum + '] obtained from device call-in.') set @HydroTelSerialNum = @SerialNum END -- Is present in database, but different to reported one. IF(@HydroTelSerialNum <> @SerialNum) BEGIN update Loggers set SerialNo=@SerialNum where Logger=@Logger insert into dbo.EventLog (DT,EventType,LogText) values(CURRENT_TIMESTAMP,6,@LoggerName + '. Serial Number [' + @SerialNum + '] updated from device call-in. Was [' + @HydroTelSerialNum + '].') insert into dbo.EventLog (DT,EventType,LogText) values(CURRENT_TIMESTAMP,3,'LOGGER[' + @LoggerName + ']. Serial Number [' + @SerialNum + '] updated from device call-in. Was [' + @HydroTelSerialNum + '].') END END -- Update IMEI string if valid if (@IMEI is not NULL) BEGIN declare @CurrentIMEI varchar(20) set @CurrentIMEI = (select IMEI from dbo.Loggers where Logger=@Logger) IF(@CurrentIMEI <> @IMEI) OR (@CurrentIMEI IS NULL) BEGIN update Loggers set IMEI=@IMEI where Logger=@Logger insert into dbo.EventLog (DT,EventType,LogText) values(CURRENT_TIMESTAMP,6,@LoggerName + '. IMEI [' + @IMEI + '] updated from device call-in. Was [' + ISNULL(@CurrentIMEI,'') + '].') insert into dbo.EventLog (DT,EventType,LogText) values(CURRENT_TIMESTAMP,3,'LOGGER[' + @LoggerName + ']. IMEI [' + @IMEI + '] updated from device call-in. Was [' + ISNULL(@CurrentIMEI,'') + '].') END END -- Update IMSI string if valid. But deal with possible duplicate first (iBETS 1851). if (@IMSI is not NULL) BEGIN declare @DuplicateLogger int = NULL set @DuplicateLogger = (select top(1) Logger from dbo.SIMs where (Logger <> @Logger) and (IMSI = @IMSI)) IF(@DuplicateLogger is not NULL) BEGIN declare @DuplicateLoggerName VARCHAR(255) set @DuplicateLoggerName = (select Name from dbo.Loggers where Logger = @DuplicateLogger) update dbo.SIMs SET IMSI = 'IMSI_' + CAST (@DuplicateLogger AS NVARCHAR(5)) where Logger = @DuplicateLogger insert into dbo.EventLog (DT,EventType,LogText) values(CURRENT_TIMESTAMP,6,@LoggerName + '. IMSI [' + @IMSI + '] is also incorrectly assigned to ' + @DuplicateLoggerName + ' (SIM swapped?). Reset duplicate IMSI to default.') insert into dbo.EventLog (DT,EventType,LogText) values(CURRENT_TIMESTAMP,3,'LOGGER[' + @LoggerName + '] IMSI [' + @IMSI + '] is also incorrectly assigned to ' + @DuplicateLoggerName + ' (SIM swapped?). Reset duplicate IMSI to default.') END declare @CurrentIMSI varchar(20) set @CurrentIMSI = (select IMSI from dbo.SIMs where Logger=@Logger) IF(@CurrentIMSI is NULL) BEGIN insert into dbo.SIMs (Logger,IMSI) values(@Logger,@IMSI) insert into dbo.EventLog (DT,EventType,LogText) values(CURRENT_TIMESTAMP,6,@LoggerName + '. SIM table entry added. IMSI [' + @IMSI + '] obtained from device call-in.') insert into dbo.EventLog (DT,EventType,LogText) values(CURRENT_TIMESTAMP,3,'LOGGER[' + @LoggerName + ']. SIM table entry added. IMSI [' + @IMSI + '] obtained from device call-in.') END IF(@CurrentIMSI <> @IMSI) BEGIN update dbo.SIMs set IMSI=@IMSI where Logger=@Logger insert into dbo.EventLog (DT,EventType,LogText) values(CURRENT_TIMESTAMP,6,@LoggerName + '. IMSI [' + @IMSI + '] updated from device call-in. Was [' + @CurrentIMSI + '].') insert into dbo.EventLog (DT,EventType,LogText) values(CURRENT_TIMESTAMP,3,'LOGGER[' + @LoggerName + ']. IMSI [' + @IMSI + '] updated from device call-in. Was [' + @CurrentIMSI + '].') END -- Check if a GDSP SIM that is new or changed IF ((@CurrentIMSI is NULL) OR (@CurrentIMSI <> @IMSI)) BEGIN IF (SUBSTRING(@IMSI,1,5) = '20404') BEGIN update dbo.SIMs set SMSNumber=@IMSI where Logger=@Logger insert into dbo.EventLog (DT,EventType,LogText) values(CURRENT_TIMESTAMP,6,@LoggerName + '. SMSNumber [' + @IMSI + '] is now IMSI from device call-in. GDSP SIM.') insert into dbo.EventLog (DT,EventType,LogText) values(CURRENT_TIMESTAMP,3,'LOGGER[' + @LoggerName + ']. SMSNumber [' + @IMSI + '] is now IMSI from device call-in. GDSP SIM.') END END END GO ----------------------------------------------------------------------- -- New column Autoscale. iBETS 2209 ALTER TABLE dbo.FavouriteTraces ADD AutoScale SMALLINT NOT NULL DEFAULT (0) GO ----------------------------------------------------------------------- -- Change user password system to use better encryption -- iBETS 375, -- Create and populate passcode backup column. ALTER TABLE dbo.Users ADD Password VARCHAR(20) NULL GO -- Copy all existing encoded passwords to the new field -- NOTE: The passcode conversion app uses this as a source -- to repopulate the Password column with new encrypted strings. UPDATE dbo.Users SET Password = PassCode GO -- Now extend Users.Password field to cater for new method of encryption. -- E.g. for a 20 char passcode, the encrypted version is 44 chars. ALTER Table dbo.Users ALTER COLUMN Password VARCHAR(50) GO -- Lastly, enable NULLs on old PassCode field ALTER TABLE dbo.Users ALTER COLUMN PassCode VARCHAR(20) NULL GO ----------------------------------------------------------------------- --Create new AlarmGroups table. iBETS 1978 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[AlarmGroups]( [AlarmGroup] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NULL, [Description] [varchar](100) NULL, CONSTRAINT [PK_ALARMGROUPS] PRIMARY KEY CLUSTERED ( [AlarmGroup] 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 -- Create default alarm group 1. This must not be deleted -- INSERT INTO [dbo].[AlarmGroups] (Name,Description) VALUES ('Default','Default alarm group') GO -- Add new AlarmGroup field to Alarms. Default is 1 ALTER TABLE dbo.Alarms ADD AlarmGroup INT NOT NULL DEFAULT 1 GO ALTER TABLE dbo.Alarms ADD CONSTRAINT FK_Alarms_AlarmGroup FOREIGN KEY (AlarmGroup) REFERENCES dbo.AlarmGroups (AlarmGroup) ON DELETE CASCADE ON UPDATE CASCADE GO ------------------------------------------------------------------------------------------- -- Make UDP configuration specific (not shared with TCPSocket). Adding Tier 3 DMR support iBETS 2393 UPDATE dbo.InterfaceTypes SET InterfacePlugin = 'cpi_IntUDP.bpl' WHERE InterfaceType = 6 GO ------------------------------------------------------------------------------------------- -- Add default icon colour (clBlack) on insert ALTER TABLE AlarmSeverities ADD CONSTRAINT DF_Severity DEFAULT 0 FOR Severity GO ------------------------------------------------------------------------------------------- -- Alter V_HydroTel_Sys_PointDescriptions to include Site for quarantine filter parameter SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER VIEW [dbo].[V_HydroTel_Sys_PointDescriptions] AS SELECT p.Point, CASE WHEN o.Name = p.Name THEN CAST(s.Name + '\' + p.Name AS varchar(150)) ELSE CAST(s.Name + '\' + o.Name + '\' + p.Name AS varchar(150)) END AS PointDescription,s.Site FROM dbo.Sites AS s INNER JOIN dbo.Objects AS o ON s.Site = o.Site INNER JOIN dbo.Points AS p ON o.Object = p.Object GO ------------------------------------------------------------------------------------------- SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- Include check for existing row in SMSQueue to prevent duplicates ALTER PROCEDURE [dbo].[sp_HydroTel_Sys_SendLoggerSMS] @Logger INTEGER = 0, @SMSMessage VARCHAR(80) = 'GOL' -- Default if this parameter not passed AS BEGIN -- ============================================= -- Author HyQuest Solutions (NZ) Ltd. -- Create date 13/08/2020 -- Description Adds SMS GOL request for Dynamic IP loggers if SMS Agent exists -- If message is 'GOL' then will send WAKE command instead of GOL -- if it is GDSP and the device supports it. -- Also logs event to the comms log. iBETS 1648 -- ============================================= SET NOCOUNT ON; DECLARE @SMSNumber VARCHAR(20) DECLARE @LoggerName VARCHAR(50) DECLARE @LoggerType INTEGER DECLARE @SoftwareVersion FLOAT DECLARE @Interface INTEGER SELECT @SMSNumber = dbo.SIMs.SMSNumber, @Interface = dbo.Loggers.Interface, @LoggerName = dbo.Loggers.Name, @LoggerType = dbo.Loggers.LoggerType, @SoftwareVersion = dbo.Loggers.SoftwareVersion FROM dbo.Loggers INNER JOIN dbo.SIMs ON dbo.Loggers.Logger = dbo.SIMs.Logger WHERE dbo.Loggers.Logger = @Logger IF LEN(@SMSNumber) > 0 BEGIN -- Check if GDSP and the logger type / software version supports 'WAKE' -- iRIS 350FX (logger type 20) V2.10+ or iCE3FX (logger type 21) V1.80+ IF (@SMSMessage = 'GOL') BEGIN IF (LEFT(@SMSNumber,5) = '20404') AND ((((@LoggerType = 20) AND (@SoftwareVersion >= 2.10))) OR ((@LoggerType = 21) AND (@SoftwareVersion >= 1.80))) BEGIN SET @SMSMessage = 'WAKE' -- Message that triggers more efficient WakeUp via GDSP END END -- SMS agent installed and same message is not already queued IF EXISTS (SELECT Hostname FROM dbo.SystemSettings WHERE SettingName like 'HydSMSAgent%') AND NOT EXISTS (SELECT * FROM dbo.SMSQueue WHERE ((PhoneNo = @SMSNumber) AND (Message = @SMSMessage))) BEGIN INSERT INTO dbo.SMSQueue (Recipient, PhoneNo, [Message]) VALUES (@LoggerName, @SMSNumber, @SMSMessage) INSERT INTO dbo.CommsLog (DT,Logger,[Type],[Message],ReqDT,Interface,CommsLevel) VALUES(CURRENT_TIMESTAMP,@Logger,1, @SMSMessage + ' request sent to device via SMS [' + @SMSNumber + '].',CURRENT_TIMESTAMP,@Interface,2) END END END ------------------------------------------------------------------------------------------- SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[v_HydroTel_Sys_Set_Interface_SOD_To_DT] @Interface INTEGER = 0, @DT DATETIME AS BEGIN -- ============================================= -- Author HyQuest Solutions (NZ) Ltd. -- Create date 17/08/2020 -- Description Sets the SOD for each logger on an interface -- to its cached SOD for a previous DT. -- ============================================= -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; UPDATE Loggers SET SOD = lc.SOD FROM (SELECT lc1.logger,lc1.SOD FROM LoggerSODCache lc1 inner join (SELECT logger, min(dt) as DT FROM LoggerSODCache WHERE DT >= @DT GROUP BY logger ) lc2 ON lc1.dt = lc2.dt and lc1.logger = lc2.logger WHERE lc1.logger in (SELECT logger FROM loggers WHERE interface = @Interface) ) AS lc WHERE loggers.logger = lc.Logger END GO -------------------------------------------------------------------------------------------