--1.初始化子系统表
INSERT INTO dbo.ST_SubSystem(System_No,System_Name,System_ExeName,Index_No)
  SELECT * FROM (SELECT 'XG'[System_No],'巡检子系统'[System_Name],'Patrol.exe'[System_ExeName],6[Index_No]) A
  WHERE A.System_No NOT IN(SELECT System_No FROM dbo.ST_SubSystem)
GO

--2.增加系统用户和组
IF NOT EXISTS(SELECT Role_No FROM dbo.ST_RoleInfo WHERE Role_No='Administrators')
   INSERT INTO dbo.ST_roleInfo(Role_No,Role_Name,Remark)
   VALUES('Administrators','系统管理员组',NULL)   
GO

IF NOT EXISTS(SELECT Role_No FROM dbo.ST_RoleInfo WHERE Role_No='Operations')
   INSERT INTO dbo.ST_roleInfo(Role_No,Role_Name,Remark)
   VALUES('Operations','操作管理员组',NULL)   
GO

IF NOT EXISTS(SELECT Role_No FROM dbo.ST_RoleInfo WHERE Role_No='Stations')
   INSERT INTO dbo.ST_roleInfo(Role_No,Role_Name,Remark)
   VALUES('Stations','站点管理员组',NULL)   
GO

IF NOT EXISTS(SELECT User_No FROM dbo.ST_UserInfo WHERE User_No='Admin')
   INSERT INTO dbo.ST_UserInfo(User_No,User_Name,User_Pwd,User_Type,Card_No,User_Moc,Role_No,Is_Del,Remark)
   VALUES('Admin','管理员','','1','','','Administrators',0,NULL)
GO

--3.巡检子系统初始菜单表
INSERT INTO dbo.ST_MenuInfo(Menu_ID,System_No,Menu_No,Menu_Name,Menu_Hint,Para_ID,Call_Mode,Form_Name,ShortKey,
                        Menu_Index,Image_Index,Separator,InToolBar,Remark)
