/* 
	ControlProcedures.sql
	Stored procedure generation statements for the Control System database schema
*/

/* --------------------------------------------------------- 
 Remove our existing stored procedures 
*/

EXEC('master.dbo.xp_cmdshell ''sqlcmd -E -d control -i C:\nvision\envy\install\RemoveExistingStoredProcsAndTriggers.sql'' ')

/* --------------------------------------------------------- 
 [Re-]Create our stored procedures 
*/
PRINT 'Adding stored procedures' 
go

use control
go

CREATE PROCEDURE NV_ConvertIntToString 
	@intValue integer, @vchValue varchar(200) OUTPUT
AS

IF ( @intValue = null )
	SELECT @vchValue = 'null'
ELSE
	SELECT @vchValue = CAST(@intValue AS VARCHAR(200))

RETURN
go

CREATE PROCEDURE NV_ForeignKeyInfo
@tableName varchar(128)
AS
SELECT CCU2.COLUMN_NAME, CCU.TABLE_NAME
FROM (INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CCU ON
RC.UNIQUE_CONSTRAINT_NAME = CCU.CONSTRAINT_NAME)
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CCU2 ON RC.CONSTRAINT_NAME = CCU2.CONSTRAINT_NAME
WHERE (((CCU2.TABLE_NAME)=@tableName));
go

CREATE PROCEDURE NV_AllCategories
AS
SELECT distinct c.Category_ID,c.Category_Mnemonic FROM Category c
Order By c.Category_Mnemonic,c.Category_ID
go

CREATE PROCEDURE NV_AllDstDevices
AS
SELECT distinct d.Device_ID,d.Device_Mnemonic FROM Device d
INNER JOIN PhysConn p ON d.Device_ID=p.PhysConn_Device
WHERE p.PhysConn_PhysLevelPortDir IN (2,3)
Order By d.Device_Mnemonic,d.Device_ID
go

CREATE PROCEDURE NV_AllDstLevels
AS
SELECT  distinct p.PhysConn_VirtualLevel,v.VirtualLevel_Mnemonic FROM PhysConn p
INNER JOIN VirtualLevel v ON p.PhysConn_VirtualLevel=v.VirtualLevel_ID WHERE
p.PhysConn_PhysLevelPortDir IN (2,3)
Order By v.VirtualLevel_Mnemonic
go

CREATE PROCEDURE NV_AllDstCategories AS
SELECT DISTINCT c.category_id, c.category_mnemonic 
FROM category c join categorydetail d on c.category_id = d.categorydetail_category 
join physconn p on d.categorydetail_device = p.physconn_device where p.physconn_physlevelportdir in (2, 3)
Order By c.Category_Mnemonic,c.Category_ID
go

CREATE PROCEDURE NV_LevelsForDstDevice @inDevID integer
AS
SELECT p.PhysConn_VirtualLevel,v.VirtualLevel_Mnemonic FROM PhysConn p
INNER JOIN VirtualLevel v ON p.PhysConn_VirtualLevel=v.VirtualLevel_ID WHERE
p.PhysConn_PhysLevelPortDir IN (2,3) AND p.PhysConn_Device=@inDevID
Order By v.VirtualLevel_Mnemonic
go

CREATE PROCEDURE NV_AllSrcDevices
AS
SELECT distinct d.Device_ID,d.Device_Mnemonic FROM Device d
INNER JOIN PhysConn p ON d.Device_ID=p.PhysConn_Device
WHERE p.PhysConn_PhysLevelPortDir IN (1,3)
Order By d.Device_Mnemonic,d.Device_ID
go

CREATE PROCEDURE NV_AllSrcLevels
 AS
SELECT distinct p.PhysConn_VirtualLevel,v.VirtualLevel_Mnemonic FROM PhysConn p
INNER JOIN VirtualLevel v ON p.PhysConn_VirtualLevel=v.VirtualLevel_ID WHERE
p.PhysConn_PhysLevelPortDir IN (1,3)
Order By v.VirtualLevel_Mnemonic
go

CREATE PROCEDURE NV_AllSrcCategories AS
SELECT DISTINCT c.category_id, c.category_mnemonic 
FROM category c join categorydetail d on c.category_id = d.categorydetail_category 
join physconn p on d.categorydetail_device = p.physconn_device where p.physconn_physlevelportdir in (1, 3)
Order By c.Category_Mnemonic,c.Category_ID
go

CREATE PROCEDURE NV_LevelsForSrcDevice @inDevID integer
 AS
SELECT  p.PhysConn_VirtualLevel,v.VirtualLevel_Mnemonic FROM PhysConn p
INNER JOIN VirtualLevel v ON p.PhysConn_VirtualLevel=v.VirtualLevel_ID WHERE
p.PhysConn_PhysLevelPortDir IN (1,3) AND p.PhysConn_Device=@inDevID
Order By v.VirtualLevel_Mnemonic
go

CREATE PROCEDURE NV_DevicesInCategory @inCategoryID integer
AS
SELECT d.CategoryDetail_Device FROM CategoryDetail d
WHERE d.CategoryDetail_Category=@inCategoryID
go

CREATE PROCEDURE NV_GetEnvyConfigHostName
AS
DECLARE @NameString varchar(45)
SET @NameString='COMPUTERNAME='
SELECT @NameString+@@SERVERNAME
go

CREATE PROCEDURE NV_DeviceNameFromID @inDevID integer
AS
SELECT  d.Device_Mnemonic FROM Device d
WHERE d.Device_ID=@inDevID
go

CREATE PROCEDURE NV_SalvoNameFromID @inSalvoID integer
AS
SELECT  s.Salvo_Mnemonic FROM Salvo s
WHERE s.Salvo_ID=@inSalvoID
go

CREATE PROCEDURE NV_CategoryNameFromID @inCatID integer
AS
SELECT  c.Category_Mnemonic FROM Category c
WHERE c.Category_ID=@inCatID
go

