--1.初始化子系統表
INSERT INTO dbo.ST_SubSystem(System_No,System_Name,System_ExeName,Index_No)
SELECT * FROM (SELECT 'KQ'[System_No],'考勤子系統'[System_Name],'Attendance.exe'[System_ExeName],1[Index_No]
                UNION ALL SELECT 'MJ','門禁子系統','AccessControl.exe',4
                UNION ALL SELECT 'SF','售飯子系統','EatSale.exe',2
                UNION ALL SELECT 'ST','一卡通平台','STONECARD.exe',0
                UNION ALL SELECT 'SK','水控子系統','WaterController.exe',5
                UNION ALL SELECT 'LH','靈活消費子系統','Consume.exe',3
              ) 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 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],'ST'[System_No],'ST_02'[Menu_No],'基礎檔案'[Menu_Name],NULL[Menu_Hint],-1[Para_ID],NULL[Call_Mode],
                      ''[Form_Name],NULL[ShortKey],1[Menu_Index],-1[Image_Index],0[Separator],0[InToolBar],NULL[Remark]
                UNION ALL SELECT 3,'ST','ST_0202','部門檔案','部門檔案',1,1,'TfrmDept',NULL,1,12,0,0,NULL
                UNION ALL SELECT 2,'ST','ST_0201','人員檔案','人員檔案',1,1,'TfrmPerson','',2,11,0,0,NULL
                UNION ALL SELECT 4,'ST','ST_0203','編碼管理','編碼管理',1,1,'TfrmDictionary','',3,13,0,0,NULL
                UNION ALL SELECT 7,'ST','ST_03','卡管理',NULL,-1,NULL,'',NULL,2,-1,0,0,NULL
                UNION ALL SELECT 8,'ST','ST_0301','卡信息設置','卡信息設置',7,2,'TfrmSetCardInfo','CTRL+Q',1,14,0,0,NULL
                UNION ALL SELECT 9,'ST','ST_0302','卡類設置','卡類設置',7,1,'TfrmSetCardType',NULL,2,14,1,0,NULL
                UNION ALL SELECT 11,'ST','ST_0304','發卡開戶','發卡開戶',7,1,'TfrmSendCard',NULL,3,16,0,0,NULL
                UNION ALL SELECT 5,'ST','ST_0305','卡挂失與解挂','卡挂失與解挂',7,1,'TfrmLockCard',NULL,4,16,0,0,NULL
                UNION ALL SELECT 6,'ST','ST_0306','換用新卡','換用新卡',7,1,'TfrmChangeCard',NULL,5,16,0,0,NULL                
                UNION ALL SELECT 18,'ST','ST_0307','補用新卡','補用新卡',7,1,'TfrmRenewCard',NULL,6,16,0,0,NULL
                UNION ALL SELECT 19,'ST','ST_0308','銷卡銷戶','銷卡銷戶',7,2,'TfrmDeleteCard',NULL,7,16,1,0,NULL
                UNION ALL SELECT 20,'ST','ST_0309','充值存款','充值存款',7,2,'TfrmSupplyFund','CTRL+S',8,17,0,0,NULL
                UNION ALL SELECT 21,'ST','ST_0310','用戶取款','用戶取款',7,2,'TfrmTakeFund','CTRL+T',9,17,0,0,NULL
                UNION ALL SELECT 22,'ST','ST_0311','小金額轉帳','小金額轉帳',7,2,'TfrmTransferAccount',NULL,10,17,0,0,NULL
                UNION ALL SELECT 23,'ST','ST_0312','發放補貼','發放補貼',7,1,'TfrmSubsidy',NULL,11,17,0,0,NULL
                UNION ALL SELECT 42,'ST','ST_0313','個人帳戶','個人帳戶',7,2,'TfrmQueryAccount',NULL,12,17,1,0,NULL
                UNION ALL SELECT 10,'ST','ST_0303','導入授權ID卡','導入授權ID卡',7,2,'TfrmImpEnCard',NULL,13,15,0,0,NULL
                UNION ALL SELECT 33,'ST','ST_0314','導出驗証名單','導出驗証名單到U盤',7,2,'TfrmExpToUDisk',NULL,14,15,0,0,NULL
                UNION ALL SELECT 12,'ST','ST_01','系統管理',NULL,-1,NULL,'',NULL,0,NULL,NULL,NULL,NULL
                UNION ALL SELECT 13,'ST','ST_0101','角色管理','角色管理',12,3,'RightManager.dll||fnRoleManager',NULL,2,7,0,0,NULL
                UNION ALL SELECT 14,'ST','ST_0102','用戶管理','用戶管理',12,3,'RightManager.dll||fnUserManager',NULL,3,8,0,0,NULL
              ) A
 WHERE A.Menu_ID NOT IN (SELECT Menu_ID FROM dbo.ST_MenuInfo)
