EXEC sys.sp_dropextendedproperty @name=N'MS_Description' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'alias', @level2type=N'COLUMN',@level2name=N'site_id'
GO
EXEC sys.sp_dropextendedproperty @name=N'MS_Description' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'alias', @level2type=N'COLUMN',@level2name=N'node_id'
GO
/****** Object:  StoredProcedure [dbo].[userLogin]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[userLogin]
GO
/****** Object:  StoredProcedure [dbo].[setActiveBroker]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[setActiveBroker]
GO
/****** Object:  StoredProcedure [dbo].[selectNodes]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[selectNodes]
GO
/****** Object:  StoredProcedure [dbo].[selectNodeLinks]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[selectNodeLinks]
GO
/****** Object:  StoredProcedure [dbo].[runBatch]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[runBatch]
GO
/****** Object:  StoredProcedure [dbo].[obj_readlist]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[obj_readlist]
GO
/****** Object:  StoredProcedure [dbo].[obj_put]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[obj_put]
GO
/****** Object:  StoredProcedure [dbo].[obj_get]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[obj_get]
GO
/****** Object:  StoredProcedure [dbo].[obj_clr]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[obj_clr]
GO
/****** Object:  StoredProcedure [dbo].[globalEditAlarm]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[globalEditAlarm]
GO
/****** Object:  StoredProcedure [dbo].[getUserTrees]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getUserTrees]
GO
/****** Object:  StoredProcedure [dbo].[getUserTree]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getUserTree]
GO
/****** Object:  StoredProcedure [dbo].[getTreeNodes]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getTreeNodes]
GO
/****** Object:  StoredProcedure [dbo].[getTreeNode]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getTreeNode]
GO
/****** Object:  StoredProcedure [dbo].[getTreeLinks]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getTreeLinks]
GO
/****** Object:  StoredProcedure [dbo].[getTreeCreator]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getTreeCreator]
GO
/****** Object:  StoredProcedure [dbo].[getTrapLogs]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getTrapLogs]
GO
/****** Object:  StoredProcedure [dbo].[getTopologyLocations]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getTopologyLocations]
GO
/****** Object:  StoredProcedure [dbo].[getTopologyLocation]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getTopologyLocation]
GO
/****** Object:  StoredProcedure [dbo].[getTopologyEquips]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getTopologyEquips]
GO
/****** Object:  StoredProcedure [dbo].[getTopologyEquipLinks]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getTopologyEquipLinks]
GO
/****** Object:  StoredProcedure [dbo].[getTopologyDevices]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getTopologyDevices]
GO
/****** Object:  StoredProcedure [dbo].[getTopologyDeviceLinks]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getTopologyDeviceLinks]
GO
/****** Object:  StoredProcedure [dbo].[getTemplateLines]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getTemplateLines]
GO
/****** Object:  StoredProcedure [dbo].[getTemplateAlarms]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getTemplateAlarms]
GO
/****** Object:  StoredProcedure [dbo].[getRoot]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getRoot]
GO
/****** Object:  StoredProcedure [dbo].[getPollingAgents]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getPollingAgents]
GO
/****** Object:  StoredProcedure [dbo].[getPmParameterLog]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getPmParameterLog]
GO
/****** Object:  StoredProcedure [dbo].[getPmLogs]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getPmLogs]
GO
/****** Object:  StoredProcedure [dbo].[getParent]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getParent]
GO
/****** Object:  StoredProcedure [dbo].[getInventoryList]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getInventoryList]
GO
/****** Object:  StoredProcedure [dbo].[getIndexOfChild]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getIndexOfChild]
GO
/****** Object:  StoredProcedure [dbo].[getImageInfos]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getImageInfos]
GO
/****** Object:  StoredProcedure [dbo].[getImage]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getImage]
GO
/****** Object:  StoredProcedure [dbo].[getFirstAgentDeviceType]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getFirstAgentDeviceType]
GO
/****** Object:  StoredProcedure [dbo].[getClients]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getClients]
GO
/****** Object:  StoredProcedure [dbo].[getClientActionLog]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getClientActionLog]
GO
/****** Object:  StoredProcedure [dbo].[getClientACL]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getClientACL]
GO
/****** Object:  StoredProcedure [dbo].[getChildCount]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getChildCount]
GO
/****** Object:  StoredProcedure [dbo].[getChild]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getChild]
GO
/****** Object:  StoredProcedure [dbo].[getBatchLines]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getBatchLines]
GO
/****** Object:  StoredProcedure [dbo].[getAlarms]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getAlarms]
GO
/****** Object:  StoredProcedure [dbo].[getAlarmLogs]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getAlarmLogs]
GO
/****** Object:  StoredProcedure [dbo].[getAgentTimeSyncer]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getAgentTimeSyncer]
GO
/****** Object:  StoredProcedure [dbo].[getAgentsDriverAgents]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getAgentsDriverAgents]
GO
/****** Object:  StoredProcedure [dbo].[getAgents]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getAgents]
GO
/****** Object:  StoredProcedure [dbo].[getAgentIds]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getAgentIds]
GO
/****** Object:  StoredProcedure [dbo].[getAgentByName]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getAgentByName]
GO
/****** Object:  StoredProcedure [dbo].[getAgentById]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[getAgentById]
GO
/****** Object:  StoredProcedure [dbo].[get_device_props]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[get_device_props]
GO
/****** Object:  StoredProcedure [dbo].[get_device_prop_items]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[get_device_prop_items]
GO
/****** Object:  StoredProcedure [dbo].[filters_clear]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[filters_clear]
GO
/****** Object:  StoredProcedure [dbo].[filter_upd]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[filter_upd]
GO
/****** Object:  StoredProcedure [dbo].[filter_del]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[filter_del]
GO
/****** Object:  StoredProcedure [dbo].[filter_add]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[filter_add]
GO
/****** Object:  StoredProcedure [dbo].[editTreeNode]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[editTreeNode]
GO
/****** Object:  StoredProcedure [dbo].[editTrapLog]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[editTrapLog]
GO
/****** Object:  StoredProcedure [dbo].[editTopologyLocation]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[editTopologyLocation]
GO
/****** Object:  StoredProcedure [dbo].[editTopologyEquip]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[editTopologyEquip]
GO
/****** Object:  StoredProcedure [dbo].[editTopologyDevice]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[editTopologyDevice]
GO
/****** Object:  StoredProcedure [dbo].[editTemplateAlarm]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[editTemplateAlarm]
GO
/****** Object:  StoredProcedure [dbo].[editRoleAccess]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[editRoleAccess]
GO
/****** Object:  StoredProcedure [dbo].[editPmParameterLog]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[editPmParameterLog]
GO
/****** Object:  StoredProcedure [dbo].[editInventoryList]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[editInventoryList]
GO
/****** Object:  StoredProcedure [dbo].[editClientACL]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[editClientACL]
GO
/****** Object:  StoredProcedure [dbo].[editClient]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[editClient]
GO
/****** Object:  StoredProcedure [dbo].[editBroker]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[editBroker]
GO
/****** Object:  StoredProcedure [dbo].[editAlarmLogEndTime]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[editAlarmLogEndTime]
GO
/****** Object:  StoredProcedure [dbo].[editAlarmLogAck]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[editAlarmLogAck]
GO
/****** Object:  StoredProcedure [dbo].[editAlarm]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[editAlarm]
GO
/****** Object:  StoredProcedure [dbo].[editAgentTimeSyncer]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[editAgentTimeSyncer]
GO
/****** Object:  StoredProcedure [dbo].[editAgentsDriver]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[editAgentsDriver]
GO
/****** Object:  StoredProcedure [dbo].[editAgentPmEnable]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[editAgentPmEnable]
GO
/****** Object:  StoredProcedure [dbo].[editAgent]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[editAgent]
GO
/****** Object:  StoredProcedure [dbo].[deleteTreeNode]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[deleteTreeNode]
GO
/****** Object:  StoredProcedure [dbo].[deleteTree]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[deleteTree]
GO
/****** Object:  StoredProcedure [dbo].[deleteTopologyEquipLink]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[deleteTopologyEquipLink]
GO
/****** Object:  StoredProcedure [dbo].[deleteTopologyEquip]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[deleteTopologyEquip]
GO
/****** Object:  StoredProcedure [dbo].[deleteTopologyDeviceLink]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[deleteTopologyDeviceLink]
GO
/****** Object:  StoredProcedure [dbo].[deletePmParameterLogByTime]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[deletePmParameterLogByTime]
GO
/****** Object:  StoredProcedure [dbo].[deletePmParameterLog]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[deletePmParameterLog]
GO
/****** Object:  StoredProcedure [dbo].[deleteInventoryList]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[deleteInventoryList]
GO
/****** Object:  StoredProcedure [dbo].[deleteImage]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[deleteImage]
GO
/****** Object:  StoredProcedure [dbo].[deleteClientACL]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[deleteClientACL]
GO
/****** Object:  StoredProcedure [dbo].[deleteClient]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[deleteClient]
GO
/****** Object:  StoredProcedure [dbo].[deleteBrokers]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[deleteBrokers]
GO
/****** Object:  StoredProcedure [dbo].[deleteBroker]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[deleteBroker]
GO
/****** Object:  StoredProcedure [dbo].[deleteAllClientACL]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[deleteAllClientACL]
GO
/****** Object:  StoredProcedure [dbo].[deleteAlarm]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[deleteAlarm]
GO
/****** Object:  StoredProcedure [dbo].[deleteAgent]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[deleteAgent]
GO
/****** Object:  StoredProcedure [dbo].[deleteAdlinks]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[deleteAdlinks]
GO
/****** Object:  StoredProcedure [dbo].[deleteAdlink]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[deleteAdlink]
GO
/****** Object:  StoredProcedure [dbo].[checkWorker]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[checkWorker]
GO
/****** Object:  StoredProcedure [dbo].[alarm_readlist001]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[alarm_readlist001]
GO
/****** Object:  StoredProcedure [dbo].[alarm_readlist_000]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[alarm_readlist_000]
GO
/****** Object:  StoredProcedure [dbo].[alarm_getagents]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[alarm_getagents]
GO
/****** Object:  StoredProcedure [dbo].[admin_traplist_clear]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[admin_traplist_clear]
GO
/****** Object:  StoredProcedure [dbo].[admin_threshold_clear]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[admin_threshold_clear]
GO
/****** Object:  StoredProcedure [dbo].[admin_messengerAgentAdd]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[admin_messengerAgentAdd]
GO
/****** Object:  StoredProcedure [dbo].[admin_driverdelete]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[admin_driverdelete]
GO
/****** Object:  StoredProcedure [dbo].[admin_agentsdelfromdriver]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[admin_agentsdelfromdriver]
GO
/****** Object:  StoredProcedure [dbo].[admin_agentNodeInsert]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[admin_agentNodeInsert]
GO
/****** Object:  StoredProcedure [dbo].[addTreeNodeFromProduct]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[addTreeNodeFromProduct]
GO
/****** Object:  StoredProcedure [dbo].[addTreeNode]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[addTreeNode]
GO
/****** Object:  StoredProcedure [dbo].[addTree]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[addTree]
GO
/****** Object:  StoredProcedure [dbo].[addTrapLog]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[addTrapLog]
GO
/****** Object:  StoredProcedure [dbo].[addTopologyLocation]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[addTopologyLocation]
GO
/****** Object:  StoredProcedure [dbo].[addTopologyEquipLink]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[addTopologyEquipLink]
GO
/****** Object:  StoredProcedure [dbo].[addTopologyEquip]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[addTopologyEquip]
GO
/****** Object:  StoredProcedure [dbo].[addTopologyDeviceLink]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[addTopologyDeviceLink]
GO
/****** Object:  StoredProcedure [dbo].[addTopologyDevice]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[addTopologyDevice]
GO
/****** Object:  StoredProcedure [dbo].[addPmParameterLog]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[addPmParameterLog]
GO
/****** Object:  StoredProcedure [dbo].[addPmLog]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[addPmLog]
GO
/****** Object:  StoredProcedure [dbo].[addImage]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[addImage]
GO
/****** Object:  StoredProcedure [dbo].[addClientActionLog]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[addClientActionLog]
GO
/****** Object:  StoredProcedure [dbo].[addClientACL]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[addClientACL]
GO
/****** Object:  StoredProcedure [dbo].[addClient]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[addClient]
GO
/****** Object:  StoredProcedure [dbo].[addBroker]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[addBroker]
GO
/****** Object:  StoredProcedure [dbo].[addAlias]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[addAlias]
GO
/****** Object:  StoredProcedure [dbo].[addAlarmLog]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[addAlarmLog]
GO
/****** Object:  StoredProcedure [dbo].[addAlarm]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[addAlarm]
GO
/****** Object:  StoredProcedure [dbo].[addAgentsDriver]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[addAgentsDriver]
GO
/****** Object:  StoredProcedure [dbo].[addAgent]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[addAgent]
GO
/****** Object:  StoredProcedure [dbo].[addAdlink]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP PROCEDURE [dbo].[addAdlink]
GO
ALTER TABLE [dbo].[trapLog] DROP CONSTRAINT [FK_trapLog_client]
GO
ALTER TABLE [dbo].[trapLog] DROP CONSTRAINT [FK_trapLog_agent]
GO
ALTER TABLE [dbo].[topologyLocation] DROP CONSTRAINT [FK_topologyLocation_treelinks]
GO
ALTER TABLE [dbo].[topologyLocation] DROP CONSTRAINT [FK_topologyLocation_image]
GO
ALTER TABLE [dbo].[topologyEquipLink] DROP CONSTRAINT [FK_topologyEquipLink_treelinks]
GO
ALTER TABLE [dbo].[topologyEquip] DROP CONSTRAINT [FK_topologyEquip_treelinks]
GO
ALTER TABLE [dbo].[topologyDeviceLink] DROP CONSTRAINT [FK_topologyDeviceLink_treelinks]
GO
ALTER TABLE [dbo].[topologyDevice] DROP CONSTRAINT [FK_topologyDevice_treelinks]
GO
ALTER TABLE [dbo].[roleAccess] DROP CONSTRAINT [FK_roleAccess_userRole]
GO
ALTER TABLE [dbo].[roleAccess] DROP CONSTRAINT [FK_roleAccess_nodes]
GO
ALTER TABLE [dbo].[pmParameterLog] DROP CONSTRAINT [FK_pmParameterLog_agent]
GO
ALTER TABLE [dbo].[pmLog] DROP CONSTRAINT [FK_pmLog_agent]
GO
ALTER TABLE [dbo].[messengerAgent] DROP CONSTRAINT [FK_messengerAgent_messenger]
GO
ALTER TABLE [dbo].[messengerAgent] DROP CONSTRAINT [FK_messengerAgent_agent]
GO
ALTER TABLE [dbo].[clientRole] DROP CONSTRAINT [FK_clientRole_userRole]
GO
ALTER TABLE [dbo].[clientRole] DROP CONSTRAINT [FK_clientRole_client]
GO
ALTER TABLE [dbo].[clientActionLog] DROP CONSTRAINT [FK_clientActionLog_clientActionCode]
GO
ALTER TABLE [dbo].[clientActionLog] DROP CONSTRAINT [FK_clientActionLog_clientActionClassify]
GO
ALTER TABLE [dbo].[clientActionLog] DROP CONSTRAINT [FK_clientActionLog_client]
GO
ALTER TABLE [dbo].[clientACL] DROP CONSTRAINT [FK_clientACL_client]
GO
ALTER TABLE [dbo].[alias] DROP CONSTRAINT [FK_alias_nodes]
GO
ALTER TABLE [dbo].[alarmLog] DROP CONSTRAINT [FK_alarmLog_client]
GO
ALTER TABLE [dbo].[alarmLog] DROP CONSTRAINT [FK_alarmLog_agent]
GO
ALTER TABLE [dbo].[alarm] DROP CONSTRAINT [FK_alarm_alarmType]
GO
ALTER TABLE [dbo].[alarm] DROP CONSTRAINT [FK_alarm_agent]
GO
ALTER TABLE [dbo].[agentsdriver] DROP CONSTRAINT [FK_agentsdriver_broker]
GO
ALTER TABLE [dbo].[agent] DROP CONSTRAINT [FK_agent_nodes]
GO
ALTER TABLE [dbo].[adlinks] DROP CONSTRAINT [FK_adlinks_agentsdriver]
GO
ALTER TABLE [dbo].[adlinks] DROP CONSTRAINT [FK_adlinks_agent]
GO
ALTER TABLE [dbo].[trapLog] DROP CONSTRAINT [DF_trapLog_clear]
GO
ALTER TABLE [dbo].[trapLog] DROP CONSTRAINT [DF_trapLog_ack]
GO
ALTER TABLE [dbo].[pmParameterLog] DROP CONSTRAINT [DF__pmParamet__errCo__693CA210]
GO
ALTER TABLE [dbo].[pmParameterLog] DROP CONSTRAINT [DF__pmParamete__type__68487DD7]
GO
/****** Object:  Table [dbo].[userRole]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[userRole]
GO
/****** Object:  Table [dbo].[trees]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[trees]
GO
/****** Object:  Table [dbo].[treelinks]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[treelinks]
GO
/****** Object:  Table [dbo].[trapLog]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[trapLog]
GO
/****** Object:  Table [dbo].[topologyLocation]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[topologyLocation]
GO
/****** Object:  Table [dbo].[topologyEquipLink]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[topologyEquipLink]
GO
/****** Object:  Table [dbo].[topologyEquip]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[topologyEquip]
GO
/****** Object:  Table [dbo].[topologyDeviceLink]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[topologyDeviceLink]
GO
/****** Object:  Table [dbo].[topologyDevice]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[topologyDevice]
GO
/****** Object:  Table [dbo].[timeSynchornizer]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[timeSynchornizer]
GO
/****** Object:  Table [dbo].[sysProperty]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[sysProperty]
GO
/****** Object:  Table [dbo].[roleAccess]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[roleAccess]
GO
/****** Object:  Index [IX_pmParameterLog]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP INDEX [IX_pmParameterLog] ON [dbo].[pmParameterLog] WITH ( ONLINE = OFF )
GO
/****** Object:  Table [dbo].[pmParameterLog]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[pmParameterLog]
GO
/****** Object:  Table [dbo].[pmParamErrCode]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[pmParamErrCode]
GO
/****** Object:  Table [dbo].[pmParamDataType]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[pmParamDataType]
GO
/****** Object:  Table [dbo].[pmLog]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[pmLog]
GO
/****** Object:  Table [dbo].[params]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[params]
GO
/****** Object:  Table [dbo].[objimage]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[objimage]
GO
/****** Object:  Table [dbo].[nodes]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[nodes]
GO
/****** Object:  Table [dbo].[messengerSMTP]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[messengerSMTP]
GO
/****** Object:  Table [dbo].[messengerSMS]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[messengerSMS]
GO
/****** Object:  Table [dbo].[messengerAgent]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[messengerAgent]
GO
/****** Object:  Table [dbo].[messenger]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[messenger]
GO
/****** Object:  Table [dbo].[inventoryListByServer]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[inventoryListByServer]
GO
/****** Object:  Table [dbo].[image]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[image]
GO
/****** Object:  Table [dbo].[device_props]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[device_props]
GO
/****** Object:  Table [dbo].[device_prop_items]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[device_prop_items]
GO
/****** Object:  Table [dbo].[clientRole]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[clientRole]
GO
/****** Object:  Table [dbo].[clientActionLog]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[clientActionLog]
GO
/****** Object:  Table [dbo].[clientActionContentCode]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[clientActionContentCode]
GO
/****** Object:  Table [dbo].[clientActionCode]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[clientActionCode]
GO
/****** Object:  Table [dbo].[clientActionClassify]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[clientActionClassify]
GO
/****** Object:  Table [dbo].[clientACL]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[clientACL]
GO
/****** Object:  Table [dbo].[client]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[client]
GO
/****** Object:  Table [dbo].[brokerlog]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[brokerlog]
GO
/****** Object:  Table [dbo].[broker]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[broker]
GO
/****** Object:  Table [dbo].[alias]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[alias]
GO
/****** Object:  Table [dbo].[alarmType]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[alarmType]
GO
/****** Object:  Table [dbo].[alarmTemplate]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[alarmTemplate]
GO
/****** Object:  Table [dbo].[alarmLog]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[alarmLog]
GO
/****** Object:  Table [dbo].[alarm]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[alarm]
GO
/****** Object:  Table [dbo].[agentsdriverlog]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[agentsdriverlog]
GO
/****** Object:  Table [dbo].[agentsdriver]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[agentsdriver]
GO
/****** Object:  Table [dbo].[agentMessenger]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[agentMessenger]
GO
/****** Object:  Table [dbo].[agentlog]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[agentlog]
GO
/****** Object:  Table [dbo].[agent]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[agent]
GO
/****** Object:  Table [dbo].[adlinks]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP TABLE [dbo].[adlinks]
GO
/****** Object:  UserDefinedFunction [dbo].[ipValue]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP FUNCTION [dbo].[ipValue]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_rmvIllegalChar]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP FUNCTION [dbo].[fn_rmvIllegalChar]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_isHasIllegalChar]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP FUNCTION [dbo].[fn_isHasIllegalChar]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_getRolesByUserName]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP FUNCTION [dbo].[fn_getRolesByUserName]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_getAllowedNodesByUserName]    Script Date: 2019/9/20 下午 03:11:49 ******/
DROP FUNCTION [dbo].[fn_getAllowedNodesByUserName]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_getAllowedNodesByUserName]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fn_getAllowedNodesByUserName](@userName varchar(64))
RETURNS @RS TABLE (id int)
AS
BEGIN
	INSERT INTO @RS 
	SELECT ra.node_id FROM client c, clientRole cr, roleAccess ra    
	WHERE c.name=@userName AND c.id=cr.client_id AND cr.role_id=ra.role_id 
	
	RETURN