CREATE PROCEDURE NV_AllSalvos
AS
SELECT distinct s.Salvo_ID,s.Salvo_Mnemonic FROM Salvo s
Order By s.Salvo_Mnemonic,s.Salvo_ID
go

CREATE PROCEDURE NV_GetApplicableTielinePaths 
@inSrcPhysLevel integer,
@inSrcSignalType integer,
@inDstPhysLevel integer,
@inDstSignalType integer
AS
select t2p.Tieline2Path_ID, t2p.Tieline2Path_Cost from
Tieline2Path t2p where
t2p.Tieline2Path_SourcePhysLevel = @inSrcPhysLevel AND
t2p.Tieline2Path_SourceSignalType = @inSrcSignalType AND
t2p.Tieline2Path_DestinationPhysLevel = @inDstPhysLevel AND
t2p.Tieline2Path_DestinationSignalType = @inDstSignalType
order by Tieline2Path_Cost
go

CREATE PROCEDURE NV_GetApplicableTielinePathsAndDetails
@inSrcPhysLevel integer,
@inSrcSignalType integer,
@inDstPhysLevel integer,
@inDstSignalType integer
AS
select t2p.Tieline2Path_ID, 
t2p.Tieline2Path_SourcePhysLevel, t2p.Tieline2Path_SourceSignalType, 
t2p.Tieline2Path_DestinationPhysLevel, t2p.Tieline2Path_DestinationSignalType,
t2p.Tieline2Path_Cost, t2pd.Tieline2PathDetail_PhysLevel, t2pd.Tieline2PathDetail_SignalType,
t2pd.Tieline2PathDetail_SegmentIndex, 
t2pd.Tieline2PathDetail_InputPort, t2pd.Tieline2PathDetail_OutputPort, 
Tieline2PathDetail_InputTieline, Tieline2PathDetail_OutputTieline from 
Tieline2Path t2p join Tieline2PathDetail t2pd on
t2p.Tieline2Path_ID = t2pd.Tieline2PathDetail_Tieline2Path
WHERE
t2p.Tieline2Path_SourcePhysLevel = @inSrcPhysLevel AND
t2p.Tieline2Path_SourceSignalType = @inSrcSignalType AND
t2p.Tieline2Path_DestinationPhysLevel = @inDstPhysLevel AND
t2p.Tieline2Path_DestinationSignalType = @inDstSignalType
order by Tieline2Path_Cost
go

CREATE PROCEDURE NV_AddSysParamDefaults 
AS  
 declare @sError varchar(255)
 set @sError = NULL
 if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NvSysParam]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  set @sError = 'Missing table NvSysParam'
 if @sError IS NULL
 BEGIN 
   if (not exists (select * from [dbo].NvSysParam where [dbo].NvSysParam.NvSysParam_Name = 'PANEL_PRESET_TIMEOUT'))
   BEGIN
     insert into NvSysParam(NvSysParam_Name, NvSysParam_Value, NvSysParam_Desc) values('PANEL_PRESET_TIMEOUT', '30', 'Timeout delay in seconds for preset blanking on Nv9601 panels when using semiautomatic data routing.')
   END
 END
 ELSE
 BEGIN 
	PRINT @sError
 END
 SELECT @sError
go

CREATE PROCEDURE NV_AddControlDBDefaults 
@inHostname varchar(15),
@bDoSerialCtrlPtsToo int
AS
BEGIN
 /* beware of changing stuff in this storedproc! if you do, it might break DB\generate_html\GenerateDBDoc.pl! (it extracts data from this storedproc.) */
 declare @sError varchar(255)
 declare @hostID int
 set @sError = NULL
 if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NVUser]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  set @sError = 'Missing table NVUser'
 else if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Host]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  set @sError = 'Missing table Host'
 else if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ControlPoint]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  set @sError = 'Missing table ControlPoint'
 else if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[App]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  set @sError = 'Missing table App'
 else if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Suffix]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  set @sError = 'Missing table Suffix'
 else if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Tieline2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  set @sError = 'Missing table Tieline2'
 else if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SignalType]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  set @sError = 'Missing table SignalType'
 else if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VirtualLevelSignalBinding]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  set @sError = 'Missing table VirtualLevelSignalBinding'
