form4.frm

财务管理系统的基本功能的实现

VERSION 5.00
Object = "{F9043C88-F6F2-101A-A3C9-08002B2F49FB}#1.2#0"; "COMDLG32.OCX"
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "MSFLXGRD.OCX"
Begin VB.Form frmjsgz 
   Caption         =   "计算工资"
   ClientHeight    =   5835
   ClientLeft      =   60
   ClientTop       =   450
   ClientWidth     =   5250
   LinkTopic       =   "Form4"
   ScaleHeight     =   5835
   ScaleWidth      =   5250
   StartUpPosition =   3  '窗口缺省
   Begin VB.CommandButton Command1 
      Caption         =   "保存"
      Default         =   -1  'True
      Height          =   495
      Left            =   480
      TabIndex        =   1
      Top             =   5160
      Width           =   1575
   End
   Begin VB.CommandButton Command2 
      Caption         =   "退出"
      Height          =   495
      Left            =   3360
      TabIndex        =   0
      Top             =   5160
      Width           =   1455
   End
   Begin MSComDlg.CommonDialog CommDlg 
      Left            =   1800
      Top             =   3480
      _ExtentX        =   847
      _ExtentY        =   847
      _Version        =   393216
   End
   Begin MSFlexGridLib.MSFlexGrid MSFlexGrid1 
      Height          =   4815
      Left            =   0
      TabIndex        =   2
      Top             =   0
      Width           =   5295
      _ExtentX        =   9340
      _ExtentY        =   8493
      _Version        =   393216
      Cols            =   5
      FixedCols       =   0
      FillStyle       =   1
   End
End
Attribute VB_Name = "frmjsgz"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Private Sub Command1_Click()
    Dim i As Long
    Dim USER_ID As String
    Dim USER_NAME As String
    Dim USER_PART As String
    Dim USER_ROLE As String
    Dim MONEY As String
    CommDlg.Flags = cdlOFNOverwritePrompt   '设置保存对话框有覆盖提示
    CommDlg.Filter = "文本文件|*.txt"       '设置保存对话框的显示文件类型
    CommDlg.ShowSave                        '显示保存对话框
    If CommDlg.FileName <> "" Then      '如果用户点确定按钮就写导出文件
        Open CommDlg.FileName For Output As #1
        Print #1, "员工ID号"; Tab; "员工姓名"; Tab; "所属部门"; Tab; "职位名称"; Tab; "本月工资"
        For i = 1 To MSFlexGrid1.Rows - 1       '从MSFlexGrid1中循环把记录信息写进文件中
            MSFlexGrid1.Row = i
            MSFlexGrid1.Col = 0
            USER_ID = MSFlexGrid1.Text
            MSFlexGrid1.Row = i
            MSFlexGrid1.Col = 1
            USER_NAME = MSFlexGrid1.Text
            MSFlexGrid1.Row = i
            MSFlexGrid1.Col = 2
            USER_PART = MSFlexGrid1.Text
            MSFlexGrid1.Row = i
            MSFlexGrid1.Col = 3
            USER_ROLE = MSFlexGrid1.Text
            MSFlexGrid1.Row = i
            MSFlexGrid1.Col = 4
            MONEY = MSFlexGrid1.Text
            Print #1, USER_ID; Tab; USER_NAME; Tab; USER_PART; Tab; USER_ROLE; Tab; MONEY
        Next i
        Close #1        '关闭文件
    End If
End Sub

Private Sub Command2_Click()
    Unload Me       '返回主窗体
End Sub