GO

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 15[Menu_ID],'ST'[System_No],'ST_0103'[Menu_No],'出納授權'[Menu_Name],'出納授權'[Menu_Hint],12[Para_ID],
                      2[Call_Mode],'TfrmPayRight'[Form_Name],NULL[ShortKey],4[Menu_Index],9[Image_Index],1[Separator],0[InToolBar],NULL[Remark]
                UNION ALL SELECT 16,'ST','ST_0104','退出系統','退出系統',12,5,'Exit',NULL,5,0,0,0,NULL
                UNION ALL SELECT 17,'ST','ST_0105','系統參數','系統參數',12,2,'TfrmSetSysParams',NULL,1,10,1,0,NULL
                UNION ALL SELECT 24,'ST','ST_04','財務報表','',-1,'','',NULL,4,-1,0,0,NULL
                UNION ALL SELECT 25,'ST','ST_0401','會計出納報表','會計出納報表',24,2,'TfrmAccountRep',NULL,1,18,0,0,NULL
                UNION ALL SELECT 26,'ST','ST_0402','區營業額匯總報表','區營業額匯總報表',24,1,'TfrmSectionRep',NULL,2,18,0,0,NULL
                UNION ALL SELECT 27,'ST','ST_0403','機台營業額匯總報表','機台營業額匯總報表',24,1,'TfrmMachineRep',NULL,3,18,1,0,NULL
                UNION ALL SELECT 28,'ST','ST_0404','個人綜合報表','個人綜合報表表',24,1,'TfrmPersonalRep',NULL,4,19,0,0,NULL
--                 UNION ALL SELECT 134,'ST','ST_0415','個人平衡報表','個人平衡報表',24,1,'TfrmPersonalBalanceRpt',NULL,5,24,1,0,NULL
                UNION ALL SELECT 29,'ST','ST_0405','系統平衡表','系統平衡表',24,2,'TfrmSysBalanceRep',NULL,6,20,0,0,NULL
              ) A
 WHERE A.Menu_ID NOT IN (SELECT Menu_ID FROM dbo.ST_MenuInfo)
GO

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 30[Menu_ID],'ST'[System_No],'ST_0406'[Menu_No],'卡狀態統計表'[Menu_Name],'卡狀態統計表'[Menu_Hint],24[Para_ID],
                      2[Call_Mode],'TfrmCardStatusRep'[Form_Name],NULL[ShortKey],7[Menu_Index],21[Image_Index],1[Separator],0[InToolBar],NULL[Remark]
                UNION ALL SELECT 31,'ST','ST_05','日志管理',NULL,-1,NULL,NULL,NULL,5,-1,0,0,NULL
                UNION ALL SELECT 32,'ST','ST_0501','系統日志管理','系統日志管理',31,1,'TfrmSysLog',NULL,1,26,0,0,NULL
                UNION ALL SELECT 34,'ST','ST_06','數據維護',NULL,-1,NULL,NULL,NULL,6,-1,0,0,NULL
                UNION ALL SELECT 133,'ST','ST_0605','財務結算','財務結算',34,2,'TfrmFinancialSettle',NULL,1,31,0,0,NULL
                UNION ALL SELECT 35,'ST','ST_0601','數據備份','數據備份',34,3,'DBManager.dll||fnBackupDB',NULL,2,27,0,0,NULL
                UNION ALL SELECT 36,'ST','ST_0602','數據清理','數據清理',34,2,'TfrmClearHistory',NULL,3,28,1,0,NULL
                UNION ALL SELECT 37,'ST','ST_0603','命令維護','命令維護',34,4,'SQLTools.exe',NULL,4,29,0,0,NULL
                UNION ALL SELECT 38,'ST','ST_0604','黑名單','黑名單',34,1,'TfrmBlackPerson',NULL,5,30,0,0,NULL
                UNION ALL SELECT 39,'ST','ST_0407','個人取款明細報表','個人取款明細報表',24,1,'TfrmTakeRpt',NULL,8,22,0,0,NULL
                UNION ALL SELECT 40,'ST','ST_0408','個人充值明細報表','個人充值明細報表',24,1,'TfrmSupplyRpt',NULL,9,22,0,0,NULL
                UNION ALL SELECT 41,'ST','ST_0409','個人轉帳明細報表','個人轉帳明細報表',24,1,'TfrmTransferRpt',NULL,10,22,0,0,NULL
                UNION ALL SELECT 43,'ST','ST_0411','個人換用新卡明細報表','個人換用新卡明細報表',24,1,'TfrmChangeCardRpt',NULL,11,22,0,0,NULL
	              UNION ALL SELECT 44,'ST','ST_0410','個人補用新卡明細報表','個人補用新卡明細報表',24,1,'TfrmRenewCardRpt',NULL,12,22,0,0,NULL
	              UNION ALL SELECT 45,'ST','ST_0412','個人發放補貼明細報表','個人發放補貼明細報表',24,1,'TfrmSubsidyRpt',NULL,13,22,1,0,NULL
                      UNION ALL SELECT 48,'ST','ST_0417','補貼領取明細','補貼領取明細',  31,1,'TfrmTakeSubsidy',NULL,13,22,0,0,NULL
	              UNION ALL SELECT 46,'ST','ST_0413','賬戶余額表','賬戶余額表',24,1,'TfrmAccountBalanceRep',NULL,14,23,0,0,NULL
	              UNION ALL SELECT 47,'ST','ST_0414','人員花名冊','人員花名冊',24,1,'TfrmPersonNameRpt',NULL,15,24,0,0,NULL
	              UNION ALL SELECT 191,'ST','ST_0416','注銷人員明細表','注銷人員明細表',24,1,'TfrmDelDetail',NULL,16,25,0,0,NULL
                      UNION ALL SELECT 49,'ST','ST_0418','未領取補貼明細','未領取補貼明細',24, 1,'TfrmUnTakeSubsidy',NULL,14,25,0,0,NULL
              ) A
 WHERE A.Menu_ID NOT IN (SELECT Menu_ID FROM dbo.ST_MenuInfo)
