frmreport.frm

物流管理系统

                        .SuppressEndnotes = False
                        .MirrorMargins = False
                        .TwoPagesOnOne = False
                        .GutterPos = wdGutterPosLeft
                        .LayoutMode = wdLayoutModeLineGrid
                    End With
                    
                    .ParagraphFormat.Alignment = wdAlignParagraphCenter
                    .Paragraphs.Last.Range.Font.Bold = True
                    .Paragraphs.Last.Range.Font.Size = 20
                    .TypeText Text:="运  输  结  算  单"
                    .TypeParagraph
                    .Paragraphs.Last.Range.Font.Size = 10
                    .Paragraphs.Last.Range.Font.Bold = False
                    .TypeText Text:="客户：                                                                                                       单位（重量：吨、单价：元）"
                    .TypeParagraph
                    .ParagraphFormat.Alignment = wdAlignParagraphCenter
                    If total <= 15 Then
                    
                        .Tables.Add Range:=.Range, NumRows:=16, NumColumns:=10, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:=wdAutoFitFixed
                    Else
                    
                        .Tables.Add Range:=.Range, NumRows:=total + 1, NumColumns:=10, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:=wdAutoFitWindow
                    End If
                    Set oTable = .Tables(1)
                    With oTable
                        .Cell(1, 1).Range.Paragraphs.Alignment = wdAlignParagraphCenter
                        .Cell(1, 1).Range.Text = "序号"
                        .Cell(1, 2).Range.Paragraphs.Alignment = wdAlignParagraphCenter
                        .Cell(1, 2).Range.Text = "日期"
                        .Cell(1, 3).Range.Paragraphs.Alignment = wdAlignParagraphCenter
                        .Cell(1, 3).Range.Text = "车号"
                        .Cell(1, 4).Range.Paragraphs.Alignment = wdAlignParagraphCenter
                        .Cell(1, 4).Range.Text = "发站"
                        .Cell(1, 5).Range.Paragraphs.Alignment = wdAlignParagraphCenter
                        .Cell(1, 5).Range.Text = "到站"
                        .Cell(1, 6).Range.Paragraphs.Alignment = wdAlignParagraphCenter
                        .Cell(1, 6).Range.Text = "发货人"
                        .Cell(1, 7).Range.Paragraphs.Alignment = wdAlignParagraphCenter
                        .Cell(1, 7).Range.Text = "品名"
                        .Cell(1, 8).Range.Paragraphs.Alignment = wdAlignParagraphCenter
                        .Cell(1, 8).Range.Text = "重量"
                        .Cell(1, 9).Range.Paragraphs.Alignment = wdAlignParagraphCenter
                        .Cell(1, 9).Range.Text = "单价"
                        .Cell(1, 10).Range.Paragraphs.Alignment = wdAlignParagraphCenter
                        .Cell(1, 10).Range.Text = "合计"
                        inum = 2
                        Dim ia As Integer
                        Do While Not rs.EOF
                            .Cell(inum, 1).Range.Paragraphs.Alignment = wdAlignParagraphCenter
                            .Cell(inum, 1).Range.Text = inum - 1
                            .Cell(inum, 2).Range.Paragraphs.Alignment = wdAlignParagraphCenter
                            .Cell(inum, 2).Range.Text = rs("DATENUM")
                            .Cell(inum, 3).Range.Paragraphs.Alignment = wdAlignParagraphCenter
                            .Cell(inum, 3).Range.Text = sys.StrToText(rs("CARNUM"))
                            .Cell(inum, 4).Range.Paragraphs.Alignment = wdAlignParagraphCenter
                            For ia = 0 To UBound(arrstation, 2)
                                If arrstation(0, ia) = sys.TextTolong(rs("SENDSTATION")) Then
                                    .Cell(inum, 4).Range.Text = arrstation(1, ia)
                                End If
                            Next
                            .Cell(inum, 5).Range.Paragraphs.Alignment = wdAlignParagraphCenter
                            For ia = 0 To UBound(arrstation, 2)
                                If arrstation(0, ia) = sys.TextTolong(rs("RECEIVESTATION")) Then
                                    .Cell(inum, 5).Range.Text = arrstation(1, ia)
                                End If
                            Next
                            .Cell(inum, 6).Range.Paragraphs.Alignment = wdAlignParagraphCenter
                            For ia = 0 To UBound(arrclient, 2)
                                If arrclient(0, ia) = sys.TextTolong(rs("SENDER")) Then
                                    .Cell(inum, 6).Range.Text = arrclient(1, ia)
                                End If
                            Next
                            .Cell(inum, 7).Range.Paragraphs.Alignment = wdAlignParagraphCenter
                            For ia = 0 To UBound(arrProduct, 2)
                                If arrProduct(0, ia) = sys.TextTolong(rs("PRODUCTNAME")) Then
                                    .Cell(inum, 7).Range.Text = arrProduct(1, ia)
                                End If
                            Next
                            totalWeight = totalWeight + rs("WEIGHT")
                            .Cell(inum, 8).Range.Paragraphs.Alignment = wdAlignParagraphCenter
                            .Cell(inum, 8).Range.Text = sys.StrToText(rs("WEIGHT"))
                            .Cell(inum, 9).Range.Paragraphs.Alignment = wdAlignParagraphCenter
                            If sys.TextTolong(rs("WEIGHT")) <> 0 Then
                                .Cell(inum, 9).Range.Text = FormatNumber(sys.TextToNum(rs("TOTAL") / rs("WEIGHT")), 2)
                            Else
                                .Cell(inum, 9).Range.Text = "-"
                            End If
                            totalMony = totalMony + rs("TOTAL")
                            .Cell(inum, 10).Range.Paragraphs.Alignment = wdAlignParagraphCenter
                            .Cell(inum, 10).Range.Text = sys.StrToText(rs("TOTAL"))
                            rs.MoveNext
                            inum = inum + 1
                        Loop
                End With
                .MoveRight Unit:=wdCharacter, Count:=1, Extend:=wdExtend
                If total <= 15 Then
                    .MoveDown Unit:=wdLine, Count:=16
                Else
                    .MoveDown Unit:=wdLine, Count:=total + 1
                End If
                .ParagraphFormat.Alignment = wdAlignParagraphRight
                .TypeText Text:="共计： " & total & " 车 (" & totalWeight & " 吨)     运费合计： " & totalMony & " 元"
                .TypeParagraph
                .TypeParagraph
                .ParagraphFormat.Alignment = wdAlignParagraphRight
                .TypeText Text:="宜春市金胜物流有限公司 "
                .TypeParagraph
                .TypeText Text:=sys.StrToText(Year(Date)) & " 年 " & sys.StrToText(Month(Date)) & " 月" & sys.StrToText(Day(Date)) & " 日"
                End With
                wordApp.PrintPreview = True
            Else
            
                MsgBox "数据错误!"
                Unload Me
            End If

    Else
    
        MsgBox "请先选择要浏览的记录!"
    End If
    