Private Sub Form_Load()
    Dim sql As String
    Dim rs As New ADODB.Recordset
    Dim work As New ADODB.Recordset     '考勤记录结果集
    Dim i As Integer        '循环变量
    Dim userid As String
    Dim MONEY As Long
    Dim temp As Long        '考勤影响到的工资
    Dim resultcount As Long     '返回记录数
    
    Me.Left = (Screen.Width - Me.ScaleWidth) / 2        '窗体居中显示
    Me.Top = (Screen.Height - Me.ScaleHeight) / 2
    If DbHandle.DbConnection Then       '选定用户表中所有记录以及关联的角色和部门记录
        sql = "SELECT USER_ID,USER_NAME,PART_NAME,ROLE_NAME,ROLE_MONEY FROM TBL_USER,TBL_ROLE,TBL_PART WHERE USER_PART=PART_ID AND USER_ROLE=ROLE_ID"
        rs.CursorType = adOpenDynamic
        rs.LockType = adLockOptimistic
        rs.Open sql, DbFinance
        resultcount = DbHandle.resultcount(rs)
        Cls     '设置电子表格的列头
        MSFlexGrid1.Cols = 5
        MSFlexGrid1.Rows = resultcount + 1
        MSFlexGrid1.Row = 0
        MSFlexGrid1.Col = 0
        MSFlexGrid1.Text = "员工ID号"
        MSFlexGrid1.Col = 1
        MSFlexGrid1.Text = "员工姓名"
        MSFlexGrid1.Col = 2
        MSFlexGrid1.Text = "所属部门"
        MSFlexGrid1.Col = 3
        MSFlexGrid1.Text = "职位名称"
        MSFlexGrid1.Col = 4
        MSFlexGrid1.Text = "本月工资"
        
        For i = 0 To 4
            MSFlexGrid1.ColWidth(i) = MSFlexGrid1.Width / 5 - 5     '平均分配每个列的宽
        Next i
        For i = 1 To resultcount    '循环将职工信息和基本工资放入电子表格
            userid = rs("USER_ID")
            MSFlexGrid1.Row = i
            MSFlexGrid1.Col = 0
            MSFlexGrid1.Text = userid
            MSFlexGrid1.Col = 1
            MSFlexGrid1.Text = rs("USER_NAME")
            MSFlexGrid1.Col = 2
            MSFlexGrid1.Text = rs("PART_NAME")
            MSFlexGrid1.Col = 3
            MSFlexGrid1.Text = rs("ROLE_NAME")
            MONEY = rs("ROLE_MONEY")
            MSFlexGrid1.Col = 4
            MSFlexGrid1.Text = Str(MONEY)
            rs.MoveNext
        Next i
        rs.Close
        Set rs = Nothing
        For i = 1 To resultcount    '通过考勤表计算考勤信息影响的工资
            MSFlexGrid1.Row = i
            MSFlexGrid1.Col = 0
            userid = MSFlexGrid1.Text
            MSFlexGrid1.Col = 4
            MONEY = Val(MSFlexGrid1.Text)
            sql = "SELECT WORK_TIME,TYPE_MARK,TYPE_ID FROM TBL_WORK,TBL_TYPE WHERE WORK_TYPE=TYPE_ID AND WORK_ID='" & userid & "'"
            work.CursorType = adOpenDynamic
            work.LockType = adLockOptimistic
            work.Open sql, DbFinance        '选取每一行职工ID的本月考勤信息
            Do While work.EOF = False
                Select Case work("TYPE_ID")     '通过考勤类别计算考勤时间和工资关系
                    Case 1
                        temp = work("WORK_TIME") * 1
                    Case 2
                        temp = work("WORK_TIME") * 2
                    Case 3
                        temp = work("WORK_TIME") * 10
                    Case 4
                        temp = work("WORK_TIME") * 20
                End Select
                If work("TYPE_MARK") Then       '当考勤类别是加班出差等要向基本工资中加工资
                    MONEY = MONEY + temp
                Else
                    MONEY = MONEY - temp        '当考勤类别是迟到早退等要向基本工资中减工资
                End If
                work.MoveNext
            Loop
            MSFlexGrid1.Text = Str(MONEY)       '重新设置每个人的月工资
            work.Close
        Next i
        DbHandle.DbClose
    Else        '打开数据库失败错误退出
        MsgBox "数据库错误！", vbExclamation
        DbHandle.DbClose
        End
    End If

End Sub

Private Sub Form_Unload(Cancel As Integer)
    On Error Resume Next
    DbHandle.DbClose        '窗体关闭时关闭数据库
End Sub