--1.考勤子系統初始菜單表
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 50[Menu_ID],'KQ'[System_No],'KQ_0101'[Menu_No],'賬套管理'[Menu_Name],NULL[Menu_Hint],79[Para_ID],2[Call_Mode],
                      'TfrmAccountMana'[Form_Name],NULL[ShortKey],1[Menu_Index],33[Image_Index],1[Separator],0[InToolBar],NULL[Remark]
                                UNION ALL SELECT 53,'KQ','KQ_02','考勤設置','',-1,NULL,NULL,NULL,'2',-1,'0',0,''
								UNION ALL SELECT 54,'KQ','KQ_0201','考勤機設置','考勤機設置',53,'2','TfrmSetMoc',NULL,'1',34,'0',0,NULL
								UNION ALL SELECT 55,'KQ','KQ_0202','規則定義','',53,'9',NULL,NULL,'2',-1,'0',0,NULL
								UNION ALL SELECT 56,'KQ','KQ_020201','考勤規則','考勤規則',55,'2','TfrmAttendRule',NULL,'1',-1,'0',0,NULL
								UNION ALL SELECT 57,'KQ','KQ_020202','假種定義','假種定義',55,'2','TfrmLeaveType',NULL,'2',-1,'0',0,NULL
								UNION ALL SELECT 58,'KQ','KQ_020203','法定假定義','法定假定義',55,'2','TfrmLawLeaveType',NULL,'3',-1,'0',0,NULL
								UNION ALL SELECT 59,'KQ','KQ_0203','人員考勤信息','人員考勤信息',53,'1','TfrmAttendInfo',NULL,'4',35,'0',0,NULL
								UNION ALL SELECT 60,'KQ','KQ_03','考勤數據',NULL,-1,NULL,NULL,NULL,'3',-1,'0',0,NULL
								UNION ALL SELECT 61,'KQ','KQ_0301','考勤數據下載','考勤數據下載',60,'1','TfrmDownloadKQ',NULL,'1',36,'0',0,NULL
								UNION ALL SELECT 62,'KQ','KQ_0302','原始刷卡數據','原始刷卡數據',60,'1','TfrmRawData',NULL,'2',37,'0',0,NULL
								UNION ALL SELECT 63,'KQ','KQ_0303','外進外出數據','外進外出數據',60,'1','TfrmInOutData',NULL,'3',38,'0',0,NULL
								UNION ALL SELECT 64,'KQ','KQ_04','班次信息',NULL,-1,'1',NULL,NULL,'3',-1,'0',0,NULL
								UNION ALL SELECT 65,'KQ','KQ_0401','設置班次','設置班次',64,'1','TfrmClassInfo',NULL,'1',39,'0',0,NULL
								UNION ALL SELECT 66,'KQ','KQ_0402','安排班次','安排班次',64,'1','TfrmSetClass',NULL,'2',40,'0',0,NULL
								UNION ALL SELECT 67,'KQ','KQ_05','異常處理','異常處理',-1,NULL,NULL,NULL,'5',-1,'0',0,NULL
								UNION ALL SELECT 68,'KQ','KQ_0501','請假登記','請假登記',67,'1','TfrmLeave',NULL,'1',-1,'0',0,NULL
								UNION ALL SELECT 69,'KQ','KQ_0502','班次調整','班次調整',67,'1','TfrmAdjustClass',NULL,'2',-1,'0',0,NULL
								UNION ALL SELECT 70,'KQ','KQ_0503','調休補班','調休補班',67,'1','TfrmAdjustRest',NULL,'3',-1,'0',0,NULL
								UNION ALL SELECT 71,'KQ','KQ_0504','加班登記','加班登記',67,'1','TfrmOverTime',NULL,'4',-1,'0',0,NULL
								UNION ALL SELECT 72,'KQ','KQ_0505','增補刷卡','增補刷卡',67,'1','TfrmSupplyTime',NULL,'5',-1,'0',0,NULL
								UNION ALL SELECT 73,'KQ','KQ_0506','補出勤補加班','補出勤補加班',67,'1','TfrmFillAttend',NULL,'6',-1,'0',0,NULL
								UNION ALL SELECT 74,'KQ','KQ_06','統計報表','統計報表',-1,NULL,NULL,NULL,'6',-1,'0',0,NULL
								UNION ALL SELECT 75,'KQ','KQ_0601','考勤日報表','考勤日報表',74,'1','TfrmDayRep',NULL,'1',41,'0',0,NULL
								UNION ALL SELECT 76,'KQ','KQ_0602','考勤月報表','考勤月報表',74,'1','TfrmMonthRep',NULL,'2',-1,'0',0,NULL
								UNION ALL SELECT 77,'KQ','KQ_0603','考勤年報表','考勤年報表',74,'1','TfrmYearRep',NULL,'3',-1,'0',0,NULL
								UNION ALL SELECT 78,'KQ','KQ_0102','退出系統','退出系統',79,'5','Exit',NULL,'2',0,'0',0,NULL
								UNION ALL SELECT 79,'KQ','KQ_01','系統管理',NULL,-1,NULL,NULL,NULL,'1',NULL,NULL,0,NULL
								UNION ALL SELECT 700,'KQ','KQ_07','拍照考勤機',NULL,-1,'1',NULL,NULL,'3',-1,'0',0,NULL
								UNION ALL SELECT 701,'KQ','KQ_0701','照片對比','照片對比',700,'1','TfrmPhotoContrast',NULL,'1',39,'0',0,NULL
								UNION ALL SELECT 702,'KQ','KQ_0702','實時顯示','實時顯示',700,'2','TfrmSetRealInfoKQ',NULL,'2',40,'0',0,NULL
								UNION ALL SELECT 703,'KQ','KQ_0703','配置參數','配置參數',700,'2','TfrmSetHardInfoKQ',NULL,'2',40,'0',0,NULL
							) A
 WHERE A.Menu_ID NOT IN (SELECT Menu_ID FROM dbo.ST_MenuInfo)
