tjyngs1.frm

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

  
    Grid2.AutoRedraw = False
    Grid2.DisplayFocusRect = False
    Grid2.Cols = 3
    Grid2.FixedCols = 2
    
    Grid2.Rows = 1
    Grid2.Column(0).Width = 2
    Grid2.Column(1).Width = 30
    Grid2.Column(2).Width = 80
    Grid2.Cell(0, 1).Text = "序号"
    Grid2.Cell(0, 2).Text = "工时类别"
    Grid2.AutoRedraw = True
    Grid2.Refresh
    
    '填列车间
    Set rsTempA = oDb.Execute("select * from acj ")
    Do Until rsTempA.EOF
        cmbcj.AddItem rsTempA!cjmc
        rsTempA.MoveNext
    Loop
    cmbcj.ListIndex = 0
    
    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
    
    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()
     '取得工序表agx中所选车间的工序数，以设置表格的列数,并填列
    Set rsTempC = oDb.Execute("select * from agx where gxtj='Y' and gxcj='" & cmbcj.Text & "' order by gxbh")
    i = rsTempC.RecordCount
    rsTempC.MoveLast
    Igx = rsTempC.RecordCount
    rsTempC.MoveFirst
    
    Grid1.Rows = 1
    Grid1.Cols = 9 + Igx + 3    '列数
    i = 9
    Do Until rsTempC.EOF
        Grid1.Cell(0, i).Text = rsTempC!gxmc
        Grid1.Column(i).Width = 50
        Grid1.Column(i).Alignment = cellRightCenter
        rsTempC.MoveNext
        i = i + 1
    Loop
    Grid1.Cell(0, i).Text = "小计工时"
    Grid1.Column(i).Alignment = cellRightCenter
    Grid1.Cell(0, i + 1).Text = "小计重量"
    Grid1.Column(i + 1).Alignment = cellRightCenter
    Grid1.Cell(0, i + 2).Text = "备注"
    
    Set rsTempA = oDb.Execute("select * from acp where cpyn='Y' order by cpbh")
    Do Until rsTempA.EOF
        '填序号＋产品名称＋产品型号
        griditem = Grid1.Rows & Chr(9) & Trim(rsTempA!cpmc) & Chr(9) & Trim(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) & Trim(rsTempB!bjmc) & Chr(9) & Trim(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 agx where gxtj='Y' and gxcj='" & cmbcj.Text & "' order by gxbh")
            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 gpdnb.gpgxmc='" & rsTempC!gxmc & "'"
                Set rsTempD = oDb.Execute(szSql)
                griditem = griditem & Chr(9) & Round(rsTempD!sumgs / 60, 1)
                If Not IsNull(rsTempD!sumgs) Then Gxgstot = Gxgstot + rsTempD!sumgs
                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), 1)  '小计工时/部件总工时*部件重量
            Else
                Grid1.AddItem griditem & Chr(9) & Round(Gxgstot / 60, 1) & Chr(9) & "0"
            End If
            rsTempB.MoveNext
        Loop
        rsTempA.MoveNext
    Loop
End Sub
Private Sub dofillgrid2()
     '取得工序表agx中所选车间的工序数，以设置表格的列数,并填列
    Set rsTempC = oDb.Execute("select * from agx where gxtj='Y' and gxcj='" & cmbcj.Text & "' order by gxbh")
    rsTempC.MoveLast
    Igx = rsTempC.RecordCount
    rsTempC.MoveFirst
    
    Grid2.Rows = 1
    Grid2.Cols = 3 + Igx + 1    '列数
    i = 3
    Do Until rsTempC.EOF
        Grid2.Cell(0, i).Text = rsTempC!gxmc
        Grid2.Column(i).Width = 50
        rsTempC.MoveNext
        i = i + 1
    Loop
    Grid2.Cell(0, i).Text = "小计工时"
     
     '"设计更改" "工艺更改" "计划更改""质量损失" "设备返修"
    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 agx where gxtj='Y' and gxcj='" & cmbcj.Text & "' order by gxbh")
        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 gpzpb.gpgxmc='" & rsTempC!gxmc & "'"
            Set rsTempD = oDb.Execute(szSql)
            griditem = griditem & Chr(9) & Round(rsTempD!sumgs / 60, 1)
            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 = "定额工时"
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 = 4
        .ActiveCell.Columns("G:G").ColumnWidth = 6
        .ActiveCell.Columns("H:H").ColumnWidth = 6
        .ActiveCell.Columns("I:I").ColumnWidth = 6
        .ActiveCell.Columns("J:J").ColumnWidth = 6
        .ActiveCell.Columns("K:K").ColumnWidth = 6
        .ActiveCell.Columns("L:L").ColumnWidth = 6
        .ActiveCell.Columns("M:M").ColumnWidth = 6
        .ActiveCell.Columns("N:N").ColumnWidth = 6
        .ActiveCell.Columns("O:O").ColumnWidth = 6
        .ActiveCell.Columns("P:P").ColumnWidth = 6
        .ActiveCell.Columns("Q:Q").ColumnWidth = 6
        .ActiveCell.Columns("R:R").ColumnWidth = 6
        .ActiveCell.Columns("S:S").ColumnWidth = 6
        .ActiveCell.Columns("T:T").ColumnWidth = 6
        .ActiveCell.Columns("U:U").ColumnWidth = 6
        .ActiveCell.Columns("V:V").ColumnWidth = 6
        .ActiveCell.Columns("W:W").ColumnWidth = 6
        .ActiveCell.Columns("X:X").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(6) & "车间名称:" & cmbcj.Text & Space(20) & "单位：小时、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 = 2 To Grid2.Cols - 1
                With mobjexcel
                    .ActiveCell.Cells(Grid1.Rows + irowNo + 5, j + 6).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:X" & (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