tjlxgs.frm

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

    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  '工时列按工序、行按部件统计
    
    Unload frmwait
End Sub
Private Sub dofillgrid1()
    If cmbcj.Text <> "全部车间" Then
         '取得工序表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 = 4 + Igx + 1    '列数
        i = 4
        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
        
        cpgstot = 0
        Set rsTempA = oDb.Execute("select * from acplx order by cpbh")
        Do Until rsTempA.EOF
            '填序号＋产品名称＋产品型号
            griditem = Grid1.Rows & Chr(9) & Trim(rsTempA!cpmc)
            Grid1.AddItem griditem
            cpgssub = 0
            
            '部件
            Set rsTempB = oDb.Execute("select * from abjlx where cpbh='" & rsTempA!cpbh & "' order by bjbh")
            Do Until rsTempB.EOF
                griditem = Grid1.Rows & Chr(9) & "" & Chr(9) & Trim(rsTempB!bjmc)
                
                '按工序统计工时数
                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(gplxb.gpgs) as sumgs from gplxh,gplxb where (gplxh.gpbh=gplxb.gpbh) and gplxh.gprq>='" & Mskdate1.Text & "' and gplxh.gprq<='" & mskdate2.Text & "'" _
                             & " and gplxb.gpbjbh='" & rsTempB!bjbh & "' and gplxb.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
                 
                Grid1.AddItem griditem & Chr(9) & Round(Gxgstot / 60, 1) & Chr(9) & "0"
                cpgssub = cpgssub + Gxgstot
                cpgstot = cpgstot + Gxgstot
                rsTempB.MoveNext
            Loop
            Grid1.AddItem "" & Chr(9) & "小计" '& Chr(9) & Round(cpgstot / 60, 1)
            Grid1.Cell(Grid1.Rows - 1, Grid1.Cols - 1).Text = Round(cpgssub / 60, 1)
            rsTempA.MoveNext
        Loop
        Grid1.AddItem "" & Chr(9) & "合计"
        Grid1.Cell(Grid1.Rows - 1, Grid1.Cols - 1).Text = Round(cpgstot / 60, 1)
    Else
        Set rsTempC = oDb.Execute("select * from acj  where  cjbh<>1003 order by cjorder ")
        Igx = 2
        Grid1.Rows = 1
        Grid1.Cols = 4 + Igx + 1    '列数
        i = 4
        Do Until rsTempC.EOF
            Grid1.Cell(0, i).Text = rsTempC!cjmc
            Grid1.Column(i).Width = 70
            Grid1.Column(i).Alignment = cellRightCenter
            rsTempC.MoveNext
            i = i + 1
        Loop
         
        Grid1.Cell(0, i).Text = "小计工时"
        Grid1.Column(i).Alignment = cellRightCenter
        
        cpgstot = 0
        Set rsTempA = oDb.Execute("select * from acplx order by cpbh")
        Do Until rsTempA.EOF
            '填序号＋产品名称＋产品型号
            griditem = Grid1.Rows & Chr(9) & Trim(rsTempA!cpmc)
            Grid1.AddItem griditem
            cpgssub = 0
            
            '部件
            Set rsTempB = oDb.Execute("select * from abjlx where cpbh='" & rsTempA!cpbh & "' order by bjbh")
            Do Until rsTempB.EOF
                griditem = Grid1.Rows & Chr(9) & "" & Chr(9) & Trim(rsTempB!bjmc)
                
                '按工序统计工时数
                Gxgstot = 0
                Set rsTempC = oDb.Execute("select * from acj  where  cjbh<>1003 order by cjorder ")
                Do Until rsTempC.EOF
                    '按车间名称、日期、部件编号条件小计工序名称相同的工时数
                    szSql = "select sum(gplxb.gpgs) as sumgs from gplxh,gplxb where (gplxh.gpbh=gplxb.gpbh) and gplxh.gprq>='" & Mskdate1.Text & "' and gplxh.gprq<='" & mskdate2.Text & "'" _
                             & " and gplxb.gpbjbh='" & rsTempB!bjbh & "' and gplxh.gpcjmc='" & rsTempC!cjmc & "'"
                    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
                 
                Grid1.AddItem griditem & Chr(9) & Round(Gxgstot / 60, 1) & Chr(9) & "0"
                cpgssub = cpgssub + Gxgstot
                cpgstot = cpgstot + Gxgstot
                rsTempB.MoveNext
            Loop
            Grid1.AddItem "" & Chr(9) & "小计" '& Chr(9) & Round(cpgstot / 60, 1)
            Grid1.Cell(Grid1.Rows - 1, Grid1.Cols - 1).Text = Round(cpgssub / 60, 1)
            rsTempA.MoveNext
        Loop
        Grid1.AddItem "" & Chr(9) & "合计"
        Grid1.Cell(Grid1.Rows - 1, Grid1.Cols - 1).Text = Round(cpgstot / 60, 1)
    End If
    
End Sub
 Private Sub dogridfill()
    Grid1.Cell(0, 1).Text = "序号"
    Grid1.Cell(0, 2).Text = "产品类别"
    Grid1.Cell(0, 3).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
    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

     
    
    With mobjexcel        '设置工作表字体，列宽
        'sRange = Chr(Asc("A")) & "2" & ":" & Chr(Asc("AL")) & irowNo
        sRange = "(" & "A4:Q" & (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