END
GO
/****** Object:  UserDefinedFunction [dbo].[fn_getRolesByUserName]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fn_getRolesByUserName](@userName varchar(64))
RETURNS @RS TABLE (id int)
AS
BEGIN
	INSERT INTO @RS 
	SELECT cr.role_id FROM client c, clientRole cr    
	WHERE c.name=@userName AND c.id=cr.client_id 
	
	RETURN
END
GO
/****** Object:  UserDefinedFunction [dbo].[fn_isHasIllegalChar]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fn_isHasIllegalChar](@name varchar(256))
RETURNS int
AS
BEGIN
	IF(CHARINDEX(' ', @name)>0 OR CHARINDEX(',', @name)>0 OR CHARINDEX(':', @name)>0 OR CHARINDEX('''', @name)>0
	 OR CHARINDEX('"', @name)>0 OR CHARINDEX('[', @name)>0 OR CHARINDEX(']', @name)>0)
		RETURN 1
	
	RETURN 0
END
GO
/****** Object:  UserDefinedFunction [dbo].[fn_rmvIllegalChar]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fn_rmvIllegalChar](@name varchar(256))
RETURNS varchar(256)
AS
BEGIN
	IF CHARINDEX(' ', @name)>0   SET @name = REPLACE(@name, ' ',  '_') 
	IF CHARINDEX(',', @name)>0   SET @name = REPLACE(@name, ',',  '_') 
	IF CHARINDEX(':', @name)>0   SET @name = REPLACE(@name, ':',  '_') 
	IF CHARINDEX('''',@name)>0   SET @name = REPLACE(@name, '''', '_') 
	IF CHARINDEX('"', @name)>0   SET @name = REPLACE(@name, '"',  '_') 
	IF CHARINDEX('[', @name)>0   SET @name = REPLACE(@name, '[',  '_') 
	IF CHARINDEX(']', @name)>0   SET @name = REPLACE(@name, ']',  '_') 
	
	RETURN @name
END
GO
/****** Object:  UserDefinedFunction [dbo].[ipValue]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[ipValue]
(
	@ip varchar(16)
)

RETURNS bigint
AS
    
    BEGIN
    	-- ip string format must to be x.x.x.x
    	return CONVERT(int, PARSENAME(@ip, 4))*POWER(2.0, 32)
    		+ CONVERT(int, PARSENAME(@ip, 3))*POWER(2.0, 24)
    		+ CONVERT(int, PARSENAME(@ip, 2))*POWER(2.0, 16)
    		+ CONVERT(int, PARSENAME(@ip, 1))*POWER(2.0, 8)
    END
GO
/****** Object:  Table [dbo].[adlinks]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[adlinks](
	[agentsdriver_id] [int] NOT NULL,
	[agent_id] [int] NOT NULL,
 CONSTRAINT [PK_adlinks] PRIMARY KEY CLUSTERED 
(
	[agent_id] ASC,
	[agentsdriver_id] 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
/****** Object:  Table [dbo].[agent]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[agent](
	[id] [int] NOT NULL,
	[ip_address] [varchar](32) NOT NULL,
	[port] [int] NOT NULL,
	[port_trap] [int] NOT NULL,
	[mib] [varchar](64) NOT NULL,
	[read_community] [varchar](64) NULL,
	[write_community] [varchar](64) NULL,
	[trap_community] [varchar](64) NULL,
	[op_name] [varchar](50) NULL,
	[version] [tinyint] NOT NULL,
	[repetition] [tinyint] NOT NULL,
	[pm_enable] [bit] NOT NULL,
	[syncTime] [tinyint] NOT NULL,
 CONSTRAINT [PK_agent] PRIMARY KEY CLUSTERED 
(
	[id] 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
/****** Object:  Table [dbo].[agentlog]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[agentlog](
	[agent_id] [int] NULL,
	[timestamp] [datetime] NULL,
	[code] [tinyint] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[agentMessenger]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[agentMessenger](
	[agent_id] [int] NOT NULL,
	[messenger_id] [int] NOT NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[agentsdriver]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[agentsdriver](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[broker_id] [int] NOT NULL,
	[name] [varchar](100) NULL,
	[delayread] [int] NULL,
	[interval] [int] NOT NULL,
	[timeout] [int] NOT NULL,
	[type] [int] NULL,
	[thread] [int] NOT NULL,
 CONSTRAINT [PK_agentsdriver] PRIMARY KEY CLUSTERED 
(
	[id] 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
/****** Object:  Table [dbo].[agentsdriverlog]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[agentsdriverlog](
	[agentsdriver_id] [int] NULL,
	[timestamp] [datetime] NULL,
	[code] [int] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[alarm]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[alarm](
	[agent_id] [int] NOT NULL,
	[rack] [tinyint] NOT NULL,
	[slot] [tinyint] NOT NULL,
	[side] [tinyint] NOT NULL,
	[model_name] [varchar](50) NULL,
	[param] [varchar](10) NOT NULL,
	[type] [smallint] NOT NULL,
	[idx] [tinyint] NOT NULL,
	[severity] [tinyint] NOT NULL,
	[enable] [bit] NOT NULL,
	[model_id] [int] NOT NULL,
 CONSTRAINT [PK_alarm] PRIMARY KEY CLUSTERED 
(
	[agent_id] ASC,
	[rack] ASC,
	[slot] ASC,
	[side] ASC,
	[type] ASC,
	[idx] 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
/****** Object:  Table [dbo].[alarmLog]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[alarmLog](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[agent_id] [int] NOT NULL,
	[time_begin] [datetime] NOT NULL,
	[time_end] [datetime] NOT NULL,
	[location] [varchar](200) NULL,
	[severity] [int] NOT NULL,
	[message] [varchar](100) NOT NULL,
	[ack] [tinyint] NOT NULL,
	[client_id] [int] NULL,
	[time_ack] [datetime] NOT NULL,
 CONSTRAINT [PK_alarmLog] PRIMARY KEY CLUSTERED 
(
	[id] 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
/****** Object:  Table [dbo].[alarmTemplate]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[alarmTemplate](
	[id] [int] NOT NULL,
	[side] [tinyint] NOT NULL,
	[type] [smallint] NOT NULL,
	[idx] [tinyint] NOT NULL,
	[severity] [tinyint] NOT NULL,
	[enable] [bit] NOT NULL,
	[param] [varchar](10) NOT NULL,
 CONSTRAINT [PK_alarmTemplate] PRIMARY KEY CLUSTERED 
(
	[id] ASC,
	[side] ASC,
	[type] ASC,
	[idx] 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
/****** Object:  Table [dbo].[alarmType]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[alarmType](
	[id] [smallint] NOT NULL,
	[name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_AlarmType] PRIMARY KEY CLUSTERED 
(
	[id] 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
/****** Object:  Table [dbo].[alias]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[alias](
	[node_id] [int] NOT NULL,
	[rack_id] [int] NOT NULL,
	[site_id] [int] NOT NULL,
	[idx] [int] NOT NULL,
	[name] [varchar](768) NULL,
 CONSTRAINT [PK_alias] PRIMARY KEY CLUSTERED 
(
	[node_id] ASC,
	[rack_id] ASC,
	[site_id] ASC,
	[idx] 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
/****** Object:  Table [dbo].[broker]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[broker](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](100) NULL,
	[ip_address] [varchar](32) NULL,
	[scanningperiod] [int] NULL,
	[port] [int] NULL,
	[enable] [bit] NOT NULL,
 CONSTRAINT [PK_broker] PRIMARY KEY CLUSTERED 
(
	[id] 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
/****** Object:  Table [dbo].[brokerlog]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[brokerlog](
	[broker_id] [int] NULL,
	[timestamp] [datetime] NULL,
	[code] [int] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[client]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[client](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](768) NULL,
	[password] [varchar](768) NULL,
	[phone] [varchar](768) NULL,
	[division] [varchar](768) NULL,
	[address] [varchar](768) NULL,
	[description] [varchar](768) NULL,
	[default_tree] [int] NOT NULL,
	[modify] [datetime] NOT NULL,
	[next] [bit] NOT NULL,
	[first] [bit] NOT NULL,
	[attempt] [tinyint] NOT NULL,
	[lock] [datetime] NULL,
 CONSTRAINT [PK_client_1] PRIMARY KEY CLUSTERED 
(
	[id] 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
/****** Object:  Table [dbo].[clientACL]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[clientACL](
	[client_id] [int] NOT NULL,
	[ip] [varchar](64) NOT NULL,
	[mac] [varchar](32) NOT NULL,
 CONSTRAINT [PK_clientACL] PRIMARY KEY CLUSTERED 
(
	[client_id] ASC,
	[ip] ASC,
	[mac] 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
/****** Object:  Table [dbo].[clientActionClassify]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[clientActionClassify](
	[id] [int] NOT NULL,
	[classification] [varchar](128) NOT NULL,
 CONSTRAINT [PK_clientActionClassify] PRIMARY KEY CLUSTERED 
(
	[id] 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
/****** Object:  Table [dbo].[clientActionCode]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[clientActionCode](
	[id] [int] NOT NULL,
	[action] [varchar](128) NOT NULL,
 CONSTRAINT [PK_clientActionCode] PRIMARY KEY CLUSTERED 
(
	[id] 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
/****** Object:  Table [dbo].[clientActionContentCode]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[clientActionContentCode](
	[id] [int] NOT NULL,
	[name] [varchar](32) NOT NULL,
 CONSTRAINT [PK_clientActionContentCode] PRIMARY KEY CLUSTERED 
(
	[id] 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
/****** Object:  Table [dbo].[clientActionLog]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[clientActionLog](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[time] [datetime] NOT NULL,
	[client_id] [int] NOT NULL,
	[classify_id] [int] NOT NULL,
	[action_id] [int] NOT NULL,
	[content] [varchar](3840) NULL,
 CONSTRAINT [PK_clientActionLog] PRIMARY KEY CLUSTERED 
(
	[id] 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
/****** Object:  Table [dbo].[clientRole]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[clientRole](
	[client_id] [int] NOT NULL,
	[role_id] [int] NOT NULL,
 CONSTRAINT [PK_clientRole] PRIMARY KEY CLUSTERED 
(
	[client_id] ASC,
	[role_id] 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
/****** Object:  Table [dbo].[device_prop_items]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[device_prop_items](
	[node_type_id] [int] NOT NULL,
	[propname] [varchar](64) NOT NULL,
	[item_id] [smallint] NOT NULL,
	[item_name] [varchar](64) NULL,
 CONSTRAINT [PK_device_prop_items] PRIMARY KEY CLUSTERED 
(
	[node_type_id] ASC,
	[propname] ASC,
	[item_id] 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
/****** Object:  Table [dbo].[device_props]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[device_props](
	[node_type_id] [int] NOT NULL,
	[propname] [varchar](64) NOT NULL,
	[oid] [varchar](128) NULL,
	[type_id] [tinyint] NOT NULL,
	[is_readonly] [tinyint] NOT NULL,
	[is_showing] [tinyint] NOT NULL,
	[in_tran] [tinyint] NOT NULL,
	[help_desc] [varchar](1000) NULL,
	[help_options] [varchar](128) NULL,
	[order_num] [int] NOT NULL,
 CONSTRAINT [PK_device_props] PRIMARY KEY CLUSTERED 
(
	[node_type_id] ASC,
	[propname] 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
/****** Object:  Table [dbo].[image]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[image](
	[name] [varchar](200) NOT NULL,
	[size] [int] NOT NULL,
	[date] [datetime] NOT NULL,
	[data] [varbinary](max) NOT NULL,
 CONSTRAINT [PK_image] PRIMARY KEY CLUSTERED 
(
	[name] 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
/****** Object:  Table [dbo].[inventoryListByServer]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[inventoryListByServer](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[agent_id] [int] NOT NULL,
	[location] [varchar](64) NOT NULL,
	[type] [varchar](128) NULL,
	[HW] [varchar](32) NOT NULL,
	[FW] [varchar](32) NOT NULL,
	[SN] [varchar](32) NOT NULL,
	[alias] [varchar](768) NULL,
	[status] [varchar](16) NOT NULL,
	[date] [datetime] NOT NULL,
	[userId] [varchar](512) NULL,
	[remark1] [varchar](512) NULL,
	[remark2] [varchar](512) NULL,
	[remark3] [varchar](512) NULL,
 CONSTRAINT [PK_inventoryListByServer] PRIMARY KEY CLUSTERED 
(
	[agent_id] ASC,
	[location] 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
/****** Object:  Table [dbo].[messenger]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[messenger](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](50) NOT NULL,
	[type] [varchar](50) NOT NULL,
	[send_mail] [varchar](50) NULL,
	[send_phone] [varchar](50) NULL,
	[work_time] [varchar](50) NOT NULL,
	[keyword] [varchar](50) NULL,
	[enable] [bit] NOT NULL,
 CONSTRAINT [PK_messenger] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UNI_messenger] UNIQUE NONCLUSTERED 
(
	[name] 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
/****** Object:  Table [dbo].[messengerAgent]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[messengerAgent](
	[messenger_id] [int] NOT NULL,
	[agent_id] [int] NOT NULL,
 CONSTRAINT [PK_messengerAgent] PRIMARY KEY CLUSTERED 
(
	[messenger_id] ASC,
	[agent_id] 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
/****** Object:  Table [dbo].[messengerSMS]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[messengerSMS](
	[name] [varchar](50) NOT NULL,
	[host] [varchar](50) NOT NULL,
	[port] [int] NOT NULL,
	[auth_account] [varchar](50) NOT NULL,
	[auth_password] [varchar](50) NOT NULL,
	[enable] [bit] NOT NULL,
 CONSTRAINT [PK_messengerSMS] PRIMARY KEY CLUSTERED 
(
	[name] 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
/****** Object:  Table [dbo].[messengerSMTP]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[messengerSMTP](
	[name] [varchar](50) NOT NULL,
	[host] [varchar](50) NOT NULL,
	[user_account] [varchar](50) NOT NULL,
	[auth_account] [varchar](50) NOT NULL,
	[auth_password] [varchar](50) NOT NULL,
	[enable] [bit] NOT NULL,
 CONSTRAINT [PK_messengerSMTP] PRIMARY KEY CLUSTERED 
(
	[name] 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
/****** Object:  Table [dbo].[nodes]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[nodes](
	[id] [int] NOT NULL,
	[name] [varchar](768) NULL,
	[type_id] [tinyint] NOT NULL,
	[client_id] [int] NOT NULL,
 CONSTRAINT [PK_nodes] PRIMARY KEY CLUSTERED 
(
	[id] 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
/****** Object:  Table [dbo].[objimage]    Script Date: 2019/9/20 下午 03:11:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[objimage](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[tree] [int] NULL,
	[node] [int] NULL,
	[obj] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[params]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[params](
	[id] [int] NULL,
	[name] [varchar](255) NULL,
	[keystr] [varchar](32) NULL,
	[val] [int] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[pmLog]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[pmLog](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[agent_id] [int] NOT NULL,
	[time] [datetime] NOT NULL,
	[trap_id] [int] NOT NULL,
	[timestamp] [int] NOT NULL,
	[body] [varchar](1024) NOT NULL,
 CONSTRAINT [PK_pmLog] PRIMARY KEY CLUSTERED 
(
	[id] 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
/****** Object:  Table [dbo].[pmParamDataType]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[pmParamDataType](
	[id] [tinyint] NOT NULL,
	[name] [varchar](128) NOT NULL,
 CONSTRAINT [PK_pmParamDataType] PRIMARY KEY CLUSTERED 
(
	[id] 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
/****** Object:  Table [dbo].[pmParamErrCode]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[pmParamErrCode](
	[id] [tinyint] NOT NULL,
	[name] [varchar](128) NOT NULL,
 CONSTRAINT [PK_pmParamErrCode] PRIMARY KEY CLUSTERED 
(
	[id] 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
/****** Object:  Table [dbo].[pmParameterLog]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[pmParameterLog](
	[agent_id] [int] NOT NULL,
	[time] [datetime] NOT NULL,
	[data] [varbinary](max) NOT NULL,
	[type] [tinyint] NOT NULL,
	[errCode] [tinyint] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Index [IX_pmParameterLog]    Script Date: 2019/9/20 下午 03:11:50 ******/