GO

--4.增加系統權限值列表

/*4.1 每個模塊默認為可使用權限,功能已經由menuinfo上的觸發器完成了*/

--4.2.1.出納授權模塊的權限設置
IF NOT EXISTS(SELECT Right_ID FROM dbo.ST_RightValue WHERE Right_ID='1' AND Form_Name='ST_0103')
   INSERT INTO dbo.ST_RightValue(Right_ID,Form_Name,Right_Name)
   VALUES('1','ST_0103','修改')
ELSE
   UPDATE dbo.ST_RightValue SET Right_Name='修改'
    WHERE Right_ID='1' AND Form_Name='ST_0103'
GO

--4.2.1.出納授權模塊的權限設置
UPDATE dbo.ST_RightInfo SET Right_Values='01'
 WHERE Form_Name='ST_0103'
GO

--4.2.2.黑名單模塊的權限設置
IF NOT EXISTS(SELECT Right_ID FROM dbo.ST_RightValue WHERE Right_ID='1' AND Form_Name='ST_0604')
   INSERT INTO dbo.ST_RightValue(Right_ID,Form_Name,Right_Name)
   VALUES('1','ST_0604','增加')
ELSE
   UPDATE dbo.ST_RightValue SET Right_Name='增加'
    WHERE Right_ID='1' AND Form_Name='ST_0604'
GO
IF NOT EXISTS(SELECT Right_ID FROM dbo.ST_RightValue WHERE Right_ID='2' AND Form_Name='ST_0604')
   INSERT INTO dbo.ST_RightValue(Right_ID,Form_Name,Right_Name)
   VALUES('2','ST_0604','刪除')
ELSE
   UPDATE dbo.ST_RightValue SET Right_Name='刪除'
    WHERE Right_ID='2' AND Form_Name='ST_0604'
GO

--4.2.2.黑名單模塊的權限設置
UPDATE dbo.ST_RightInfo SET Right_Values='012'
 WHERE Form_Name='ST_0604'
GO

--4.2.3.卡類設置模塊的權限設置
IF NOT EXISTS(SELECT Right_ID FROM dbo.ST_RightValue WHERE Right_ID='1' AND Form_Name='ST_0302')
   INSERT INTO dbo.ST_RightValue(Right_ID,Form_Name,Right_Name)
   VALUES('1','ST_0302','增加')
ELSE
   UPDATE dbo.ST_RightValue SET Right_Name='增加'
    WHERE Right_ID='1' AND Form_Name='ST_0302'
