tjymgs2.frm

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

    Next i
    
    Grid1.Cell(0, 9).WrapText = True    ' 单元格自动换行
    Grid1.Cell(0, 10).WrapText = True   ' 单元格自动换行
    Grid1.Cell(0, 11).WrapText = True   ' 单元格自动换行
    Grid1.Cell(0, 12).WrapText = True   ' 单元格自动换行
    Grid1.Cell(0, 13).WrapText = True   ' 单元格自动换行
    Grid1.Cell(0, 14).WrapText = True   ' 单元格自动换行
    Grid1.RowHeight(0) = 40
    
    dogridfill
    Grid1.AutoRedraw = True
    Grid1.Refresh
  
    Grid2.AutoRedraw = False
    Grid2.DisplayFocusRect = False
    Grid2.Cols = 7
    
    Grid2.Rows = 1
    Grid2.Column(0).Width = 2
    Grid2.Column(1).Width = 40
    Grid2.Column(2).Width = 80
    For i = 3 To 6
        Grid2.Column(i).Width = 90
        Grid2.Column(i).Alignment = cellRightCenter
    Next i
    Grid2.Cell(0, 1).Text = "序号"
    Grid2.Cell(0, 2).Text = "工时类别"
    Grid2.Cell(0, 3).Text = "金工车间工时"
    Grid2.Cell(0, 4).Text = "铆焊车间工时"
    Grid2.Cell(0, 5).Text = "装配车间工时"
    Grid2.Cell(0, 6).Text = "小计工时"
    Grid2.AutoRedraw = True
    Grid2.Refresh
    
    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()
    frmwait.Show 0
    DoEvents
    
    If Mskdate1.Text = "" Then gsdate1 = (NOWDate - 30) Else gsdate1 = (Mskdate1.Text)
    If mskdate2.Text = "" Then gsdate2 = NOWDate Else gsdate2 = mskdate2.Text
    
    lblym.Caption = Left(mskdate2.Text, 4) & Mid(mskdate2.Text, 6, 2)
 
    dofillgrid1  '定额工时列按工序、行按部件统计
    dofillgrid2  '增拨工时列按工序、行按工时类别统计
    
     Unload frmwait
End Sub
Private Sub dofillgrid1()
    Set rsTempA = oDb.Execute("select * from acp where cpyn='Y' order by cpbh")
    Do Until rsTempA.EOF
        '填序号＋产品名称＋产品型号
        griditem = Grid1.Rows & Chr(9) & rsTempA!cpmc & Chr(9) & rsTempA!cpxh
        Grid1.AddItem griditem
        
        '部件
        Set rsTempB = oDb.Execute("select * from abj where cpbh='" & rsTempA!cpbh & "' order by bjbh")
        Do Until rsTempB.EOF
            griditem = Grid1.Rows & Chr(9) & "" & Chr(9) & "" & Chr(9) & rsTempB!bjmc & Chr(9) & rsTempB!bjth & Chr(9) & rsTempB!bjsl & Chr(9) & rsTempB!bjzl & Chr(9) & rsTempB!bjtotgs
            If Not IsNull(rsTempB!bjzl) Then Bjdnzl = rsTempB!bjzl Else Bjdnzl = 0 '重量比例用
            If Not IsNull(rsTempB!bjtotgs) Then Bjdngs = rsTempB!bjtotgs Else Bjdngs = 0
            '按车间统计工时数
            Gxgstot = 0
            Set rsTempC = oDb.Execute("select * from acj  order by cjbh")
            Do Until rsTempC.EOF
                '按车间名称、日期、部件编号条件小计工序名称相同的工时数
                szSql = "select sum(gpdnb.gpgs) as sumgs from gpdnh,gpdnb where (gpdnh.gpbh=gpdnb.gpbh) and gpdnh.gprq>='" & Mskdate1.Text & "' and gpdnh.gprq<='" & mskdate2.Text & "'" _
                         & " and gpdnb.gpbjbh='" & rsTempB!bjbh & "' and gpdnh.gpcjbh=" & rsTempC!cjbh & ""
                Set rsTempD = oDb.Execute(szSql)
                If rsTempD!sumgs > 0 Then
                    If Bjdngs > 0 Then
                        griditem = griditem & Chr(9) & Round(rsTempD!sumgs / 60, 1) & Chr(9) & Round((Round(rsTempD!sumgs / 60, 1) / Bjdngs * Bjdnzl), 0)
                    Else
                        griditem = griditem & Chr(9) & Round(rsTempD!sumgs / 60, 1) & Chr(9) & ""
                    End If
                    Gxgstot = Gxgstot + rsTempD!sumgs
                Else
                    griditem = griditem & Chr(9) & "" & Chr(9) & ""
                End If
                
                rsTempC.MoveNext
            Loop
            If Bjdngs <> 0 Then
                Grid1.AddItem griditem & Chr(9) & Round(Gxgstot / 60, 1) & Chr(9) & Round((Round(Gxgstot / 60, 1) / Bjdngs * Bjdnzl), 0)   '小计工时/部件总工时*部件重量
            Else
                Grid1.AddItem griditem & Chr(9) & Round(Gxgstot / 60, 1) & Chr(9) & "0"
            End If
            rsTempB.MoveNext
        Loop
        rsTempA.MoveNext
    Loop
    
    '合计工时、重量
    For i = 1 To Grid1.Rows - 1
        Grid1.Cell(i, 15).Text = Val(Grid1.Cell(i, 9).Text) + Val(Grid1.Cell(i, 11).Text) + Val(Grid1.Cell(i, 13).Text)
        Grid1.Cell(i, 16).Text = Val(Grid1.Cell(i, 10).Text) + Val(Grid1.Cell(i, 12).Text) + Val(Grid1.Cell(i, 14).Text)
    Next i