if @sError IS NULL
BEGIN 
   if not exists (select * from dbo.nvuser)
   BEGIN
    SET IDENTITY_INSERT dbo.NVUser ON
    PRINT 'adding NVUser'
    insert into dbo.NVUser (NVUser_ID,NVUser_Name) values(1, N'EnvyUser')
    SET IDENTITY_INSERT dbo.NVUser OFF
   END

   /* <DOC4SCRIPT>
	Create default Signal Type records, if tbl is empty
	</DOC4SCRIPT> */
	declare @count int
	SELECT @count=COUNT(*) FROM SignalType 
	if (@count=0)
	BEGIN
		SET IDENTITY_INSERT dbo.SignalType ON
		PRINT 'adding Signal Types'
		insert into dbo.SignalType(SignalType_ID, SignalType_Name, SignalType_Description) 
			values (1, '1080i/59.94', 'SMPTE 292M High Definition Video')
		insert into dbo.SignalType(SignalType_ID, SignalType_Name, SignalType_Description) 
			values (2, '720p/27.97', 'SMPTE 292M High Definition Video')
		insert into dbo.SignalType(SignalType_ID, SignalType_Name, SignalType_Description) 
			values (3, '525i/59.94', 'SMPTE 259M Standard Definition Video')
		insert into dbo.SignalType(SignalType_ID, SignalType_Name, SignalType_Description) 
			values (4, '1080i/50', 'SMPTE 292M High Definition Video')
		insert into dbo.SignalType(SignalType_ID, SignalType_Name, SignalType_Description) 
			values (5, '720p/50', 'SMPTE 292M High Definition Video')
		insert into dbo.SignalType(SignalType_ID, SignalType_Name, SignalType_Description) 
			values (6, '625i/50', 'SMPTE 259M Standard Definition Video')
		insert into dbo.SignalType(SignalType_ID, SignalType_Name, SignalType_Description) 
			values (7, '1080p/23.98', 'SMPTE 292M High Definition Video')
		insert into dbo.SignalType(SignalType_ID, SignalType_Name, SignalType_Description) 
			values (8, 'NTSC', 'NTSC Analog Video')
		insert into dbo.SignalType(SignalType_ID, SignalType_Name, SignalType_Description) 
			values (9, 'PAL', 'PAL Analog Video')
		insert into dbo.SignalType(SignalType_ID, SignalType_Name, SignalType_Description) 
			values (20, 'AES/EBU', 'AES/EBU Digital Audio Interface')
		insert into dbo.SignalType(SignalType_ID, SignalType_Name, SignalType_Description) 
			values (21, 'Dolby E', 'Dolby E Encoded Digital Audio')
		insert into dbo.SignalType(SignalType_ID, SignalType_Name, SignalType_Description) 
			values (22, 'Analog Audio', 'Analog Monoaural Audio')
		insert into dbo.SignalType(SignalType_ID, SignalType_Name, SignalType_Description) 
			values (23, 'LtRt', 'Left Total/Right Total Analog Audio')
		insert into dbo.SignalType(SignalType_ID, SignalType_Name, SignalType_Description) 
			values (24, 'AES Mono', 'NVISION TDM AES')
		insert into dbo.SignalType(SignalType_ID, SignalType_Name, SignalType_Description) 
			values (30, 'RS422 Forward', 'RS422 Serial Data Forward')
		insert into dbo.SignalType(SignalType_ID, SignalType_Name, SignalType_Description) 
			values (31, 'RS422 Reverse', 'RS422 Serial Data Reverse')
		insert into dbo.SignalType(SignalType_ID, SignalType_Name, SignalType_Description) 
			values (32, 'RS232 DCE', 'RS232 Data Circuit-Terminating Equipment')
		insert into dbo.SignalType(SignalType_ID, SignalType_Name, SignalType_Description) 
			values (33, 'RS232 DTE', 'RS232 Data Terminal Equipment')
		insert into dbo.SignalType(SignalType_ID, SignalType_Name, SignalType_Description) 
			values (40, 'Timecode', 'SMPTE Timecode')
/*		insert into dbo.SignalType(SignalType_ID, SignalType_Name, SignalType_Description) 
			values (, '', '')
*/
		set IDENTITY_INSERT dbo.SignalType OFF
	END

   /* <DOC4SCRIPT>
	Create default Virtual Level records, if tbl is empty
	</DOC4SCRIPT> */
	SELECT @count=COUNT(*) FROM VirtualLevel 
	if (@count=0)
	BEGIN
		SET IDENTITY_INSERT dbo.VirtualLevel ON
		PRINT 'adding Virtual Level defaults'
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (1, 'HD')
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (2, 'SD')
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (3, 'VIDEO')
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (4, 'AV')
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (5, 'AA')
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (6, 'AES 1/2')
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (7, 'AES 3/4')
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (8, 'AES 5/6')
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (9, 'AES 7/8')
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (10, 'AES 9/10')
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (11, 'AES11/12')
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (12, 'AES13/14')
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (13, 'AES15/16')
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (14, 'AUDIO 1')
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (15, 'AUDIO 2')
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (16, 'AUDIO 3')
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (17, 'AUDIO 4')
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (18, 'AUDIO 5')
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (19, 'AUDIO 6')
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (20, 'AUDIO 7')
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (21, 'AUDIO 8')
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (22, 'AUDIO 9')
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (23, 'AUDIO 10')
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (24, 'AUDIO 11')
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (25, 'AUDIO 12')
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (26, 'AUDIO 13')
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (27, 'AUDIO 14')
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (28, 'AUDIO 15')
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (29, 'AUDIO 16')
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (30, 'RS422')
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (31, 'TIMECODE')
/*
		insert into dbo.VirtualLevel(VirtualLevel_ID, VirtualLevel_Mnemonic) 
			values (, '')
*/
		set IDENTITY_INSERT dbo.VirtualLevel OFF
	END

   /* <DOC4SCRIPT>
	Create default Virtual Level Signal Binding records, if tbl is empty
	</DOC4SCRIPT> */
	SELECT @count=COUNT(*) FROM VirtualLevelSignalBinding 
	if (@count=0)
	BEGIN
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (1, 1)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (1, 2)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (1, 4)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (1, 5)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (1, 7)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (2, 3)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (2, 6)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (3, 1)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (3, 2)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (3, 3)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (3, 4)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (3, 5)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (3, 6)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (3, 7)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (4, 8)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (4, 9)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (5, 22)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (5, 23)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (6, 20)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (6, 21)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (7, 20)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (7, 21)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (8, 20)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (8, 21)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (9, 20)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (9, 21)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (10, 20)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (10, 21)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (11, 20)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (11, 21)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (12, 20)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (12, 21)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (13, 20)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (13, 21)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (14, 24)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (15, 24)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (16, 24)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (17, 24)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (18, 24)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (19, 24)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (20, 24)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (21, 24)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (22, 24)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (23, 24)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (24, 24)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (25, 24)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (26, 24)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (27, 24)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (28, 24)
		insert into dbo.VirtualLevelSignalBinding(VirtualLevelSignalBinding_VirtualLevel, VirtualLevelSignalBinding_SignalType) 
			values (29, 24)
	END

   /* <DOC4SCRIPT>
	Create 10 default suffix records, if tbl is empty
	</DOC4SCRIPT> */
	SELECT @count=COUNT(*) FROM Suffix 
	if (@count=0)
	BEGIN
		SET IDENTITY_INSERT dbo.Suffix ON
		PRINT 'adding Suffixs'
		insert into dbo.Suffix(Suffix_ID, Suffix_Name) values (1, '1')
		insert into dbo.Suffix(Suffix_ID, Suffix_Name) values (2, '2')
		insert into dbo.Suffix(Suffix_ID, Suffix_Name) values (3, '3')
		insert into dbo.Suffix(Suffix_ID, Suffix_Name) values (4, '4')
		insert into dbo.Suffix(Suffix_ID, Suffix_Name) values (5, '5')
		insert into dbo.Suffix(Suffix_ID, Suffix_Name) values (6, '6')
		insert into dbo.Suffix(Suffix_ID, Suffix_Name) values (7, '7')
		insert into dbo.Suffix(Suffix_ID, Suffix_Name) values (8, '8')
		insert into dbo.Suffix(Suffix_ID, Suffix_Name) values (9, '9')
		insert into dbo.Suffix(Suffix_ID, Suffix_Name) values (10, '0')
		set IDENTITY_INSERT dbo.Suffix OFF
	END

   select @hostID=host_id from dbo.host where host_name=@inHostname
   if @hostID IS NULL
   BEGIN
     PRINT 'adding Host ' + @inHostname
     insert into dbo.Host (Host_Name) values(@inHostname)
     select @hostID=host_id from dbo.host where host_name=@inHostname
   END