GO
IF NOT EXISTS(SELECT Right_ID FROM dbo.ST_RightValue WHERE Right_ID='2' AND Form_Name='ST_0302')
   INSERT INTO dbo.ST_RightValue(Right_ID,Form_Name,Right_Name)
   VALUES('2','ST_0302','刪除')
ELSE
   UPDATE dbo.ST_RightValue SET Right_Name='刪除'
    WHERE Right_ID='2' AND Form_Name='ST_0302'
GO
IF NOT EXISTS(SELECT Right_ID FROM dbo.ST_RightValue WHERE Right_ID='3' AND Form_Name='ST_0302')
   INSERT INTO dbo.ST_RightValue(Right_ID,Form_Name,Right_Name)
   VALUES('3','ST_0302','修改')
ELSE
   UPDATE dbo.ST_RightValue SET Right_Name='修改'
    WHERE Right_ID='3' AND Form_Name='ST_0302'
GO

--4.2.3.卡類設置模塊的權限設置
UPDATE dbo.ST_RightInfo SET Right_Values='0123'
 WHERE Form_Name='ST_0302'
GO

--4.2.4.部門檔案模塊的權限配置
IF NOT EXISTS(SELECT Right_ID FROM dbo.ST_RightValue WHERE Right_ID='1' AND Form_Name='ST_0202')
   INSERT INTO dbo.ST_RightValue(Right_ID,Form_Name,Right_Name)
   VALUES('1','ST_0202','增加')
ELSE
   UPDATE dbo.ST_RightValue SET Right_Name='增加'
    WHERE Right_ID='1' AND Form_Name='ST_0202'
GO
IF NOT EXISTS(SELECT Right_ID FROM dbo.ST_RightValue WHERE Right_ID='2' AND Form_Name='ST_0202')
   INSERT INTO dbo.ST_RightValue(Right_ID,Form_Name,Right_Name)
   VALUES('2','ST_0202','修改')
ELSE
   UPDATE dbo.ST_RightValue SET Right_Name='修改'
    WHERE Right_ID='2' AND Form_Name='ST_0202'
GO
IF NOT EXISTS(SELECT Right_ID FROM dbo.ST_RightValue WHERE Right_ID='3' AND Form_Name='ST_0202')
   INSERT INTO dbo.ST_RightValue(Right_ID,Form_Name,Right_Name)
   VALUES('3','ST_0202','注銷 ')
ELSE
   UPDATE dbo.ST_RightValue SET Right_Name='注銷 '
    WHERE Right_ID='3' AND Form_Name='ST_0202'
GO
IF NOT EXISTS(SELECT Right_ID FROM dbo.ST_RightValue WHERE Right_ID='4' AND Form_Name='ST_0202')
   INSERT INTO dbo.ST_RightValue(Right_ID,Form_Name,Right_Name)
   VALUES('4','ST_0202','調整順序')
ELSE
   UPDATE dbo.ST_RightValue SET Right_Name='調整順序'
    WHERE Right_ID='4' AND Form_Name='ST_0202'
GO

--4.2.4部門檔案模塊的權限配置
UPDATE dbo.ST_RightInfo SET Right_Values='01234'
 WHERE Form_Name='ST_0202'
GO

--4.2.5.卡挂失與解挂模塊的權限設置
IF NOT EXISTS(SELECT Right_ID FROM dbo.ST_RightValue WHERE Right_ID='1' AND Form_Name='ST_0305')
   INSERT INTO dbo.ST_RightValue(Right_ID,Form_Name,Right_Name)
   VALUES('1','ST_0305','挂失 ')
ELSE
   UPDATE dbo.ST_RightValue SET Right_Name='挂失 '
    WHERE Right_ID='1' AND Form_Name='ST_0305'
GO
IF NOT EXISTS(SELECT Right_ID FROM dbo.ST_RightValue WHERE Right_ID='2' AND Form_Name='ST_0305')
   INSERT INTO dbo.ST_RightValue(Right_ID,Form_Name,Right_Name)
   VALUES('2','ST_0305','解挂')
ELSE
   UPDATE dbo.ST_RightValue SET Right_Name='解挂'
    WHERE Right_ID='2' AND Form_Name='ST_0305'
GO

--4.2.5卡挂失與解挂模塊的權限配置
UPDATE dbo.ST_RightInfo SET Right_Values='012'
 WHERE Form_Name='ST_0305'
GO

