--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

