tjymcj.frm

一个机械产品（产品、部件、零件）的工时、工资及进度软件

    cmbcj.ListIndex = 0
    
    Set rsTempA = oDb.Execute("select * from abz where cjmc='" & cmbcj.Text & "' and bzyn='Y'")
    Do Until rsTempA.EOF
        cmbbz.AddItem rsTempA!bzmc
        rsTempA.MoveNext
    Loop
    
    Mskdate1.Text = NOWDate - 10
    MonthView1.Visible = False
    MonthView1.Value = NOWDate
    mskdate2.Text = NOWDate
    MonthView2.Visible = False
    MonthView2.Value = NOWDate
End Sub
Private Sub cmdfind_Click()
    If cmbcj.Text = "" Then
        MsgBox "车间必须选择！", vbOKOnly, "车间选择"
        Exit Sub
    End If
    
    frmwait.Show 0
    DoEvents
   
    Grid1.Rows = 2
    If cmbcj.Text <> "" And cmbbz.Text <> "" Then '选车间，选班组
        szSql = "select * from abz where bzyn='Y' and cjmc = '" & cmbcj.Text & "'and bzmc = '" & cmbbz.Text & "'"
    End If
    
    If cmbcj.Text <> "" And cmbbz.Text = "" Then '选车间，不选班组
        szSql = "select * from abz where bzyn='Y' and cjmc = '" & cmbcj.Text & "' order by cjbh,bzbh"
    End If
    
    
    If Mskdate1.Text = "" Then gsdate1 = (NOWDate - 30) Else gsdate1 = (Mskdate1.Text)
    If mskdate2.Text = "" Then gsdate2 = NOWDate Else gsdate2 = mskdate2.Text
    
    i = 1
    Set rsTempA = oDb.Execute(szSql)
    Do Until rsTempA.EOF
        Gsdn = 0
        gszp = 0
        gslx = 0
        szSql = "select gpdnh.gpbh as RSgpbh,gpdnh.gpbzbh as RSgpbzbh,gpdnh.gpbzmc as RSgpbzmc,gpdnh.gpbzmc as RSgpbzmc,gpdnb.gpgs as RSgpgs from gpdnh,gpdnb " _
                & "where (gpdnh.gpbh=gpdnb.gpbh) and gpdnh.gpcjbh='" & rsTempA!cjbh & "' and gpdnh.gpbzbh='" & rsTempA!bzbh & "' and gpdnh.gprq>='" & gsdate1 & "' and gpdnh.gprq<='" & gsdate2 & "'"
            Set rsTempB = oDb.Execute(szSql)
            '定额工时汇总到个人
            Do Until rsTempB.EOF
                Gsdn = Gsdn + rsTempB!rsgpgs
                bzname = rsTempB!rsgpbzmc
                rsTempB.MoveNext
            Loop
            
             szSql = "select gpzph.gpbh as RSgpbh,gpzph.gpbzbh as RSgpbzbh,gpzph.gpbzmc as RSgpbzmc,gpzph.gpbzmc as RSgpbzmc,gpzpb.gpgs as RSgpgs from gpzph,gpzpb " _
                & "where (gpzph.gpbh=gpzpb.gpbh) and gpzph.gpcjbh='" & rsTempA!cjbh & "' and gpzph.gpbzbh='" & rsTempA!bzbh & "' and gpzph.gprq>='" & gsdate1 & "' and gpzph.gprq<='" & gsdate2 & "'"
            Set rsTempB = oDb.Execute(szSql)
            '增拨工时汇总到个人
            Do Until rsTempB.EOF
                gszp = gszp + rsTempB!rsgpgs
                bzname = rsTempB!rsgpbzmc
                rsTempB.MoveNext
            Loop
    
          szSql = "select gplxh.gpbh as RSgpbh,gplxh.gpbzbh as RSgpbzbh,gplxh.gpbzmc as RSgpbzmc,gplxh.gpbzmc as RSgpbzmc,gplxb.gpgs as RSgpgs from gplxh,gplxb " _
                & "where (gplxh.gpbh=gplxb.gpbh) and gplxh.gpcjbh='" & rsTempA!cjbh & "' and gplxh.gpbzbh='" & rsTempA!bzbh & "' and gplxh.gprq>='" & gsdate1 & "' and gplxh.gprq<='" & gsdate2 & "'"
            Set rsTempB = oDb.Execute(szSql)
            '零星工时汇总到个人
            Do Until rsTempB.EOF
                gslx = gslx + rsTempB!rsgpgs
                bzname = rsTempB!rsgpbzmc
                rsTempB.MoveNext
            Loop
            
            gstot = Round((Gsdn + gszp + gslx) / 60, 1)
            If gstot <> 0 Then
                griditem = i & Chr(9) & bzname & Chr(9) & Gsdn & Chr(9) & gszp & Chr(9) & gslx & Chr(9) & (Gsdn + gszp + gslx) & Chr(9) & gstot
                '工资计算
                If gstot <= 200 Then griditem = griditem & Chr(9) & gstot * 6 '4.8
                If gstot > 200 And (gstot <= 250) Then griditem = griditem & Chr(9) & (200 * 6 + (gstot - 200) * 6.5) '5.5
                If gstot > 250 And (gstot <= 300) Then griditem = griditem & Chr(9) & (200 * 6 + (gstot - 200) * 7)   '6
                If gstot > 300 And (gstot <= 350) Then griditem = griditem & Chr(9) & (200 * 6 + (gstot - 200) * 7.5) '6.5
                If gstot > 350 Then griditem = griditem & Chr(9) & (200 * 6 + (gstot - 200) * 8)    '7
                'If gstot > 350 And (gstot <= 400) Then griditem = griditem & Chr(9) & (200 * 6 + (gstot - 200) * 8)   '7
                'If gstot > 400 Then griditem = griditem & Chr(9) & (200 * 4.8 + (gstot - 200) * 7.5)
                Grid1.AddItem griditem
                i = i + 1
            End If
        rsTempA.MoveNext
    Loop
    
    Totgs = 0
    Totgz = 0
    totdn = 0
    totzp = 0
    totlx = 0
    For i = 2 To Grid1.Rows - 1
        totdn = totdn + Val(Grid1.Cell(i, 3).Text)
        totzp = totzp + Val(Grid1.Cell(i, 4).Text)
        totlx = totlx + Val(Grid1.Cell(i, 5).Text)
        Totgs = Totgs + Val(Grid1.Cell(i, 7).Text)
        Totgz = Totgz + Val(Grid1.Cell(i, 8).Text)
    Next i
    Grid1.AddItem "" & Chr(9) & "合计" & Chr(9) & totdn & Chr(9) & totzp & Chr(9) & totlx & Chr(9) & "" & Chr(9) & Totgs & Chr(9) & Totgz
    
    Unload frmwait
    '基本工时：200时以内         4.8元/时结算
    '工资按≤200小时-4.8元/小时、201～250小时-5.5元/小时、251～300小时-6元/小时、301～350小时-6.5元/小时、351～400小时-7元/小时、≥401小时-7.5元/小时结算