if @hostID IS NULL
BEGIN
  set @sError = 'Error, HostID is null'
END
ELSE
BEGIN
   if not exists (select * from dbo.ControlPoint WHERE ControlPoint_Host=@hostID)
   BEGIN
	   /* <DOC4SCRIPT>
	   Only create Serial CtrlPts if instructed to do so.
	   Then Create the "E Panel Svr" ControlPoint on this host.
		</DOC4SCRIPT> */
     if @bDoSerialCtrlPtsToo>0
     BEGIN
		 /* see 'beware' note at beginning of proc. before changing anything! */
		 insert into dbo.ControlPoint (ControlPoint_Host,ControlPoint_SetupParams,ControlPoint_Description) values(@hostID,N'S,3,38400,N81', N'COM3 P0 ' + @inHostname)

		 insert into dbo.ControlPoint (ControlPoint_Host,ControlPoint_SetupParams,ControlPoint_Description) values(@hostID,N'S,4,38400,N81', N'COM4 P1 ' + @inHostname)
		 insert into dbo.ControlPoint (ControlPoint_Host,ControlPoint_SetupParams,ControlPoint_Description) values(@hostID,N'S,5,38400,N81', N'COM5 P2 ' + @inHostname)
		 insert into dbo.ControlPoint (ControlPoint_Host,ControlPoint_SetupParams,ControlPoint_Description) values(@hostID,N'S,6,38400,N81', N'COM6 P3 ' + @inHostname)
		 insert into dbo.ControlPoint (ControlPoint_Host,ControlPoint_SetupParams,ControlPoint_Description) values(@hostID,N'S,7,38400,N81', N'COM7 P4 ' + @inHostname)
		 insert into dbo.ControlPoint (ControlPoint_Host,ControlPoint_SetupParams,ControlPoint_Description) values(@hostID,N'S,8,38400,N81', N'COM8 P5 ' + @inHostname)
		 insert into dbo.ControlPoint (ControlPoint_Host,ControlPoint_SetupParams,ControlPoint_Description) values(@hostID,N'S,9,38400,N81', N'COM9 P6 ' + @inHostname)
		 insert into dbo.ControlPoint (ControlPoint_Host,ControlPoint_SetupParams,ControlPoint_Description) values(@hostID,N'S,10,38400,N81', N'COM10 P7 ' + @inHostname)
     END
     insert into dbo.ControlPoint (ControlPoint_Host,ControlPoint_SetupParams,ControlPoint_Description) values(@hostID,N'E,192.168.1.0', N'E Panel Svr ' + @inHostname)
   END

	   /* <DOC4SCRIPT>
		If the "E Panel Svr" ControlPoint on this host
		doesn't yet have a Panelserver App, create one:
	   		</DOC4SCRIPT> */
   declare @ControlPointID int
   SELECT @ControlPointID=controlPoint_ID FROM controlpoint LEFT OUTER JOIN App ON App_ControlPoint=ControlPoint_ID WHERE ((ControlPoint_Host=@hostID AND ControlPoint_Description LIKE 'E Panel Svr%') and app_ID is null) 
   if @ControlPointID IS NOT NULL
   BEGIN
	    INSERT INTO dbo.App( App_Name,App_ControlPoint,App_Type,App_RunCondition) values(N'PanelSvr_cp' + convert(varchar(8),@ControlPointID),@ControlPointID, 2,0)
   END
END /* nonnull hostID */


END /* all tables present in schema */
if @sError IS NULL
	set @sError = ''
ELSE
	PRINT @sError
END
select @sError
go


