main_gzgl_xyfgz.frm

人事管理系统

VERSION 5.00
Object = "{86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}#2.0#0"; "MSCOMCT2.OCX"
Object = "{C932BA88-4374-101B-A56C-00AA003668DC}#1.1#0"; "MSMASK32.OCX"
Begin VB.Form main_gzgl_xyfgz 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "选择工资月份"
   ClientHeight    =   2565
   ClientLeft      =   45
   ClientTop       =   330
   ClientWidth     =   4080
   Icon            =   "main_gzgl_xyfgz.frx":0000
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   2565
   ScaleWidth      =   4080
   Begin VB.CommandButton Command2 
      Caption         =   "取消"
      Height          =   390
      Left            =   2055
      TabIndex        =   5
      Top             =   1950
      Width           =   1335
   End
   Begin VB.Frame Frame1 
      Height          =   1740
      Left            =   30
      TabIndex        =   1
      Top             =   -30
      Width           =   4020
      Begin MSComCtl2.DTPicker DTPDate 
         Height          =   315
         Left            =   1515
         TabIndex        =   6
         Top             =   1005
         Width           =   2070
         _ExtentX        =   3651
         _ExtentY        =   556
         _Version        =   393216
         Format          =   59768833
         CurrentDate     =   39459
      End
      Begin MSMask.MaskEdBox newmonth 
         Height          =   315
         Left            =   1515
         TabIndex        =   4
         Top             =   465
         Width           =   2070
         _ExtentX        =   3651
         _ExtentY        =   556
         _Version        =   393216
         MaxLength       =   7
         Mask            =   "####-##"
         PromptChar      =   "_"
      End
      Begin VB.Label Label3 
         Caption         =   "工资月份："
         ForeColor       =   &H000000FF&
         Height          =   195
         Left            =   555
         TabIndex        =   3
         Top             =   525
         Width           =   1095
      End
      Begin VB.Label Label1 
         Caption         =   "当前日期："
         Height          =   195
         Left            =   555
         TabIndex        =   2
         Top             =   1065
         Width           =   1275
      End
   End
   Begin VB.CommandButton Command1 
      Caption         =   "确定"
      Height          =   390
      Left            =   630
      TabIndex        =   0
      Top             =   1950
      Width           =   1335
   End
End
Attribute VB_Name = "main_gzgl_xyfgz"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim rs As New ADODB.Recordset, rs1 As New ADODB.Recordset
Private Sub Form_Load()
  DTPDate = Date
  newmonth.Text = Year(Date) & "-" & Format(Month(Date), "00")
End Sub
Private Sub Command1_Click()
  Dim i As Integer, r As Integer, c As Integer
  myMonth = newmonth.Text
  Select Case intSelMonth
    Case Is = 1
       If newmonth.Text = "____-__" Then
         Load main_gzgl_gzgl
         main_gzgl_gzgl.Show
         With main_gzgl_gzgl
           .Adodc1.RecordSource = "select * from 工资表 order by 所属工资月份"
           .Adodc1.Refresh
         End With
       Else
         rs1.Open "select * from 工资表 where 工资表.所属工资月份='" + newmonth.Text + "'", cnn, adOpenKeyset, adLockOptimistic
         If rs1.RecordCount > 0 Then
           Load main_gzgl_gzgl
           main_gzgl_gzgl.Show
           With main_gzgl_gzgl
             .Adodc1.RecordSource = "select * from 工资表 where 所属工资月份='" + newmonth.Text + "'"
             .Adodc1.Refresh
           End With
         Else
           cnn.Execute ("insert into 工资表(员工编号,员工姓名,基本工资) select 编号,姓名,基本工资 from 员工信息表")
           cnn.Execute ("update 工资表 set 所属工资月份='" + newmonth.Text + "' from 员工福利表 where 所属工资月份 is null")
           cnn.Execute ("update 工资表 set 工资表.养老保险=员工福利表.养老保险,工资表.失业保险=员工福利表.失业保险,工资表.医疗保险=员工福利表.医疗保险 from 员工福利表 where 所属工资月份='" + myMonth + "'and 工资表.员工编号=员工福利表.员工编号")
           cnn.Execute ("update 工资表 set 应发工资=基本工资+工龄工资+加班费+全勤奖+奖励总额+职务津贴 where 工资表.所属工资月份='" + newmonth.Text + "'")
           cnn.Execute ("update 工资表 set 应扣工资=请假扣除+惩罚总额+养老保险+失业保险+医疗保险 where 工资表.所属工资月份='" + newmonth.Text + "'")
           cnn.Execute ("update 工资表 set 实发工资=应发工资-应扣工资 where 工资表.所属工资月份='" + newmonth.Text + "'")
           Load main_gzgl_gzgl
           main_gzgl_gzgl.Show
           main_gzgl_gzgl.Adodc1.Refresh
         End If
         rs1.Close
       End If
    Case Is = 2
      Dim newxls As Excel.Application
      Dim newbook As Excel.Workbook
      Dim newsheet As Excel.Worksheet
      Set newxls = CreateObject("Excel.Application") '创建excel应用程序,打开excel2000
      Set newbook = newxls.Workbooks.Add   '创建工作簿
      Set newsheet = newbook.Worksheets(1) '创建工作表
      rs.Open "select * from 工资表 where 所属工资月份='" + newmonth.Text + "'", cnn, adOpenKeyset, adLockOptimistic
      If rs.RecordCount > 0 Then
        newxls.Visible = True
        For i = 0 To rs.Fields.Count - 1
             newsheet.Cells(1, i + 1) = rs.Fields(i).Name
        Next i
        '指定表格内容
        rs.MoveFirst
        Do Until rs.EOF
            r = r + 1
            For c = 0 To rs.Fields.Count - 1
                newsheet.Cells(r + 1, c + 1) = rs.Fields(c).Value
            Next c
            rs.MoveNext
        Loop
      End If
      rs.Close
    Case Is = 3
      rs.Open "select * from 工资表 where 所属工资月份='" + newmonth.Text + "'", cnn, adOpenKeyset, adLockOptimistic
      If rs.RecordCount > 0 Then
         r = rs.RecordCount
         Open App.Path & "\MyFile.gbpt" For Output As #1
            Print #1, "#总计信息"
            Print #1, "#注意：本文件中的金额均以分为单位！"
            Print #1, "#币种|日期|总计标志|总金额|总笔数|"
            Print #1, "RMB|20070417|1|1495000|8|"
            Print #1, "#明细指令信息"
            Print #1, "#其中付款账号类型：灵通卡、理财金0；信用卡1"
            Print #1, "#币种|日期|顺序号|付款帐号|付款账号类型|收款帐号|收款帐号名称|金额|用途|备注信息|是否允许收款人查看付款人信息|"
            rs.MoveFirst
            Do Until rs.EOF
                i = i + 1
                Print #1, "RMB|" & Year(Date) & Format(Month(Date), "00") & Format(Day(Date), "00") & "|" & i & "|9558XXXXXXXXXXXXXXX|灵通卡|" & _
                "XXXXXXXXXXXXXXXXXXX" & "|" & rs.Fields("员工姓名") & "|" & rs.Fields("实发工资") & "00|mr|mr|0|"
                rs.MoveNext
            Loop
            Print #1, "*"
         Close
      End If
      rs.Close
  End Select
  Unload Me
End Sub
Private Sub Command2_Click()
  Unload Me
End Sub