End Sub

Private Sub cmdQuery_Click()
    
    Dim frmQ As New frmQuery
    frmQ.parentFrm = "frmReport"
    frmQ.Show
End Sub

Private Sub Form_Load()

    Me.Top = 0
    Me.Left = 0
    Me.Width = MainForm.Width * 0.8
    Me.Height = MainForm.Height * 0.7
    
    Call query
    
     '初始化品名
     ReDim arrProduct(1, 0)
     strsql = "SELECT * FROM PRODUCT"
     Set rs = sys.DB.OpenRecordSet(strsql)
     If Not (rs.BOF) Or (rs.EOF) Then
         inum = 0
         Do While Not rs.EOF
             ReDim Preserve arrProduct(1, inum)
             arrProduct(0, inum) = rs("ID")
             arrProduct(1, inum) = rs("NAME")
             
             rs.MoveNext
             inum = inum + 1
         Loop
     End If
     
     '初始化车站
     ReDim arrstation(1, 0)
     strsql = "SELECT * FROM STATION ORDER BY NAME"
     Set rs = sys.DB.OpenRecordSet(strsql)
     If Not (rs.BOF) Or (rs.EOF) Then
         inum = 0
         Do While Not rs.EOF
             ReDim Preserve arrstation(1, inum)
             arrstation(0, inum) = rs("ID")
             arrstation(1, inum) = rs("NAME")
             
             rs.MoveNext
             inum = inum + 1
         Loop
     End If
     
     '初始化客户
     ReDim arrclient(1, 0)
     strsql = "SELECT * FROM CLIENT  ORDER BY NAME"
     Set rs = sys.DB.OpenRecordSet(strsql)
     If Not (rs.BOF) Or (rs.EOF) Then
         inum = 0
         Do While Not rs.EOF
             ReDim Preserve arrclient(1, inum)
             arrclient(0, inum) = rs("ID")
             arrclient(1, inum) = rs("NAME")
             
             rs.MoveNext
             inum = inum + 1
         Loop
     End If
End Sub