End Sub

Private Sub cmbcj_LostFocus()
    cmbbz.Clear
    Set rsTempA = oDb.Execute("select * from abz where cjmc='" & cmbcj.Text & "'")
    Do Until rsTempA.EOF
        cmbbz.AddItem rsTempA!bzmc
        rsTempA.MoveNext
    Loop
End Sub

Private Sub dogridfill()
    Grid1.Range(0, 1, 1, 1).Merge
    Grid1.Range(0, 2, 1, 2).Merge
    Grid1.Range(0, 3, 0, 5).Merge
    Grid1.Range(0, 6, 0, 7).Merge
    Grid1.Range(0, 8, 1, 8).Merge
    Grid1.Range(0, 9, 1, 9).Merge
    Grid1.Cell(0, 1).Text = "序号"
    Grid1.Cell(0, 2).Text = "班组/个人"
    Grid1.Cell(0, 3).Text = "工时"
    Grid1.Cell(0, 6).Text = "小计"
    Grid1.Cell(1, 3).Text = "定额"
    Grid1.Cell(1, 4).Text = "增拨"
    Grid1.Cell(1, 5).Text = "零星"
    Grid1.Cell(1, 6).Text = "分"
    Grid1.Cell(1, 7).Text = "时"
    Grid1.Cell(0, 8).Text = "工资"
    Grid1.Cell(0, 9).Text = "备注"
    Grid1.Column(8).FormatString = "#####0.00"
    Grid1.Column(7).Alignment = cellRightCenter
    Grid1.Column(8).Alignment = cellRightCenter
