calcpay.frm

感觉还可以了 大家看看 指点下 有什么不足的地方,大家多多指教啊

VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "MSFLXGRD.OCX"
Object = "{F9043C88-F6F2-101A-A3C9-08002B2F49FB}#1.2#0"; "COMDLG32.OCX"
Begin VB.Form CalcPay 
   Caption         =   "工资计算"
   ClientHeight    =   7515
   ClientLeft      =   60
   ClientTop       =   450
   ClientWidth     =   9195
   LinkTopic       =   "Form1"
   ScaleHeight     =   7515
   ScaleWidth      =   9195
   StartUpPosition =   3  '窗口缺省
   Begin VB.Frame Frame1 
      Caption         =   "工资计算结果"
      Height          =   7215
      Left            =   120
      TabIndex        =   0
      Top             =   120
      Width           =   8895
      Begin MSComDlg.CommonDialog CommDlg 
         Left            =   1920
         Top             =   3840
         _ExtentX        =   847
         _ExtentY        =   847
         _Version        =   393216
      End
      Begin VB.CommandButton Command2 
         Caption         =   "退出"
         Height          =   495
         Left            =   4680
         TabIndex        =   3
         Top             =   6360
         Width           =   4095
      End
      Begin VB.CommandButton Command1 
         Caption         =   "保存到文件"
         Default         =   -1  'True
         Height          =   495
         Left            =   120
         TabIndex        =   2
         Top             =   6360
         Width           =   4095
      End
      Begin MSFlexGridLib.MSFlexGrid MSFlexGrid1 
         Height          =   5895
         Left            =   120
         TabIndex        =   1
         Top             =   360
         Width           =   8655
         _ExtentX        =   15266
         _ExtentY        =   10398
         _Version        =   393216
         Cols            =   5
         FixedCols       =   0
         FillStyle       =   1
      End
   End
End
Attribute VB_Name = "CalcPay"
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