--4.2.6.人員檔案模塊的權限設置
IF NOT EXISTS(SELECT Right_ID FROM dbo.ST_RightValue WHERE Right_ID='1' AND Form_Name='ST_0201')
   INSERT INTO dbo.ST_RightValue(Right_ID,Form_Name,Right_Name)
   VALUES('1','ST_0201','增加')
ELSE
   UPDATE dbo.ST_RightValue SET Right_Name='增加'
    WHERE Right_ID='1' AND Form_Name='ST_0201'
GO
IF NOT EXISTS(SELECT Right_ID FROM dbo.ST_RightValue WHERE Right_ID='2' AND Form_Name='ST_0201')
   INSERT INTO dbo.ST_RightValue(Right_ID,Form_Name,Right_Name)
   VALUES('2','ST_0201','注銷')
ELSE
   UPDATE dbo.ST_RightValue SET Right_Name='注銷'
    WHERE Right_ID='2' AND Form_Name='ST_0201'
GO
IF NOT EXISTS(SELECT Right_ID FROM dbo.ST_RightValue WHERE Right_ID='3' AND Form_Name='ST_0201')
   INSERT INTO dbo.ST_RightValue(Right_ID,Form_Name,Right_Name)
   VALUES('3','ST_0201','修改')
ELSE
   UPDATE dbo.ST_RightValue SET Right_Name='修改'
    WHERE Right_ID='3' AND Form_Name='ST_0201'
GO
IF NOT EXISTS(SELECT Right_ID FROM dbo.ST_RightValue WHERE Right_ID='4' AND Form_Name='ST_0201')
   INSERT INTO dbo.ST_RightValue(Right_ID,Form_Name,Right_Name)
   VALUES('4','ST_0201','調動部門')
ELSE
   UPDATE dbo.ST_RightValue SET Right_Name='調動部門'
    WHERE Right_ID='4' AND Form_Name='ST_0201'
GO

--4.2.6.人員檔案模塊的權限設置
UPDATE dbo.ST_RightInfo SET Right_Values='01234'
 WHERE Form_Name='ST_0201'
GO


--5.默認管理員組角色權限為所有
/*模塊插入功能已經由menuinfo上的觸發器完成了*/
/*如果有增加權限項目必須執行以下*/
UPDATE dbo.ST_RoleRight SET Right_Values=dbo.ST_RightInfo.Right_Values
  FROM dbo.ST_RightInfo
 WHERE dbo.ST_RoleRight.Menu_ID=dbo.ST_RightInfo.Menu_ID AND dbo.ST_RoleRight.Role_No='Administrators'
GO


--7.初始列配置信息


--8.初始化卡信息
IF NOT EXISTS(SELECT Card_Type FROM dbo.ST_CardInfo)
   INSERT INTO dbo.ST_CardInfo(Card_Type,Card_Foregift,Card_Charge,Passwd_Value,SmallAccount_Max,Subsidy_CutOffDate,Is_DelSubsidy)
    VALUES('IC卡',30,30,'1EA447AF04332A54E91E06DDE6EC451B',500,NULL,0)
GO

--10.初始化民族編碼
INSERT INTO dbo.ST_Dictionary(Table_Name,Table_ChsName,Dict_Id,Dict_Value,Is_SysCol)
SELECT * FROM (SELECT 'BM_NATIONALITY'[Table_Name],'民族'[Table_ChsName],'1'[Dict_Id],'漢族'[Dict_Value],0[Is_SysCol]
                UNION ALL SELECT 'BM_NATIONALITY','民族','2','蒙古族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','3','回族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','4','藏族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','5','維吾爾族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','6','苗族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','7','彝族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','8','壯族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','9','布依族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','10','朝鮮族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','11','滿族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','12','侗族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','13','瑤族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','14','白族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','15','土家族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','16','哈尼族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','17','哈薩克族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','18','傣族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','19','黎族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','20','  僳族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','21','佤族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','22','畬族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','23','高山族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','24','拉祜族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','25','水族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','26','東鄉族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','27','納西族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','28','景頗族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','29','柯爾克孜族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','30','土族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','31','達斡爾族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','32','  佬族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','33','羌族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','34','布朗族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','35','撒拉族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','36','毛南族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','37','仡佬族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','38','錫伯族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','39','阿昌族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','40','普米族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','41','怒族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','42','塔吉克族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','43','烏孜別克族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','44','俄羅斯族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','45','鄂溫克族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','46','德昂族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','47','保安族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','48','裕固族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','49','京族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','50','塔塔爾族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','51','獨龍族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','52','鄂倫春族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','53','赫哲族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','54','門巴族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','55','珞巴族',1
                UNION ALL SELECT 'BM_NATIONALITY','民族','56','基諾族',1
              ) A
 WHERE A.Dict_ID NOT IN (SELECT Dict_ID FROM dbo.ST_Dictionary WHERE Table_Name='BM_NATIONALITY')