GO

--2.初始化賬套結轉表
INSERT INTO dbo.KQ_TransferTables(Table_Name,Date_Field,Transfer_Index,Delete_Index)
SELECT * FROM (SELECT 'KQ_AdjustClass'[Table_Name],'Adjust_Date'[Date_Field],3[Transfer_Index],10[Delete_Index]
								UNION ALL SELECT 'KQ_AdjustRest','Adjust_Date',6,7
								UNION ALL SELECT 'KQ_BrushCard','Brush_Date',9,4
								UNION ALL SELECT 'KQ_Daily','WorkTime_Date',11,2
								UNION ALL SELECT 'KQ_DefineClassSub','WorkTime_Date',10,3
								UNION ALL SELECT 'KQ_Download','Brush_Date',7,6
								UNION ALL SELECT 'KQ_FillAttend','Fill_Date',1,12
								UNION ALL SELECT 'KQ_InOutData','Brush_Date',8,5
								UNION ALL SELECT 'KQ_Leave','Leave_Date',5,8
								UNION ALL SELECT 'KQ_Monthly','Account_Code',12,1
								UNION ALL SELECT 'KQ_OverTime','Over_Date',4,9
								UNION ALL SELECT 'KQ_SupplyTime','Supply_Date',2,11
                                                                UNION ALL SELECT 'KQ_ModifyMonthly','Account_Code',12,1
							) A
WHERE A.Table_Name NOT IN(SELECT Table_Name FROM dbo.KQ_TransferTables)
GO	

--3.初始化考勤規則定義參數
INSERT INTO dbo.KQ_SysParam(Param_Code,Param_Name,Param_Value)
SELECT * FROM (SELECT 'Late1S'[Param_Code],'開始分鐘'[Param_Name],'1'[Param_Value]
							 UNION ALL SELECT 'Late1E','結束分鐘','5'
							 UNION ALL SELECT 'Late2S','開始分鐘','5'
							 UNION ALL SELECT 'Late2E','結束分鐘','10'
							 UNION ALL SELECT 'Late3S','開始分鐘','10'
							 UNION ALL SELECT 'Late3E','結束分鐘','30'
							 UNION ALL SELECT 'IS_Deducte','遲到、早退是否扣全勤','1'
							 UNION ALL SELECT 'Early1S','開始分鐘','6'
							 UNION ALL SELECT 'Early1E','結束分鐘','10'
							 UNION ALL SELECT 'Early2S','開始分鐘','10'
							 UNION ALL SELECT 'Early2E','結束分鐘','11'
							 UNION ALL SELECT 'Early3S','開始分鐘','10'
							 UNION ALL SELECT 'Early3E','結束分鐘','30'
							 UNION ALL SELECT 'Brush_Time','重復刷卡分鐘數','0'
							 UNION ALL SELECT 'KQ_Card','考勤卡位數','10'
							 UNION ALL SELECT 'MinToHour','小時轉換基准','0'
							 UNION ALL SELECT 'OneHour','分鐘轉小時','55'
							 UNION ALL SELECT 'HalfHour','分鐘轉半小時','25'
							 UNION ALL SELECT 'HourToDay','小時轉1天','1'
							 UNION ALL SELECT 'OneDay','出勤比率算1天','80'
							 UNION ALL SELECT 'HalfDay','出勤比率算半天','20'
							 UNION ALL SELECT 'Absent_OneDay','曠工比率算1天','75'
							 UNION ALL SELECT 'Absent_HalfDay','曠工比率算半天','25'
							 UNION ALL SELECT 'Decimal','保留小數位','2'
							 UNION ALL SELECT 'Leave_Standard','請假基准','0'
							 UNION ALL SELECT 'Overtime_Standard','加班基准','1'
							 UNION ALL SELECT 'OverTimeBig','超過X分鐘算加班','29'
							 UNION ALL SELECT 'OverTimeDay','加班X小時算1天','8'
							 UNION ALL SELECT 'KQ_OutIn','考勤外進','61'
							 UNION ALL SELECT 'KQ_OutLeave','考勤外出','31'
							 UNION ALL SELECT 'LastMonthlyDate','最后一次月報處理日期',''
							 UNION ALL SELECT 'IsDeductLE','請假是否抵扣遲到、早退','0'		
							 UNION ALL SELECT 'Is_Auditing','是否啟用審核流程', '0'					 
							) A
