【正文】 update 月工資統(tǒng)計(jì)表 set 考勤扣除=0 where 考勤扣除 is null update 月工資統(tǒng)計(jì)表 set 住房公積金=0 where 住房公積金 is null update 月工資統(tǒng)計(jì)表 set 醫(yī)療保險(xiǎn)=0 where 醫(yī)療保險(xiǎn) is null update 月工資統(tǒng)計(jì)表 set 養(yǎng)老保險(xiǎn)=0 where 養(yǎng)老保險(xiǎn) is null update 月工資統(tǒng)計(jì)表 set 應(yīng)扣金額合計(jì)=0 where 應(yīng)扣金額合計(jì) is null update 月工資統(tǒng)計(jì)表 set 失業(yè)保險(xiǎn)=0 where 失業(yè)保險(xiǎn) is null update 月工資統(tǒng)計(jì)表 set 個(gè)人所得稅=0 where 個(gè)人所得稅 is null update 月工資統(tǒng)計(jì)表 set 工資合計(jì)=0 where 工資合計(jì) is null update 月工資統(tǒng)計(jì)表 set 實(shí)發(fā)金額=0 where 實(shí)發(fā)金額 is null update 個(gè)人所得稅表 set 個(gè)人所得稅率=0 where 個(gè)人所得稅率 is nullmitGOcreate procedure sf_當(dāng)月工資統(tǒng)計(jì)asbegin transaction declare month int select month = 日期 from 月工資統(tǒng)計(jì)表 計(jì)算獎(jiǎng)金 update 月工資統(tǒng)計(jì)表 set 獎(jiǎng)金 = (select sum(獎(jiǎng)勵(lì)金額) from 職員獎(jiǎng)勵(lì)表 as i where =39。 and month*100 and (month+1)*100 group by having =) update 月工資統(tǒng)計(jì)表 set 獎(jiǎng)金=0 where 獎(jiǎng)金 is null 計(jì)算懲罰 update 月工資統(tǒng)計(jì)表 set 罰款 = (select sum(懲罰金額) from 職員懲罰表 as i where =39。 and month*100 and (month+1)*100 group by having =) update 月工資統(tǒng)計(jì)表 set 罰款=0 where 罰款 is null 將空數(shù)據(jù)置0 exec sf_空數(shù)據(jù)置0 計(jì)算應(yīng)發(fā)/應(yīng)扣/工資合計(jì) update 月工資統(tǒng)計(jì)表 set 應(yīng)扣金額合計(jì) = 房租+水電費(fèi)+請(qǐng)假扣除+罰款+考勤扣除+住房公積金+醫(yī)療保險(xiǎn) +養(yǎng)老保險(xiǎn)+失業(yè)保險(xiǎn) update 月工資統(tǒng)計(jì)表 set 應(yīng)發(fā)金額合計(jì)=基本工資+浮動(dòng)工資+合同補(bǔ)+糧副補(bǔ)+房補(bǔ)+臨時(shí)補(bǔ) +職務(wù)工資+工齡工資+ 考核工資+ 獎(jiǎng)金 update 月工資統(tǒng)計(jì)表 set 工資合計(jì) = 應(yīng)發(fā)金額合計(jì) 應(yīng)扣金額合計(jì) 計(jì)算個(gè)人所得稅 declare gzhj numeric(20,4), zybh varchar(20), grsds numeric(20,4),sl float, gzxx numeric(20,4),gzsx numeric(20,4) declare mycur cursor for select 職員編號(hào),工資合計(jì) from 月工資統(tǒng)計(jì)表 open mycur fetch next from mycur into zybh,gzhj WHILE (FETCH_STATUS = 0 ) BEGIN 扣除不計(jì)稅部分 select gzhj = gzhj (select top 1 不計(jì)稅工資 from 個(gè)人所得稅表 order by 級(jí)數(shù)) select grsds = 0 declare mycur1 cursor for select 工資下限,工資上限,case when 個(gè)人所得稅率1 then 個(gè)人所得稅率/100 else 個(gè)人所得稅率 end from 個(gè)人所得稅表 where 工資上限 = gzhj order by 工資上限 open mycur1 fetch next from mycur1 into gzxx, gzsx,sl WHILE (FETCH_STATUS = 0 ) BEGIN select grsds = grsds + (gzsxgzxx)*sl fetch next from mycur1 into gzxx, gzsx,sl END close mycur1 deallocate mycur1 select sl = case when 個(gè)人所得稅率1 then 個(gè)人所得稅率/100 else 個(gè)人所得稅率 end , gzxx = 工資下限 from 個(gè)人所得稅表 where 工資下限=gzsx select grsds = grsds + (gzhjgzxx)*sl update 月工資統(tǒng)計(jì)表 set 個(gè)人所得稅 = grsds where current of mycur fetch next from mycur into zybh,gzhj END close mycur deallocate mycur 計(jì)算實(shí)發(fā)工資 update 月工資統(tǒng)計(jì)表 set 實(shí)發(fā)金額 = 工資合計(jì)個(gè)人所得稅mitGOcreate procedure sf_形成月工資統(tǒng)計(jì)表 導(dǎo)入工資月份 int, 統(tǒng)計(jì)工資月份 intasbegin transaction delete from 月工資統(tǒng)計(jì)表 刪除已有數(shù)據(jù) if exists (select * from 工資發(fā)放歷史表 where 日期=導(dǎo)入工資月份) begin 從歷史倒入 insert into 月工資統(tǒng)計(jì)表 (日期, 職員編號(hào), 基本工資, 浮動(dòng)工資, 合同補(bǔ), 糧副補(bǔ), 房補(bǔ), 臨時(shí)補(bǔ), 職務(wù)工資, 工齡工資, 考核工資, 獎(jiǎng)金, 應(yīng)發(fā)金額合計(jì), 房租, 水電費(fèi), 請(qǐng)假扣除, 考勤扣除, 罰款, 住房公積金, 醫(yī)療保險(xiǎn), 養(yǎng)老保險(xiǎn), 失業(yè)保險(xiǎn), 生育保險(xiǎn), 工傷保險(xiǎn), 應(yīng)扣金額合計(jì), 工資合計(jì), 個(gè)人所得稅, 實(shí)發(fā)金額, 發(fā)放否, 月份) select 統(tǒng)計(jì)工資月份, 職員編號(hào), 基本工資, 浮動(dòng)工資, 合同補(bǔ), 糧副補(bǔ), 房補(bǔ), 臨時(shí)補(bǔ), 職務(wù)工資, 工齡工資, 考核工資, 獎(jiǎng)金, 應(yīng)發(fā)金額合計(jì), 房租, 水電費(fèi), 請(qǐng)假扣除, 考勤扣除, 罰款, 住房公積金, 醫(yī)療保險(xiǎn), 養(yǎng)老保險(xiǎn), 失業(yè)保險(xiǎn), 生育保險(xiǎn), 工傷保險(xiǎn), 應(yīng)扣金額合計(jì), 工資合計(jì), 個(gè)人所得稅, 實(shí)發(fā)金額, 發(fā)放否, 月份 from 工資發(fā)放歷史表 as h where =導(dǎo)入工資月份 end 新的員工,歷史沒有記錄,生成記錄 insert into 月工資統(tǒng)計(jì)表 (職員編號(hào),日期) select 職員編號(hào),統(tǒng)計(jì)工資月份 from 職員基本信息表 as h where not in (select 職員編號(hào) from 月工資統(tǒng)計(jì)表) 將空數(shù)據(jù)置0 exec sf_空數(shù)據(jù)置0 自動(dòng)計(jì)算工資 exec sf_當(dāng)月工資統(tǒng)計(jì)mitGO 發(fā)放指定職員的工資create procedure sf_當(dāng)月工資發(fā)放 職員編號(hào) char(10)asbegin transaction update 月工資統(tǒng)計(jì)表 set 發(fā)放否=39。 from 月工資統(tǒng)計(jì)表 where 職員編號(hào)=職員編號(hào) insert into 工資發(fā)放歷史表 select * from 月工資統(tǒng)計(jì)表 where 職員編號(hào)=職員編號(hào) delete 月工資統(tǒng)計(jì)表 where 職員編號(hào)=職員編號(hào)mitGO4代碼與界面設(shè)計(jì)本系統(tǒng)采用多文檔窗體程序，每一功能對(duì)應(yīng)一個(gè)子窗體。選擇主菜單中的各菜單項(xiàng)即可進(jìn)入相應(yīng)的功能。圖4 實(shí)例運(yùn)行結(jié)果圖5 當(dāng)月工資管理功能窗體啟動(dòng)Microsoft Visual ,在主菜單中選擇【文件】|【新建】|【項(xiàng)目】命令，彈出【新建項(xiàng)目】對(duì)話框沒，在【項(xiàng)目類型】列表框中選擇【Visual Basic 項(xiàng)目】，然后在【模板】列表框中選擇【W(wǎng)indow 應(yīng)用程序】。（1） 利用系統(tǒng)默認(rèn)生成的窗體作為主窗體，并將其對(duì)應(yīng)的文件改名為“”,為其添加主菜單控件以及StatusBar控件，布局如圖6所示。表2 主窗體屬性設(shè)置對(duì)象（控件）名屬性取值（說(shuō)明）frmMainNamefrmMainfrmMainText人力資源管理系統(tǒng)frmMainIsMdiContainerTrueStatusBarText人力資源管理系統(tǒng)（3）窗體的主菜單屬性設(shè)置如表5所示。Private Function GetInstanceState(ByVal name As String) As Boolean 39。循環(huán)判斷是否有名為name的子窗體實(shí)例 For i = 0 To 1 If (i).Name = name Then 39。不存在名為Name的子窗體False Return False End Function（5）添加采單項(xiàng)MenuItemDepManage的Click事件響應(yīng)函數(shù)。判斷機(jī)構(gòu)設(shè)置及編碼窗體是是否已經(jīng)創(chuàng)建 If GetInstanceState(frmDepManage) Then 39。創(chuàng)建并顯示商品資料維護(hù)窗體 Dim childFrm As frmDepManage = New frmDepManage = Me () End Sub因?yàn)楦鱾€(gè)窗體均需要對(duì)數(shù)據(jù)庫(kù)進(jìn)行訪問(wèn)，可以把對(duì)數(shù)據(jù)庫(kù)的一些操作通過(guò)一個(gè)類來(lái)實(shí)現(xiàn)，這樣可以使對(duì)數(shù)據(jù)庫(kù)的訪問(wèn)更加簡(jiǎn)單。Imports Imports Imports Public Class DataBase 39。數(shù)據(jù)庫(kù)連接對(duì)象 Private SqlConn As SqlConnection Public Shared sConn As String = Persist Security Info=False。database=hrmbook。Connect Timeout=30 Public Sub Dispose() Implements Dispose(True) (True) End Sub Protected Sub Dispose(ByVal disposing As Boolean) If disposing True Then Return End If If SqlConn Is Nothing = False Then () SqlConn = Nothing End If End Sub Public Sub Open() If SqlConn Is Nothing = True Then 39。打開數(shù)據(jù)庫(kù)連接 () End If End Sub Public Sub Close() 39。機(jī)構(gòu)設(shè)置的編碼機(jī)構(gòu)設(shè)置及編碼功能主要用于實(shí)現(xiàn)設(shè)置機(jī)構(gòu)的層級(jí)關(guān)系。圖7機(jī)構(gòu)設(shè)置及編碼窗體（2）部分控件的屬性設(shè)置如表4所示。Private dvList As DataView 39。39。以下是TreeNodeData六個(gè)共有變量 Public AbsIndex As String Public ItemIndex As String Public ItemLevel As String Public ParentIndex As String Public KindCode As String Public DepCode As String 39。39。選出數(shù)據(jù)源中ParentIndex為sParentIndex數(shù)據(jù)行 Dim dataRows() As DataRow = _ (ParentIndex = 39。) 39。獲得節(jié)點(diǎn)所需數(shù)據(jù) tmpNode = New TreeNode = dr(類別) 39。添加節(jié)點(diǎn) (tmpNode) 39。Private Sub frmDepManage_Load(ByVal sender As Object, _ ByVal e As ) Handles Dim db As DataBase = New DataBase Dim strSQL As String strSQL