End Sub
Private Sub dofillgrid2()
     '"设计更改" "工艺更改" "计划更改""质量损失" "设备返修"
    Set rsTempB = oDb.Execute("select * from gpgslb")
    Do Until rsTempB.EOF
        Gxgstot = 0
        griditem = Grid2.Rows & Chr(9) & rsTempB!gslb
        Set rsTempC = oDb.Execute("select * from acj  order by cjbh")
        Do Until rsTempC.EOF
            szSql = "select sum(gpzpb.gpgs) as sumgs from gpzph,gpzpb where (gpzph.gpbh=gpzpb.gpbh) and gpgslb='" & rsTempB!gslb & "' and gpzph.gprq>='" & Mskdate1.Text & "' and gpzph.gprq<='" & mskdate2.Text & "'" _
                     & " and gpzph.gpcjbh=" & rsTempC!cjbh & ""
            Set rsTempD = oDb.Execute(szSql)
            If rsTempD!sumgs > 0 Then
                griditem = griditem & Chr(9) & Round(rsTempD!sumgs / 60, 1)
            Else
                griditem = griditem & Chr(9) & ""
            End If
            If Not IsNull(rsTempD!sumgs) Then Gxgstot = Gxgstot + rsTempD!sumgs
            rsTempC.MoveNext
        Loop
        Grid2.AddItem griditem & Chr(9) & Round(Gxgstot / 60, 1)
        rsTempB.MoveNext
    Loop
End Sub
 
Private Sub dogridfill()
    Grid1.Cell(0, 1).Text = "序号"
    Grid1.Cell(0, 2).Text = "产品名称"
    Grid1.Cell(0, 3).Text = "产品型号"
    Grid1.Cell(0, 4).Text = "部件名称"
    Grid1.Cell(0, 5).Text = "部件型号"
    Grid1.Cell(0, 6).Text = "数量"
    Grid1.Cell(0, 7).Text = "重量"
    Grid1.Cell(0, 8).Text = "定额工时"
    Grid1.Cell(0, 9).Text = "金工车间工时"
    Grid1.Cell(0, 10).Text = "金工车间重量"
    Grid1.Cell(0, 11).Text = "铆焊车间工时"
    Grid1.Cell(0, 12).Text = "铆焊车间重量"
    Grid1.Cell(0, 13).Text = "装配车间工时"
    Grid1.Cell(0, 14).Text = "装配车间重量"
    Grid1.Cell(0, 15).Text = "合计工时"
    Grid1.Cell(0, 16).Text = "合计重量"
    For i = 6 To 16
        Grid1.Column(i).Alignment = cellRightCenter
    Next i
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()
    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 = 8
        .ActiveCell.Columns("C:C").ColumnWidth = 10
        .ActiveCell.Columns("D:D").ColumnWidth = 10
        .ActiveCell.Columns("E:E").ColumnWidth = 10
        .ActiveCell.Columns("F:F").ColumnWidth = 6
        .ActiveCell.Columns("G:G").ColumnWidth = 6
        .ActiveCell.Columns("H:H").ColumnWidth = 6
        .ActiveCell.Columns("I:I").ColumnWidth = 8
        .ActiveCell.Columns("J:J").ColumnWidth = 8
        .ActiveCell.Columns("K:K").ColumnWidth = 8
        .ActiveCell.Columns("L:L").ColumnWidth = 8
        .ActiveCell.Columns("M:M").ColumnWidth = 6
        .ActiveCell.Columns("N:N").ColumnWidth = 6
    End With

    'irowNo = 1                  'Excel row
    mobjexcel.Visible = True    'Excel visible
    
    With mobjexcel
        .ActiveCell.Cells(1, 1).Value = "绍兴金氏机械设备有限公司  工时车间汇总情况"
        .ActiveCell.Cells(3, 1).Value = "日期：" & Mskdate1.Text & "--" & mskdate2.Text & Space(6) & "单位：小时、kg"
    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

    For irowNo = 0 To Grid2.Rows - 1
            For j = 1 To Grid2.Cols - 1
                With mobjexcel
                    .ActiveCell.Cells(Grid1.Rows + irowNo + 5, j).Value = Grid2.Cell(irowNo, j).Text
                End With
            Next j
    Next irowNo
    
    With mobjexcel        '设置工作表字体，列宽
        'sRange = Chr(Asc("A")) & "2" & ":" & Chr(Asc("AL")) & irowNo
        sRange = "(" & "A4:N" & (Grid1.Rows + irowNo + 4) & ")"
        .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