SELECT * FROM (SELECT 1[Menu_ID],'XG'[System_No],'XG_01'[Menu_No],'系统管理'[Menu_Name],NULL[Menu_Hint],-1[Para_ID],NULL[Call_Mode],
                      NULL[Form_Name],NULL[ShortKey],'1'[Menu_Index],NULL[Image_Index],NULL[Separator],0[InToolBar],NULL[Remark]
                UNION ALL SELECT 2,'XG','XG_0101','角色管理','角色管理',1,'3','RightManager.dll||fnRoleManager',NULL,'1',27,'0',0,NULL
		            UNION ALL SELECT 3,'XG','XG_0102','用户管理','用户管理',1,'3','RightManager.dll||fnUserManager',NULL,'2',28,'1',0,NULL
		            UNION ALL SELECT 4,'XG','XG_0103','系统参数','系统参数',1,'2','TfrmSysParam',NULL,'3',30,'1',0,NULL
                UNION ALL SELECT 5,'XG','XG_0104','退出系统','退出系统',1,'5','Exit',NULL,'5',4,'0',0,NULL
                UNION ALL SELECT 41,'XG','XG_0105','巡检站管理','巡检站管理',1,1,'TfrmPatrolStation',NULL,4,2,1,0,NULL
                UNION ALL SELECT 6,'XG','XG_02','巡检设定',NULL,-1,NULL,NULL,NULL,'3',NULL,NULL,0,NULL
                UNION ALL SELECT 7,'XG','XG_0201','巡检人员','巡检人员',6,'2','TfrmPatrolMan',NULL,'1',29,'1',0,NULL
                UNION ALL SELECT 8,'XG','XG_0202','巡检事件','巡检事件',6,'2','TfrmPatrolEvent',NULL,'2',4,'0',0,NULL
                UNION ALL SELECT 9,'XG','XG_0203','巡检地点','巡检地点',6,'2','TfrmPatrolPlace',NULL,'3',4,'0',0,NULL
                UNION ALL SELECT 10,'XG','XG_0204','巡检路线','巡检路线',6,'1','TfrmPatrolRoute',NULL,'4',4,'1',0,NULL
                UNION ALL SELECT 11,'XG','XG_0205','巡检排班','巡检排班',6,'1','TfrmPatrolWork',NULL,'5',4,'0',0,NULL
                UNION ALL SELECT 12,'XG','XG_03','巡检通讯',NULL,-1,NULL,NULL,NULL,'3',NULL,NULL,0,NULL
                UNION ALL SELECT 13,'XG','XG_0301','巡检器登记','巡检器登记',12,'2','TfrmPatrolMoc',NULL,'1',4,'1',0,NULL
                UNION ALL SELECT 14,'XG','XG_0302','巡检数据采集','巡检数据采集',12,'2','TfrmDownLoad',NULL,'2',7,'0',0,NULL
                UNION ALL SELECT 15,'XG','XG_0303','巡检硬件设置','巡检硬件设置',12,'2','TfrmMocSet',NULL,'3',7,'0',0,NULL
                UNION ALL SELECT 16,'XG','XG_04','异常处理',NULL,-1,NULL,NULL,NULL,'4',NULL,NULL,0,NULL
                UNION ALL SELECT 17,'XG','XG_0401','增补巡检记录','增补巡检记录',16,'1','TfrmSupplyPatrol',NULL,'1',15,'0',0,NULL
                UNION ALL SELECT 18,'XG','XG_0402','增补巡检事件','增补巡检事件',16,'1','TfrmSupplyEvent',NULL,'2',15,'1',0,NULL
                UNION ALL SELECT 19,'XG','XG_0403','调整巡检班次','调整巡检班次',16,'1','TfrmAdjustWork',NULL,'3',15,'0',0,NULL
                UNION ALL SELECT 20,'XG','XG_05','巡检报表',NULL,-1,NULL,NULL,NULL,'4',NULL,NULL,0,NULL
                UNION ALL SELECT 21,'XG','XG_0501','巡检人员日报','巡检人员日报',20,'1','TfrmPsnDaily',NULL,'1',31,'0',0,NULL
                UNION ALL SELECT 22,'XG','XG_0502','巡检人员汇总','巡检人员汇总',20,'1','TfrmPsnSum',NULL,'2',32,'1',0,NULL
                UNION ALL SELECT 23,'XG','XG_0503','巡检路线日报','巡检路线日报',20,'1','TfrmRouteDaily',NULL,'3',33,'0',0,NULL
                UNION ALL SELECT 24,'XG','XG_0504','巡检路线汇总','巡检路线汇总',20,'1','TfrmRouteSum',NULL,'3',34,'0',0,NULL
                UNION ALL SELECT 28,'XG','XG_0505','巡检点整月报表','巡检点整月报表',20,'1','TfrmMonthRouteRep',NULL,'3',34,0,0,NULL
                UNION ALL SELECT 30,'XG','XG_0508','站点事件统计报表','站点事件统计报表',20,'1','TfrmStationSiteEventRep',NULL,'3',34,'0',0,NULL       
	        UNION ALL SELECT 40,'XG','XG_0506','值勤情况查询','值勤情况查询',20,'1','TfrmAttendance',NULL,'4',31,'0',0,NULL
                UNION ALL SELECT 29,'XG','XG_0507','线路点日巡检报表','线路点日巡检报表',20,'1','TfrmRouteSiteDayRep',NULL,'3',34,'0',0,NULL                                
                UNION ALL SELECT 25,'XG','XG_06','数据管理',NULL,-1,NULL,NULL,NULL,'4',NULL,NULL,0,NULL
                UNION ALL SELECT 26,'XG','XG_0601','数据清理','数据清理',25,'2','TfrmDataClean',NULL,'1',35,'0',0,NULL
                UNION ALL SELECT 27,'XG','XG_0602','数据备份','数据备份',25,'3','DBManager.dll||fnBackupDB',NULL,'2',36,'1',0,NULL
                UNION ALL SELECT 42,'XG','XG_0603','巡检数据导出','巡检数据导出',25,2,'TfrmPatrolExport',NULL,3,12,'0',0,NULL
            	  UNION ALL SELECT 43,'XG','XG_0604','巡检数据导入','巡检数据导入',25,2,'TfrmPatrolImport',NULL,4,16,'0',0,NULL
							) A
 WHERE A.Menu_ID NOT IN (SELECT Menu_ID FROM dbo.ST_MenuInfo)