WHERE A.Param_Code NOT IN(SELECT Param_Code FROM dbo.KQ_SysParam)
GO	

--4.初始化節假日表
INSERT INTO dbo.KQ_Holiday(Holiday_Date,Holiday_Name)
SELECT * FROM (SELECT '01-01'[Holiday_Date],'元旦'[Holiday_Name]
							 UNION ALL SELECT '05-01','勞動節'
							 UNION ALL SELECT '05-02','勞動節'
							 UNION ALL SELECT '05-03','勞動節'
							 UNION ALL SELECT '10-02','國慶節'
							 UNION ALL SELECT '10-03','國慶節'
							 UNION ALL SELECT '10-04','國慶節'
							) A
WHERE A.Holiday_Date NOT IN(SELECT Holiday_Date FROM dbo.KQ_Holiday)
GO							 

--5.初始化請假類別表
INSERT INTO dbo.KQ_LeaveKind(Kind_No,Kind_Name,Kind_Code,Is_Deduct)
SELECT * FROM (SELECT 'J101'[Kind_No],'事假'[Kind_Name],'S'[Kind_Code],1[Is_Deduct]
							 UNION ALL SELECT 'J102','病假','B',1
							 UNION ALL SELECT 'J103','婚假','H',1
							 UNION ALL SELECT 'J104','J104',NULL,1
							 UNION ALL SELECT 'J105','J105',NULL,1
							 UNION ALL SELECT 'J106','J106',NULL,1
							 UNION ALL SELECT 'J107','J107',NULL,1
							 UNION ALL SELECT 'J108','J108',NULL,1
							 UNION ALL SELECT 'J109','J109',NULL,1
							 UNION ALL SELECT 'J110','J110',NULL,1
							 UNION ALL SELECT 'J111','J111',NULL,1
							 UNION ALL SELECT 'J112','J112',NULL,1
							 UNION ALL SELECT 'J113','J113',NULL,1
							 UNION ALL SELECT 'J114','J114',NULL,1
							 UNION ALL SELECT 'J115','J115',NULL,1
							 UNION ALL SELECT 'J116','J116',NULL,1
							 UNION ALL SELECT 'J117','J117',NULL,1
							 UNION ALL SELECT 'J118','J118',NULL,1
							) A
WHERE A.Kind_No NOT IN(SELECT Kind_No FROM dbo.KQ_LeaveKind)
GO	

--6.增加系統權限值列表
--6.1 日報中
IF NOT EXISTS(SELECT Right_ID FROM ST_RightValue WHERE Right_ID='1' AND Form_Name='KQ_0601')
   INSERT INTO ST_RightValue(Right_ID,Form_Name,Right_Name)
   VALUES('1','KQ_0601','班次調整')
ELSE
   UPDATE ST_RightValue SET Right_Name='班次調整'
    WHERE Right_ID='1' AND Form_Name='KQ_0601'
GO
IF NOT EXISTS(SELECT Right_ID FROM ST_RightValue WHERE Right_ID='2' AND Form_Name='KQ_0601')
   INSERT INTO ST_RightValue(Right_ID,Form_Name,Right_Name)
   VALUES('2','KQ_0601','請假登記')
ELSE
   UPDATE ST_RightValue SET Right_Name='請假登記'
    WHERE Right_ID='2' AND Form_Name='KQ_0601'
GO
IF NOT EXISTS(SELECT Right_ID FROM ST_RightValue WHERE Right_ID='3' AND Form_Name='KQ_0601')
   INSERT INTO ST_RightValue(Right_ID,Form_Name,Right_Name)
   VALUES('3','KQ_0601','增補刷卡')
ELSE
   UPDATE ST_RightValue SET Right_Name='增補刷卡'
    WHERE Right_ID='3' AND Form_Name='KQ_0601'
GO
IF NOT EXISTS(SELECT Right_ID FROM ST_RightValue WHERE Right_ID='4' AND Form_Name='KQ_0601')
   INSERT INTO ST_RightValue(Right_ID,Form_Name,Right_Name)
   VALUES('4','KQ_0601','調休補班')
ELSE
   UPDATE ST_RightValue SET Right_Name='調休補班'
    WHERE Right_ID='4' AND Form_Name='KQ_0601'
GO

UPDATE ST_RightInfo SET Right_Values='01234'
 WHERE Form_Name='KQ_0601'
GO
--7.默認管理員組角色權限為所有
/*模塊插入功能已經由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


--8.初始列配置信息

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