GO

--11.初始化職務編碼
INSERT INTO dbo.ST_Dictionary(Table_Name,Table_ChsName,Dict_Id,Dict_Value,Is_SysCol)
SELECT * FROM (SELECT 'BM_DUTY'[Table_Name],'職務'[Table_ChsName],'1'[Dict_Id],'員工'[Dict_Value],1[Is_SysCol]
                UNION ALL SELECT 'BM_DUTY','職務','2','班長',1
                UNION ALL SELECT 'BM_DUTY','職務','3','課長',1
                UNION ALL SELECT 'BM_DUTY','職務','4','經理',1
                UNION ALL SELECT 'BM_DUTY','職務','5','總經理',0
                UNION ALL SELECT 'BM_DUTY','職務','6','董事長',0
              ) A
 WHERE A.Dict_ID NOT IN (SELECT Dict_ID FROM dbo.ST_Dictionary WHERE Table_Name='BM_DUTY')
GO

--12.初始化計量單位編碼
INSERT INTO dbo.ST_Dictionary(Table_Name,Table_ChsName,Dict_Id,Dict_Value,Is_SysCol)
SELECT * FROM (SELECT 'BM_UNIT'[Table_Name],'計量單位'[Table_ChsName],'1'[Dict_Id],'秒'[Dict_Value],1[Is_SysCol]
                UNION ALL SELECT 'BM_UNIT','計量單位','2','分',1
                UNION ALL SELECT 'BM_UNIT','計量單位','3','小時',1
                UNION ALL SELECT 'BM_UNIT','計量單位','4','天',1
                UNION ALL SELECT 'BM_UNIT','計量單位','5','星期',1
                UNION ALL SELECT 'BM_UNIT','計量單位','6','月',1
              ) A
 WHERE A.Dict_ID NOT IN (SELECT Dict_ID FROM dbo.ST_Dictionary WHERE Table_Name='BM_UNIT')
GO

--13.初始化商品單位編碼
INSERT INTO dbo.ST_Dictionary(Table_Name,Table_ChsName,Dict_Id,Dict_Value,Is_SysCol)
SELECT * FROM (SELECT 'BM_WAREUNIT'[Table_Name],'商品單位'[Table_ChsName],'1'[Dict_Id],'個'[Dict_Value],1[Is_SysCol]
                UNION ALL SELECT 'BM_WAREUNIT','商品單位','2','個',1
                UNION ALL SELECT 'BM_WAREUNIT','商品單位','3','包',1
                UNION ALL SELECT 'BM_WAREUNIT','商品單位','4','盒',1
                UNION ALL SELECT 'BM_WAREUNIT','商品單位','5','條',1
                UNION ALL SELECT 'BM_WAREUNIT','商品單位','6','箱',1
                UNION ALL SELECT 'BM_WAREUNIT','商品單位','7','根',1
                UNION ALL SELECT 'BM_WAREUNIT','商品單位','8','瓶',1
                UNION ALL SELECT 'BM_WAREUNIT','商品單位','9','只',1
                UNION ALL SELECT 'BM_WAREUNIT','商品單位','10','支',1
              ) A
 WHERE A.Dict_ID NOT IN (SELECT Dict_ID FROM dbo.ST_Dictionary WHERE Table_Name='BM_WAREUNIT')
GO
--14.考勤標識
INSERT INTO dbo.ST_Dictionary(Table_Name,Table_ChsName,Dict_Id,Dict_Value,Is_SysCol)
SELECT * FROM (SELECT 'BM_KAOQIN'[Table_Name],'考勤標識'[Table_ChsName],'1'[Dict_Id],'正常={}'[Dict_Value],1[Is_SysCol]
                UNION ALL SELECT 'BM_KAOQIN','考勤標識','2','曠工={曠}',1
                UNION ALL SELECT 'BM_KAOQIN','考勤標識','3','遲到={<}',1
                UNION ALL SELECT 'BM_KAOQIN','考勤標識','4','早退={>}',1
                UNION ALL SELECT 'BM_KAOQIN','考勤標識','5','加班={+}',1
                UNION ALL SELECT 'BM_KAOQIN','考勤標識','6','請假={假}',1
              ) A
 WHERE A.Dict_ID NOT IN (SELECT Dict_ID FROM dbo.ST_Dictionary WHERE Table_Name='BM_KAOQIN')