GO


--4.增加系统权限值列表
--4.1增加每个模块的默认权限值

--5.增加角色权限默认管理员组
--初始化站管理员组的权限
INSERT INTO ST_RoleRight(Role_No,Menu_ID,Right_Values)
SELECT 'Stations',Menu_ID,'0' FROM ST_MenuInfo
 WHERE Menu_ID<>43 AND Menu_ID<>2 AND Menu_ID<>3 AND Menu_ID<>41
   AND Menu_ID NOT IN (SELECT Menu_ID FROM ST_RoleRight WHERE Role_No='Stations')
GO

--初始化操作管理员组的权限
INSERT INTO ST_RoleRight(Role_No,Menu_ID,Right_Values)
SELECT 'Operations',Menu_ID,'0' FROM ST_MenuInfo
 WHERE Menu_ID NOT IN (SELECT Menu_ID FROM ST_RoleRight WHERE Role_No='Operations')
GO

--6.初始化系统参数
INSERT INTO dbo.ST_SysParam(Param_Code,Param_Name,Param_Value)
SELECT * FROM (SELECT 'CORP_NAME'[Param_Code],'公司名称'[Param_Name],'XX公司名称'[Param_Value]
                UNION ALL SELECT 'IN_RANGE','巡检点允许误差范围','5'
                UNION ALL SELECT 'OUT_RANGE','巡检点允许早到迟到范围','10'
                UNION ALL SELECT 'REPEAT_INTERVAL','重复巡检记录间隔','3'
                UNION ALL SELECT 'MONTH_STARTDATE','每个月排班起始日期','1'
                UNION ALL SELECT 'XG_COM','巡检机通讯串口','COM3'
                UNION ALL SELECT 'COM','读卡器通讯串口','COM1'
                UNION ALL SELECT 'EVENT_MODE','巡检事件模式','1'
                UNION ALL SELECT 'CLEAR_DATE','数据清理日期','2006-04-15'
                UNION ALL SELECT 'UsePatrolStation','是否启用巡检站管理功能','0'
              ) A
WHERE A.Param_Code NOT IN (SELECT Param_Code FROM dbo.ST_SysParam)
GO

--初始化巡检事件
INSERT INTO dbo.XG_Event(Event_No,Event_Name,Remark)
SELECT * FROM (SELECT '0001'[Event_No],'正常'[Event_Name],'正常情况'[Remark]
                UNION ALL SELECT '0002','异常','异常情况'
              ) A
WHERE A.Event_No NOT IN (SELECT Event_No FROM dbo.XG_Event)
GO

--初始化巡检站
INSERT INTO XG_PatrolStation(Station_ID,Station_No,Station_Code,Station_Name,Station_User,Addr,Tel,Remark,Is_Station)
SELECT * FROM
   (
   SELECT 1 [Station_ID],'1001' [Station_No],'1001' [Station_Code],'XX省' [Station_Name],NULL [Station_User],NULL [Addr],
          NULL [Tel],NULL [Remark],0[Is_Station]
    UNION ALL SELECT 2,'100101','100101','默认站点',NULL,NULL,NULL,NULL,1
   ) A
 WHERE A.Station_ID NOT IN (SELECT Station_ID FROM XG_PatrolStation)
GO

IF NOT EXISTS(SELECT Table_Name FROM ST_Identity WHERE Table_Name='XG_PatrolStation')  
   INSERT INTO ST_Identity(Table_Name,Current_ID)
   VALUES('XG_PatrolStation',3)
GO   

--7.更新子系统创建日期
UPDATE dbo.ST_SubSystem SET DBCreateDate=CONVERT(VARCHAR(10),GETDATE(),121),DBVersion = '3.0.0'
WHERE  System_No = 'XG'
GO