CREATE CLUSTERED INDEX [IX_pmParameterLog] ON [dbo].[pmParameterLog]
(
	[agent_id] ASC,
	[time] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[roleAccess]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[roleAccess](
	[role_id] [int] NOT NULL,
	[node_id] [int] NOT NULL,
 CONSTRAINT [PK_roleAccess] PRIMARY KEY CLUSTERED 
(
	[role_id] ASC,
	[node_id] 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
/****** Object:  Table [dbo].[sysProperty]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[sysProperty](
	[name] [varchar](50) NOT NULL,
	[value] [varchar](128) NOT NULL,
 CONSTRAINT [PK_sysProperty] PRIMARY KEY CLUSTERED 
(
	[name] 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
/****** Object:  Table [dbo].[timeSynchornizer]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[timeSynchornizer](
	[id] [int] NOT NULL,
	[name] [varchar](128) NOT NULL,
 CONSTRAINT [PK_timeSynchornizer] PRIMARY KEY CLUSTERED 
(
	[id] 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
/****** Object:  Table [dbo].[topologyDevice]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[topologyDevice](
	[tree_id] [int] NOT NULL,
	[node_id] [int] NOT NULL,
	[location_x] [smallint] NOT NULL,
	[location_y] [smallint] NOT NULL,
	[width] [tinyint] NOT NULL,
	[height] [tinyint] NOT NULL,
 CONSTRAINT [PK_topologyDevice] PRIMARY KEY CLUSTERED 
(
	[tree_id] ASC,
	[node_id] 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
/****** Object:  Table [dbo].[topologyDeviceLink]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[topologyDeviceLink](
	[tree_id] [int] NOT NULL,
	[node_id1] [int] NOT NULL,
	[interface1] [varchar](50) NOT NULL,
	[node_id2] [int] NOT NULL,
	[interface2] [varchar](50) NOT NULL,
 CONSTRAINT [PK_topologyDeviceLink] PRIMARY KEY CLUSTERED 
(
	[tree_id] ASC,
	[node_id1] ASC,
	[interface1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UK_topologyDeviceLink] UNIQUE NONCLUSTERED 
(
	[tree_id] ASC,
	[node_id2] ASC,
	[interface2] 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
/****** Object:  Table [dbo].[topologyEquip]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[topologyEquip](
	[tree_id] [int] NOT NULL,
	[node_id] [int] NOT NULL,
	[name] [varchar](200) NOT NULL,
	[location_x] [int] NOT NULL,
	[location_y] [int] NOT NULL,
	[width] [int] NOT NULL,
	[height] [int] NOT NULL,
 CONSTRAINT [PK_TopologyEquip] PRIMARY KEY CLUSTERED 
(
	[tree_id] ASC,
	[node_id] ASC,
	[name] 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
/****** Object:  Table [dbo].[topologyEquipLink]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[topologyEquipLink](
	[tree_id] [int] NOT NULL,
	[node_id1] [int] NOT NULL,
	[interface1] [varchar](50) NOT NULL,
	[node_id2] [int] NOT NULL,
	[name2] [varchar](200) NOT NULL,
 CONSTRAINT [PK_topologyEquipLink] PRIMARY KEY CLUSTERED 
(
	[tree_id] ASC,
	[node_id1] ASC,
	[interface1] 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
/****** Object:  Table [dbo].[topologyLocation]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[topologyLocation](
	[tree_id] [int] NOT NULL,
	[node_id] [int] NOT NULL,
	[image_name] [varchar](200) NULL,
	[scale] [smallint] NOT NULL,
	[location_x] [smallint] NOT NULL,
	[location_y] [smallint] NOT NULL,
	[width] [tinyint] NOT NULL,
	[height] [tinyint] NOT NULL,
 CONSTRAINT [PK_topologyLocation] PRIMARY KEY CLUSTERED 
(
	[tree_id] ASC,
	[node_id] 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
/****** Object:  Table [dbo].[trapLog]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[trapLog](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[agent_id] [int] NOT NULL,
	[time] [datetime] NOT NULL,
	[specific_type] [int] NOT NULL,
	[timestamp] [int] NOT NULL,
	[message] [varchar](1024) NOT NULL,
	[ack] [tinyint] NOT NULL,
	[clear] [tinyint] NOT NULL,
	[time_ack] [datetime] NULL,
	[remark] [varchar](50) NULL,
	[client_id] [int] NULL,
 CONSTRAINT [PK_trapLog] PRIMARY KEY CLUSTERED 
(
	[id] 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
/****** Object:  Table [dbo].[treelinks]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[treelinks](
	[tree_id] [int] NOT NULL,
	[parent_id] [int] NOT NULL,
	[child_id] [int] NOT NULL,
	[idx] [int] NOT NULL,
	[lvl] [smallint] NOT NULL,
 CONSTRAINT [PK_treelinks] PRIMARY KEY CLUSTERED 
(
	[tree_id] ASC,
	[child_id] 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
/****** Object:  Table [dbo].[trees]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[trees](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[node_id] [int] NOT NULL,
	[repository] [tinyint] NOT NULL,
	[type] [tinyint] NOT NULL,
	[dat] [datetime] NULL,
	[usr] [varchar](32) NULL,
 CONSTRAINT [PK_trees] PRIMARY KEY CLUSTERED 
(
	[id] 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
/****** Object:  Table [dbo].[userRole]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[userRole](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](768) NULL,
	[description] [varchar](768) NULL,
	[function] [varchar](10) NULL,
 CONSTRAINT [PK_userRole] PRIMARY KEY CLUSTERED 
(
	[id] 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].[pmParameterLog] ADD  DEFAULT ('1') FOR [type]
GO
ALTER TABLE [dbo].[pmParameterLog] ADD  DEFAULT ('0') FOR [errCode]
GO
ALTER TABLE [dbo].[trapLog] ADD  CONSTRAINT [DF_trapLog_ack]  DEFAULT ((0)) FOR [ack]
GO
ALTER TABLE [dbo].[trapLog] ADD  CONSTRAINT [DF_trapLog_clear]  DEFAULT ((0)) FOR [clear]
GO
ALTER TABLE [dbo].[adlinks]  WITH CHECK ADD  CONSTRAINT [FK_adlinks_agent] FOREIGN KEY([agent_id])
REFERENCES [dbo].[agent] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[adlinks] CHECK CONSTRAINT [FK_adlinks_agent]
GO
ALTER TABLE [dbo].[adlinks]  WITH CHECK ADD  CONSTRAINT [FK_adlinks_agentsdriver] FOREIGN KEY([agentsdriver_id])
REFERENCES [dbo].[agentsdriver] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[adlinks] CHECK CONSTRAINT [FK_adlinks_agentsdriver]
GO
ALTER TABLE [dbo].[agent]  WITH CHECK ADD  CONSTRAINT [FK_agent_nodes] FOREIGN KEY([id])
REFERENCES [dbo].[nodes] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[agent] CHECK CONSTRAINT [FK_agent_nodes]
GO
ALTER TABLE [dbo].[agentsdriver]  WITH CHECK ADD  CONSTRAINT [FK_agentsdriver_broker] FOREIGN KEY([broker_id])
REFERENCES [dbo].[broker] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[agentsdriver] CHECK CONSTRAINT [FK_agentsdriver_broker]
GO
ALTER TABLE [dbo].[alarm]  WITH CHECK ADD  CONSTRAINT [FK_alarm_agent] FOREIGN KEY([agent_id])
REFERENCES [dbo].[agent] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[alarm] CHECK CONSTRAINT [FK_alarm_agent]
GO
ALTER TABLE [dbo].[alarm]  WITH CHECK ADD  CONSTRAINT [FK_alarm_alarmType] FOREIGN KEY([type])
REFERENCES [dbo].[alarmType] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[alarm] CHECK CONSTRAINT [FK_alarm_alarmType]
GO
ALTER TABLE [dbo].[alarmLog]  WITH CHECK ADD  CONSTRAINT [FK_alarmLog_agent] FOREIGN KEY([agent_id])
REFERENCES [dbo].[agent] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[alarmLog] CHECK CONSTRAINT [FK_alarmLog_agent]
GO
ALTER TABLE [dbo].[alarmLog]  WITH CHECK ADD  CONSTRAINT [FK_alarmLog_client] FOREIGN KEY([client_id])
REFERENCES [dbo].[client] ([id])
ON UPDATE CASCADE
ON DELETE SET NULL
GO
ALTER TABLE [dbo].[alarmLog] CHECK CONSTRAINT [FK_alarmLog_client]
GO
ALTER TABLE [dbo].[alias]  WITH CHECK ADD  CONSTRAINT [FK_alias_nodes] FOREIGN KEY([node_id])
REFERENCES [dbo].[nodes] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[alias] CHECK CONSTRAINT [FK_alias_nodes]
GO
ALTER TABLE [dbo].[clientACL]  WITH CHECK ADD  CONSTRAINT [FK_clientACL_client] FOREIGN KEY([client_id])
REFERENCES [dbo].[client] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[clientACL] CHECK CONSTRAINT [FK_clientACL_client]
GO
ALTER TABLE [dbo].[clientActionLog]  WITH CHECK ADD  CONSTRAINT [FK_clientActionLog_client] FOREIGN KEY([client_id])
REFERENCES [dbo].[client] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[clientActionLog] CHECK CONSTRAINT [FK_clientActionLog_client]
GO
ALTER TABLE [dbo].[clientActionLog]  WITH CHECK ADD  CONSTRAINT [FK_clientActionLog_clientActionClassify] FOREIGN KEY([classify_id])
REFERENCES [dbo].[clientActionClassify] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[clientActionLog] CHECK CONSTRAINT [FK_clientActionLog_clientActionClassify]
GO
ALTER TABLE [dbo].[clientActionLog]  WITH CHECK ADD  CONSTRAINT [FK_clientActionLog_clientActionCode] FOREIGN KEY([action_id])
REFERENCES [dbo].[clientActionCode] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[clientActionLog] CHECK CONSTRAINT [FK_clientActionLog_clientActionCode]
GO
ALTER TABLE [dbo].[clientRole]  WITH CHECK ADD  CONSTRAINT [FK_clientRole_client] FOREIGN KEY([client_id])
REFERENCES [dbo].[client] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[clientRole] CHECK CONSTRAINT [FK_clientRole_client]
GO
ALTER TABLE [dbo].[clientRole]  WITH CHECK ADD  CONSTRAINT [FK_clientRole_userRole] FOREIGN KEY([role_id])
REFERENCES [dbo].[userRole] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[clientRole] CHECK CONSTRAINT [FK_clientRole_userRole]
GO
ALTER TABLE [dbo].[messengerAgent]  WITH CHECK ADD  CONSTRAINT [FK_messengerAgent_agent] FOREIGN KEY([agent_id])
REFERENCES [dbo].[agent] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[messengerAgent] CHECK CONSTRAINT [FK_messengerAgent_agent]
GO
ALTER TABLE [dbo].[messengerAgent]  WITH CHECK ADD  CONSTRAINT [FK_messengerAgent_messenger] FOREIGN KEY([messenger_id])
REFERENCES [dbo].[messenger] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[messengerAgent] CHECK CONSTRAINT [FK_messengerAgent_messenger]
GO
ALTER TABLE [dbo].[pmLog]  WITH CHECK ADD  CONSTRAINT [FK_pmLog_agent] FOREIGN KEY([agent_id])
REFERENCES [dbo].[agent] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[pmLog] CHECK CONSTRAINT [FK_pmLog_agent]
GO
ALTER TABLE [dbo].[pmParameterLog]  WITH CHECK ADD  CONSTRAINT [FK_pmParameterLog_agent] FOREIGN KEY([agent_id])
REFERENCES [dbo].[agent] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[pmParameterLog] CHECK CONSTRAINT [FK_pmParameterLog_agent]
GO
ALTER TABLE [dbo].[roleAccess]  WITH CHECK ADD  CONSTRAINT [FK_roleAccess_nodes] FOREIGN KEY([node_id])
REFERENCES [dbo].[nodes] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[roleAccess] CHECK CONSTRAINT [FK_roleAccess_nodes]
GO
ALTER TABLE [dbo].[roleAccess]  WITH CHECK ADD  CONSTRAINT [FK_roleAccess_userRole] FOREIGN KEY([role_id])
REFERENCES [dbo].[userRole] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[roleAccess] CHECK CONSTRAINT [FK_roleAccess_userRole]
GO
ALTER TABLE [dbo].[topologyDevice]  WITH CHECK ADD  CONSTRAINT [FK_topologyDevice_treelinks] FOREIGN KEY([tree_id], [node_id])
REFERENCES [dbo].[treelinks] ([tree_id], [child_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[topologyDevice] CHECK CONSTRAINT [FK_topologyDevice_treelinks]
GO
ALTER TABLE [dbo].[topologyDeviceLink]  WITH CHECK ADD  CONSTRAINT [FK_topologyDeviceLink_treelinks] FOREIGN KEY([tree_id], [node_id1])
REFERENCES [dbo].[treelinks] ([tree_id], [child_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[topologyDeviceLink] CHECK CONSTRAINT [FK_topologyDeviceLink_treelinks]
GO
ALTER TABLE [dbo].[topologyEquip]  WITH CHECK ADD  CONSTRAINT [FK_topologyEquip_treelinks] FOREIGN KEY([tree_id], [node_id])
REFERENCES [dbo].[treelinks] ([tree_id], [child_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[topologyEquip] CHECK CONSTRAINT [FK_topologyEquip_treelinks]
GO
ALTER TABLE [dbo].[topologyEquipLink]  WITH CHECK ADD  CONSTRAINT [FK_topologyEquipLink_treelinks] FOREIGN KEY([tree_id], [node_id1])
REFERENCES [dbo].[treelinks] ([tree_id], [child_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[topologyEquipLink] CHECK CONSTRAINT [FK_topologyEquipLink_treelinks]
GO
ALTER TABLE [dbo].[topologyLocation]  WITH CHECK ADD  CONSTRAINT [FK_topologyLocation_image] FOREIGN KEY([image_name])
REFERENCES [dbo].[image] ([name])
ON UPDATE SET NULL
ON DELETE SET NULL
GO
ALTER TABLE [dbo].[topologyLocation] CHECK CONSTRAINT [FK_topologyLocation_image]
GO
ALTER TABLE [dbo].[topologyLocation]  WITH CHECK ADD  CONSTRAINT [FK_topologyLocation_treelinks] FOREIGN KEY([tree_id], [node_id])
REFERENCES [dbo].[treelinks] ([tree_id], [child_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[topologyLocation] CHECK CONSTRAINT [FK_topologyLocation_treelinks]
GO
ALTER TABLE [dbo].[trapLog]  WITH CHECK ADD  CONSTRAINT [FK_trapLog_agent] FOREIGN KEY([agent_id])
REFERENCES [dbo].[agent] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[trapLog] CHECK CONSTRAINT [FK_trapLog_agent]
GO
ALTER TABLE [dbo].[trapLog]  WITH CHECK ADD  CONSTRAINT [FK_trapLog_client] FOREIGN KEY([client_id])
REFERENCES [dbo].[client] ([id])
ON UPDATE CASCADE
ON DELETE SET NULL
GO
ALTER TABLE [dbo].[trapLog] CHECK CONSTRAINT [FK_trapLog_client]
GO
/****** Object:  StoredProcedure [dbo].[addAdlink]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[addAdlink]
	@agent_id	     int,
	@agentsDriver_id int
AS
	DECLARE @broker_id int
	
	-- check if the driver's broker exists in the mapping, it means that the agent had joined broker's drivers before.  
	-- broker's agents only can join one driver of the same broker.
	SELECT @broker_id=broker_id from agentsdriver where id=@agentsDriver_id 
	
	IF EXISTS (SELECT * FROM adlinks l, agentsdriver ad WHERE l.agent_id=@agent_id and l.agentsdriver_id=ad.id AND ad.broker_id=@broker_id)
		GOTO ERR

	INSERT INTO adlinks 
		VALUES (@agentsDriver_id, @agent_id);

	RETURN 0
	
ERR:
RETURN -1		
GO
/****** Object:  StoredProcedure [dbo].[addAgent]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[addAgent]
	@name            varchar(768),	-- could be unicode format %uxxxx for 1 char 
	@node_type_id    tinyint,
	@ip_address      varchar(50),
	@port            int,			-- snmp port
	@port_trap       int,
	@mib             varchar(64),
	@read_community  varchar(64),
	@write_community varchar(64),
	@trap_community  varchar(64),
	@op_name         varchar(50),	-- operator name
	@version         tinyint,
	@repetition      tinyint	
AS

DECLARE 
	@username_admin varchar(50),	-- client user admin
	@agent_id       int,			-- agent/node id which is inserted 
	@r              int

	-- query admin's username
	SELECT @username_admin=name FROM client WHERE id=1
	
	BEGIN TRAN
		-- create nodes, treelinks
		EXEC @agent_id=admin_agentNodeInsert @username_admin, @name, @node_type_id	
		IF @@error<>0 OR @agent_id=-1 GOTO ERR	

		-- create agent
		INSERT INTO agent(id, ip_address, port, port_trap, mib, read_community, write_community, trap_community, op_name, [version], repetition, pm_enable, syncTime)
			VALUES(@agent_id, @ip_address, @port, @port_trap, @mib, @read_community, @write_community, @trap_community, @op_name, @version, @repetition, 0, 0)	
		IF @@error<>0 GOTO ERR
	
		-- create alias
		EXEC @r=addAlias @agent_id
		IF @@error<>0 OR @r=-1 GOTO ERR
		
	COMMIT TRAN
	
	SELECT agent_id=@agent_id
	
	RETURN 0
ERR:
	ROLLBACK TRAN
	RETURN -1

GO
/****** Object:  StoredProcedure [dbo].[addAgentsDriver]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[addAgentsDriver]
    @broker_id       int,
	@driverName      varchar(100),
	@delayRead       int,
	@interval        int,
	@timeout         int,
	@type            int,
	@thread          int	

AS
    INSERT INTO agentsdriver (broker_id, name, delayread, interval, timeout, type, thread)
	    VALUES (@broker_id, @driverName, @delayRead, @interval, @timeout, @type, @thread)
GO
/****** Object:  StoredProcedure [dbo].[addAlarm]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[addAlarm]
    @agent_id	 int,
    @rack		 tinyint,
    @slot		 tinyint,
    @side		 tinyint,
    -- @model_name	 varchar(50),
    @type		 smallint,
    @idx		 tinyint, 
    @param	     varchar(10),
    @severity	 tinyint,
    @enable		 bit,
    @model_id int
     
AS
	INSERT INTO alarm(agent_id, rack, slot, side, type, idx, param, severity, enable, model_id)
		VALUES(@agent_id, @rack, @slot, @side, @type, @idx, @param, @severity, @enable, @model_id)

GO
/****** Object:  StoredProcedure [dbo].[addAlarmLog]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[addAlarmLog]
    @agent_id	int,           
    @time_begin datetime,        
    @location	varchar(200),           
    @severity   int,           
    @message    varchar(100)
     
AS
	INSERT INTO alarmLog(agent_id, time_begin, time_end, location, severity, message, ack, time_ack)
		VALUES(@agent_id, @time_begin, '', @location, @severity, @message, 0, '')
	
	IF @@error<>0 
		GOTO ERR

	-- select record for file storage in host pc		
	DECLARE @fromSql varchar(512)
	/*
	SET @fromSql='alarmLog WHERE agent_id='''+convert(varchar(512), @agent_id)+''''
			+' AND time_begin='''+convert(varchar(512), @time_begin,120)+''''
			+' AND location='''+convert(varchar(512), @location)+'''' 
			+' AND severity='''+convert(varchar(512), @severity)+''''
			+' AND message='''+convert(varchar(512), @message)+''''
	*/
	SET @fromSql='alarmLog WHERE id='''+convert(varchar(100), (SELECT @@identity))+''''

	EXEC getAlarmLogs '3', '', '', @fromSql, '', ''

	RETURN 0

ERR:
RETURN -1
GO
/****** Object:  StoredProcedure [dbo].[addAlias]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[addAlias]
	@id int		-- node id
AS
DECLARE 
	@type_id  tinyint,
	@rack_id  int,
	@site_id  int,
	@idx      int,			 
	@c        int,	-- operate rack_id
	@c1		  int,	-- operate site_id
	@c2		  int	-- operate idx				
	
	--SELECT @type_name=(SELECT n2.name FROM nodes n2 WHERE n2.id=n.type_id) FROM nodes n WHERE id=@id				
	SELECT @type_id=type_id FROM nodes WHERE id=@id
	
	-- ETU01A, ELINK, FMUX04E, FMUX160, FMUX80, FMUX1600, FMUX800, FRM220A-1000EAS/X-1, IMC-1000M, IMC-100M, FRM220-100AS-1 
	IF @type_id IN (21, 24, 33, 34, 35, 36, 37, 45, 71, 74, 80)
	BEGIN
		SET @rack_id=1			-- standalone 
		SET @site_id=2			-- local/remote
		SET @idx    =1			-- index start from 1, 1 alias index for 1 site
	END
				
	-- ERM-MUX/Plus
	ELSE IF @type_id=25
	BEGIN
		SET @rack_id=1			-- standalone 
		SET @site_id=2			-- local/remote
		SET @idx    =10			-- index start from 1, 1 site has 10 io slots. 10 for 10 io slots
	END
	
	-- FMUX01A, FMUX01APlus
	ELSE IF @type_id IN (30, 31)
	BEGIN
		SET @rack_id=1			-- standalone
		SET @site_id=2			-- local/remote
		SET @idx    =4*5    	-- index start from 1, site has 4 ports, port has 4 channels. 1 alias index for 1 port, 4 alias index for 4 channels 
	                            -- index 1,6,11,16 is for port, others is for 4 channels
	END
	
	-- FMUX04
	ELSE IF @type_id=32	
	BEGIN
		SET @rack_id=1			-- standalone
		SET @site_id=2			-- local/remote
		SET @idx    =5			-- index start from 1, site has 4 ports, port has 4 channels. 1 alias index for 1 port, 4 alias index for 4 channels  
	                            -- index 1 is for port, others is for 4 channels
	END
	
	-- FRM220, FRM220B
	ELSE IF @type_id IN (40, 44)
	BEGIN
		SET @rack_id=10			-- 10 rack,
		SET @site_id=3			-- local/remote(A)/remoteB
		SET @idx    =20			-- index start from 1, site has 20 slots, 20 alias index for 20 slots
	END
	
	-- FRM220A, FRM220R4
	ELSE IF @type_id IN (41, 83)
	BEGIN
		SET @rack_id=10			-- 10 rack,
		SET @site_id=5			-- local/remote(A)/remoteB/remoteC/remoteD
		SET @idx    =20			-- index start from 1, site has 20 slots, 20 alias index for 20 slots
	END
	
	-- FRM220A-1000EAS/X, FRM220-1000EAS, FRM220-1000EAS/X-2F 
	ELSE IF @type_id IN (42, 47, 81)
	BEGIN
		SET @rack_id=1			-- standalone
		SET @site_id=3			-- local/remoteA/remoteB
		SET @idx    =1			-- index start from 1, 1 alias index for 1 site
	END	
	
	BEGIN TRAN
		SET @c=0
	
		WHILE @c<@rack_id
		BEGIN
			SET @c1=0
	
			WHILE @c1<@site_id		
			BEGIN
				SET @c2=1              --index start from 1
	
				WHILE @c2<=@idx
				BEGIN
					INSERT alias(node_id, rack_id, site_id, idx, name) 
						VALUES(@id, @c, @c1, @c2, '')
					
					IF @@error<>0 goto ERR
	
					SET @c2=@c2+1
				END
	
				SET @c1=@c1+1
			END
		
			SET @c=@c+1
		END
	COMMIT TRAN
	RETURN 0
	
ERR:
	ROLLBACK TRAN
	RETURN -1

GO
/****** Object:  StoredProcedure [dbo].[addBroker]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[addBroker]                                                                                            
    @brokerName varchar(100),	-- could be unicode format %uxxxx for 1 char                                                  
    @ipAddress  varchar(32),                                                                                                  
    @scanPeriod int,                                                                                                          
    @port       int                                                                                                           
                                                                                                                              
AS                                                                                                                            
BEGIN TRAN                                                                                                                    
    INSERT INTO broker(name, ip_address, scanningperiod, port, enable)                                                        
	    VALUES(@brokerName, @ipAddress, @scanPeriod, @port, 0);                                                               
                                                                                                                              
    -- check if this broker's polling driver is none, add one.                                                                
    DECLARE @broker_id int                                                                                                    
                                                                                                                              
    SELECT @broker_id=id                                                                                                      
	    FROM broker                                                                                                           
	    WHERE name=@brokerName AND ip_address=@ipAddress AND scanningperiod=@scanPeriod AND port=@port AND enable=0           
                                                                                                                              
    IF NOT EXISTS (SELECT * FROM agentsdriver WHERE broker_id=@broker_id)                                                     
	    EXEC addAgentsDriver @broker_id, 'Polling-1', 0, 1, 3, 0, 1                                                           
COMMIT TRAN                                                                                                                   
GO
/****** Object:  StoredProcedure [dbo].[addClient]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[addClient]
	@name		  varchar(50),
	@password	  varchar(32),
	@role		  varchar(128),
	@phone  	  varchar(100),
	@division	  varchar(100),
	@address	  varchar(200),
	@description  varchar(200)
AS
	DECLARE @newId int
	
	INSERT INTO client (name, password, phone, division, address, description, default_tree, modify, next, first, attempt, lock)
		VALUES (@name, @password, @phone, @division, @address, @description, 0, GETDATE(), 0, 0, 0, NULL)
	
	IF @@error<>0 
		GOTO ERR
	
	SELECT @newId = @@identity
	
	DECLARE @i int  
    SET @role = RTRIM(LTRIM(@role))  
    SET @i = CHARINDEX(',',@role)  
    WHILE(@i>=1)  
    BEGIN  
		INSERT INTO clientRole VALUES(@newId, LEFT(@role,@i-1))  
		SET @role = SUBSTRING(@role,@i+1,LEN(@role)-@i)  
		SET @i = CHARINDEX(',',@role)  
    END  
    
    -- add default empty ACL ip/mac
    INSERT INTO clientACL VALUES(@newId, '', '')

ERR:
RETURN -1

GO
/****** Object:  StoredProcedure [dbo].[addClientACL]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[addClientACL]
	@name  varchar(50),
	@ip	   varchar(64),
	@mac   varchar(32)
AS	
	INSERT INTO clientACL 
	VALUES ((SELECT id FROM client WHERE name=@name), @ip, @mac)
	
	IF @@error<>0 
		GOTO ERR

ERR:
RETURN -1

GO
/****** Object:  StoredProcedure [dbo].[addClientActionLog]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[addClientActionLog]
	@time       datetime,
	@username   varchar(100),	-- client name
	@classifyId int,
	@actionId   int,
	@content    varchar(3840)
AS
	DECLARE
		@i         int, 
		@client_id int
	
	-- select client id
	SELECT @client_id=id FROM client 
	WHERE name=@username
	
	-- check if user exist
	IF @client_id IS NULL AND @username<>'*' 	-- username * for special case
	GOTO ERR
	
	INSERT INTO clientActionLog ([time], [client_id], [classify_id], [action_id], [content]) 
		VALUES(@time, @client_id, @classifyId, @actionId, @content)
	
	IF @@error<>0 
		GOTO ERR

	-- select record for file storage in host pc		
	DECLARE @fromSql varchar(512)
	/*
	SET @fromSql='clientlog WHERE client_id='''+convert(varchar(512), @client_id)+''''
			+' AND timestamp='''+convert(varchar(512), @timestamp,120)+''''
			+' AND code='''+convert(varchar(512), @code)+''''
			+' AND client_ip='''+convert(varchar(512), @client_ip)+''''
	*/
	SET @fromSql='clientActionLog WHERE id='''+convert(varchar(100), (SELECT @@identity))+''''
	
	EXEC getClientActionLog '3', '', '', @fromSql, '', ''
	
	RETURN 0 	

ERR:
RETURN -1
GO
/****** Object:  StoredProcedure [dbo].[addImage]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[addImage] 
	@name varchar(200),
	@size int,
	@date varchar(50),
	@data varbinary(MAX)

AS
	-- if the item existed, overwrite it.
	IF EXISTS(SELECT * FROM image WHERE name=@name)
		UPDATE image 
			SET size=@size, 
		        date=@date, 
		        data=@data 
			WHERE name=@name
	ELSE		                  
		INSERT INTO image(name, size, date, data) 
			VALUES(@name, @size, @date, @data)
GO
/****** Object:  StoredProcedure [dbo].[addPmLog]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[addPmLog]
    @agent_id    int,
    @receiveTime datetime, 
	@body        varchar(1024)
	
AS 
	INSERT INTO pmLog(agent_id, time, trap_id, timestamp, body) 
		VALUES(@agent_id, @receiveTime, 0, 0, @body)

	IF @@error<>0 
		GOTO ERR

	-- select record for file storage in host pc		
	DECLARE @fromSql varchar(512)

	SET @fromSql='pmLog WHERE id='+convert(varchar(100), (SELECT @@identity))

	EXEC getPmLogs '3', '', '', @fromSql, '', ''

	RETURN 0

ERR:
RETURN -1
GO
/****** Object:  StoredProcedure [dbo].[addPmParameterLog]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[addPmParameterLog]
    @agent_id int,
    @time     datetime, 
	@data     varbinary(MAX),
	@eCode tinyint,
	@dType tinyint
AS 
	INSERT INTO pmParameterLog(agent_id, time, data, type, errCode) 
		VALUES(@agent_id, @time, @data, @dType, @eCode)

	IF @@error<>0 
		GOTO ERR

	RETURN 0

ERR:
RETURN -1

GO
/****** Object:  StoredProcedure [dbo].[addTopologyDevice]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[addTopologyDevice] 
	@tree_id  int,
	@node_id  int
	
AS
	-- create device for tree which existed before topology implement
	IF NOT EXISTS(SELECT * FROM topologyDevice WHERE tree_id=@tree_id) 
	BEGIN		
		-- add topology device
		DECLARE @child_id int
     
		DECLARE cur1 CURSOR FOR (SELECT child_id FROM treelinks WHERE tree_id=@tree_id) 
		OPEN cur1                                          
		FETCH NEXT FROM cur1 INTO @child_id
		WHILE @@FETCH_STATUS=0                          
		BEGIN
			IF EXISTS(SELECT * FROM agent WHERE id=@child_id)		
				INSERT INTO topologyDevice(tree_id, node_id, location_x, location_y, width, height) 
					VALUES(@tree_id, @child_id, 0, 0, 0, 0)			
				
			FETCH NEXT FROM cur1 INTO @child_id                  
		END
		CLOSE cur1                                          
		DEALLOCATE cur1    	
	END
	ELSE
		INSERT INTO topologyDevice(tree_id, node_id, location_x, location_y, width, height) 
			VALUES(@tree_id, @node_id, 0, 0, 0, 0)
GO
/****** Object:  StoredProcedure [dbo].[addTopologyDeviceLink]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[addTopologyDeviceLink] 
	@tree_id     int,
	@node_id1    int,
	@interface1  varchar(50),
	@node_id2    int,
	@interface2  varchar(50)	
	
AS
	INSERT INTO topologyDeviceLink(tree_id, node_id1, interface1, node_id2, interface2) 
		VALUES(@tree_id, @node_id1, @interface1, @node_id2, @interface2)
GO
/****** Object:  StoredProcedure [dbo].[addTopologyEquip]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[addTopologyEquip] 
	@tree_id    int,
	@node_id    int,
	@name       varchar(200),
	@location_x int,
	@location_y int,
	@time       datetime,
	@username   varchar(200)

AS	
	INSERT INTO topologyEquip(tree_id, node_id, name, location_x, location_y, width, height) 
			VALUES(@tree_id, @node_id, @name, @location_x, @location_y, 0, 0)
			
	-- write to clientActionLog
	DECLARE @clientId int, @content varchar(1000) 
	SET @clientId = (SELECT id FROM client WHERE name=@username) 
	
	-- code 9/11 for Parent/Tree
	SET @content = @name+',9:'+CAST(@node_id AS varchar)+',11:'+CAST(@tree_id AS varchar) 
	 
	INSERT INTO clientActionLog ([time], [client_id], [classify_id], [action_id], [content])
			VALUES (@time, @clientId, 4, 420, @content) 
GO
/****** Object:  StoredProcedure [dbo].[addTopologyEquipLink]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[addTopologyEquipLink] 
	@tree_id     int,
	@node_id1    int,
	@interface1  varchar(50),
	@node_id2    int,
	@equip_name  varchar(200)	
	
AS
	INSERT INTO topologyEquipLink(tree_id, node_id1, interface1, node_id2, name2) 
		VALUES(@tree_id, @node_id1, @interface1, @node_id2, @equip_name)
GO
/****** Object:  StoredProcedure [dbo].[addTopologyLocation]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[addTopologyLocation] 
	@tree_id  int,
	@node_id  int
	
AS
	-- create location for tree which existed before topology implement
	IF NOT EXISTS(SELECT * FROM topologyLocation WHERE tree_id=@tree_id) 
	BEGIN	
		DECLARE @child_id int
     
		DECLARE cur1 CURSOR FOR (SELECT child_id FROM treelinks WHERE tree_id=@tree_id) 
		OPEN cur1                                          
		FETCH NEXT FROM cur1 INTO @child_id
		WHILE @@FETCH_STATUS=0                          
		BEGIN
			IF NOT EXISTS(SELECT * FROM agent WHERE id=@child_id)		
				INSERT INTO topologyLocation(tree_id, node_id, image_name, scale, location_x, location_y, width, height) 
					VALUES(@tree_id, @child_id, NULL, 0, 0, 0, 0, 0)				
				
			FETCH NEXT FROM cur1 INTO @child_id                  
		END
		CLOSE cur1                                          
		DEALLOCATE cur1   	
	END
	ELSE
		INSERT INTO topologyLocation(tree_id, node_id, image_name, scale, location_x, location_y, width, height) 
			VALUES(@tree_id, @node_id, NULL, 0, 0, 0, 0, 0)
GO
/****** Object:  StoredProcedure [dbo].[addTrapLog]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[addTrapLog]
    @agent_id         int,           -- agentDdId
    @time             datetime,      -- receiveTime
    @timestamp        int,           -- timeStamp
    @specific_type    int,           -- specificType
    @message          varchar(1024)  -- message       

AS
	INSERT INTO trapLog(agent_id, time, timestamp, specific_type, message)
		values(@agent_id, @time, @timestamp, @specific_type, @message)
	
	IF @@error<>0 
		GOTO ERR	

	-- select record for file storage in host pc		
	DECLARE @fromSql varchar(512)

	SET @fromSql='trapLog WHERE id='''+convert(varchar(100), (SELECT @@identity))+''''

	EXEC getTrapLogs '3', '', '', @fromSql, '', ''

	RETURN 0
	
ERR:
RETURN -1
GO
/****** Object:  StoredProcedure [dbo].[addTree]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[addTree]
	@work_name varchar(50), 
	@name      varchar(768), 
	@type      tinyint

AS 
DECLARE 
	@client_id int, 
	@tree_id   int,
	@node_id   int 
	-- @acl       tinyint

BEGIN TRAN
	-- get worker info 
	SELECT @client_id=id FROM client WHERE name=@work_name 
	IF @@ERROR<>0 GOTO ERR

	-- add node, user node id from 1000
	SELECT @node_id=ISNULL(MAX(id),1000)+1 FROM nodes WHERE id>1000	
	
	-- EMSv281, table 'nodes' has no column 'acl'
	INSERT nodes(id, name, client_id, type_id) 
		VALUES(@node_id, @name, @client_id, 1)
	IF @@ERROR<>0 GOTO ERR
	
	-- default allowed for system roles administrator/operator/user
	INSERT INTO roleAccess SELECT id,@node_id FROM userRole WHERE id<4
	
	-- default allowed for creator roles 
	INSERT INTO roleAccess SELECT id,@node_id FROM [dbo].[fn_getRolesByUserName](@work_name) WHERE id>3 

	-- add tree
	INSERT trees(node_id, repository, type) 
		VALUES(@node_id, 0, @type)
	IF @@ERROR<>0 GOTO ERR

	-- add treelink to map tree_id and node_id
	SELECT @tree_id=id FROM trees WHERE node_id=@node_id
	
	INSERT treelinks(tree_id, parent_id, child_id, idx, lvl)
		VALUES(@tree_id, -1, @node_id, 0, 0)
	IF @@ERROR<>0 GOTO ERR

	--	update client default tree
	UPDATE client SET default_tree=@tree_id WHERE name=@work_name
	IF @@ERROR<>0 GOTO ERR
	
	-- add topology location
	EXEC addTopologyLocation @tree_id, @node_id
	IF @@ERROR<>0 GOTO ERR
	
COMMIT TRAN
	SELECT dBId=@node_id, treeId=@tree_id, name=@name, typeId=1
	
	RETURN 0	

ERR:
ROLLBACK TRAN

	RETURN -1
GO
/****** Object:  StoredProcedure [dbo].[addTreeNode]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[addTreeNode] 
	@worker_name varchar(50), 
	@tree_id     int, 
	@parent_id   int,
	@name        varchar(768)

AS
DECLARE 
	@client_id int,			
	-- @acl       tinyint,			
	@tree_type int,
	@id        int,
	@idx       int,	
	@lvl       int,		
	@r         int			

	-- get user info
	SELECT @client_id=id FROM client WHERE name=@worker_name 
	SELECT @tree_type=type FROM trees WHERE id=@tree_id

	-- only acl<creator can modify the tree, except public tree type=1 
	--if @acl_client>@acl_node and @tree_type<>1 goto ERR1		
	--if @is_rep=1 and @workerId<>1 goto ERR1	-- only client id=1 super admin can modify defaut repository tree

	-- select node has tree name to identify the tree acl when create
	--SELECT @acl_node=n.acl FROM nodes n, treelinks l 
	--WHERE l.tree_id=@tree_id and l.parent_id=-1 and l.child_id=n.id		

	-- check tree is modified
	----- EMS 2.81_01, remove procedure 'treeIsModify'
	-- EXEC @r=treeIsModify @worker_name, @tree_id 
	-- IF @r=0 GOTO ERR1

BEGIN TRAN
	-- add node
	SELECT @id=ISNULL(MAX(id), 1000)+1 FROM nodes WHERE id>1000
	
	-- EMSv281, table 'nodes' has no column 'acl'
	INSERT nodes(id, name, type_id, client_id) 
		VALUES(@id, @name, 1, @client_id)
	IF @@ERROR<>0 GOTO ERR
	
	-- new location node default allowed for system roles administrator/operator/user
	INSERT INTO roleAccess SELECT id,@id FROM userRole WHERE id<4

	-- new location node default allowed for creator roles 
	INSERT INTO roleAccess SELECT id,@id FROM [dbo].[fn_getRolesByUserName](@worker_name) WHERE id>3 
	-- EMS 2.88
	-- user02-Net/user04-Net has role '02,Network'/'04,Network', 
	-- 'Network' is function role, can access no location or device, 
	-- user02-Net add new location node '02-2659'
	-- bug is user04-Net can show location '02-2659'
	AND (SELECT COUNT(*) FROM roleAccess WHERE role_id=id AND node_id=@parent_id)>0
	
	
	-- find index, index starts from 0 
	SET @idx=(SELECT ISNULL(MAX(idx), -1) FROM treelinks 
		WHERE tree_id=@tree_id AND parent_id=@parent_id)
	
	-- find level,
	SET @lvl=(SELECT lvl FROM treelinks 
		WHERE tree_id=@tree_id AND child_id=@parent_id)	

	-- add tree link
	INSERT treelinks (tree_id, parent_id, child_id, idx, lvl) 
		VALUES (@tree_id, @parent_id, @id, @idx+1, @lvl+1)
	IF @@ERROR<>0 GOTO ERR	
	
	-- add topology location
	EXEC addTopologyLocation @tree_id, @id
	IF @@ERROR<>0 GOTO ERR	
	
COMMIT TRAN

	SELECT id=@id
	RETURN 0

ERR:
ROLLBACK TRAN

ERR1:
	RETURN -1
GO
/****** Object:  StoredProcedure [dbo].[addTreeNodeFromProduct]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[addTreeNodeFromProduct]
	@worker_name      varchar(50),
	@tree_id          int,
	@node_id          int,
	@product_tree_id  int,
	@product_node_id  int

AS
	DECLARE
		@idx int, 
		@lvl int,		
		--@acl      tinyint,	-- client
		--@acl1     tinyint,	-- source tree node acl
		--@acl_node tinyint,	--  destination tree node acl
		@r   int

	-- worker acl
	--SELECT @acl=acl FROM client WHERE name=@worker_name

	-- source tree node acl
	--SELECT @acl1=n.acl FROM  treelinks l, nodes n 
	--WHERE l.tree_id=@from_tree_id and l.parent_id=-1 and l.child_id=n.id  

	-- destination tree node acl
	--SELECT @acl_node=n.acl FROM  treelinks l, nodes n 
	--WHERE l.tree_id=@to_tree_id and l.parent_id=-1 and l.child_id=n.id 

	-- acl must > tree node creator 
	--if (@acl>@acl_node) goto ERR1
	--if (@to_tree_id=@from_tree_id) goto ERR1	--same tree

	-- check tree is modified
	----- EMS 2.81_01, remove procedure 'treeIsModify'
	-- EXEC @r=treeIsModify @worker_name, @tree_id
	-- IF @r=0 GOTO ERR2
	
	
	-- check if same tree
	IF @tree_id=@product_tree_id GOTO ERR2	

	SET @lvl=(SELECT lvl FROM treelinks 
		WHERE tree_id=@tree_id AND child_id=@node_id)
	
	SET @idx=ISNULL((SELECT MAX(idx) FROM treelinks 
		WHERE tree_id=@tree_id AND parent_id=@node_id), -1)
	
	-- store tree links for nodes which will join to the destination tree
	CREATE TABLE #x(			
		parent_id int,
		child_id  int,
		idx       int,
		lvl       int
	)

	-- store node
	CREATE TABLE #a(			
		parent_id int,
		child_id  int,
		idx       int,
		lvl       int
	)

	-- store node's children
	CREATE TABLE #b(			
		parent_id int,
		child_id  int,
		idx       int,
		lvl       int
	)

	-- find all children nodes from the product node.
	INSERT #a SELECT parent_id, child_id, idx, lvl FROM treelinks 
		WHERE tree_id=@product_tree_id AND child_id=@product_node_id

	WHILE EXISTS(SELECT * FROM #a)
	BEGIN
		INSERT #b SELECT t.parent_id, t.child_id, t.idx, t.lvl 
			FROM treelinks t, #a a
			WHERE t.tree_id=@product_tree_id AND t.parent_id=a.child_id

		INSERT #x SELECT * FROM #a
		TRUNCATE TABLE #a

		INSERT #a SELECT * FROM #b
		TRUNCATE TABLE #b		
	END

	-- check if duplicate node
	IF EXISTS(
		SELECT * FROM treelinks t, #x x
			WHERE t.tree_id=@tree_id AND t.child_id=x.child_id
	)	
	GOTO ERR1
	
	-- store creator roles
	IF OBJECT_ID(N'[tempdb].[dbo].[#creatorRoles]') IS NOT NULL
		DROP TABLE #creatorRoles
	
	SELECT * INTO #creatorRoles FROM [dbo].[fn_getRolesByUserName](@worker_name)
	
	-- store role allowed nodes
	IF OBJECT_ID(N'[tempdb].[dbo].[#roleNodes]') IS NOT NULL
		DROP TABLE #roleNodes
	
	SELECT * INTO #roleNodes FROM [dbo].[fn_getAllowedNodesByUserName](@worker_name)

BEGIN TRAN
	DECLARE @child_id  int
     
    DECLARE cur CURSOR FOR (SELECT child_id FROM #x) 
    OPEN cur                                          
    FETCH NEXT FROM cur INTO @child_id
    WHILE @@FETCH_STATUS=0                          
    BEGIN
		-- insert node for agent, not for product series name 
		IF @child_id>1000	
		BEGIN
			-- create tree links, join product nodes to management tree
			SET @idx=@idx+1
			
			INSERT INTO treelinks(tree_id, parent_id, child_id, idx, lvl)
				VALUES(@tree_id, @node_id, @child_id, @idx, @lvl+1)
			IF @@ERROR<>0 GOTO ERR
		   
	   		-- add topology device
			EXEC addTopologyDevice @tree_id, @child_id			
			IF @@ERROR<>0 GOTO ERR
			
			-- default allowed for creator roles
			DELETE FROM roleAccess WHERE role_id IN (SELECT id FROM #creatorRoles) AND node_id=@child_id -- to avoid key duplication
			INSERT INTO roleAccess SELECT id,@child_id FROM #creatorRoles 
					WHERE @child_id IN (SELECT id FROM #roleNodes) ----- EMS 2.84_01, check if creator roles can access child nodes 
					AND (SELECT COUNT(*) FROM roleAccess WHERE role_id=id AND node_id=@node_id)>0 -- EMS 2.88 
					
		END	
		FETCH NEXT FROM cur INTO @child_id               
    END
    CLOSE cur                                          
    DEALLOCATE cur                                 
COMMIT TRAN

	DROP TABLE #x
	DROP TABLE #a
	DROP TABLE #b
	DROP TABLE #creatorRoles
	DROP TABLE #roleNodes

	SELECT id=0
	RETURN 0

ERR:
    CLOSE cur                                          
    DEALLOCATE cur
    DROP TABLE #creatorRoles
    DROP TABLE #roleNodes 
ROLLBACK TRAN

ERR1:
	DROP TABLE #x
	DROP TABLE #a
	DROP TABLE #b
	
ERR2:
	SELECT id=-1
	RETURN -1

GO
/****** Object:  StoredProcedure [dbo].[admin_agentNodeInsert]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[admin_agentNodeInsert]
    @worker_name  varchar(50),       
    @agent_name   varchar(768),      --Chinese to unicode 6 char %uxxxx
    @node_type_id tinyint

AS
DECLARE 
    @client_id int,
    -- @acl       tinyint, 
    @id        int, 
    @idx       int, 
    @lvl       int, 
    @i         int, 
    @j         int, 
    @k         int, 
    @l         int,
	@agent_id  int, 
	@rack_id   int, 
	@slot_id   int, 
	@loop_id   int, 
	@mark_id   int, 
	@port_id   int, 	
	@tree_id   int,
	@parent_id int

select @tree_id=1
--select @parent_id=root_id from node_types where id=@node_type_id
set @parent_id=@node_type_id
select @client_id=id from client where name=@worker_name
-- if @acl<>255 return -1
-- EMSv281 removed
-- if @acl<>1 return -1		-- only admin can run admin tool

begin tran
	SELECT @agent_id=isnull(max(id),1000)+1 from nodes where id>1000
	if @@error<>0 goto err

	if not exists (select 1 from treelinks where tree_id=@tree_id and parent_id=@parent_id)
		select @idx=0 
	else
		select @idx=max(idx)+1 from treelinks where tree_id=@tree_id and parent_id=@parent_id
	if @@error<>0 goto err

	select @lvl=lvl+1 from treelinks where tree_id=@tree_id and child_id=@parent_id
	if @@error<>0 goto err

-- insert nodes
	-- EMSv281, table 'nodes' has no column 'acl'
	insert nodes(id,name,type_id,client_id) values(@agent_id,@agent_name,@node_type_id,@client_id)
	if @@error<>0 goto err
	
	-- new device node default allowed for role administrator/operator/user
	INSERT INTO roleAccess SELECT id,@agent_id FROM userRole WHERE id<4

-- insert treelinks	
	insert treelinks(tree_id,parent_id,child_id,idx,lvl) values(@tree_id,@parent_id,@agent_id,@idx,@lvl)	
	if @@error<>0 goto err
commit tran
return @agent_id
err:
rollback tran
return -1
GO
/****** Object:  StoredProcedure [dbo].[admin_agentsdelfromdriver]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
create procedure [dbo].[admin_agentsdelfromdriver]
@driver_id	int,
@agent_id	int
as
insert into adlinks select @driver_id, @agent_id
return 0
GO
/****** Object:  StoredProcedure [dbo].[admin_driverdelete]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
create procedure [dbo].[admin_driverdelete]
@id	int
as

delete
from	agentsdriver
where	id = @id

return 0
GO
/****** Object:  StoredProcedure [dbo].[admin_messengerAgentAdd]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	update messengerAgent 
-- =============================================
CREATE PROCEDURE [dbo].[admin_messengerAgentAdd] 
	@messenger_name varchar(50)

AS
DECLARE 
	@messenger_id int

select @messenger_id=id from messenger where messenger.name=@messenger_name

BEGIN TRAN
	if not exists(select 1 from messengerAgent where messenger_id=@messenger_id)	-- first time map
	begin
		insert messengerAgent select @messenger_id, a.id from agent a
		if @@error<>0 goto ERR
	end
	else			-- messenger map exist
	begin
		insert messengerAgent 
			select @messenger_id,a.id from agent a 
			where a.id not in(select agent_id from messengerAgent where messenger_id=@messenger_id)
		if @@error<>0 goto ERR
	end
COMMIT TRAN
return 0

ERR:
ROLLBACK TRAN
return -1
GO
/****** Object:  StoredProcedure [dbo].[admin_threshold_clear]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
create procedure [dbo].[admin_threshold_clear]
as
truncate table pmList
return 0
GO
/****** Object:  StoredProcedure [dbo].[admin_traplist_clear]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
create procedure [dbo].[admin_traplist_clear]
as
truncate table traplist
return 0
GO
/****** Object:  StoredProcedure [dbo].[alarm_getagents]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE procedure [dbo].[alarm_getagents]
as

select	id = agent_id,
	n.name
from 	adlinks al, agent a, agentsdriver ad, params p, nodes n
where	agent_id = a.id
	and agentsdriver_id = ad.id
	and p.id = 1
	and broker_id = val
	and a.id = n.id

return 0
GO
/****** Object:  StoredProcedure [dbo].[alarm_readlist_000]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
create procedure [dbo].[alarm_readlist_000]
@agent	int = 0,
@rack	int = -1,
@slot	int = -1,
@status	tinyint = 0
as

declare @stts varchar(20)
select	@stts = (case @status	when 1 then ''
				when 2 then '(Event)'
				when 3 then '(Non-urgent)'
				when 4 then '(Urgent)'
				else	''
		end)

/*
select	top 50
	agent = agent_id,
	rack = rack,
	slot = slot,
	timestamp = timestamp_system,
	alarm_id = alarm_id,
	status = status,
	source = source,
	location = location,
	meaning = meaning
from 	trapalarm, alarm
where 	id = alarm_id 
--	and id = 0
	and (@agent=0 or (agent_id = @agent))
	and (@rack=-1 or (rack = @rack))
	and (@slot=-1 or (slot = @slot))
	and (@status=0 or (status = @stts))
order by timestamp_system desc
*/

create	table #ta(trapagent_id int,agent_id int,timestamp_system datetime,status varchar(64),source varchar(64),location varchar(64),meaning varchar(64))

insert into #ta
select 	top 150
	trapagent_id,
	agent_id,
	timestamp_system,
	status,
	source,
	location,
	meaning
from	trapagent ta, decompositiontrapagent dt, alarm a (nolock)
where	ta.trap_id = 0
	and ta.id = dt.trapagent_id
	and dt.structuretrap_id = 3
	and a.id = convert(int,dt.val_float)
order by ta.id desc

select 	
	agent = agent_id,
	agent_name = (select convert(char(15),name) from nodes n where agent_id = n.id),
	agent_IP = (select convert(char(15),ip_address) from agent a where agent_id = a.id),
	rack = (select convert(int,dtr.val_float)
			from decompositiontrapagent dtr
				where ta.trapagent_id = dtr.trapagent_id and dtr.structuretrap_id = 1),
	slot = (select convert(int,dtr.val_float)
			from decompositiontrapagent dtr
				where ta.trapagent_id = dtr.trapagent_id and dtr.structuretrap_id = 2),
	timestamp = timestamp_system,
	status = status,
	source = source,
	location = location,
	meaning = meaning
from	#ta ta,
	decompositiontrapagent dtr,
	decompositiontrapagent dts
where	ta.trapagent_id = dtr.trapagent_id and dtr.structuretrap_id = 1
	and ta.trapagent_id = dts.trapagent_id and dts.structuretrap_id = 2
	and (@agent=0 or (ta.agent_id = @agent))
	and (@rack=-1 or (convert(int,dtr.val_float) = @rack))
	and (@slot=-1 or (convert(int,dts.val_float) = @slot))
	and (@status=0 or (status = @stts))


drop table #ta

return 0
GO
/****** Object:  StoredProcedure [dbo].[alarm_readlist001]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE  procedure [dbo].[alarm_readlist001]
@agent	int = 0,
@rack	int = -1,
@slot	int = -1,
@status	tinyint = 0
as

declare @stts varchar(20), @srack varchar(2), @sslot varchar(2)
select @srack=convert(varchar(2),@rack)
select @sslot=convert(varchar(2),@slot)

select	@stts = (case @status	when 1 then ''
				when 2 then '(Event)'
				when 3 then '(Non-urgent)'
				when 4 then '(Urgent)'
				else	''
		end)

select	top 50
	agent = agent_id,
	timestamp = timestamp_system,
	meaning = body
from 	trapagent
where 	
	trap_id=0
	and (@agent=0 or (agent_id = @agent))
	and (@rack=-1 or (@srack  = substring(body,1,charindex(CHAR(9),body,1)-1)))
	and (@slot=-1 or (@sslot  = substring(body,3,charindex(CHAR(9),body,3)-3)))
order by timestamp_system desc

return 0
GO
/****** Object:  StoredProcedure [dbo].[checkWorker]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[checkWorker] 
	@username varchar(50), 
	@password varchar(32) 
AS
	IF EXISTS (SELECT 1 FROM client WHERE name=@username AND password=@password) 
		SELECT result=1   -- passed check
	ELSE 
		SELECT result=0
GO
/****** Object:  StoredProcedure [dbo].[deleteAdlink]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[deleteAdlink]
	@agent_id	     int,
	@agentsDriver_id int
AS
	DELETE FROM adlinks 
		WHERE agentsdriver_id=@agentsDriver_id AND agent_id=@agent_id
GO
/****** Object:  StoredProcedure [dbo].[deleteAdlinks]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[deleteAdlinks]
	@broker_id int
AS
	DELETE FROM adlinks 
		WHERE agentsdriver_id in (SELECT id FROM agentsdriver WHERE broker_id=@broker_id)
GO
/****** Object:  StoredProcedure [dbo].[deleteAgent]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[deleteAgent]
	@agent_id	int
AS
DECLARE 
	@adminName varchar(50),		--admin privilege username
	@tree_id   int, 
	@idx       int, 
	@parent_id int, 
	@r         int,
	@count     int

	CREATE TABLE #a(
		id        int IDENTITY(1,1),
		tree_id   int NOT NULL, 
		parent_id int NOT NULL) 
		-- idx    int)

	SELECT @adminName=name FROM client WHERE id=1
	--select @tree_id=id from trees where repository=1	-- agent node exists in default repository tree
	
	--select @parent_id=parent_id,@idx=idx from treelinks 
	--where tree_id=@tree_id and child_id=@agent_id
	
	-- every tree has it's only one device agent
	INSERT #a 
		-- SELECT tree_id, parent_id, idx FROM treelinks WHERE child_id=@agent_id
		SELECT tree_id, parent_id FROM treelinks WHERE child_id=@agent_id
	
	BEGIN TRAN
		-- delete nodes and treelinks
		SELECT @count=count(*) FROM #a 
		
		WHILE @count>0
		BEGIN
			SELECT @tree_id=tree_id,@parent_id=parent_id -- ,@idx=idx 
			FROM #a 
			WHERE id=@count
			
			-- EXEC @r=deleteTreeNode @adminName, @tree_id, @parent_id, @idx
			EXEC @r=deleteTreeNode @adminName, @tree_id, @parent_id, @agent_id, NULL -- EMSv288, add time as null
			
			IF @r<>0 GOTO ERR
	
			SET @count=@count-1
		END	
	
		-- delete adlinks, has done by table FK
		--DELETE FROM adlinks WHERE agent_id=@agent_id
		--IF @@error<>0 GOTO ERR
		
		-- delete agent
		DELETE FROM agent WHERE id=@agent_id
		
		-- delete inventory
		DELETE FROM inventoryListByServer WHERE agent_id=@agent_id
		
		-- delete log 'Ack Alarm', action id=601, code id=5
		-- 5:168,true
		DELETE FROM clientActionLog 
		WHERE action_id=601 
		AND (SELECT COUNT(id) 
				FROM alarmLog 
				WHERE id=CAST(SUBSTRING(content,3,CHARINDEX(',',content)-3) AS int))=0  
		-- AND CAST(SUBSTRING(content,3,CHARINDEX(',',content)-3) AS int)  
		-- IN (SELECT id FROM alarmLog WHERE agent_id=@agent_id)
		
		-- delete log 'Ack Trap', action id=602, code id=6
		--6:168,true
		DELETE FROM clientActionLog 
		WHERE  action_id=602 
		AND (SELECT COUNT(id) 
				FROM trapLog 
				WHERE id=CAST(SUBSTRING(content,3,CHARINDEX(',',content)-3) AS int))=0  
		-- AND CAST(SUBSTRING(content,3,CHARINDEX(',',content)-3) AS int)  
		-- IN (SELECT id FROM trapLog WHERE agent_id=@agent_id)
		
		-- delete client action log, content code=1 for Node
		DELETE FROM clientActionLog 
		WHERE CHARINDEX('1:'+CONVERT(VARCHAR,@agent_id),content)>0 
		OR CHARINDEX(' '+CONVERT(VARCHAR,@agent_id)+' ',content)>0 -- topology link
	
		IF @@error<>0 GOTO ERR
	COMMIT TRAN
	
	DROP TABLE #a
	
	RETURN 0
	
ERR:
DROP TABLE #a
ROLLBACK TRAN 
RETURN -1
GO
/****** Object:  StoredProcedure [dbo].[deleteAlarm]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[deleteAlarm]
	@agent_id	int,
	@rack		int,
	@slot		int,
	@side		int,
	@type		int,
	@idx		int

AS
	
	IF @idx != -1
		-- delete specified alarm
		DELETE FROM alarm WHERE agent_id=@agent_id AND rack=@rack AND slot=@slot AND side=@side AND type=@type AND idx=@idx
	
	ELSE IF @type != -1
		-- delete type alarms
		DELETE FROM alarm WHERE agent_id=@agent_id AND rack=@rack AND slot=@slot AND side=@side AND type=@type
	
	ELSE IF @side != -1
	BEGIN 
		-- delete side alarms
		-- delete local side, also delete all remote sides
		IF @side = 0
			DELETE FROM alarm WHERE agent_id=@agent_id AND rack=@rack AND slot=@slot
		ELSE
			DELETE FROM alarm WHERE agent_id=@agent_id AND rack=@rack AND slot=@slot AND side=@side 
	END
	
	ELSE
		-- delete device alarm
		DELETE FROM alarm WHERE agent_id=@agent_id
GO
/****** Object:  StoredProcedure [dbo].[deleteAllClientACL]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[deleteAllClientACL]
AS	
	DELETE FROM clientACL  
GO
/****** Object:  StoredProcedure [dbo].[deleteBroker]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[deleteBroker] 
	@broker_id int
AS
	-- id=1 default broker,  it cannot be deleted.
	IF (@broker_id=1) RETURN	
		
	DELETE FROM broker WHERE id=@broker_id
	
	-- if no active enable broker, set default broker to be active broker.
	IF NOT EXISTS (SELECT * FROM broker WHERE enable=1)		
		UPDATE broker SET enable=1 WHERE id=1;	
GO
/****** Object:  StoredProcedure [dbo].[deleteBrokers]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[deleteBrokers] 
AS
	-- delete all broker expect default broker id=1
	DELETE FROM broker WHERE id<>1
	
	UPDATE broker SET enable=1 WHERE id=1;	
GO
/****** Object:  StoredProcedure [dbo].[deleteClient]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[deleteClient]
	@client_id int
AS
	DELETE FROM client WHERE id=@client_id and id<>1	-- sql administrator should not be delete
GO
/****** Object:  StoredProcedure [dbo].[deleteClientACL]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[deleteClientACL]
	@name varchar(50),
	@ip	  varchar(64),
	@mac  varchar(32)
AS	
	DELETE FROM clientACL  
	WHERE client_id=(SELECT id FROM client WHERE name=@name) 
	AND ip=@ip AND mac=@mac
GO
/****** Object:  StoredProcedure [dbo].[deleteImage]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[deleteImage]
	@name varchar(200)

AS
	DELETE FROM image WHERE name=@name
GO
/****** Object:  StoredProcedure [dbo].[deleteInventoryList]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[deleteInventoryList] 
	@agentId     int,
	@location    varchar(128)

AS
	
	DELETE FROM inventoryListByServer
		WHERE agent_id=@agentId AND location=@location
GO
/****** Object:  StoredProcedure [dbo].[deletePmParameterLog]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[deletePmParameterLog]
	@agent_id int

AS
	DELETE FROM pmParameterLog WHERE agent_id=@agent_id

GO
/****** Object:  StoredProcedure [dbo].[deletePmParameterLogByTime]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[deletePmParameterLogByTime]
	@time datetime

AS
	DECLARE @id int

	DECLARE cur CURSOR FOR (SELECT id FROM agent)
	OPEN cur

	FETCH NEXT FROM cur INTO @id
	WHILE @@FETCH_STATUS = 0
	BEGIN
		DELETE FROM pmParameterLog WHERE agent_id=@id AND time<@time
		
		FETCH NEXT FROM cur INTO @id
	END

	CLOSE cur
	DEALLOCATE cur
GO
/****** Object:  StoredProcedure [dbo].[deleteTopologyDeviceLink]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[deleteTopologyDeviceLink]
	@tree_id     int,
	@node_id1    int,
	@interface1  varchar(50)	
	
AS
	IF @tree_id IS NULL
			-- delete links to the node in all trees
		DELETE FROM topologyDeviceLink WHERE node_id1=@node_id1 OR node_id2=@node_id1
	
	ELSE IF @interface1 IS NULL
		-- delete links belong to the node in the tree.
		DELETE FROM topologyDeviceLink 
			WHERE @tree_id=tree_id AND node_id1=@node_id1 OR node_id2=@node_id1
	
	ELSE
		-- delete the link to the node in the tree. 
		DELETE FROM topologyDeviceLink WHERE tree_id=@tree_id 
			AND (node_id1=@node_id1 AND interface1=@interface1) 
			OR  (node_id2=@node_id1 AND interface2=@interface1) 
GO
/****** Object:  StoredProcedure [dbo].[deleteTopologyEquip]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[deleteTopologyEquip]
	@tree_id int,
	@node_id int,
	@name    varchar(200)
	
AS
	-- delete topology equip
	DELETE FROM topologyEquip 
		WHERE tree_id=@tree_id AND node_id=@node_id AND name=@name
		
	-- delete topology equip link
	DELETE FROM topologyEquipLink
		WHERE tree_id=@tree_id
		AND node_id2=@node_id
		AND name2=@name		
GO
/****** Object:  StoredProcedure [dbo].[deleteTopologyEquipLink]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[deleteTopologyEquipLink]
	@tree_id     int,
	@node_id1    int,
	@interface1  varchar(50)
	
AS
	IF @tree_id IS NULL
		-- delete links to the node in all trees
		DELETE FROM topologyEquipLink WHERE node_id1=@node_id1

	ELSE IF @interface1 IS NULL
		-- delete links belong to the node in the tree.
		DELETE FROM topologyEquipLink 
			WHERE @tree_id=tree_id AND node_id1=@node_id1	
	
	ELSE
		-- delete the link to the node in the tree. 
		DELETE FROM topologyEquipLink WHERE tree_id=@tree_id 
			AND node_id1=@node_id1 AND interface1=@interface1
GO
/****** Object:  StoredProcedure [dbo].[deleteTree]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[deleteTree]
	@worker_name varchar(100),
	@tree_id     int,
	@time        datetime

AS
	DECLARE 
		@client_id  int, 	
		--@acl_client tinyint, 
		--@acl_node   tinyint,
		@tree_type  int,
		@r			int		--exec

	-- worker info
	--select @acl_client=acl,@client_id=id from client where name=@worker_name
	--select @acl_node=n.acl, @tree_type=t.type from nodes n,trees t 
	--where t.id=@tree_id and t.node_id=n.id 

	SELECT @client_id=id FROM client WHERE name=@worker_name
	SELECT @tree_type=t.type FROM nodes n, trees t 
		WHERE t.id=@tree_id and t.node_id=n.id 

	-- only acl<creator can modify the tree, except public tree type=1 
	--if @acl_client>@acl_node and @tree_type<>1 goto ERR1

	-- check tree is modified
	----- EMS 2.81_01, remove procedure 'treeIsModify'
	-- EXEC @r=treeIsModify @worker_name, @tree_id 
	-- IF @r=0 GOTO ERR1 

	-- check tree type, only creator can modify the private tree
	SELECT @tree_type=type FROM trees WHERE id=@tree_id
	
	IF @tree_type=0		-- private tree
	BEGIN
		IF NOT EXISTS(
			SELECT * FROM trees t, nodes n 
				WHERE t.id=@tree_id and type=0 and t.node_id=n.id and n.client_id=@client_id
			)
		GOTO ERR1  
	END

	-- prepare for clientActionLog
	DECLARE @treeNodeDbId int, @treeNodeName varchar(768) 
	SELECT @treeNodeDbId=node_id FROM trees WHERE id=@tree_id
	SELECT @treeNodeName=n.name FROM trees t, nodes n WHERE t.id=@tree_id AND t.node_id=n.id 
	
BEGIN TRAN

	-- delete treelinks
	DELETE FROM treelinks where tree_id=@tree_id	
	IF @@error<>0 GOTO ERR

	-- delete trees
	DELETE FROM trees WHERE @tree_id=id
	IF @@error<>0 GOTO ERR

	-- delete nodes
	DELETE FROM nodes 
		WHERE client_id=@client_id	
			AND NOT EXISTS(SELECT 1 FROM treelinks WHERE treelinks.child_id=nodes.id)
			AND NOT EXISTS(SELECT 1 FROM treelinks WHERE treelinks.parent_id=nodes.id)
	IF @@error<>0 GOTO ERR
	
	-- delete clientActionLog
	DELETE FROM clientActionLog 
	WHERE CHARINDEX('11:'+CAST(@tree_id AS varchar),content)>0  -- code Tree id=11
	OR CHARINDEX('1:'+CAST(@treeNodeDbId AS varchar),content)>0 -- code Node id=1
	OR CHARINDEX('3:['+CAST(@tree_id AS varchar)+' ',content)=1     -- code TopologyDeviceLink id=3
	OR CHARINDEX('4:['+CAST(@tree_id AS varchar)+' ',content)=1     -- code TopologyEquipLink id=4
		
	-- write to clientActionLog
	INSERT INTO clientActionLog ([time],[client_id],[classify_id],[action_id],[content])
	VALUES (@time, @client_id, 4, 403, @treeNodeName)

COMMIT TRAN

	SELECT id=0
	
	RETURN 0

ERR:
	ROLLBACK TRAN 
	
ERR1:
	SELECT id=-1
	
	RETURN -1
GO
/****** Object:  StoredProcedure [dbo].[deleteTreeNode]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[deleteTreeNode]
	@worker_name     varchar(50),	
	@tree_id         int,			
	@parent_id       int,			
	-- @idx          int -- not to use UI tree node index, it may changed, 'query' the right value here
	@selectedNode_id int,
	@time            datetime			

AS	
	-- remove parent node's index in the tree. 
	-- not allowed for not administrators (acl<>255), who try to do it in repository

	-- IF @idx<0 GOTO ERR

	DECLARE 
		@workerId   int,			-- client id
		@c		    int,			-- total children count
		@tree_type  int,			-- tree
		--	@is_rep     tinyint,	-- tree
		--	@acl_client tinyint,	-- client
		@type_id    tinyint,		-- node
		--	@acl_node   tinyint,	-- parent node created acl
		@id         int,				
		@r          int,			-- exec
		@idx        int             -- not to use UI tree node index, it may changed, 'query' the right value here

	SELECT @idx=idx FROM treelinks WHERE tree_id=@tree_id AND parent_id=@parent_id AND child_id=@selectedNode_id

	SELECT @workerId=id FROM client WHERE name=@worker_name
	--SELECT @acl_client=acl FROM client WHERE name=@worker_name
	--SELECT @tree_type=type, @is_rep=repository FROM trees t WHERE id=@tree_id  -- is_repository=1 default tree 

	-- check parent node acl 
	--SELECT @acl_node=n.acl FROM nodes n, treelinks l 
	--WHERE l.tree_id=@tree_id and l.parent_id=-1 and l.child_id=n.id

	-- only worker acl <= parent node's acl can do except public tree type=1
	--if @acl_client>@acl_node and @tree_type<>1 goto ERR1

	-- check tree is modified
	----- EMS 2.81_01, remove procedure 'treeIsModify'
	-- EXEC @r=treeIsModify @worker_name, @tree_id 
	-- IF @r=0 GOTO ERR1

	-- only administrator can modify default repository tree 
	--if @is_rep=1 and @workerId<>1 goto ERR1		--client id=1 is super admin

	--SELECT @type_id=type_id FROM nodes WHERE id=@parent_id
	--if @type_id in (10,11,12,13,14) goto err1

	CREATE TABLE #x(id int NULL)		-- store all nodes id to be delete
	CREATE TABLE #y(id int NULL)		-- store node id  
	CREATE TABLE #z(id int NULL)		-- store node's children id

	-- get the deleted index node's children and children relative nodes in the tree 
	-- remove the node, also need to remove the nodes below it
	INSERT #x SELECT child_id FROM treelinks 
		WHERE tree_id=@tree_id AND parent_id=@parent_id AND idx=@idx
	
	INSERT #y SELECT id FROM #x

	SELECT @c=1
	WHILE @c>0 
	BEGIN
		TRUNCATE TABLE #z
		INSERT #z SELECT t.child_id FROM treelinks t, #y 
			WHERE t.tree_id=@tree_id AND t.parent_id=#y.id
		
		SELECT @c=count(*) FROM #z
	
		INSERT #x SELECT id FROM #z
		TRUNCATE TABLE #y
		INSERT #y SELECT id FROM #z
	END

	-- prepare for write to clientActionLog
	-- get delete node name before deleted
	DECLARE @delNodeName varchar(128), @parentNodeName varchar(128), @content varchar(256)  
	SELECT @delNodeName=name FROM nodes WHERE id=@selectedNode_id
	SELECT @parentNodeName=name FROM nodes WHERE id=@parent_id
	SET @content = '10:'+@delNodeName+',9:'+@parentNodeName

BEGIN TRAN
	-- delete relative tables based on the delete node

	DELETE FROM treelinks 
		WHERE tree_id=@tree_id AND child_id IN (SELECT id FROM #x)
	IF @@ERROR<>0 GOTO ERR

	DELETE FROM nodes 
		WHERE NOT EXISTS(SELECT 1 FROM treelinks WHERE nodes.id=treelinks.child_id)
	IF @@ERROR<>0 GOTO ERR

	DELETE FROM agent 
		WHERE NOT EXISTS(SELECT 1 FROM nodes WHERE agent.id=nodes.id)
	IF @@error<>0 GOTO ERR

	-- delete topology link
	-- delete clientActionLog
    DECLARE cur CURSOR FOR (SELECT id from #x)   
	OPEN cur                                                                                         
	FETCH NEXT FROM cur INTO @id                     
	WHILE @@FETCH_STATUS=0                         
	BEGIN                                            
		EXEC deleteTopologyDeviceLink null, @id, null 
		EXEC deleteTopologyEquipLink null, @id, null 
	                         
	    DELETE FROM clientActionLog 
	    WHERE (CHARINDEX('1:'+CAST(@id AS varchar), content)>0       -- code Node
			    OR CHARINDEX('9:'+CAST(@id AS varchar), content)>0   -- code Parent
			    OR CHARINDEX('10:'+CAST(@id AS varchar), content)>0) -- code Child
	    AND   (CHARINDEX('11:'+CAST(@tree_id AS varchar), content)>0 -- code Tree
	            OR CHARINDEX(',11:', content)=0) -- some action has no tree record
	    
	    DELETE FROM clientActionLog 
		WHERE (CHARINDEX('3:['+CAST(@tree_id AS varchar)+' ', content)=1 -- code TopologyDeviceLink
	           OR CHARINDEX('4:['+CAST(@tree_id AS varchar)+' ', content)=1) -- code TopologyEquipLink 
		AND CHARINDEX(' '+CAST(@id AS varchar)+' ', content)>0
	    
		FETCH NEXT FROM cur INTO @id                 
	END                                                                                              
	CLOSE cur                                        
	DEALLOCATE cur                                   

	-- delete adlinks, has done by FK
	--DELETE FROM adlinks 
	--	WHERE not exists (SELECT 1 FROM agent WHERE adlinks.agent_id=agent.id)
	--IF @@error<>0 GOTO ERR

	DELETE FROM objimage 
		WHERE NOT EXISTS(SELECT 1 FROM nodes WHERE objimage.node=nodes.id)
	IF @@error<>0 GOTO ERR

	-- shift index -1 for treelink index > delete node's   
	UPDATE treelinks 
		SET idx=idx-1 
		WHERE tree_id=@tree_id AND parent_id=@parent_id AND idx>@idx
	IF @@error<>0 GOTO ERR
	
	-- write to clientActionLog
	IF @time IS NOT NULL 
		INSERT INTO clientActionLog ([time],[client_id],[classify_id],[action_id],[content])  
		VALUES(@time, @workerId, 4, 403, @content) 
	
COMMIT TRAN

	DROP TABLE #z
	DROP TABLE #y
	DROP TABLE #x

	SELECT id=0
	RETURN 0

ERR:
ROLLBACK TRAN
	DROP TABLE #z
	DROP TABLE #y
	DROP TABLE #x

ERR1:			
	SELECT id=-1
	RETURN -1
GO
/****** Object:  StoredProcedure [dbo].[editAgent]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[editAgent]
	@agent_id		 int,
	@name			 varchar(768),	-- could be unicode format %uxxxx for 1 char  
	@type_id		 tinyint,		-- device type from node id
	@ip_address		 varchar(50),
	@port			 int,			-- snmp port
	@port_trap		 int,
	@mib			 varchar(64),
	@read_community	 varchar(64),
	@write_community varchar(64),
	@trap_community	 varchar(64),
	@op_name		 varchar(50),
	@version         tinyint,
	@repetition      tinyint 
AS

	DECLARE @old_type_id int

	SELECT @old_type_id=type_id FROM nodes WHERE id=@agent_id

	BEGIN TRAN
		-- update nodes
		UPDATE nodes 
			SET name=@name,type_id=@type_id 
			WHERE id=@agent_id
		
		IF @@error<>0 GOTO ERR
	
		-- update agent
		UPDATE agent 
			SET	ip_address      = @ip_address,
				port            = @port,
				port_trap       = @port_trap,
				mib             = @mib,
				read_community  = @read_community,
				write_community = @write_community,
				trap_community  = @trap_community,
				op_name         = @op_name,
				[version]       = @version,
				repetition      = @repetition
			WHERE id=@agent_id
		
		IF @@error<>0 GOTO ERR
	
		-- update treelinks
		DECLARE @parent_id int, -- treelink	
				@indx      int  -- treelink
	
		-- origin type parent node, shift children idx above changed node          	
		SELECT @parent_id=parent_id, @indx=idx 
			FROM treelinks 
			WHERE tree_id=1 AND child_id=@agent_id
	  
		UPDATE treelinks 
			SET idx=idx-1 
			WHERE tree_id=1 AND parent_id=@parent_id AND idx>@indx
		
		IF @@error<>0 GOTO ERR
	
		-- new type parent join chaged node
		--SELECT @parent_id=root_id FROM node_types 
		--WHERE id=@type_id
		SET @parent_id=@type_id
	
		IF NOT EXISTS (SELECT 1 FROM treelinks WHERE tree_id=1 AND parent_id=@parent_id AND child_id!=@agent_id)
			SELECT @indx=0   -- no other data
		ELSE 
			SELECT @indx=MAX(idx)+1 
				FROM treelinks
				WHERE tree_id=1 AND parent_id=@parent_id AND child_id!=@agent_id
	
		-- only update tree_id 1 (default repository tree)
		UPDATE treelinks 
			SET parent_id=@parent_id,idx=@indx	
			WHERE tree_id=1 AND child_id=@agent_id
		
		IF @@error<>0 GOTO ERR
	
		-- update alias 
		--delete from alias where node_id=@agent_id
		--if @@error<>0 goto ERR
	
		-- insert alias
		--declare @c int
		
		--exec @c=aliasInsert @agent_id
		--if @c=-1 goto ERR
		
	COMMIT TRAN
	
	IF @old_type_id = @type_id
		SELECT type_changed='0'
	ELSE 
		SELECT type_changed='1'	
	
	RETURN 0

ERR:
    ROLLBACK TRAN
    RETURN -1

GO
/****** Object:  StoredProcedure [dbo].[editAgentPmEnable]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[editAgentPmEnable] 
	@id     int,
	@enable bit
	
AS

	UPDATE agent
		SET pm_enable=@enable WHERE id=@id

GO
/****** Object:  StoredProcedure [dbo].[editAgentsDriver]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[editAgentsDriver]    
    @driverId       int,                     
    @driverName     varchar(100),            
    @delayRead      int,                     
    @interval       int,                     
    @timeout        int,                     
    @type           int,                     
    @thread         int                      
                                             
AS                                           
    UPDATE agentsdriver                      
	    SET name          = @driverName,     
		    delayread     = @delayRead,      
		    interval      = @interval,  
		    timeout       = @timeout,   
		    type          = @type,           
		    thread        = @thread          
	    WHERE id=@driverId                   
GO
/****** Object:  StoredProcedure [dbo].[editAgentTimeSyncer]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[editAgentTimeSyncer] 
	@id   int,
	@type int
	
AS

	UPDATE agent
		SET syncTime=@type WHERE id=@id

GO
/****** Object:  StoredProcedure [dbo].[editAlarm]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[editAlarm]
    @agent_id	int,
    @rack		tinyint,
    @slot		tinyint,
    @side		tinyint,
    -- @model_name	varchar(50),
    @type		smallint,
    @idx		tinyint,
    @param	    varchar(10),  
    @severity	tinyint,
    @enable		bit
	
AS
	UPDATE alarm 
		SET param=@param, 
		    severity=@severity, 
		    enable=@enable 
		WHERE agent_id=@agent_id AND rack=@rack AND slot=@slot AND side=@side AND type=@type AND idx=@idx

GO
/****** Object:  StoredProcedure [dbo].[editAlarmLogAck]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[editAlarmLogAck] 
	@clientName	varchar(100),	
	@id			int,
	@ack		tinyint,
	@time_ack	datetime
	
AS
	DECLARE @client_id int

	SELECT @client_id=(SELECT id FROM client WHERE name=@clientName)
	IF @client_id IS NULL
		SET @client_id=NULL

	-- ack 0:to be non-acked, erase use and ack time
	-- ack 1:to be acked, record ack time 
	IF @ack != 1
	BEGIN
		SET @client_id=NULL
		SET @time_ack=''
	END

	UPDATE alarmLog
		SET ack=@ack, client_id=@client_id, time_ack=@time_ack	
		WHERE id=@id


	-- get data for EMS server to do ack alarm event
	SELECT id, agent_id, time_begin, location, severity, message, ack 
		FROM alarmLog
		WHERE id=@id
GO
/****** Object:  StoredProcedure [dbo].[editAlarmLogEndTime]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[editAlarmLogEndTime] 
	@id			int,
	@time_end	datetime
	
AS

	UPDATE alarmLog
		SET time_end=@time_end	WHERE id=@id
GO
/****** Object:  StoredProcedure [dbo].[editBroker]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[editBroker] 
	@broker_id   int,
	@brokerName  varchar(100),	-- could be unicode format %uxxxx for 1 char
	@ipAddress   varchar(32),
	@scanPeriod  int,
	@servicePort int			-- name service port
AS
	UPDATE broker
		SET name=@brokerName,
			ip_address=@ipAddress,
			scanningperiod=@scanPeriod,
			port=@servicePort
		WHERE id=@broker_id
GO
/****** Object:  StoredProcedure [dbo].[editClient]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[editClient]
	@client_id   int,
	@name		 varchar(50),
	@password	 varchar(32),
	@role		 varchar(128),
	@phone  	 varchar(100),
	@division	 varchar(100),
	@address	 varchar(200),
	@description varchar(200)

AS
	UPDATE client
		SET	name     = 	CASE 
							WHEN @client_id=1 THEN name -- id=1 administrator name cannot be changed
							ELSE @name
						END,
			password = @password,
    		phone    = @phone,
    		division = @division,
    		address  = @address,
    		description = @description
		
		WHERE id=@client_id
		
	-- role
	-- account admin can not change role
	IF @client_id > 1
	BEGIN
		DELETE FROM clientRole WHERE client_id = @client_id
	
		DECLARE @i int  
		SET @role = RTRIM(LTRIM(@role))  
		SET @i = CHARINDEX(',',@role)  
		WHILE(@i>=1)  
		BEGIN  
			INSERT INTO clientRole VALUES(@client_id, LEFT(@role,@i-1))  
			SET @role = SUBSTRING(@role,@i+1,LEN(@role)-@i)  
			SET @i = CHARINDEX(',',@role)  
		END
	END  
GO
/****** Object:  StoredProcedure [dbo].[editClientACL]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[editClientACL]
	@name   varchar(50),
	@oldIp	varchar(64),
	@oldMac varchar(32),
	@newIp	varchar(64),
	@newMac varchar(32)
AS	
	UPDATE clientACL 
	SET ip=@newIp, mac=@newMac 
	WHERE client_id=(SELECT id FROM client WHERE name=@name) 
	AND ip=@oldIp AND mac=@oldMac 
GO
/****** Object:  StoredProcedure [dbo].[editInventoryList]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[editInventoryList] 
	@agentId    int,
	@location   varchar(128),
	@idt 	    varchar(512),
	@remark1	varchar(512),
	@remark2	varchar(512),
	@remark3	varchar(512)

AS
	
	UPDATE inventoryListByServer 
		SET userId = @idt, remark1 = @remark1, remark2 = @remark2, remark3 = @remark3  	
		WHERE agent_id = @agentId AND location = @location
GO
/****** Object:  StoredProcedure [dbo].[editPmParameterLog]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[editPmParameterLog]
    @agent_id int,
    @time     datetime, 
	@data     varbinary(MAX)
AS 
	UPDATE pmParameterLog SET data=@data 
	WHERE agent_id=@agent_id AND time=@time 

	IF @@error<>0 
		GOTO ERR

	RETURN 0

ERR:
RETURN -1

GO
/****** Object:  StoredProcedure [dbo].[editRoleAccess]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[editRoleAccess]
	@node_id     int,
	@role		 varchar(128) -- role 'administrator' already filtered out in UI 

AS
	-- edit step 1: delete
	DELETE FROM roleAccess WHERE node_id = @node_id AND role_id > 1 -- id=1 for role 'administrator'
	
	-- edit step 2: recover by UI parameter @role
	IF LEN(@role) > 0  -- one node may become only accessable by role 'administrator'
	BEGIN
		DECLARE @i int
		SET @role = RTRIM(LTRIM(@role))  
		SET @i = CHARINDEX(',',@role)  
		WHILE(@i>=1)  
		BEGIN
			INSERT INTO roleAccess VALUES(LEFT(@role,@i-1),@node_id) 
			SET @role = SUBSTRING(@role,@i+1,LEN(@role)-@i)  
			SET @i = CHARINDEX(',',@role)  
		END
	END
GO
/****** Object:  StoredProcedure [dbo].[editTemplateAlarm]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[editTemplateAlarm]
    @id			int,
    @side		tinyint,
    @type		smallint,
    @idx		tinyint,
    @param		varchar(10), 
    @severity	tinyint,
    @enable		bit
AS
	UPDATE alarmTemplate 
		SET severity=@severity,
			param=@param,
		    enable=@enable 
		WHERE id=@id AND side=@side AND type=@type AND idx=@idx  
GO
/****** Object:  StoredProcedure [dbo].[editTopologyDevice]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[editTopologyDevice] 
	@tree_id      int,
	@node_id      int,
	@location_x   smallint,
	@location_y   smallint,
	@width        tinyint, 
	@height       tinyint
	
AS
	UPDATE topologyDevice 
		SET location_x=@location_x,
		    location_y=@location_y,
		    width=@width,
		    height=@height
		WHERE tree_id=@tree_id AND node_id=@node_id	
GO
/****** Object:  StoredProcedure [dbo].[editTopologyEquip]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[editTopologyEquip] 
	@tree_id      int,
	@node_id      int,
	@name         varchar(200),
	@location_x   smallint,
	@location_y   smallint,
	@width        tinyint, 
	@height       tinyint
	
AS
	UPDATE topologyEquip
		SET location_x=@location_x,
		    location_y=@location_y,
		    width=@width,
		    height=@height
		WHERE tree_id=@tree_id AND name=@name
GO
/****** Object:  StoredProcedure [dbo].[editTopologyLocation]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[editTopologyLocation] 
	@tree_id      int,
	@node_id      int,	
	@image_name   varchar(200),
	@scale        smallint,
	@location_x   smallint,
	@location_y   smallint,
	@width        tinyint,
	@height       tinyint
	
AS
	-- image may be removed.
	IF NOT EXISTS (SELECT * FROM image WHERE name=@image_name)
		SET @image_name=NULL


	UPDATE topologyLocation 
		SET image_name=@image_name,
		    scale=@scale,
		    location_x=@location_x,
		    location_y=@location_y,
		    width=@width,
		    height=@height		
		WHERE tree_id=@tree_id AND node_id=@node_id	
GO
/****** Object:  StoredProcedure [dbo].[editTrapLog]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[editTrapLog] 
	@clientName	varchar(100),	
	@id			int,
	@ack		tinyint,
	@time_ack	datetime

AS
	DECLARE @client_id int

	SELECT @client_id=(SELECT id FROM client WHERE name=@clientName)

	-- ack 0:to be non-acked, erase use and ack time
	-- ack 1:to be acked, record ack time 
	IF @ack != 1
	BEGIN
		SET @client_id=NULL
		SET @time_ack=''
	END

	UPDATE trapLog
		SET ack=@ack, client_id=@client_id, time_ack=@time_ack	
		WHERE id=@id
GO
/****** Object:  StoredProcedure [dbo].[editTreeNode]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

----- EMS 2.90_01, modify procedure 'editTreeNode', change name length 100 to 768. UI length 128, DB needs 768.
CREATE PROCEDURE [dbo].[editTreeNode]
	@worker_name varchar(50),
	@tree_id     int,
	@node_id     int,
	@name        varchar(768)


AS
DECLARE  
	@client_id      int,			-- modifier
--	@acl	        tinyint, 
--	@node_acl       tinyint,		-- creator
--	@node_client_id int,
	@r				int				-- exec

	--SELECT @client_id=id, @acl=acl FROM client 
	--WHERE name=@worker_name

	--SELECT @node_client_id=client_id, @node_acl=acl FROM nodes 
	--WHERE id=@node_id

	--IF @node_client_id IS NULL GOTO ERR1

	-- modifier and creator are same client, can do node modify 
	-- different client to compare acl if has right to modify node, need acl > creator acl
	--IF @client_id<>@node_client_id AND @acl<=@node_acl GOTO ERR1	

	-- check tree is modified
	----- EMS 2.81_01, remove procedure 'treeIsModify'
	-- EXEC @r=treeIsModify @worker_name, @tree_id 
	-- if @r=0 goto ERR
	
	UPDATE nodes SET name=@name WHERE id=@node_id
 
	SELECT id=0
	RETURN 0

ERR:
	SELECT id=-1
	RETURN -1
GO
/****** Object:  StoredProcedure [dbo].[filter_add]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
create procedure [dbo].[filter_add]
@address varchar(255),
@type varchar(10),
@from_time_hour tinyint,
@from_time_min tinyint,
@to_time_hour tinyint,
@to_time_min tinyint,
@txt varchar(255),
@mask varchar(255),
@agent_name varchar(64)
as
insert filters(address,type,from_time_hour,from_time_min,to_time_hour,to_time_min,txt,mask,agent_name)
values(@address,@type,@from_time_hour,@from_time_min,@to_time_hour,@to_time_min,@txt,@mask,@agent_name)
return 0
GO
/****** Object:  StoredProcedure [dbo].[filter_del]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
create procedure [dbo].[filter_del]
@address varchar(255),
@type varchar(10),
@agent_name varchar(64)
as
delete from filters where address=@address and type=@type and agent_name=@agent_name
return 0
GO
/****** Object:  StoredProcedure [dbo].[filter_upd]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
create procedure [dbo].[filter_upd]
@address varchar(255),
@type varchar(10),
@from_time_hour tinyint,
@from_time_min tinyint,
@to_time_hour tinyint,
@to_time_min tinyint,
@txt varchar(255),
@mask varchar(255),
@agent_name varchar(64)
as
update filters set
	from_time_hour=@from_time_hour,
	from_time_min=@from_time_min,
	to_time_hour=@to_time_hour,
	to_time_min=@to_time_min,
	txt=@txt,
	mask=@mask
where address=@address and type=@type and agent_name=@agent_name
return 0
GO
/****** Object:  StoredProcedure [dbo].[filters_clear]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
create procedure [dbo].[filters_clear]
as
delete from filters
return 0
GO
/****** Object:  StoredProcedure [dbo].[get_device_prop_items]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE  procedure [dbo].[get_device_prop_items]
	@node_type_id tinyint,
	@propname varchar(64)
as
select item_id,item_name 
from device_prop_items 
where node_type_id=@node_type_id and propname=@propname
order by item_id
GO
/****** Object:  StoredProcedure [dbo].[get_device_props]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE  procedure [dbo].[get_device_props]
	@node_type_id tinyint
as
select
	propname,oid,type_id,is_readonly,is_showing,in_tran,help_desc,help_options
from
	device_props
where
	node_type_id=@node_type_id
	--and order_num<10
order by
	order_num
GO
/****** Object:  StoredProcedure [dbo].[getAgentById]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getAgentById]
	@id	int
AS

	SELECT
		a.id, 
		n.name,
		n.type_id,
		a.ip_address,
		a.version,
		a.port, 
		a.port_trap,
		a.read_community, 
		a.write_community, 
		a.trap_community, 
		a.repetition,
		a.pm_enable,
        a.syncTime
	FROM agent a, nodes n WHERE a.id=n.id AND a.id=@id
GO
/****** Object:  StoredProcedure [dbo].[getAgentByName]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getAgentByName]
	@agentName	varchar(768)
AS

	SELECT
		a.id, 
		n.name,
		n.type_id,
		a.ip_address,
		a.version,
		a.port, 
		a.port_trap,
		a.read_community, 
		a.write_community, 
		a.trap_community,
	    a.repetition,
	    a.pm_enable,
        a.syncTime 
	FROM agent a, nodes n WHERE a.id=n.id AND n.name=@agentName
GO
/****** Object:  StoredProcedure [dbo].[getAgentIds]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getAgentIds]
@name varchar(100) 
AS	
	SELECT id FROM nodes WHERE id>1000 AND id IN (SELECT id FROM [dbo].[fn_getAllowedNodesByUserName](@name)) 
GO
/****** Object:  StoredProcedure [dbo].[getAgents]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getAgents]
	@orderCol varchar(100),
	@orderDir varchar(100)
AS
	IF @orderCol = 'ip_address'
		SET @orderCol='dbo.ipValue(ip_address)'


	EXEC('SELECT
			a.id, 
			n.name,
			a.ip_address,
			n.type_id,
			a.version,
			a.port, 
			a.port_trap,
			a.read_community, 
			a.write_community, 
			a.trap_community,
			a.repetition 
			FROM agent a, nodes n 
			WHERE a.id=n.id 
			ORDER BY '+@orderCol+' '+@orderDir
		)
GO
/****** Object:  StoredProcedure [dbo].[getAgentsDriverAgents]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getAgentsDriverAgents]
	@agentsDriver_id	int	
AS

	SELECT 
		a.id, 
		n.name,
		n.type_id,
		a.ip_address,
		a.version,
		a.port, 
		a.port_trap,
		a.read_community, 
		a.write_community, 
		a.trap_community, 
	    a.repetition,
	    a.pm_enable,
        a.syncTime
	FROM adlinks l, agent a, nodes n  
	WHERE l.agentsdriver_id=@agentsDriver_id AND l.agent_id=a.id AND a.id=n.id
GO
/****** Object:  StoredProcedure [dbo].[getAgentTimeSyncer]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getAgentTimeSyncer]
@name varchar(100)
AS	
	SELECT id, syncTime 
	FROM agent 
	WHERE id IN (SELECT id FROM [dbo].[fn_getAllowedNodesByUserName](@name))
	ORDER BY ip_address
GO
/****** Object:  StoredProcedure [dbo].[getAlarmLogs]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getAlarmLogs]
	@mode     int,
	@pageRow  int,
	@dataRow  int,
	@fromSql  varchar(512),
	@orderCol varchar(100),
	@orderDir varchar(100)	
AS
	-- convert column name to real column in table
	IF (@orderCol='actor')	
		SET @orderCol='client_id'

	-- create order column statement 
	-- add sub-index for order column if need.
	DECLARE 
		@orderState1 varchar(100),
		@orderState2 varchar(100)

	IF(@orderDir='ASC')
	BEGIN
		SET @orderState1 = @orderCol+' ASC,  id ASC'
		SET @orderState2 = @orderCol+' DESC, id DESC'
	END
	ELSE
	BEGIN
		SET @orderState1 = @orderCol+' DESC, id DESC'
		SET @orderState2 = @orderCol+' ASC,  id ASC'
	END

	-- create tmp table with older data for further query
	IF OBJECT_ID(N'[tempdb].[dbo].[#order]') IS NOT NULL
		DROP TABLE #order

	CREATE TABLE #order (
		id      	int,
		agent_id    int,
		time_begin  datetime,
		time_end    datetime,
		location    varchar(200),
		severity    int,
		message     varchar(100),
		ack         tinyint,
		client_id	int,
		time_ack	datetime
	)


	IF(@mode = 1)	-- page	
	BEGIN 
		-- select rows for page
		EXEC('INSERT INTO #order 
			SELECT * FROM (SELECT TOP '+@pageRow+' * FROM 
				(SELECT TOP '+@dataRow+' * FROM '+@fromSql+' ORDER BY '+@orderState1+') t1 
				 ORDER BY '+@orderState2+') t2 ORDER BY '+@orderState1
		)
	END
	ELSE IF (@mode = 2)	-- report	
	BEGIN
		-- select all rows for report
		EXEC('INSERT INTO #order 
				SELECT * FROM '+@fromSql+' ORDER BY '+@orderState1
		)
	END
	ELSE IF (@mode = 3) -- history
	BEGIN
		-- select inserted record for history file storage
		EXEC('INSERT INTO #order
				SELECT * FROM '+@fromSql
		)
	END
	
	IF(@orderDir='ASC')
	BEGIN
		SET @orderState1 = 'o.'+@orderCol+' ASC,  o.id ASC'
	END
	ELSE IF(@orderDir='DESC')
	BEGIN
		SET @orderState1 = 'o.'+@orderCol+' DESC, o.id DESC'
	END 
	ELSE BEGIN
		SET @orderState1 = 'o.time_begin ASC,  o.id ASC'
	END

	EXEC('
		SELECT o.id, o.ack, o.time_begin, o.time_end, device=n.name, a.ip_address, o.location, o.message, o.severity, actor=c.name, o.time_ack 
		FROM #order o LEFT OUTER JOIN client c on o.client_id=c.id, agent a, nodes n
		WHERE o.agent_id=a.id AND o.agent_id=n.id 
		 
		ORDER BY '+@orderState1)
		
	DROP TABLE #order
GO
/****** Object:  StoredProcedure [dbo].[getAlarms]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getAlarms]
	@agent_id		int

AS
	
	SELECT * FROM alarm WHERE agent_id=@agent_id

GO
/****** Object:  StoredProcedure [dbo].[getBatchLines]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[getBatchLines]
	@batch_id int
AS
select 
	id=l.id,
	ag_id=l.agent_id,
	agent_id=n.name,
	prop=l.prop,
	row=l.row,
	val=l.val,
	result=-1
from
	batchlines l, nodes n
where
	l.batch_id=@batch_id and l.agent_id=n.id
return 0
GO
/****** Object:  StoredProcedure [dbo].[getChild]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE  procedure [dbo].[getChild] 
	@tree_id int, 
	@parent_id int, 
	@idx int 
as 
select id=l.child_id,text=n.name,n.type_id
from treelinks l, nodes n 
where l.parent_id=@parent_id and l.idx=@idx and l.tree_id=@tree_id and l.child_id=n.id 
return 0
GO
/****** Object:  StoredProcedure [dbo].[getChildCount]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
create procedure [dbo].[getChildCount] 
	@tree_id int, 
	@parent_id int 
as 
select cnt=count(*) from treelinks where tree_id=@tree_id and parent_id=@parent_id 
return 0
GO
/****** Object:  StoredProcedure [dbo].[getClientACL]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getClientACL]
	@orderCol varchar(100),
	@orderDir varchar(100)
AS
	IF OBJECT_ID(N'[tempdb].[dbo].[#order]') IS NOT NULL
		DROP TABLE #order

	CREATE TABLE #order (
		name  varchar(100),
		ip    varchar(64),
		mac   varchar(32)
	)
	
	INSERT INTO #order 
	SELECT c.name, ca.ip, ca.mac  
	FROM client c, clientACL ca 
	WHERE ca.client_id=c.id
	
	-- order by statement
	EXEC('SELECT * FROM #order ORDER by ['+@orderCol+'] '+@orderDir)
	DROP TABLE #order
GO
/****** Object:  StoredProcedure [dbo].[getClientActionLog]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getClientActionLog]
	@mode     int,	
	@pageRow  int,
	@dataRow  int,
	@fromSql  varchar(1024),
	@orderCol varchar(100),
	@orderDir varchar(100)	
AS
	-- convert column name to real column in table
	IF (@orderCol='client_name')	
		SET @orderCol='client_id'

	-- create order column statement 
	-- add sub-index for order column if need.
	DECLARE 
		@orderState1 varchar(100),
		@orderState2 varchar(100)

	IF(@orderDir='ASC')
	BEGIN
		SET @orderState1 = @orderCol+' ASC,  id ASC'
		SET @orderState2 = @orderCol+' DESC, id DESC'
	END
	ELSE
	BEGIN
		SET @orderState1 = @orderCol+' DESC, id DESC'
		SET @orderState2 = @orderCol+' ASC,  id ASC'
	END

	-- create tmp table with older data for further query
	IF OBJECT_ID(N'[tempdb].[dbo].[#order]') IS NOT NULL
		DROP TABLE #order

	CREATE TABLE #order (
		[id]           int,
		[time]         datetime,
		[client_id]    int,
		[classify_id]  int,
		[action_id]    int,
		[content]      varchar(3840)
	)
	
	IF (@mode = 1)	-- page	
	BEGIN 
		-- select row for page
		EXEC('INSERT INTO #order 
			SELECT * FROM (SELECT TOP '+@pageRow+' * FROM 
				(SELECT TOP '+@dataRow+' * FROM '+@fromSql+' ORDER BY '+@orderState1+') t1 
				 ORDER BY '+@orderState2+') t2 ORDER BY '+@orderState1
		)
	END	
	ELSE IF (@mode = 2)	-- report	
	BEGIN
		-- select all rows for report
		EXEC('INSERT INTO #order 
				SELECT * FROM '+@fromSql+' ORDER BY '+@orderState1
		)
	END
	ELSE IF (@mode = 3)	-- history
	BEGIN
		-- select inserted record for history file storage
		EXEC('INSERT INTO #order
				SELECT * FROM '+@fromSql
		)
	END
	
	-- parse content
	DECLARE @id int, @time datetime, @clientId int, @classifyId int, @actionId int, @content varchar(3840), 
	        @parsed varchar(3840), @idxA int, @idxB int, 
	        @tmp5 varchar(5), @tmp10 varchar(10), @tmp20 varchar(20), @tmp100 varchar(3840), @tmp128 varchar(3840) 
	
	CREATE TABLE #order2 (
		[id]           int,
		[time]         datetime,
		[client_id]    varchar(128),
		[classify_id]  int,
		[action_id]    int,
		[content]      varchar(3840)
	)
	
	DECLARE cur CURSOR FOR (SELECT [id],[time],[client_id],[classify_id],[action_id],[content] from #order) 
	OPEN cur 
	FETCH NEXT FROM cur INTO @id, @time, @clientId, @classifyId, @actionId, @content 
	WHILE @@FETCH_STATUS=0 
	BEGIN 
		-- action id=501/502/503/504/505 for 'Configure Param', 'Upgrade', 'Backup Param', 'Restore Param', 'Copy Param'
		-- action id=301/302 for Edit/Delete Inventory
		-- action id=418/419 for Map Import/Scale
		-- action id=603 for Edit Device Alarm
		-- content code=1/2/11/12 for Node/Location/Tree/Alias
		IF @actionId IN (501,502,503,504,505,301,302,418,419,603) 
		BEGIN
			-- 1:1001,[ oidxx.xx ]: disable --> enable
			-- 1:1001,2015-10-30xxxxxx
			-- 1:1001,backupFileName
			-- 1:1001,backupWebMessage
			-- 1:1001,restoreWebMessage
			-- 1:1001,copyWebMessage
			-- 1:1001,2:[4:0][5:1][1]
			-- 1:1015,14:map.png,11:2
			-- 1:1015,13:150,11:2
			-- 1:1001,2:[4:0][5:1][1]
			-- 1:1001,19:modelId
			
			SET @idxA = CHARINDEX(',',@content) -- first comma index
			SET @tmp5 = SUBSTRING(@content,3,@idxA-3) -- agentDbId
			SET @parsed = (SELECT name FROM nodes WHERE id=CONVERT(int,@tmp5)) -- agent name, UI length 16, DB needs 96.
			 
			SET @tmp128 = SUBSTRING(@content,@idxA+1,3999) -- [ oidxx.xx ]: disable --> enable
			                                               -- 2015-10-30xxxxxx
			                                               -- filePath
			                                               -- 2:[4:0][5:1][1]
			                                               -- 14:map.png,11:2
			                                               -- 13:150,11:2
			                                               -- 19:modelId
			IF @actionId IN (301,302,418,419) -- remove location/image/scale code  
				SET @tmp128 = SUBSTRING(@tmp128,CHARINDEX(':',@tmp128)+1,3999) -- [4:0][5:1][1]
																			   -- map.png,11:2
																			   -- 150,11:2
			IF @actionId IN (418,419) 
			BEGIN
				SET @idxA = CHARINDEX(':',@tmp128) -- first colon index
				SET @tmp5 = SUBSTRING(@tmp128,@idxA+1,3999) -- treeId
				SET @tmp128 = SUBSTRING(@tmp128,1,CHARINDEX(',',@tmp128)-1) -- image/scale
				SET @tmp128 = @tmp128 +', '+ (SELECT n.name FROM nodes n, trees t WHERE t.id=CAST(@tmp5 AS int) AND t.node_id=n.id)
			END
			
			SET @parsed = @parsed+', '+@tmp128 
			
			INSERT INTO #order2 VALUES(@id, @time, @clientId, @classifyId, @actionId, @parsed) 
		END
		
		-- action id=305/306 for enable/disable PM Setting
		-- action id=401 for Add Tree
		ELSE IF @actionId IN (305,306,401)
		BEGIN
			-- 1:1001
			SET @tmp5 = SUBSTRING(@content,3,3999) -- agentDbId
			SET @tmp100 = (SELECT name FROM nodes WHERE id=CONVERT(int,@tmp5)) -- agent name, UI length 16, DB needs 96.
			INSERT INTO #order2 VALUES(@id, @time, @clientId, @classifyId, @actionId, @tmp100)
		END
		
		-- action id=402/420/421 for 'Add Node', 'Topology Add Node', 'Topology Delete Node'
		-- code id=9/10/11 for Parent/Child/Tree
		ELSE IF @actionId IN (402,420,421)
		BEGIN
			-- 10:1006,9:1005,11:2
			-- equipName,9:1005,11:2
			SET @idxA = CHARINDEX(',',@content) -- first comma index
			IF @actionId=402
			BEGIN
				SET @tmp5 = SUBSTRING(@content,4,@idxA-4) -- childDbId 
				SET @parsed = (SELECT name FROM nodes WHERE id=CONVERT(int,@tmp5)) -- child name, UI length 16, DB needs 96.
			END
			ELSE BEGIN
				SET @parsed = SUBSTRING(@content,1,@idxA-1) -- equip name
			END
			
			
			SET @content = SUBSTRING(@content,@idxA+1,3999) -- 9:1005,11:2
			SET @idxA = CHARINDEX(',',@content) -- first comma index
			SET @tmp5 = SUBSTRING(@content,3,@idxA-3) -- parentDbId 
			SET @tmp100 = (SELECT name FROM nodes WHERE id=CONVERT(int,@tmp5)) -- parent name, UI length 16, DB needs 96.
			SET @parsed = @parsed+','+@tmp100 
						
			SET @content = SUBSTRING(@content,@idxA+1,3999) -- 11:2
			SET @idxA = CHARINDEX(':',@content) -- colon index
			SET @tmp5 = SUBSTRING(@content,@idxA+1,3999) -- treeId 
			SET @tmp100 = (SELECT n.name FROM nodes n, trees t WHERE t.id=CONVERT(int,@tmp5) AND t.node_id=n.id) -- tree name, UI length 16, DB needs 96.
			SET @parsed = @parsed+','+@tmp100 
			
			INSERT INTO #order2 VALUES(@id, @time, @clientId, @classifyId, @actionId, @parsed)
		END
		
		-- action id=404/405/407 for 'Move Node Up', 'Move Node Down', 'Edit Role Access'
		-- code id=1/11 for Node/Tree
		ELSE IF @actionId IN (404,405,407)
		BEGIN
			-- 1:1006,11:2
			SET @idxA = CHARINDEX(',',@content) -- first comma index
			SET @tmp5 = SUBSTRING(@content,3,@idxA-3) -- nodeDbId 
			SET @parsed = (SELECT name FROM nodes WHERE id=CONVERT(int,@tmp5)) -- node name
						
			SET @content = SUBSTRING(@content,@idxA+1,3999) -- 11:2
			SET @idxA = CHARINDEX(':',@content) -- colon index
			SET @tmp5 = SUBSTRING(@content,@idxA+1,3999) -- treeId 
			SET @tmp10 = (SELECT n.name FROM nodes n, trees t WHERE t.id=CONVERT(int,@tmp5) AND t.node_id=n.id) -- tree name
			SET @parsed = @parsed+','+@tmp10 
			
			INSERT INTO #order2 VALUES(@id, @time, @clientId, @classifyId, @actionId, @parsed)
		END
		
		-- action id=406/408 for 'Edit Name', 'Edit Alias'
		-- content code=1/2/11/12 for Node/Location/Tree/Alias
		ELSE IF @actionId IN (406,408)
		BEGIN
			-- 1:1001,namexx --> namexxx,11:2
			SET @idxA = CHARINDEX(',',@content) -- first comma index
			SET @content = SUBSTRING(@content,@idxA+1,3999) -- namexx --> namexxx,11:2
			SET @idxA = CHARINDEX(',',@content) -- first comma index
			SET @parsed = SUBSTRING(@content,1,@idxA-1) -- namexx --> namexxx 
			
			INSERT INTO #order2 VALUES(@id, @time, @clientId, @classifyId, @actionId, @parsed)
		END
		
		-- action id=422/423/424 for Topology Drag Location/Device/Equip Node
		-- action id=425/426/427 for Topology Location/Device/Equip Node Icon
		-- content code=1/9/11/15/16 for Node/Parent/Tree/PositionX/PositionY
		ELSE IF @actionId IN (422,423,424,425,426,427)
		BEGIN
			DECLARE @codeX varchar(5), @codeY varchar(5) 
			IF @actionId IN (422,423,424)
			BEGIN
				SET @codeX = '15:' -- position X
				SET @codeY = '16:' -- position Y
			END
			ELSE BEGIN
				SET @codeX = '17:' -- width
				SET @codeY = '18:' -- height
			END
		
			-- 1:1015,15:500,16:500,9:1013,11:2
			-- equipName,15:500,16:500,9:1013,11:2
			SET @idxA = CHARINDEX(',',@content) -- first comma index
			
			IF @actionId IN (422,423,425,426)
			BEGIN
				SET @tmp5 = SUBSTRING(@content,3,@idxA-3) -- location/device nodeDbId
				SET @parsed = (SELECT name FROM nodes WHERE id=CONVERT(int,@tmp5)) -- location/device nodeName
			END
			ELSE BEGIN
				SET @parsed = SUBSTRING(@content,1,@idxA-1) -- equip nodeName
			END
			
			SET @content = SUBSTRING(@content,@idxA+1,3999) -- 15:500,16:500,9:1013,11:2
			SET @content = REPLACE(@content,@codeX,'') -- 500,16:500,9:1013,11:2
			SET @idxA = CHARINDEX(',',@content) -- first comma index
			SET @parsed = @parsed + ',' + SUBSTRING(@content,1,@idxA-1) -- nodeName,500
			
			SET @content = SUBSTRING(@content,@idxA+1,3999) -- 16:500,9:1013,11:2
			SET @content = REPLACE(@content,@codeY,'') -- 500,9:1013,11:2
			SET @idxA = CHARINDEX(',',@content) -- first comma index
			SET @parsed = @parsed + ',' + SUBSTRING(@content,1,@idxA-1) -- nodeName,500,500
			
			SET @content = SUBSTRING(@content,@idxA+1,3999) -- 9:1013,11:2
			SET @content = REPLACE(@content,'9:','') -- 1013,11:2
			SET @idxA = CHARINDEX(',',@content) -- first comma index
			SET @tmp5 = SUBSTRING(@content,1,@idxA-1) -- parent nodeDbId
			SET @tmp100 = (SELECT name FROM nodes WHERE id=CONVERT(int,@tmp5)) -- parentName
			SET @parsed = @parsed + ',' + @tmp100 -- nodeName,500,500,parentName

			SET @idxA = CHARINDEX(':',@content) -- first colon index
			SET @tmp5 = SUBSTRING(@content,@idxA+1,3999) -- treeId
			SET @tmp100 = (SELECT n.name FROM nodes n, trees t WHERE t.id=CONVERT(int,@tmp5) AND t.node_id=n.id) -- treeName
			SET @parsed = @parsed + ',' + @tmp100 -- nodeName,500,500,parentName,treeName
				
			INSERT INTO #order2 VALUES(@id, @time, @clientId, @classifyId, @actionId, @parsed)
		END
		
		-- action id=409/410 for Topology Add Dvc/Eqp Link
		-- action id=413/414 for Topology Delete Dvc/Eqp Link
		-- content code=3/4 for Topology Dvc/Eqp Link
		ELSE IF @actionId IN (409,410,413,414)
		BEGIN
			-- add device link, 3:[treeId node1DbId if1 node2DbId if2] 
			-- add equip link,  4:[treeId node1DbId if1 node2DbId name]
			-- delete link,     3:[treeId node1DbId if1]
			SET @idxA = CHARINDEX('[',@content) -- first '[' index
			SET @content = SUBSTRING(@content,@idxA+1,3999) -- treeId node1DbId if1 node2DbId if2]
			SET @idxA = CHARINDEX(' ',@content) -- first space index
			SET @tmp5 = SUBSTRING(@content,1,@idxA-1) -- treeId
			SELECT @parsed=n.name FROM trees t, nodes n WHERE t.id=CAST(@tmp5 AS int) AND t.node_id=n.id -- treeName
			
			SET @content = SUBSTRING(@content,@idxA+1,3999) -- node1DbId if1 node2DbId if2]
			SET @idxA = CHARINDEX(' ',@content) -- first space index
			SET @tmp5 = SUBSTRING(@content,1,@idxA-1) -- node1DbId
			SELECT @tmp100=[name], @tmp10=[type_id] FROM nodes WHERE id=CAST(@tmp5 AS int) -- node1Name, node1DeviceType
			SET @parsed = @parsed+','+@tmp100+','+@tmp10 -- treeName,node1Name,node1DeviceType
			
			IF @actionId IN (413,414)
			BEGIN
				SET @content = SUBSTRING(@content,@idxA+1,3999) -- if1]
				SET @idxA = CHARINDEX(']',@content) -- first ']' index
				SET @tmp20 = SUBSTRING(@content,1,@idxA-1) -- if1
				SET @parsed = @parsed+','+@tmp20 -- treeName,node1Name,,node1DeviceType,if1
			END
			
			IF @actionId IN (409,410)
			BEGIN
				SET @content = SUBSTRING(@content,@idxA+1,3999) -- if1 node2DbId if2]
				SET @idxA = CHARINDEX(' ',@content) -- first space index
				SET @tmp20 = SUBSTRING(@content,1,@idxA-1) -- if1
				SET @parsed = @parsed+','+@tmp20 -- treeName,node1Name,,node1DeviceType,if1
			
				SET @content = SUBSTRING(@content,@idxA+1,3999) -- node2DbId if2]
				SET @idxA = CHARINDEX(' ',@content) -- first space index
				SET @tmp5 = SUBSTRING(@content,1,@idxA-1) -- node2DbId
				SELECT @tmp100=[name] , @tmp10=[type_id] FROM nodes WHERE id=CAST(@tmp5 AS int) -- node2Name, node2DeviceType
				SET @parsed = @parsed+','+@tmp100+','+@tmp10 -- treeName,node1Name,node1DeviceType,if1,node2Name,node2DeviceType
				
				SET @content = SUBSTRING(@content,@idxA+1,3999) -- if2]
				SET @idxA = CHARINDEX(']',@content) -- first ']' index

				----- EMS 2.100_01, modify procedure 'getClientActionLog', fix actionId 410 bug, if2 becomes equip name, length not enough
				SET @tmp100 = SUBSTRING(@content,1,@idxA-1) -- if2
				SET @parsed = @parsed+','+@tmp100 -- treeName,node1Name,node1DeviceType,if1,node2Name,node2DeviceType,if2
			END 
			
			INSERT INTO #order2 VALUES(@id, @time, @clientId, @classifyId, @actionId, @parsed)
		END
		
		-- action id=601 for Ack Alarm
		-- content code=5 for Alarm Log
		ELSE IF @actionId=601
		BEGIN
			-- 5:alarmLogId,true
			SET @idxA = CHARINDEX(',',@content) -- first comma index
			SET @tmp5 = SUBSTRING(@content,3,@idxA-3) -- logDbId 
			SET @parsed = SUBSTRING(@content,@idxA+1,3999) -- true
			
			SET @tmp10 = (SELECT agent_id FROM alarmLog WHERE id=CAST(@tmp5 AS int)) -- agentDbId
			SET @parsed = @parsed + ',' + (SELECT name FROM nodes WHERE id=CAST(@tmp10 AS int)) -- true,agentName 
			SET @parsed = @parsed + ',' + (SELECT location+','+message FROM alarmLog WHERE id=CAST(@tmp5 AS int)) -- true,agentName,location,message
			
			INSERT INTO #order2 VALUES(@id, @time, @clientId, @classifyId, @actionId, @parsed)
		END
		
		-- action id=602 for Ack Trap
		-- content code=6 for Trap Log
		ELSE IF @actionId=602
		BEGIN
			-- 6:trapLogId,true
			SET @idxA = CHARINDEX(',',@content) -- first comma index
			SET @tmp5 = SUBSTRING(@content,3,@idxA-3) -- logDbId 
			SET @parsed = SUBSTRING(@content,@idxA+1,3999) -- true
			
			SET @tmp10 = (SELECT agent_id FROM trapLog WHERE id=CAST(@tmp5 AS int)) -- agentDbId
			SET @parsed = @parsed + ', ' + (SELECT name FROM nodes WHERE id=CAST(@tmp10 AS int)) -- true, agentName 
			SET @parsed = @parsed + ', ' + (SELECT [message] FROM trapLog WHERE id=CAST(@tmp5 AS int)) -- true, agentName, message
			
			INSERT INTO #order2 VALUES(@id, @time, @clientId, @classifyId, @actionId, @parsed)
		END
		
		-- others
		ELSE BEGIN
			INSERT INTO #order2 VALUES(@id, @time, @clientId, @classifyId, @actionId, @content) 
		END
		                                             
		FETCH NEXT FROM cur INTO @id, @time, @clientId, @classifyId, @actionId, @content 
	END                                                                                              
	CLOSE cur                                        
	DEALLOCATE cur        
	
----- EMS 2.88_01, modify procedure 'getClientActionLog', fix SQL 2014 ordry by issue
	IF(@orderDir='ASC')
	BEGIN
		SET @orderState1 = 'o2.'+@orderCol+' ASC,  o2.id ASC'
	END
	ELSE IF(@orderDir='DESC')
	BEGIN
		SET @orderState1 = 'o2.'+@orderCol+' DESC, o2.id DESC'
	END
	ELSE
	BEGIN
		SET @orderState1 = 'o2.id ASC,  o2.time ASC'
	END
	
	EXEC('
		SELECT o2.[id], o2.[time], client_name=c.[name], o2.[classify_id], o2.[action_id], o2.[content]
		FROM #order2 o2, client c 
		WHERE o2.[client_id]=c.[id] 
	
		ORDER BY '+@orderState1)
	
	DROP TABLE #order
	DROP TABLE #order2
GO
/****** Object:  StoredProcedure [dbo].[getClients]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getClients]
	@orderCol varchar(100),
	@orderDir varchar(100)
AS
	IF OBJECT_ID(N'[tempdb].[dbo].[#order]') IS NOT NULL
		DROP TABLE #order

	CREATE TABLE #order (
		id          int,
		name        varchar(768),
		description varchar(768),
		role        int,
		division    varchar(768),
		phone       varchar(768),
		address     varchar(768)
	)
	
	DECLARE @tempId          int,
	        @tempName        varchar(768),
	        @tempDescription varchar(768),
		    @tempRole        int,
		    @tempDivision    varchar(768),
		    @tempPhone       varchar(768),
		    @tempAddress     varchar(768)
        
	DECLARE Cur CURSOR FOR
	SELECT id,name,description,division,phone,address FROM client

	-- EMSv297, replace role name list with total role number
	-- SET @tempRole = ''

	OPEN Cur
	FETCH NEXT FROM Cur INTO @tempId, @tempName, @tempDescription, @tempDivision, @tempPhone, @tempAddress   
	WHILE ( @@FETCH_STATUS = 0 )
    BEGIN
        SELECT @tempRole = COUNT(1) FROM clientRole WHERE client_id=@tempId    
        
        INSERT INTO #order  
        VALUES(@tempId,@tempName,@tempDescription,@tempRole,@tempDivision,@tempPhone,@tempAddress)  
        
        FETCH NEXT FROM Cur INTO @tempId, @tempName, @tempDescription, @tempDivision, @tempPhone, @tempAddress  
    END
	CLOSE Cur 
	DEALLOCATE Cur  
	
	-- order by statement
	EXEC('SELECT * FROM #order ORDER by ['+@orderCol+'] '+@orderDir)
	DROP TABLE #order
GO
/****** Object:  StoredProcedure [dbo].[getFirstAgentDeviceType]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[getFirstAgentDeviceType]
AS
	SELECT TOP 1 type_id FROM agent a, nodes n where a.id=n.id
GO
/****** Object:  StoredProcedure [dbo].[getImage]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getImage] 
	@name varchar(200)

AS
	SELECT name, size, date=CONVERT(varchar, date, 120), data FROM image 
		WHERE name=@name
GO
/****** Object:  StoredProcedure [dbo].[getImageInfos]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getImageInfos] 
AS
	SELECT name, size, date=CONVERT(varchar, date, 120) FROM image 
GO
/****** Object:  StoredProcedure [dbo].[getIndexOfChild]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
create procedure [dbo].[getIndexOfChild] 
	@tree_id int, 
	@parent_id int, 
	@child_id int 
as 
declare @idx int 
select @idx=idx from treelinks where tree_id=@tree_id and child_id=@child_id and parent_id=@parent_id 
select idx=isnull(@idx,-1) 
return 0
GO
/****** Object:  StoredProcedure [dbo].[getInventoryList]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[getInventoryList]
	@mode     int,
	@pageRow  int,
	@dataRow  int,
	@fromSql  varchar(512),
	@orderCol varchar(100),
	@orderDir varchar(100)	

AS
	-- create order column statement 
	-- add sub-index for order column if need.
	DECLARE 
		@orderState1 varchar(100),
		@orderState2 varchar(100)
		
	IF(@orderDir='ASC')
	BEGIN
		IF(@orderCol='agent_id')
		BEGIN
			SET @orderState1 = @orderCol+' ASC,  location ASC'
			SET @orderState2 = @orderCol+' DESC, location DESC'
		END
		ELSE
		BEGIN
			SET @orderState1 = @orderCol+' ASC,  agent_id ASC, location ASC'
			SET @orderState2 = @orderCol+' DESC, agent_id DESC, location DESC'
		END
	END
	ELSE
	BEGIN
		IF(@orderCol='agent_id')
		BEGIN
			SET @orderState1 = @orderCol+' DESC, location DESC'
			SET @orderState2 = @orderCol+' ASC,  location ASC'
		END
		ELSE
		BEGIN
			SET @orderState1 = @orderCol+' DESC, agent_id DESC, location DESC'
			SET @orderState2 = @orderCol+' ASC,  agent_id ASC, location ASC'
		END
	END
	
	-- create tmp table with older data for further query
	-- temp table column 'type' length should be 128
	IF OBJECT_ID(N'[tempdb].[dbo].[#order]') IS NOT NULL
	DROP TABLE #order
	
----- EMS 2.87_15, modify procedure 'getInventoryList', change alias length 128 to 768. UI length 128, DB needs 768.
	CREATE TABLE #order(
		id       int,
		agent_id int,
        location varchar(64), 
        type     varchar(128), -- temp table column 'type' length should be 128
        hw       varchar(64),
        fw       varchar(64),
        sn       varchar(64),
        alias    varchar(768),
        status   varchar(16),
        date     datetime,
        userId   varchar(512),
        remark1  varchar(512),
        remark2  varchar(512),
        remark3  varchar(512)
	)
		
	IF(@mode = 1)	-- page	
	BEGIN 
		-- select rows for page
		EXEC('INSERT INTO #order
			SELECT * FROM (SELECT TOP '+@pageRow+' * FROM 
				(SELECT TOP '+@dataRow+' * FROM '+@fromSql+' ORDER BY '+@orderState1+') t1 
				 ORDER BY '+@orderState2+') t2 ORDER BY '+@orderState1
		)
	END
	ELSE IF (@mode = 2)	-- report	
	BEGIN
		-- select all rows for report
		EXEC('INSERT INTO #order
				SELECT * FROM '+@fromSql+' ORDER BY '+@orderState1
		)
	END
	ELSE IF (@mode = 3) -- history
	BEGIN
		-- select inserted record for history file storage
		EXEC('INSERT INTO #order
				SELECT * FROM '+@fromSql
		)
	END
		
----- EMS 2.89_03, modify procedure 'getInventoryList', fix SQL 2014 ordry by issue
	IF(@orderDir='ASC')
	BEGIN
		IF(@orderCol='agent_id')
		BEGIN
			SET @orderState1 = @orderCol+' ASC,  location ASC'
		END
		ELSE
		BEGIN
			SET @orderState1 = @orderCol+' ASC,  agent_id ASC, location ASC'
		END
	END
	ELSE IF(@orderDir='DESC')
	BEGIN
		IF(@orderCol='agent_id')
		BEGIN
			SET @orderState1 = @orderCol+' DESC, location DESC'
		END
		ELSE
		BEGIN
			SET @orderState1 = @orderCol+' DESC, agent_id DESC, location DESC'
		END
	END
	ELSE 
	BEGIN
		SET @orderState1 = 'o.agent_id ASC, o.location ASC'
	END
		
	EXEC('
		SELECT o.id, o.agent_id, device=n.name, ip=a.ip_address, o.location, o.type, o.hw, o.fw, o.sn, o.alias, o.status, o.date, o.userId, o.remark1, o.remark2, o.remark3 
		FROM #order o, agent a, nodes n 
		WHERE o.agent_id=a.id AND o.agent_id=n.id
		
		ORDER BY '+@orderState1)

	DROP TABLE #order

GO
/****** Object:  StoredProcedure [dbo].[getParent]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE  procedure [dbo].[getParent] 
	@tree_id int, 
	@child_id int 
as 
select id=l.parent_id,text=n.name,n.type_id
from treelinks l, nodes n 
where l.tree_id=@tree_id and l.child_id=@child_id and l.parent_id=n.id 
return 0
GO
/****** Object:  StoredProcedure [dbo].[getPmLogs]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[getPmLogs]
	@mode     int,
	@pageRow  int,
	@dataRow  int,
	@fromSql  varchar(512),
	@orderCol varchar(100),
	@orderDir varchar(100)	

AS
	-- create order column statement 
	-- add sub-index for order column if need.
	DECLARE 
		@orderState1 varchar(100),
		@orderState2 varchar(100)

	IF(@orderDir='ASC')
	BEGIN
		SET @orderState1 = @orderCol+' ASC,  id ASC'
		SET @orderState2 = @orderCol+' DESC, id DESC'
	END
	ELSE
	BEGIN
		SET @orderState1 = @orderCol+' DESC, id DESC'
		SET @orderState2 = @orderCol+' ASC,  id ASC'
	END

	-- create tmp table with older data for further query
	IF OBJECT_ID(N'[tempdb].[dbo].[#order]') IS NOT NULL
		DROP TABLE #order
	
	CREATE TABLE #order(
		id             int,
		agent_id       int,
		time           datetime,
		trap_id        int,
	    timestamp      int,
	    body           varchar(1024), 
	)

	IF(@mode = 1)	-- page	
	BEGIN 
		-- select rows for page
		EXEC('INSERT INTO #order 
			SELECT * FROM (SELECT TOP '+@pageRow+' * FROM 
				(SELECT TOP '+@dataRow+' * FROM '+@fromSql+' ORDER BY '+@orderState1+') t1 
				 ORDER BY '+@orderState2+') t2 ORDER BY '+@orderState1
		)
	END
	ELSE IF (@mode = 2)	-- report	
	BEGIN
		-- select all rows for report
		EXEC('INSERT INTO #order 
				SELECT * FROM '+@fromSql+' ORDER BY '+@orderState1
		)
	END
	ELSE IF (@mode = 3) -- history
	BEGIN
		-- select inserted record for history file storage
		EXEC('INSERT INTO #order
				SELECT * FROM '+@fromSql
		)
	END
	
	SELECT o.id, o.time, 'device'=n.name, a.ip_address, o.body
		FROM #order o, agent a, nodes n
		WHERE o.agent_id=a.id AND o.agent_id=n.id	
		
	DROP TABLE #order
GO
/****** Object:  StoredProcedure [dbo].[getPmParameterLog]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getPmParameterLog]
    @agent_id  int,
    @timeBegin datetime,
    @timeEnd   datetime
	
AS 
	SELECT agent_id, time=CONVERT(varchar, time, 120), data, type, errCode FROM pmParameterLog 
		WHERE agent_id=@agent_id AND time>=@timeBegin AND time<=@timeEnd

GO
/****** Object:  StoredProcedure [dbo].[getPollingAgents]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getPollingAgents]
	@broker_id int,
	@orderCol varchar(100),
	@orderDir varchar(100)
AS
	IF @orderCol = 'ip_address'
		SET @orderCol = 'dbo.ipValue(ip_address)'

	IF OBJECT_ID(N'[tempdb].[dbo].[#x]') IS NOT NULL
		DROP TABLE #x

	SELECT 
		agent_id=a.id, driver_id=ad.id, driver_name=ad.name 
		INTO #x 	-- new temp table
		FROM agent a, adlinks l, agentsdriver ad 
		WHERE a.id=l.agent_id AND l.agentsdriver_id=ad.id AND ad.broker_id=@broker_id


	IF OBJECT_ID(N'[tempdb].[dbo].[#order]') IS NOT NULL
		DROP TABLE #order
		
----- EMS 2.90_01, modify procedure 'getPollingAgents', change name length 100 to 768. UI length 128, DB needs 768.
	CREATE TABLE #order (
		id             int,
		name           varchar(768),
		ip_address     varchar(100),
		driver_id      int,
		driver_name    varchar(100),
		polling_status int
	)

	INSERT INTO #order
		SELECT 
			a.id, 
			n.name, 
			a.ip_address, 
			driver_id=isnull((SELECT driver_id FROM #x d WHERE a.id=d.agent_id), '-1'), 
			driver_name=isnull((SELECT driver_name FROM #x d WHERE a.id=d.agent_id), ''),
			polling_status=isnull((SELECT 1 FROM #x d WHERE a.id=d.agent_id), '0')
			FROM nodes n, agent a
			WHERE n.id=a.id
	
	-- order by statement
	EXEC('SELECT * FROM #order ORDER BY polling_status DESC, '+@orderCol+' '+@orderDir)
	DROP TABLE #order

	DROP TABLE #x

GO
/****** Object:  StoredProcedure [dbo].[getRoot]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE  procedure [dbo].[getRoot] 
	@tree_id int 
as 
select id=l.child_id,text=n.name,n.type_id
from treelinks l, nodes n 
where l.parent_id=-1 and l.tree_id=@tree_id and l.child_id=n.id return 0
GO
/****** Object:  StoredProcedure [dbo].[getTemplateAlarms]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getTemplateAlarms]
	@id		int,
	@side	int

AS	
	SELECT * FROM alarmTemplate WHERE id = @id AND side = @side

GO
/****** Object:  StoredProcedure [dbo].[getTemplateLines]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE  PROCEDURE [dbo].[getTemplateLines]
	@template_id int
AS
select 
	id=l.id,
	ag_id=l.agent_id,
	agent_id=n.name,
	prop=l.prop,
	row=l.row,
	val=l.val
from
	templatelines l, nodes n
where
	l.template_id=@template_id and l.agent_id=n.id
return 0
GO
/****** Object:  StoredProcedure [dbo].[getTopologyDeviceLinks]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getTopologyDeviceLinks]
	@tree_id  int
	
AS
	SELECT * FROM TopologyDeviceLink WHERE tree_id=@tree_id
GO
/****** Object:  StoredProcedure [dbo].[getTopologyDevices]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getTopologyDevices]
	@tree_id  int

AS
	SELECT * FROM TopologyDevice WHERE tree_id=@tree_id
GO
/****** Object:  StoredProcedure [dbo].[getTopologyEquipLinks]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getTopologyEquipLinks]
	@tree_id  int
	
AS
	SELECT * FROM TopologyEquipLink WHERE tree_id=@tree_id
GO
/****** Object:  StoredProcedure [dbo].[getTopologyEquips]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getTopologyEquips]
	@tree_id  int
	
AS
	SELECT * FROM topologyEquip WHERE tree_id=@tree_id
GO
/****** Object:  StoredProcedure [dbo].[getTopologyLocation]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getTopologyLocation]
	@tree_id  int,
	@node_id  int
AS
	SELECT * FROM TopologyLocation WHERE tree_id=@tree_id AND node_id=@node_id
GO
/****** Object:  StoredProcedure [dbo].[getTopologyLocations]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getTopologyLocations]
	@tree_id  int
AS
	-- create location/device for tree which existed before topology implement
	IF NOT EXISTS(SELECT * FROM topologyLocation WHERE tree_id=@tree_id)
		EXEC addTopologyLocation @tree_id, -1

	IF NOT EXISTS(SELECT * FROM topologyDevice WHERE tree_id=@tree_id)		
		EXEC addTopologyDevice @tree_id, -1
	
	SELECT * FROM TopologyLocation WHERE tree_id=@tree_id
GO
/****** Object:  StoredProcedure [dbo].[getTrapLogs]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[getTrapLogs]
	@mode     int,
	@pageRow  int,
	@dataRow  int,
	@fromSql  varchar(512),
	@orderCol varchar(100),
	@orderDir varchar(100)	

AS
	-- convert column name to real column in table
	IF (@orderCol='actor')	
		SET @orderCol='client_id'

	-- create order column statement 
	-- add sub-index for order column if need.
	DECLARE 
		@orderState1 varchar(100),
		@orderState2 varchar(100)

	IF(@orderDir='ASC')
	BEGIN
		SET @orderState1 = @orderCol+' ASC,  id ASC'
		SET @orderState2 = @orderCol+' DESC, id DESC'
	END
	ELSE
	BEGIN
		SET @orderState1 = @orderCol+' DESC, id DESC'
		SET @orderState2 = @orderCol+' ASC,  id ASC'
	END

	-- create tmp table with older data for further query
	IF OBJECT_ID(N'[tempdb].[dbo].[#order]') IS NOT NULL
		DROP TABLE #order
	
	CREATE TABLE #order(
		id             int,
		agent_id       int,
		time           datetime,
		specific_type  int,
	    timestamp      int,
	    message       varchar(1024), 
	    ack            tinyint,
		clear          tinyint,	     
		time_ack       datetime,
		remark         varchar(50),
		client_id      int
	)

	IF(@mode = 1)	-- page	
	BEGIN 
		-- select rows for page
		EXEC('INSERT INTO #order 
			SELECT * FROM (SELECT TOP '+@pageRow+' * FROM 
				(SELECT TOP '+@dataRow+' * FROM '+@fromSql+' ORDER BY '+@orderState1+') t1 
				 ORDER BY '+@orderState2+') t2 ORDER BY '+@orderState1
		)
	END
	ELSE IF (@mode = 2)	-- report	
	BEGIN
		-- select all rows for report
		EXEC('INSERT INTO #order 
				SELECT * FROM '+@fromSql+' ORDER BY '+@orderState1
		)
	END
	ELSE IF (@mode = 3) -- history
	BEGIN
		-- select inserted record for history file storage
		EXEC('INSERT INTO #order
				SELECT * FROM '+@fromSql
		)
	END
	
	IF(@orderDir='ASC')
	BEGIN
		SET @orderState1 = 'o.'+@orderCol+' ASC,  o.id ASC'
	END
	ELSE IF(@orderDir='DESC')
	BEGIN
		SET @orderState1 = 'o.'+@orderCol+' DESC,  o.id DESC'
	END
	ELSE
	BEGIN
		SET @orderState1 = 'o.time ASC,  o.id ASC'
	END

	EXEC('
		SELECT o.id, o.ack, o.time, device=n.name, a.ip_address, o.timestamp, o.specific_type, o.message, actor=c.name, o.time_ack 
		FROM #order o LEFT OUTER JOIN client c on o.client_id=c.id, agent a, nodes n
		WHERE o.agent_id=a.id AND o.agent_id=n.id	
		
		ORDER BY '+@orderState1)

	DROP TABLE #order
GO
/****** Object:  StoredProcedure [dbo].[getTreeCreator]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getTreeCreator]
	@rootId  int

AS
	SELECT c.name FROM client c, nodes n
	WHERE n.id=@rootId AND n.client_id=c.id 
GO
/****** Object:  StoredProcedure [dbo].[getTreeLinks]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getTreeLinks]
	@treeId int,
	@userName varchar(64)
AS

IF OBJECT_ID(N'[tempdb].[dbo].[#tNodes]') IS NOT NULL
	DROP TABLE #tNodes

SELECT * INTO #tNodes FROM [dbo].[fn_getAllowedNodesByUserName](@userName)

IF OBJECT_ID(N'[tempdb].[dbo].[#tLink]') IS NOT NULL
	DROP TABLE #tLink
	
select parentId=parent_id, childId=child_id, level=lvl, 'index'=idx 
INTO #tLink
from treelinks 

-- where tree_id=@treeId order by lvl,idx 
WHERE tree_id=@treeId 
		AND (parent_id<1001 OR parent_id IN (SELECT id FROM #tNodes)) -- -1 for root's parent, 1~1000 for product tree nodes
		AND (child_id<1001  OR child_id  IN (SELECT id FROM #tNodes)) -- 1~1000 for product tree nodes

order by treelinks.lvl ASC, treelinks.parent_id ASC, treelinks.idx ASC

IF OBJECT_ID(N'[tempdb].[dbo].[#tAdjIdx]') IS NOT NULL
	DROP TABLE #tAdjIdx

DECLARE @pid int, @cid int, @lv int, @compareLV int, @newLV int, @compareParent int  
SELECT TOP(1) @compareLV=level FROM #tLink
ORDER BY [level] ASC, [parentId] ASC, [index] ASC


CREATE TABLE #tAdjIdx (
	[parentId] int, [childId] int, [level] int, [index] int IDENTITY(0,1)
) 

IF @compareLV=0 -- if first level not 0, this user not in allow list of root node
BEGIN
	SET @newLV=0 
	SET @compareParent=-1 
	
	DECLARE Cur CURSOR FOR
	SELECT [parentId], [childId], [level] FROM #tLink ORDER BY [level] ASC, [parentId] ASC, [index] ASC
	OPEN Cur
	FETCH NEXT FROM Cur INTO @pid, @cid, @lv    
	WHILE ( @@FETCH_STATUS = 0 )
	BEGIN
		IF @compareLV<>@lv
		BEGIN
			DBCC checkident (#tAdjIdx, reseed, -1)
			SET @compareLV=@lv
			SET @newLV = @newLV+1
		END
		ELSE
		BEGIN
			IF @compareParent<>@pid 
				DBCC checkident (#tAdjIdx, reseed, -1) 
		END
		
		INSERT INTO #tAdjIdx (parentId, childId, level) VALUES (@pid, @cid, @newLV) 
		
		SET @compareParent=@pid 
		FETCH NEXT FROM Cur INTO @pid, @cid, @lv	
	END
	CLOSE Cur 
	DEALLOCATE Cur  
END

SELECT * FROM #tAdjIdx ORDER BY [level] ASC, [index] ASC 

DROP TABLE #tNodes
DROP TABLE #tLink
DROP TABLE #tAdjIdx

GO
/****** Object:  StoredProcedure [dbo].[getTreeNode]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getTreeNode]
	@tree_id  int,
	@node_id  int

AS
	SELECT * FROM treelinks t, nodes n 
	WHERE t.tree_id=@tree_id AND child_id=@node_id AND t.child_id=n.id
GO
/****** Object:  StoredProcedure [dbo].[getTreeNodes]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getTreeNodes]
	@treeId int,
	@userName varchar(64)
AS

IF OBJECT_ID(N'[tempdb].[dbo].[#tLink]') IS NOT NULL
	DROP TABLE #tLink
	
CREATE TABLE #tLink (
	id int
)

insert into #tLink select distinct child_id from treelinks where tree_id=@treeId

select dBId=n.id, name=n.name, typeId=n.type_id  
from nodes n, #tLink k     
where n.id=k.id 
		AND (n.id<1001 OR n.id IN (SELECT id FROM [dbo].[fn_getAllowedNodesByUserName](@userName)))

drop table #tLink

GO
/****** Object:  StoredProcedure [dbo].[getUserTree]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getUserTree] 
	@username varchar(100)
AS
	
DECLARE
	@treeId   int,
	@clientId int

BEGIN
	-- Client default_tree id is set to 0 by default,
	SELECT @treeId=default_tree FROM client WHERE name=@username

	-- Tree has an instance	of node, and tree id is from 1 (repository product tree)
	-- If the tree of the tree id is not found, 
    -- select the the tree of max tree id created by user to be default_tree 
	IF NOT EXISTS(SELECT * FROM trees WHERE id=@treeId)
	BEGIN
		SELECT @clientId=id FROM client WHERE name=@username

		SELECT @treeId=isnull(max(t.id),0) FROM trees t, nodes n 
		WHERE t.id<>1 and t.node_id=n.id and n.client_id=@clientId
				 AND t.node_id IN (SELECT id FROM [dbo].[fn_getAllowedNodesByUserName](@username)) -- check role access

		-- add new loaded tree to be default tree
		update client set default_tree=@treeId where name=@username;
	END 	
END

SELECT tree_id=@treeId
GO
/****** Object:  StoredProcedure [dbo].[getUserTrees]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[getUserTrees]
	@username varchar(50), 
	@orderCol varchar(100),
	@orderDir varchar(100)
	
AS 
	DECLARE 
		@client_id int

	-- client information
	SELECT @client_id=id
		FROM client 
		WHERE name=@username 

	IF OBJECT_ID(N'[tempdb].[dbo].[#t]') IS NOT NULL
		DROP TABLE #t

	-- add public trees and trees created by user
	-- excluding default repository tree (id=1)
	-- search tree's root node in tree links.
	SELECT t.id, n.name, t.type, 'username'=c.name INTO #t
		FROM trees t, treelinks l, nodes n, client c  
		WHERE l.parent_id=-1 AND l.child_id=t.node_id
			  AND l.tree_id=t.id AND t.node_id=n.id AND n.client_id=c.id 
			  AND t.id<>1 AND (t.type=1 OR c.id=@client_id)
			  AND t.node_id IN (SELECT id FROM [dbo].[fn_getAllowedNodesByUserName](@username)) 
	
	-- distinguish duplication
	-- select distinct tree_id, name, type from #t  
	
	EXEC('SELECT * FROM #t ORDER BY '+ @orderCol +' '+ @orderDir)

	DROP TABLE #t
GO
/****** Object:  StoredProcedure [dbo].[globalEditAlarm]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[globalEditAlarm]
    @agtId		int,
    -- model_name varchar(50),
    @modelId	int,
    @type		smallint,
    @idx		tinyint, 
    @param	    varchar(10),
    @severity	tinyint,
    @enable		bit
	
AS	
	UPDATE alarm 
	SET param=@param, 
		severity=@severity, 
		enable=@enable 
	WHERE agent_id=@agtId AND model_id=@modelId AND type=@type AND idx=@idx 
GO
/****** Object:  StoredProcedure [dbo].[obj_clr]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE	procedure	[dbo].[obj_clr]
@tree		int,
@node		int
as

/*-------------  --------------------*/
/*-------------  --------------------*/
/* test */
--1
if @tree is null or @node is null return -1
if @tree < 1 or @node < 1 return -1

if (select type_id from nodes where id=@node)<>2 return -1
/*-------------  --------------------*/
/*-------------  --------------------*/
/* exec */
delete
from 	objimage
where	tree = @tree
	and node = @node

return 0
GO
/****** Object:  StoredProcedure [dbo].[obj_get]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
create	procedure	[dbo].[obj_get]
@tree		int,
@node		int
as
/*-------------  --------------------*/
/*-------------  --------------------*/
/* test */
--1
if @tree is null or @node is null return -1
if @tree < 1 or @node < 1 return -1

/* exec */
select	tree = tree,
	node = node,
	obj = obj
--	length = DATALENGTH(obj)
from 	objimage
where	tree = @tree
	and node = @node

return 0
GO
/****** Object:  StoredProcedure [dbo].[obj_put]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE	procedure	[dbo].[obj_put]
@tree		int,
@node		int,
@object		image
as
/*-------------  --------------------*/
/*-------------  --------------------*/
/* test */
--1
if @tree is null or @node is null return -1
if @tree < 1 or @node < 1 return -1
--2
if exists(select 1 from objimage where tree = @tree and node = @node) return -1

if (select type_id from nodes where id=@node)<>2 return -1
/*-------------  --------------------*/
/*-------------  --------------------*/
/* exec */
insert into objimage(tree,node,obj)
values(@tree,@node,0x0)

DECLARE @ptrval varbinary(16)
SELECT 	@ptrval = TEXTPTR(obj) 
FROM 	objimage
where	tree = @tree
	and node = @node
WRITETEXT objimage.obj @ptrval @object

return 0
GO
/****** Object:  StoredProcedure [dbo].[obj_readlist]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
create	procedure	[dbo].[obj_readlist]
as
/*--------------------------------- type 2  ----------------------------------------*/
/*-------------  --------------------*/
/* test */
--1
--if @tree_id is null or @node_id is null return -1
--if @tree_id < 1 or @node_id < 1 return -1
--2
--if exists(select 1 from objimage where type = @type and int1 = @tree_id and int2 = @node_id) return -1
/*-------------  --------------------*/
/*-------------  --------------------*/
/* exec */
select	id = id,
	tree = tree,
	node = node,
	object = obj
from 	objimage

return 0
GO
/****** Object:  StoredProcedure [dbo].[runBatch]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE   PROCEDURE [dbo].[runBatch]
	@id int
AS
if exists (select 1 from batches where id=@id and doit<>0) begin
	select id=-1
	return -1
end
begin tran
	update batches set doit=1 where id=@id
	if @@error<>0 goto err
commit tran
select id=0
return 0
err:
rollback tran
select id=-1
return -1
GO
/****** Object:  StoredProcedure [dbo].[selectNodeLinks]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE   procedure [dbo].[selectNodeLinks]
	@tree_id int,
	@node_id int,
	@userName varchar(64)
	
/**
	return tree links from parent node @node_id
*/
as

declare @c int,@lvl int

create table #a(
	parent_id int,
	child_id int,
	idx int,
	lvl int
)

create table #b(
	parent_id int,
	child_id int,
	idx int,
	lvl int
)

create table #c(
	parent_id int,
	child_id int,
	idx int,
	lvl int
)

insert #b select parent_id,child_id,idx,lvl from treelinks where tree_id=@tree_id and parent_id=@node_id
select @c=count(*) from #b
while @c>0 begin
	truncate table #c
	insert #c select t1.parent_id,t1.child_id,t1.idx,t1.lvl 
		from treelinks t1, #b t2
		where t1.tree_id=@tree_id and t1.parent_id=t2.child_id

	select @c=count(*) from #b

	insert #a select * from #b

	truncate table #b
	insert #b select * from #c
end

-- need to adjust tree structure
-- select parentId=parent_id,childId=child_id,level=lvl,'index'=idx from #a order by parent_id,idx

IF OBJECT_ID(N'[tempdb].[dbo].[#tNodes]') IS NOT NULL
	DROP TABLE #tNodes

SELECT * INTO #tNodes FROM [dbo].[fn_getAllowedNodesByUserName](@userName)

IF OBJECT_ID(N'[tempdb].[dbo].[#tLink]') IS NOT NULL
	DROP TABLE #tLink
	
select parentId=parent_id, childId=child_id, level=lvl,'index'=idx 
INTO #tLink
from #a 
WHERE (parent_id<1001 OR parent_id IN (SELECT id FROM #tNodes)) -- -1 for root's parent, 1~1000 for product tree nodes
		AND (child_id<1001  OR child_id  IN (SELECT id FROM #tNodes)) -- 1~1000 for product tree nodes 
order by lvl,parent_id,idx -- EMS 2.88, firstly order by level


IF OBJECT_ID(N'[tempdb].[dbo].[#tAdjIdx]') IS NOT NULL
	DROP TABLE #tAdjIdx
	
CREATE TABLE #tAdjIdx (
	[parentId] int, [childId] int, [level] int, [index] int IDENTITY(0,1)
) 

DECLARE @pid int, @cid int, @lv int, @compareParent int  

-- EMS 2.88, fix index bug
SELECT TOP(1) @compareParent=parentId FROM #tLink 
	
DECLARE Cur CURSOR FOR
SELECT parentId, childId, level FROM #tLink
OPEN Cur
FETCH NEXT FROM Cur INTO @pid, @cid, @lv    
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
	IF @compareParent<>@pid 
		DBCC checkident (#tAdjIdx, reseed, -1) 
	
	INSERT INTO #tAdjIdx (parentId, childId, level) VALUES (@pid, @cid, @lv) 
	
	SET @compareParent=@pid 
	FETCH NEXT FROM Cur INTO @pid, @cid, @lv	
END
CLOSE Cur 
DEALLOCATE Cur  

SELECT * FROM #tAdjIdx ORDER BY [level],[parentId],[index]

drop table #b
drop table #c
drop table #a
DROP TABLE #tNodes
DROP TABLE #tLink
DROP TABLE #tAdjIdx 

return 0
err:
return -1
GO
/****** Object:  StoredProcedure [dbo].[selectNodes]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[selectNodes]
	@tree_id int,
	@node_id int,
	@userName varchar(64)
AS

declare @c int,@lvl int

create table #a(
	parent_id int,
	child_id int,
	idx int,
	lvl int
)

create table #b(
	parent_id int,
	child_id int,
	idx int,
	lvl int
)

create table #c(
	parent_id int,
	child_id int,
	idx int,
	lvl int
)

insert #b select parent_id,child_id,idx,lvl from treelinks where tree_id=@tree_id and parent_id=@node_id
select @c=count(*) from #b
while @c>0 begin
	truncate table #c
	insert #c select t1.parent_id,t1.child_id,t1.idx,t1.lvl 
		from treelinks t1, #b t2
		where t1.tree_id=@tree_id and t1.parent_id=t2.child_id

	select @c=count(*) from #b

	insert #a select * from #b

	truncate table #b
	insert #b select * from #c
end

create table #n(id int, text varchar(768) NULL,type_id tinyint NULL)

insert #n(id) values(@node_id)
insert #n(id) select distinct child_id from #a

update #n set text=n.name, type_id=n.type_id from #n, nodes n where #n.id=n.id

select dBId=id, name=text, typeId=type_id from #n 

-- check role access
WHERE id IN (SELECT id FROM [dbo].[fn_getAllowedNodesByUserName](@userName))
OR id<1001 -- fix bug that loss Product Tree nodes

drop table #n
drop table #b
drop table #c
drop table #a
return 0
err:
return -1
GO
/****** Object:  StoredProcedure [dbo].[setActiveBroker]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[setActiveBroker]
	@broker_id	int
AS
	UPDATE broker SET enable=0 WHERE enable<>0
	UPDATE broker SET enable=1 WHERE id=@broker_id
GO
/****** Object:  StoredProcedure [dbo].[userLogin]    Script Date: 2019/9/20 下午 03:11:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[userLogin] 
	@username varchar(50), 
	@password varchar(32) 
AS
	IF EXISTS (SELECT * FROM client WHERE name=@username AND password=@password) 
		SELECT result=1   -- passed check
	ELSE 
		SELECT result=0
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'reference node, node id is agent id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'alias', @level2type=N'COLUMN',@level2name=N'node_id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'0: local, 1:remote' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'alias', @level2type=N'COLUMN',@level2name=N'site_id'
GO