GO
--門禁防潛回分組名稱
INSERT INTO dbo.ST_Dictionary(Table_Name,Table_ChsName,Dict_Id,Dict_Value,Is_SysCol)
SELECT * FROM (
                SELECT 'BM_MJDOORHideBackNo' [Table_Name],'防潛回分組名稱' [Table_Chsname], '1'[Dict_Id],'A組'[Dict_Value], 1 [Is_SysCol]
                UNION ALL SELECT 'BM_MJDOORHideBackNo','防潛回分組名稱','2','B組',1
                UNION ALL SELECT 'BM_MJDOORHideBackNo','防潛回分組名稱','3','C組',1
                UNION ALL SELECT 'BM_MJDOORHideBackNo','防潛回分組名稱','4','D組',0
                UNION ALL SELECT 'BM_MJDOORHideBackNo','防潛回分組名稱','5','E組',0
                UNION ALL SELECT 'BM_MJDOORHideBackNo','防潛回分組名稱','6','F組',0
              ) A
 WHERE A.Dict_ID NOT IN (SELECT Dict_ID FROM dbo.ST_Dictionary WHERE Table_Name='BM_MJDOORHideBackNo')
GO
--門禁防尾隨分組名稱
INSERT INTO dbo.ST_Dictionary(Table_Name,Table_ChsName,Dict_Id,Dict_Value,Is_SysCol)
SELECT * FROM (
                SELECT 'BM_MJDOORAPBNo' [Table_Name],'防尾隨分組名稱' [Table_Chsname], '1'[Dict_Id],'A組'[Dict_Value], 1 [Is_SysCol]
                UNION ALL SELECT 'BM_MJDOORAPBNo','防尾隨分組名稱','2','B組',1
                UNION ALL SELECT 'BM_MJDOORAPBNo','防尾隨分組名稱','3','C組',1
                UNION ALL SELECT 'BM_MJDOORAPBNo','防尾隨分組名稱','4','D組',0
                UNION ALL SELECT 'BM_MJDOORAPBNo','防尾隨分組名稱','5','E組',0
                UNION ALL SELECT 'BM_MJDOORAPBNo','防尾隨分組名稱','6','F組',0
              ) A
 WHERE A.Dict_ID NOT IN (SELECT Dict_ID FROM dbo.ST_Dictionary WHERE Table_Name='BM_MJDOORAPBNo')
GO

--14.初始化消費模式編碼(已刪除初始化)

--15.初始化系統參數表
INSERT INTO dbo.ST_SysParam(Param_Code,Param_Name,Param_Value)
SELECT * FROM (SELECT 'SettleDate'[Param_Code],'財務結算日期'[Param_Name],'2006-01-15'[Param_Value]
                UNION ALL SELECT 'ClearDate','數據清理日期','2006-01-15'
                UNION ALL SELECT 'PERSON_VALIDTYPE','人員驗証方式','1'
                UNION ALL SELECT 'COM','COM端口','COM1'
                UNION ALL SELECT 'MODE_SF','0:計次;1:計份;2:鍵值;3:金額;4:菜譜;6:自定義單價','0;1;2;3;4;6'
                UNION ALL SELECT 'MODE_LH','2:鍵值;3:金額;4:品種;5:時長;6:用量','2;3;4;5;6'
                UNION ALL SELECT 'IsPersonNoAsCardNo','用編號作卡號(IC卡)' ,'0'
                UNION ALL SELECT 'SubsidyInterval','SubsidyInterval','60'
                UNION ALL SELECT 'IsAutoReadID','IsAutoReadID','1'                
              ) A
WHERE A.Param_Code NOT IN (SELECT Param_Code FROM dbo.ST_SysParam)
GO