CREATE PROCEDURE NV_ConvertPaths
@fromSubdir varchar(8), @toSubdir varchar(8), @bPreventOverconversion int
AS
BEGIN
	DECLARE @subdir varchar(10)
	DECLARE @pathprefix sysname
	SET @pathPrefix='\usersys\'
	IF (LEN(@fromSubdir)>0 AND RIGHT(@fromSubdir,1)<>'\')
		SET @fromSubdir = @fromSubdir + '\'
	IF (LEN(@toSubdir)>0 AND RIGHT(@toSubdir,1)<>'\')
		SET @toSubdir = @toSubdir + '\'
		
	DECLARE @replCmdStr sysname
	DECLARE @updateCmdStr varchar(255)
	
	SET @replCmdStr='REPLACE(Path_FilePath,''' + @pathPrefix + @fromSubdir + ''',''' + @pathPrefix + @toSubdir + ''')'
	SET @updateCmdStr='UPDATE Path SET Path_FilePath=' + @replCmdStr 
	IF (@bPreventOverconversion=1) 
	BEGIN
		DECLARE @notlikeCmdStr sysname
		SET @notlikeCmdStr='(Path_FilePath NOT LIKE ''%ys\' + @toSubdir
			+ '%'')'
		SET @updateCmdStr=@updateCmdStr
			+ ' WHERE ' + @notlikeCmdStr
	END
	PRINT @updateCmdStr
	EXEC(@updateCmdStr)
END
go

CREATE PROCEDURE NV_GetEnumStrings
@inTableName varchar(40),
@inColName varchar(40)
AS

	if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb.dbo.tmpType'))
		drop table tempdb.dbo.tmpType

	IF @inTableName = 'Host'
	BEGIN
		IF @inColName = 'Host_LocalLogMode'
		BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				Host_LocalLogMode varchar(80)
			)
			insert into tempdb.dbo.tmpType values(0, 'No logging.')
			insert into tempdb.dbo.tmpType values(1, 'Log NV_ConvertIntToString .')
			insert into tempdb.dbo.tmpType values(2, 'Log warnings.')
			insert into tempdb.dbo.tmpType values(3, 'Log system events.')
			insert into tempdb.dbo.tmpType values(4, 'Log transactions.')
			insert into tempdb.dbo.tmpType values(5, 'Log all events.')
			insert into tempdb.dbo.tmpType values(6, 'Log debugging info.')
			insert into tempdb.dbo.tmpType values(7, 'Log verbose debugging info.')
			/* if you change them here, also change them in 
				'enum NvLocalLogMode', in NvEnums.idl 
				AND for App_LocalLogMode. */

		END
		ELSE
		BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				InvalidParams varchar(30)
			)
		END
	END
	ELSE IF @inTableName = 'Router'
	BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				InvalidParams varchar(30)
			)
	END
	ELSE IF @inTableName = 'PhysConn'
	BEGIN
		IF @inColName = 'PhysConn_PhysLevelPortDir'
		BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				PhysConn_PhysLevelPortDir varchar(80)
			)
			insert into tempdb.dbo.tmpType values(1, 'Input Port')
			insert into tempdb.dbo.tmpType values(2, 'Output Port')
			insert into tempdb.dbo.tmpType values(3, 'Bidirectional Port')
		END
		ELSE
		BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				InvalidParams varchar(30)
			)
		END
	END
	ELSE IF @inTableName = 'PhysLevel'
	BEGIN
		IF @inColName = 'PhysLevel_InputProtect'
		BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				PhysLevel_InputProtect varchar(80)
			)
			insert into tempdb.dbo.tmpType values(1, 'In Server')
			insert into tempdb.dbo.tmpType values(3, 'Disabled')
		END
		ELSE IF @inColName = 'PhysLevel_InputLock'
		BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				PhysLevel_InputLock varchar(80)
			)
			insert into tempdb.dbo.tmpType values(1, 'In Server')
			insert into tempdb.dbo.tmpType values(3, 'Disabled')
		END
		ELSE IF @inColName = 'PhysLevel_OutputProtect'
		BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				PhysLevel_OutputProtect varchar(80)
			)
			insert into tempdb.dbo.tmpType values(1, 'In Server')
			insert into tempdb.dbo.tmpType values(2, 'In Router')
			insert into tempdb.dbo.tmpType values(3, 'Disabled')
		END
		ELSE IF @inColName = 'PhysLevel_OutputLock'
		BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				PhysLevel_OutputLock varchar(80)
			)
			insert into tempdb.dbo.tmpType values(1, 'In Server')
			insert into tempdb.dbo.tmpType values(2, 'In Router')
			insert into tempdb.dbo.tmpType values(3, 'Disabled')
		END
		ELSE IF @inColName = 'PhysLevel_SharedControl'
		BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				PhysLevel_SharedControl varchar(80)
			)
			insert into tempdb.dbo.tmpType values(0, 'False')
			insert into tempdb.dbo.tmpType values(1, 'True')
		END
		ELSE IF @inColName = 'PhysLevel_SignalType'
		BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				PhysLevel_SignalType varchar(80)
			)
			insert into tempdb.dbo.tmpType values(1, 'XY or Standard')
			insert into tempdb.dbo.tmpType values(2, 'NVISION Machine Control')
		END
		ELSE
		BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				InvalidParams varchar(30)
			)
		END
	END
	ELSE IF @inTableName = 'RouterControl'
	BEGIN
		IF @inColName = 'RouterControl_Protocol'
		BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				RouterControl_Protocol varchar(80)
			)
			insert into tempdb.dbo.tmpType values(1, 'NV Serial Protocol-Private')
			insert into tempdb.dbo.tmpType values(2, 'Utah RCP-1 Protocol')
			insert into tempdb.dbo.tmpType values(3, 'Jupiter ESbus Protocol')
			insert into tempdb.dbo.tmpType values(4, 'GVG Horizon TCI Protocol')
			insert into tempdb.dbo.tmpType values(5, 'Virtual Router Protocol')
			insert into tempdb.dbo.tmpType values(6, 'Probel Router Protocol')
			insert into tempdb.dbo.tmpType values(7, 'NV Ethernet Protocol')
			insert into tempdb.dbo.tmpType values(8, 'Pesa Protocol')
			insert into tempdb.dbo.tmpType values(9, 'NV Serial Protocol-Public')
			insert into tempdb.dbo.tmpType values(10, 'ISIS Serial Protocol')
			insert into tempdb.dbo.tmpType values(11, 'Datatek D-2815 Serial Protocol')
			insert into tempdb.dbo.tmpType values(12, 'Sony CART+ Serial Protocol')
			insert into tempdb.dbo.tmpType values(13, 'NV Master Control Ethernet Protocol')
			insert into tempdb.dbo.tmpType values(14, 'Jupiter Bridge Router Protocol')
			insert into tempdb.dbo.tmpType values(15, 'Encore Router Protocol')
			insert into tempdb.dbo.tmpType values(16, 'NV Compact Router Ethernet Protocol')
			insert into tempdb.dbo.tmpType values(17, 'Sierra Video Systems Protocol')
			insert into tempdb.dbo.tmpType values(18, 'NV9000 TCP/IP Protocol')
			insert into tempdb.dbo.tmpType values(19, 'Stagetec Nexus Protocol')
			insert into tempdb.dbo.tmpType values(20, 'Klotz Protocol')
			insert into tempdb.dbo.tmpType values(21, 'Lawo Protocol')
			insert into tempdb.dbo.tmpType values(22, 'DHD Protocol')
			/* if you add a new protocol, the Config GUI's "Analyze Config" might need to
			know about it. */
		END
		ELSE IF @inColName = 'RouterControl_ControlMode'
		BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				RouterControl_ControlMode varchar(80)
			)
			insert into tempdb.dbo.tmpType values(1, 'Primary control card')
			insert into tempdb.dbo.tmpType values(2, 'Secondary control card')
		END
		ELSE
		BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				InvalidParams varchar(30)
			)
		END
	END
	ELSE IF @inTableName = 'App'
	BEGIN
		IF @inColName = 'App_Type'
		BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				App_Type varchar(80)
			)
			insert into tempdb.dbo.tmpType values(0, 'ESbus PhysLevel interface')
			insert into tempdb.dbo.tmpType values(1, 'Probel PhysLevel interface')
			insert into tempdb.dbo.tmpType values(2, 'NV9000 Panel server')
			insert into tempdb.dbo.tmpType values(3, 'NV9000 Tieline server')
			insert into tempdb.dbo.tmpType values(4, 'NV Ethernet interface')
			insert into tempdb.dbo.tmpType values(5, 'NV Serial PhysLevel interface')
			insert into tempdb.dbo.tmpType values(6, 'Jupiter Bridge Interface')
			/* if you add a new App type:
				Be sure to put it in enum NvAppType, in share/utilities/NvAppTypes.h 
				If it's a MajorComponent, not just an itf), NvMaster needs to know how to start it!
			*/
		END
		ELSE IF @inColName = 'App_RunCondition'
		BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				App_RunCondition varchar(80)
			)
			insert into tempdb.dbo.tmpType values(0, 'Manage automatically.')
			insert into tempdb.dbo.tmpType values(1, 'Do not start.')
		END
		ELSE IF @inColName = 'App_LocalLogMode'
		BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				App_LocalLogMode varchar(80)
			)
			insert into tempdb.dbo.tmpType values(0, 'No logging.')
			insert into tempdb.dbo.tmpType values(1, 'Log errors.')
			insert into tempdb.dbo.tmpType values(2, 'Log warnings.')
			insert into tempdb.dbo.tmpType values(3, 'Log system events.')
			insert into tempdb.dbo.tmpType values(4, 'Log transactions.')
			insert into tempdb.dbo.tmpType values(5, 'Log all events.')
			insert into tempdb.dbo.tmpType values(6, 'Log debugging info.')
			insert into tempdb.dbo.tmpType values(7, 'Log verbose debugging info.')
			insert into tempdb.dbo.tmpType values(255, 'Use host logging default.')
			/* if you change them here, also change them in 
				'enum NvLocalLogMode', in NvEnums.idl 
				AND for Host_LocalLogMode. (Except for the 255 one.) */
		END
		ELSE IF @inColName = 'App_RunPriority'
		BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				App_RunPriority varchar(80)
			)
			insert into tempdb.dbo.tmpType values(0, 'Normal priority')
			insert into tempdb.dbo.tmpType values(1, 'High priority')
		END
		ELSE
		BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				InvalidParams varchar(30)
			)
		END
	END
	ELSE IF @inTableName = 'Path'
	BEGIN
		IF @inColName = 'Path_FileType'
		BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				Path_FileType varchar(80)
			)
			insert into tempdb.dbo.tmpType values(4, 'Salvo execution file')
			insert into tempdb.dbo.tmpType values(6, 'Tally Panel config file')
			insert into tempdb.dbo.tmpType values(9, 'Control Panel config file')
			insert into tempdb.dbo.tmpType values(10,'GUI config file')
		END
		ELSE
		BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				InvalidParams varchar(30)
			)
		END
	END
	ELSE IF @inTableName = 'Panel'
	BEGIN
		IF @inColName = 'Panel_Language'
		BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				Panel_Language varchar(80)
			)
			insert into tempdb.dbo.tmpType values(1, 'English')
			insert into tempdb.dbo.tmpType values(2, 'Other')
		END
		ELSE
		BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				InvalidParams varchar(30)
			)
		END
	END
	ELSE IF @inTableName = 'GUI'
	BEGIN
		IF @inColName = 'GUI_Language'
		BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				GUI_Language varchar(80)
			)
			insert into tempdb.dbo.tmpType values(1, 'English')
			insert into tempdb.dbo.tmpType values(2, 'Other')
		END
		ELSE
		BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				InvalidParams varchar(30)
			)
		END
	END
	ELSE IF @inTableName = 'InterLvlsetMap'
	BEGIN
		IF @inColName = 'InterLvlsetMap_DefaultConns'
		BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				InterLvlsetMap_DefaultConns varchar(80)
			)
			insert into tempdb.dbo.tmpType values(0, 'Specify in Details')
			insert into tempdb.dbo.tmpType values(1, 'Auto-match VL without Tieline')
		END
		ELSE
		BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				InvalidParams varchar(30)
			)
		END
	END
	ELSE IF @inTableName = 'InterLSMapDetail'
	BEGIN
		IF @inColName = 'InterLSMapDetail_TielineNeeded'
		BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				InterLSMapDetail_TielineNeeded varchar(80)
			)
			insert into tempdb.dbo.tmpType values(0, 'No')
			insert into tempdb.dbo.tmpType values(1, 'Yes-Strict')
			insert into tempdb.dbo.tmpType values(2, 'Yes-Flexible')
		END
		ELSE
		BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				InvalidParams varchar(30)
			)
		END
	END
	ELSE IF @inTableName = 'Tieline'
	BEGIN
		IF @inColName = 'Tieline_DefaultState'
		BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				Tieline_DefaultState varchar(80)
			)
			insert into tempdb.dbo.tmpType values(0, 'Offline')
			insert into tempdb.dbo.tmpType values(1, 'Online')
		END
		ELSE
		BEGIN
			create table tempdb.dbo.tmpType
			(
				TypeID int NOT NULL,
				InvalidParams varchar(30)
			)
		END
	END

	ELSE
	BEGIN
		create table tempdb.dbo.tmpType
		(
			TypeID int NOT NULL,
			InvalidParams varchar(30)
		)
	END
	IF exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb.dbo.tmpType'))
	BEGIN
		select * from tempdb.dbo.tmpType order by TypeID
		drop table tempdb.dbo.tmpType
	END
go

CREATE PROCEDURE NV_SysParamByName @inParamName varchar(24)
AS
SELECT  sp.NvSysParam_Value FROM NvSysParam sp
WHERE sp.NvSysParam_Name = @inParamName
go



/* --------------------------------------------------------- 
	Add triggers
	WARNING, DON'T put INSERTS into triggers since they can screw up 
	the @@IDENTITY [of last INSERTed ID] value
	that's used by ConfigGUI.
*/

/* alas since the default isn't a const, had to do these
ones as triggers:
*/
go

CREATE TRIGGER NV_SetDefaultControlPointHost ON ControlPoint FOR INSERT, UPDATE AS
BEGIN
	DECLARE @firstHostID int
	SELECT @firstHostID=MIN(Host_ID) FROM Host
	if @firstHostID IS NULL /* no hosts! */
		DELETE ControlPoint /* ...therefore no ctrlpt */
	else
		UPDATE ControlPoint SET ControlPoint_Host=@firstHostID WHERE ControlPoint_Host IS NULL
END
go

CREATE TRIGGER NV_SetDefaultRouterPrimaryHost ON Router FOR INSERT, UPDATE AS
BEGIN
	DECLARE @firstHostID int
	SELECT @firstHostID=MIN(Host_ID) FROM Host
	if @firstHostID IS NOT NULL /* a host exists */
		UPDATE Router SET Router_PrimaryHost=@firstHostID WHERE Router_PrimaryHost IS NULL
END
go

IF EXISTS (SELECT * FROM master..sysdatabases WHERE name = N'NvLogs' )
BEGIN
drop database NvLogs
END
GO

USE CONTROL
GO

IF EXISTS (SELECT name FROM sysobjects WHERE NAME='NV_ReleaseTieline2ReservationTrigger')
	DROP TRIGGER NV_ReleaseTieline2ReservationTrigger
go

CREATE TRIGGER NV_ReleaseTieline2ReservationTrigger
ON Tieline2Reservation

FOR DELETE
AS

INSERT INTO Tieline2ReservationHistory(
	Tieline2ReservationHistory_Tieline2,
	Tieline2ReservationHistory_ControlSystem_Name,
	Tieline2ReservationHistory_User,
	Tieline2ReservationHistory_IPAddress,
	Tieline2ReservationHistory_LTC,
	Tieline2ReservationHistory_Timestamp,
	Tieline2ReservationHistory_SourcePhysLevel,
	Tieline2ReservationHistory_SourceSignalType,
	Tieline2ReservationHistory_SourcePort,
	Tieline2ReservationHistory_DestinationPhysLevel,
	Tieline2ReservationHistory_DestinationSignalType,
	Tieline2ReservationHistory_DestinationPort,
	Tieline2ReservationHistory_DownstreamPort,
	Tieline2ReservationHistory_UpstreamPort )

SELECT
	deleted.Tieline2Reservation_Tieline2,
	deleted.Tieline2Reservation_ControlSystem_Name,
	deleted.Tieline2Reservation_User,
	deleted.Tieline2Reservation_IPAddress,
	deleted.Tieline2Reservation_LTC,
	deleted.Tieline2Reservation_Timestamp,
	deleted.Tieline2Reservation_SourcePhysLevel,
	deleted.Tieline2Reservation_SourceSignalType,
	deleted.Tieline2Reservation_SourcePort,
	deleted.Tieline2Reservation_DestinationPhysLevel,
	deleted.Tieline2Reservation_DestinationSignalType,
	deleted.Tieline2Reservation_DestinationPort,
	deleted.Tieline2Reservation_DownstreamPort,
	deleted.Tieline2Reservation_UpstreamPort 
FROM deleted;

GO

CREATE TRIGGER NV_Tieline2InsertTrigger 
ON Tieline2
FOR INSERT 

AS
	UPDATE Tieline2 SET Tieline2_ControlSystem_Tieline2 = Tieline2_ID 
	WHERE Tieline2_ControlSystem = 1 
GO

CREATE TRIGGER NV_Tieline2UpdateTrigger 
ON Tieline2 
FOR UPDATE

AS
	UPDATE Tieline2 SET Tieline2_ControlSystem_Tieline2 = Tieline2_ID 
	WHERE Tieline2_ControlSystem = 1 and Tieline2_ControlSystem_Tieline2 != Tieline2_ID

GO

/* TIELINE2 STORED PROCEDURES */
/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

EXEC('master.dbo.xp_cmdshell ''sqlcmd -E -d control -i C:\nvision\envy\install\NV_IsTielineSegmentShared.sql'' ')
EXEC('master.dbo.xp_cmdshell ''sqlcmd -E -d control -i C:\nvision\envy\install\NV_ReleaseTieline2Path.sql'' ')
EXEC('master.dbo.xp_cmdshell ''sqlcmd -E -d control -i C:\nvision\envy\install\NV_ReleaseUpstreamTieline2Path.sql'' ')
EXEC('master.dbo.xp_cmdshell ''sqlcmd -E -d control -i C:\nvision\envy\install\NV_ReserveTieline2Segment.sql'' ')
EXEC('master.dbo.xp_cmdshell ''sqlcmd -E -d control -i C:\nvision\envy\install\NV_ReserveExistingTieline2Path.sql'' ')
EXEC('master.dbo.xp_cmdshell ''sqlcmd -E -d control -i C:\nvision\envy\install\NV_ReserveTieline2Path.sql'' ')
EXEC('master.dbo.xp_cmdshell ''sqlcmd -E -d control -i C:\nvision\envy\install\NV_ReserveTieline2.sql'' ')
EXEC('master.dbo.xp_cmdshell ''sqlcmd -E -d control -i C:\nvision\envy\install\NV_ReleaseTieline2GroupTrigger.sql'' ')
EXEC('master.dbo.xp_cmdshell ''sqlcmd -E -d control -i C:\nvision\envy\install\NV_ReserveTieline2GroupTrigger.sql'' ')
GO 	



 	
/* More Tieline2 related stuff */
/* At least one of these is guaranteed to fail, no big deal if it does. */
PRINT 'Creating links between redundant controllers, if any'
IF NOT EXISTS (SELECT srvname FROM master.dbo.sysservers WHERE srvname='172.16.1.1')
	EXEC sp_addlinkedserver '172.16.1.1', 'SQL Server'
go
IF NOT EXISTS (SELECT srvname FROM master.dbo.sysservers WHERE srvname='172.16.1.2')
	EXEC sp_addlinkedserver '172.16.1.2', 'SQL Server'
go

PRINT 'Creating Tieline2 reservation replication procedure'
GO

CREATE PROCEDURE NV_ReplicateTielineReservations
@inServerIP varchar(20)
AS
BEGIN
	DECLARE @commandStr varchar(500)
	
	ALTER TABLE Tieline2Reservation DISABLE TRIGGER All

	DELETE Tieline2Reservation

	SET @commandStr = 
		'INSERT INTO Tieline2Reservation
		SELECT * FROM [' + @inServerIP + ']' + '.control.dbo.Tieline2Reservation'
	EXEC(@commandStr)

	ALTER TABLE Tieline2Reservation ENABLE TRIGGER All
END
GO

/* This does not drop default constraints, use NV_DropColumnDefaultConstraints*/
CREATE PROCEDURE NV_DropColumnConstraints
	@tableName varchar(200), @columnName varchar(200), @returnStatus int OUTPUT
AS
BEGIN
	SET @returnStatus = 0;
	DECLARE @selectCmd nvarchar(200);
	DECLARE @cursorCmd nvarchar(400);
	DECLARE @constraintName varchar(40);

	SET @selectCmd = 'SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE ' +
		' WHERE TABLE_NAME = ''' +  @tableName +
		''' AND COLUMN_NAME = ''' + @columnName + '''';
	SET @cursorCmd =' DECLARE constraintCursor cursor for ' + @selectCmd;
	EXEC sp_executesql @cursorCmd;

	OPEN constraintCursor;

	fetch next from constraintCursor into @constraintName;	
	PRINT 'FETCH_STATUS: ' +CAST(@@FETCH_STATUS AS VARCHAR);

	DECLARE @SQLCmd varchar(200);
	WHILE ( @@FETCH_STATUS = 0 )
	BEGIN
		IF (@constraintName IS NOT NULL) OR (LEN(@constraintName ) > 0)
		BEGIN					
			SET @SQLCmd = 'ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @constraintName ;
			EXEC (@SQLCmd);
			SET @returnStatus = 1;
		END
		FETCH NEXT FROM constraintCursor INTO @constraintName;
	END

	CLOSE constraintCursor ;
	DEALLOCATE constraintCursor ;	

    IF @returnStatus = 0
		PRINT 'NV_DropColumnConstraints failed.' 

END
GO

CREATE PROCEDURE NV_DropColumnDefaultConstraint
	@tableName varchar(200), @columnName varchar(200), @returnStatus int OUTPUT
AS
BEGIN
	SET @returnStatus = 0;
	DECLARE @selectCmd nvarchar(1000);
	DECLARE @cursorCmd nvarchar(1000);
	DECLARE @constraintName varchar(40);

	SET @selectCmd = 'SELECT o.name FROM sysobjects o ' + 
				' INNER JOIN syscolumns c ON o.id = c.cdefault ' +
				' INNER JOIN sysobjects t ON c.id = t.id ' +
				' WHERE o.xtype = ''D'' AND t.name = ''' + @tableName + ''' AND c.name = ''' + @columnName + '''';

	SET @cursorCmd =' DECLARE constraintCursor cursor for ' + @selectCmd;
	EXEC sp_executesql @cursorCmd;

	OPEN constraintCursor;

	fetch next from constraintCursor into @constraintName;	
	PRINT 'FETCH_STATUS: ' +CAST(@@FETCH_STATUS AS VARCHAR);

	DECLARE @SQLCmd varchar(200);
	WHILE ( @@FETCH_STATUS = 0 )
	BEGIN
		IF (@constraintName IS NOT NULL) OR (LEN(@constraintName ) > 0)
		BEGIN					
			SET @SQLCmd = 'ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @constraintName ;
			EXEC (@SQLCmd);
			SET @returnStatus = 1;
		END
		FETCH NEXT FROM constraintCursor INTO @constraintName;
	END

	CLOSE constraintCursor ;
	DEALLOCATE constraintCursor ;	

    IF @returnStatus = 0
		PRINT 'NV_DropColumnDefaultConstraint failed.' 

END
GO


/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

/* --------------------------------------------------------- */
/* --------------------------------------------------------- 
 Grant EXEC permissions on our existing stored procedures to Public
 
 (note: the "Remove our existing stored procedures" section
 in RemoveExistingStoredProcsAndTriggers.sql
 is almost identical code, so if something has to
 change here, see if it needs changing there too.)
*/
PRINT 'Granting EXEC permissions on stored procedures' 
go

declare @procName sysname
declare ourCursor cursor
FOR
(SELECT name FROM sysobjects 
WHERE OBJECTPROPERTY(id, N'IsProcedure') = 1 AND Name LIKE 'NV_%')
open ourCursor
FETCH NEXT FROM ourCursor into @procName
WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC('Grant EXEC ON ' + @procName + ' to public')
	FETCH NEXT FROM ourCursor into @procName
END
close ourCursor
deallocate ourCursor
go

PRINT  CHAR(13) + CHAR(10)+ N'Finished processing Control Procedures script.'
go