Public Sub query(Optional ByVal strsql As String = "SELECT * FROM TRAFFIC ORDER BY ID DESC")
    


    '查询运单
    Dim inum As Integer
    Dim rs As New ADODB.Recordset
    ReDim arrTraffic(8, 0)

    Set rs = sys.DB.OpenRecordSet(strsql)
    rs.PageSize = 50
    If Not (rs.BOF) Or (rs.EOF) Then
        '计算翻页
        MaxPage = rs.PageCount - 1
        total = rs.RecordCount
        NowPage = 0
        '取出记录集
        inum = 0
        Do While Not rs.EOF
            ReDim Preserve arrTraffic(8, inum)
            arrTraffic(0, inum) = rs.Fields("ID")
            arrTraffic(1, inum) = rs.Fields("CARNUM")
            arrTraffic(2, inum) = rs.Fields("DATENUM")
            arrTraffic(3, inum) = rs.Fields("PRODUCTNAME")
            arrTraffic(4, inum) = rs.Fields("SENDSTATION")
            arrTraffic(5, inum) = rs.Fields("RECEIVESTATION")
            arrTraffic(6, inum) = rs.Fields("SENDER")
            arrTraffic(7, inum) = rs.Fields("WEIGHT")
            arrTraffic(8, inum) = rs.Fields("TOTAL")
            inum = inum + 1
            rs.MoveNext
        Loop
    
         '初始化品名
         ReDim arrProduct(1, 0)
         strsql = "SELECT * FROM PRODUCT"
         Set rs = sys.DB.OpenRecordSet(strsql)
         If Not (rs.BOF) Or (rs.EOF) Then
             inum = 0
             Do While Not rs.EOF
                 ReDim Preserve arrProduct(1, inum)
                 arrProduct(0, inum) = rs("ID")
                 arrProduct(1, inum) = rs("NAME")
                 
                 rs.MoveNext
                 inum = inum + 1
             Loop
         End If
         
         '初始化车站
         ReDim arrstation(1, 0)
         strsql = "SELECT * FROM STATION ORDER BY NAME"
         Set rs = sys.DB.OpenRecordSet(strsql)
         If Not (rs.BOF) Or (rs.EOF) Then
             inum = 0
             Do While Not rs.EOF
                 ReDim Preserve arrstation(1, inum)
                 arrstation(0, inum) = rs("ID")
                 arrstation(1, inum) = rs("NAME")
                 
                 rs.MoveNext
                 inum = inum + 1
             Loop
         End If
         
         '初始化客户
         ReDim arrclient(1, 0)
         strsql = "SELECT * FROM CLIENT  ORDER BY NAME"
         Set rs = sys.DB.OpenRecordSet(strsql)
         If Not (rs.BOF) Or (rs.EOF) Then
             inum = 0
             Do While Not rs.EOF
                 ReDim Preserve arrclient(1, inum)
                 arrclient(0, inum) = rs("ID")
                 arrclient(1, inum) = rs("NAME")
                 
                 rs.MoveNext
                 inum = inum + 1
             Loop
         End If
        
            '清除原有
        lsvTraffic.ListItems.Clear
        With lsvTraffic
            lsvTraffic.ColumnHeaders.Clear
            .ColumnHeaders.Add , , "序号", 600
            .ColumnHeaders.Add , , "日期", 1200
            .ColumnHeaders.Add , , "品名", 1200
            .ColumnHeaders.Add , , "车号", 1200
            .ColumnHeaders.Add , , "发站", 1200
            .ColumnHeaders.Add , , "到站", 1200
            .ColumnHeaders.Add , , "发货人", 1200
            .ColumnHeaders.Add , , "重量", 1200
            .ColumnHeaders.Add , , "运费", 1200
            .GridLines = True
            .ColumnHeaders.Item(1).Alignment = lvwColumnLeft
            .ColumnHeaders.Item(2).Alignment = lvwColumnCenter
            .ColumnHeaders.Item(3).Alignment = lvwColumnCenter
            .ColumnHeaders.Item(4).Alignment = lvwColumnCenter
            .ColumnHeaders.Item(5).Alignment = lvwColumnCenter
            .ColumnHeaders.Item(6).Alignment = lvwColumnCenter
            .ColumnHeaders.Item(7).Alignment = lvwColumnCenter
            .ColumnHeaders.Item(8).Alignment = lvwColumnCenter
            .ColumnHeaders.Item(9).Alignment = lvwColumnCenter
        End With
    
        Dim iq As Integer
        Dim ia As Integer
        For iq = 0 To UBound(arrTraffic, 2)
            Set Item = lsvTraffic.ListItems.Add(, , "")
            Item.Tag = sys.StrToText(arrTraffic(0, iq))
            Item.Text = sys.StrToText(iq + 1)
            Item.SubItems(1) = sys.StrToText(arrTraffic(2, iq))
            For ia = 0 To UBound(arrProduct, 2)
                If arrProduct(0, ia) = sys.TextTolong(arrTraffic(3, iq)) Then
                    Item.SubItems(2) = arrProduct(1, ia)
                End If
            Next
            Item.SubItems(3) = sys.StrToText(arrTraffic(1, iq))
            For ia = 0 To UBound(arrstation, 2)
                If arrstation(0, ia) = sys.TextTolong(arrTraffic(4, iq)) Then
                    Item.SubItems(4) = arrstation(1, ia)
                End If
            Next
            For ia = 0 To UBound(arrstation, 2)
                If arrstation(0, ia) = sys.TextTolong(arrTraffic(5, iq)) Then
                    Item.SubItems(5) = arrstation(1, ia)
                End If
            Next
            For ia = 0 To UBound(arrclient, 2)
                If arrclient(0, ia) = sys.TextTolong(arrTraffic(6, iq)) Then
                    Item.SubItems(6) = arrclient(1, ia)
                End If
            Next
            Item.SubItems(7) = sys.StrToText(arrTraffic(7, iq))
            Item.SubItems(8) = sys.StrToText(arrTraffic(8, iq))
        Next
        Label2.Caption = "共 " & total & " 条记录"
    End If

End Sub