--16.初始化財務結算或數據清理結轉或清理表
INSERT INTO dbo.ST_ClearTable(Table_Name,Date_Field,Clear_Index,Delete_Index,Other_Field)
SELECT * FROM (SELECT 'ST_AlterCard'[Table_Name],'Alter_Date'[Date_Field],1[Clear_Index],1[Delete_Index],'Person_ID,Alter_Date,Card_No,NewCard_No,Alter_Fee,Alter_Type,User_No,Remark'[Other_Field]
								UNION ALL SELECT 'ST_DeleteCard','Del_Date',2,2,'Person_ID,New_Date,Del_Date,Card_No,Back_Fund,Back_SmallFund,Back_Foregift,Card_Charge,User_No,New_User_No,Remark,Fact_Fund'
								UNION ALL SELECT 'ST_Subsidy','Subsidy_Date',4,4,'Person_ID,Subsidy_Date,Subsidy_Fund,Plan_Fund,User_No,Remark'
								UNION ALL SELECT 'ST_SupplyFund','Supply_Date',3,3,'Person_ID,Supply_Date,Supply_Fund,Card_Balance,Data_Type,Moc_No,User_No,Remark'
								UNION ALL SELECT 'ST_TakeFund','Take_Date',5,5,'Person_ID,Take_Date,Take_Fund,Card_Balance,Moc_No,User_No,Remark'
								UNION ALL SELECT 'ST_TransferAccount','Transfer_Date',6,6,'Person_ID,Transfer_Date,Transfer_Fund,Card_Balance,Moc_No,User_No,Remark'
								UNION ALL SELECT 'XF_Consume','Consume_Time',7,7,'Person_ID,System_No,Consume_Mode,Consume_Time,Consume_Fund,Consume_Copy,Consume_Timelen,Consume_Capacity,Repast_No,Ware_ID,Moc_No,Com_No,Card_Balance,Card_SmallAccount,Data_Type,Remark'
							) A
WHERE A.Table_Name NOT IN(SELECT Table_Name FROM dbo.ST_ClearTable)
GO	

--初始化業務應用數據
IF NOT EXISTS(SELECT Table_Name FROM ST_Identity WHERE Table_Name='ST_Department')  
   INSERT INTO ST_Identity(Table_Name,Current_ID)
   VALUES('ST_Department',2)
GO

IF NOT EXISTS(SELECT Dept_No FROM ST_Department WHERE Dept_No='1001')
   INSERT INTO ST_Department(Dept_ID,Dept_No,Dept_Name,Dept_Chief,Dept_Total,Dept_Duty,Is_Del,Remark,Add_Col1,Add_Col2)
   VALUES(1,'1001','公司',NULL,NULL,NULL,0,NULL,NULL,NULL)
GO

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

IF NOT EXISTS(SELECT Person_ID FROM ST_Person WHERE Person_ID=1)
   INSERT INTO ST_Person(Person_ID,Dept_ID,Person_No,Person_Name)
    VALUES(1,1,'1001','陳先生')
GO

IF NOT EXISTS(SELECT Person_ID FROM ST_Person WHERE Person_ID=2)
   INSERT INTO ST_Person(Person_ID,Dept_ID,Person_No,Person_Name)
    VALUES(2,1,'1002','王先生')
GO

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

IF NOT EXISTS(SELECT Type_No FROM ST_CardType WHERE Type_No=1)
   INSERT INTO ST_CardType(Type_No,Type_Name,Min_Balance,Subsidy,Validity,Use_MOC,SF_Max_Fund,SF_Max_DayFund,SF_LimitTimes,
      Max_IcFunds,SF_Max_Count,SF_Discount,LH_Max_Fund,LH_Max_DayFund,LH_Discount,Use_Repast,Small_Discount,Is_Del,Is_OnlySubsidy)
   VALUES(1,'消費卡',0,0,3650,'ALL',99999,99999,0,655,99999,100,99999,99999,100,'001|0',100,0,0)
GO

IF NOT EXISTS(SELECT Person_ID FROM ST_SubSysRight WHERE Person_ID=1)
   INSERT INTO ST_SubSysRight(Person_ID,System_No)
   SELECT 1,'KQ'
    UNION ALL SELECT 1,'SF'
    UNION ALL SELECT 1,'LH'  
GO

IF NOT EXISTS(SELECT Person_ID FROM ST_SubSysRight WHERE Person_ID=2)
   INSERT INTO ST_SubSysRight(Person_ID,System_No)
   SELECT 2,'KQ'
    UNION ALL SELECT 2,'SF'
    UNION ALL SELECT 2,'LH'  
GO

/**未領取補貼不能消費則售飯機顯示18**/
IF not Exists(Select * from ST_SysParam where Param_Code='ConsumeHasSubsidy')
  INSERT INTO ST_SysParam(Param_Code,Param_name,Param_Value)
   Values('ConsumeHasSubsidy','ConsumeHasSubsidy','0')
GO

--17.更新子系統創建日期
UPDATE dbo.ST_SubSystem SET DBCreateDate=CONVERT(VARCHAR(10),GETDATE(),121),DBVersion = '3.2.0'
WHERE  System_No = 'ST'
GO