'griditem = "序号" & Chr(9) & "班组/个人" & Chr(9) & "定额工时" & Chr(9) & "增拨工时" & Chr(9) & "工时小计(分)" & Chr(9) & "工时数(时)" & Chr(9) & "工资" & Chr(9) & "备注"
'Grid1.AddItem griditem
End Sub
Private Sub cmddate1_Click()
    MonthView1.Visible = True
End Sub

  Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
    MonthView1.Visible = False
    Mskdate1.Text = MonthView1.Value
End Sub

Private Sub cmddate2_Click()
    MonthView2.Visible = True
End Sub
  Private Sub MonthView2_DateClick(ByVal DateClicked As Date)
    MonthView2.Visible = False
    mskdate2.Text = MonthView2.Value
End Sub
Private Sub cmdexcel_Click()
    'If Grid1.ExportToExcel("") Then
    '    MsgBox "输出成功。", vbExclamation, "提示"
    'End If
    Dim irowNo As Integer, sRange As String
    If excelsetup = False Then
        Set mobjexcel = CreateObject("Excel.application")  '启动excel  在 Form load ()过程
    End If
    Me.MousePointer = vbHourglass
    excelsetup = True
 
    With mobjexcel         '添加工作表
        .workbooks.Add
    End With

    With mobjexcel        '设置工作表字体，列宽
        .ActiveCell.Columns("A:A").ColumnWidth = 3
        .ActiveCell.Columns("B:B").ColumnWidth = 6
        .ActiveCell.Columns("C:C").ColumnWidth = 8
        .ActiveCell.Columns("D:D").ColumnWidth = 6
        .ActiveCell.Columns("E:E").ColumnWidth = 6
        .ActiveCell.Columns("F:F").ColumnWidth = 6
        .ActiveCell.Columns("G:G").ColumnWidth = 6
        .ActiveCell.Columns("H:H").ColumnWidth = 6
        .ActiveCell.Columns("I:I").ColumnWidth = 6
        .ActiveCell.Columns("J:J").ColumnWidth = 6
    End With
 
    mobjexcel.Visible = True    'Excel visible
    
    With mobjexcel
        .ActiveCell.Cells(1, 1).Value = "绍兴金氏机械设备有限公司 工时工资汇总表"
        .ActiveCell.Cells(3, 1).Value = "日期：" & Mskdate1.Text & "--" & mskdate2.Text & Space(4) & "车间名称：" & cmbcj.Text
    End With
    
    For irowNo = 0 To Grid1.Rows - 1
            For j = 1 To Grid1.Cols - 1
                With mobjexcel
                    .ActiveCell.Cells(irowNo + 4, j).Value = Grid1.Cell(irowNo, j).Text
                End With
            Next j
    Next irowNo

    With mobjexcel        '设置工作表字体，列宽
        'sRange = Chr(Asc("A")) & "2" & ":" & Chr(Asc("AL")) & irowNo
        sRange = "(" & "A4:J" & (irowNo + 3) & ")"
        .Range(sRange).Select            '设置范围
        .Selection.RowHeight = 16        'Excel行高
        .Selection.Font.Name = "宋体"    'Excel 字体
        .Selection.Font.Size = 9         'Excel 字号
        .Selection.Borders.LineStyle = tvwRootLines   '画边框线
    End With

   Me.MousePointer = vbDefault
   excelsetup = True

          '打印设置
    With mobjexcel                 '定义页眉、页尾
        .ActiveSheet.PageSetup.LeftHeader = ""
        '.ActiveSheet.PageSetup.CenterHeader = "海亮集团 "
        '.ActiveSheet.PageSetup.RightHeader = curdate2 & "     "
        '.ActiveSheet.PageSetup.PaperSize = vbPRPSA4  'A4 纸纵向打印
        .ActiveCell.Range("A1").Select  '焦点行 取消黑框
    End With
End Sub
Private Sub cmdexit_Click()
    If excelsetup = True Then
        mobjexcel.activeworkbook.saved = True   '放弃对工作表的改变
        excelsetup = False
        mobjexcel.Quit
    End If

    Set mobjexcel = Nothing
    Unload Me
End Sub