frm_intout.frm

ktv场所的包房开房、迎宾、预定管理系统。

        TxtSQL = TxtSQL & " and a.order_id=b.ps_id and b.p_flag=no and b.ps_date>=#" & DTPicker1.Value & "# and b.ps_date<=#" & DTPicker2.Value & "#"
    Else
        TxtSQL = TxtSQL & " and a.order_id=b.ps_id and b.p_flag=no"
    End If
    
    TxtSQL = TxtSQL & " group by a.p_id"
    mrc.Open TxtSQL, cnn, adOpenForwardOnly, adLockOptimistic
    Do While Not mrc.EOF
        i = 1
        Do While i < msglist.rows
            If mrc.Fields(0) = Trim$(msglist.TextMatrix(i, 0)) Then
                msglist.TextMatrix(i, 3) = Int(Val("" & mrc.Fields(1)))
                Exit Do
            End If
            i = i + 1
        Loop
        mrc.MoveNext
    Loop
    If mrc.State = adStateOpen Then mrc.Close
    pb.Value = pb.Value + 1

    TxtSQL = "select a.p_id,sum(a.qty) from order_detail_b as a,ps_head_b as b"
    TxtSQL = TxtSQL & " where b.ps_type='其它入库'"
    If Check1.Value = 1 Then
        TxtSQL = TxtSQL & " and a.order_id=b.ps_id and b.p_flag=no and b.ps_date>=#" & DTPicker1.Value & "# and b.ps_date<=#" & DTPicker2.Value & "#"
    Else
        TxtSQL = TxtSQL & " and a.order_id=b.ps_id and b.p_flag=no"
    End If
    TxtSQL = TxtSQL & " group by a.p_id"
    mrc.Open TxtSQL, cnn, adOpenForwardOnly, adLockOptimistic
    Do While Not mrc.EOF
        i = 1
        Do While i < msglist.rows
            If mrc.Fields(0) = Trim$(msglist.TextMatrix(i, 0)) Then
                msglist.TextMatrix(i, 4) = Int(Val("" & mrc.Fields(1)))
                Exit Do
            End If
            i = i + 1
        Loop
        mrc.MoveNext
    Loop
    If mrc.State = adStateOpen Then mrc.Close
    pb.Value = pb.Value + 1

    TxtSQL = "select a.p_id,sum(a.qty) from order_detail_b as a,ps_head_b as b"     '退货
    TxtSQL = TxtSQL & " where b.ps_type='退库单'"
    If Check1.Value = 1 Then
        TxtSQL = TxtSQL & " and a.order_id=b.ps_id and b.p_flag=no and b.ps_date>=#" & DTPicker1.Value & "# and b.ps_date<=#" & DTPicker2.Value & "#"
    Else
        TxtSQL = TxtSQL & " and a.order_id=b.ps_id and b.p_flag=no"
    End If
    TxtSQL = TxtSQL & " group by a.p_id"
    mrc.Open TxtSQL, cnn, adOpenForwardOnly, adLockOptimistic
    Do While Not mrc.EOF
        i = 1
        Do While i < msglist.rows
            If mrc.Fields(0) = Trim$(msglist.TextMatrix(i, 0)) Then
                msglist.TextMatrix(i, 7) = Int(Val("" & mrc.Fields(1)))
                Exit Do
            End If
            i = i + 1
        Loop
        mrc.MoveNext
    Loop
    If mrc.State = adStateOpen Then mrc.Close
  
    TxtSQL = "select a.p_id,sum(a.qty) from order_detail_b as a,ps_head_b as b"
    TxtSQL = TxtSQL & " where b.ps_type='报损单'"
    
    If Check1.Value = 1 Then
        TxtSQL = TxtSQL & " and a.order_id=b.ps_id and b.p_flag=no and b.ps_date>=#" & DTPicker1.Value & "# and b.ps_date<=#" & DTPicker2.Value & "#"
    Else
        TxtSQL = TxtSQL & " and a.order_id=b.ps_id and b.p_flag=no"
    End If
    
    TxtSQL = TxtSQL & " group by a.p_id"
    mrc.Open TxtSQL, cnn, adOpenForwardOnly, adLockOptimistic
    Do While Not mrc.EOF
        i = 1
        Do While i < msglist.rows
            If mrc.Fields(0) = Trim$(msglist.TextMatrix(i, 0)) Then
                msglist.TextMatrix(i, 8) = Int(Val("" & mrc.Fields(1)))
                Exit Do
            End If
            i = i + 1
        Loop
        mrc.MoveNext
    Loop
    If mrc.State = adStateOpen Then mrc.Close
    pb.Value = pb.Value + 1
    
    TxtSQL = "select a.p_id,sum(a.qty)  from psout_detail as a,psout_head as b"  '出库
    If Check1.Value = 1 Then
        TxtSQL = TxtSQL & " where a.order_id=b.ps_id and b.p_flag=no and b.ps_date>=#" & DTPicker1.Value & "# and b.ps_date<=#" & DTPicker2.Value & "#"
    Else
        TxtSQL = TxtSQL & " where a.order_id=b.ps_id and b.p_flag=no"
    End If
    TxtSQL = TxtSQL & " group by a.p_id"
    TxtSQL = TxtSQL & " order by a.p_id"
    mrc.Open TxtSQL, cnn, adOpenForwardOnly, adLockOptimistic
    Do While Not mrc.EOF
    
        i = 1
        Do While i < msglist.rows
            If mrc.Fields(0) = Trim$(msglist.TextMatrix(i, 0)) Then
                msglist.TextMatrix(i, 10) = Int(Val("" & mrc.Fields(1)))
                Exit Do
            End If
            i = i + 1
        Loop
        mrc.MoveNext
    Loop
    If mrc.State = adStateOpen Then mrc.Close
    pb.Value = pb.Value + 1
    
    TxtSQL = "select a.p_id,sum(a.qty)  from pos_detail as a,pos_head as b"  '超市退库
    If Check1.Value = 1 Then
        TxtSQL = TxtSQL & " where a.order_id=b.ps_id and left(b.ps_id,2)='TK'and  b.ps_date>=#" & DTPicker1.Value & "# and b.ps_date<=#" & DTPicker2.Value & "#"
    Else
        TxtSQL = TxtSQL & " where a.order_id=b.ps_id and left(b.ps_id,2)='TK'"
    End If
    TxtSQL = TxtSQL & " group by a.p_id"
    TxtSQL = TxtSQL & " order by a.p_id"
    mrc.Open TxtSQL, cnn, adOpenForwardOnly, adLockOptimistic
    Do While Not mrc.EOF

        i = 1
        Do While i < msglist.rows
            If mrc.Fields(0) = Trim$(msglist.TextMatrix(i, 0)) Then
                msglist.TextMatrix(i, 6) = 0 - Val(Int(Val("" & mrc.Fields(1))))
                Exit Do
            End If
            i = i + 1
        Loop
        
        mrc.MoveNext
    Loop
    If mrc.State = adStateOpen Then mrc.Close
    pb.Value = pb.Value + 1
    
    TxtSQL = "select p_id,sum(qty)  as qty8 from mat_detail  "              '库存
    TxtSQL = TxtSQL & " group by p_id"
    TxtSQL = TxtSQL & " order by p_id"
    mrc.Open TxtSQL, cnn, adOpenForwardOnly, adLockOptimistic
    Do While Not mrc.EOF
        i = 1
        Do While i < msglist.rows
            If mrc.Fields(0) = Trim$(msglist.TextMatrix(i, 0)) Then
                msglist.TextMatrix(i, 12) = Int(Val("" & mrc.Fields(1)))
                Exit Do
            End If
            i = i + 1
        Loop
        mrc.MoveNext
    Loop
    If mrc.State = adStateOpen Then mrc.Close
    pb.Value = pb.Value + 1
    
    TxtSQL = "select a.p_id,sum(a.qty) from order_detail_b as a,ps_head_b as b"
    TxtSQL = TxtSQL & " where b.ps_type='仓库盘点报损'"
    If Check1.Value = 1 Then
        TxtSQL = TxtSQL & " and a.order_id=b.ps_id and b.p_flag=no and b.ps_date>=#" & DTPicker1.Value & "# and b.ps_date<=#" & DTPicker2.Value & "#"
    Else
        TxtSQL = TxtSQL & " and a.order_id=b.ps_id and b.p_flag=no"
    End If
    TxtSQL = TxtSQL & " group by a.p_id"
    mrc.Open TxtSQL, cnn, adOpenForwardOnly, adLockOptimistic
    Do While Not mrc.EOF
        For i = 1 To msglist.rows - 1
            If mrc.Fields(0) = Trim$(msglist.TextMatrix(i, 0)) Then
                msglist.TextMatrix(i, 9) = Int(Val("" & mrc.Fields(1)))
            End If
        Next
        mrc.MoveNext
    Loop
    If mrc.State = adStateOpen Then mrc.Close
    pb.Value = pb.Value + 1

    TxtSQL = "select a.p_id,sum(a.qty) from order_detail_b as a,ps_head_b as b"
    TxtSQL = TxtSQL & " where b.ps_type='仓库盘点报溢'"
    If Check1.Value = 1 Then
        TxtSQL = TxtSQL & " and a.order_id=b.ps_id and b.p_flag=no and b.ps_date>=#" & DTPicker1.Value & "# and b.ps_date<=#" & DTPicker2.Value & "#"
    Else
        TxtSQL = TxtSQL & " and a.order_id=b.ps_id and b.p_flag=no"
    End If
    TxtSQL = TxtSQL & " group by a.p_id"
    mrc.Open TxtSQL, cnn, adOpenForwardOnly, adLockOptimistic
    Do While Not mrc.EOF
        i = 1
        Do While i < msglist.rows
            If mrc.Fields(0) = Trim$(msglist.TextMatrix(i, 0)) Then
                msglist.TextMatrix(i, 5) = Int(Val("" & mrc.Fields(1)))
                Exit Do
            End If
            i = i + 1
        Loop
        mrc.MoveNext
    Loop
    If mrc.State = adStateOpen Then mrc.Close
    pb.Value = pb.Value + 1
    
    '仓库总数量和金额
    mrc.Open "select * from product", cnn, adOpenForwardOnly, adLockOptimistic
    Do While Not mrc.EOF
        i = 1
        Do While i < msglist.rows
            If mrc.Fields(0) = Trim$(msglist.TextMatrix(i, 0)) Then
                msglist.TextMatrix(i, 11) = (Val("" & mrc.Fields("product_cos")))
                msglist.TextMatrix(i, 13) = Val("0" & msglist.TextMatrix(i, 11)) * Val("0" & msglist.TextMatrix(i, 12))
                Exit Do
            End If
            i = i + 1
        Loop
        mrc.MoveNext
    Loop
    If mrc.State = adStateOpen Then mrc.Close
    pb.Value = pb.Value + 1
    
    '超市总数量和金额
    mrc.Open "SELECT p_id, sum(qty)  FROM mat_detail_bt GROUP BY p_id", cnn, adOpenForwardOnly, adLockOptimistic
    Do While Not mrc.EOF
        i = 1
        Do While i < msglist.rows
            If mrc.Fields(0) = Trim$(msglist.TextMatrix(i, 0)) Then
                msglist.TextMatrix(i, 14) = (Val("" & mrc.Fields(1)))
                msglist.TextMatrix(i, 15) = Val("0" & msglist.TextMatrix(i, 11)) * Val("0" & msglist.TextMatrix(i, 14))
                Exit Do
            End If
            i = i + 1
        Loop
        mrc.MoveNext
    Loop
    If mrc.State = adStateOpen Then mrc.Close
    pb.Value = pb.Value + 1
    
    Dim tS As Double, tC As Double

    tS = 0: tC = 0
    With msglist
        .rows = .rows + 1
        For i = 1 To .rows - 2
            tS = tS + Val("0" & .TextMatrix(i, 13))
            tC = tC + Val("0" & .TextMatrix(i, 15))
            
        Next
        .TextMatrix(.rows - 1, 1) = "合计:"
        .TextMatrix(.rows - 1, 13) = tS
        .TextMatrix(.rows - 1, 15) = tC
    End With
    pb.Value = pb.Value + 1
    
    pb.Value = 0
    pb.Appearance = ccFlat
    pb.Visible = False
    Check1.Enabled = True

    

End Sub

Private Sub showtitle()
    Dim i As Integer
    With msglist
        .Cols = 16

        .TextMatrix(0, 0) = "编号"
        .TextMatrix(0, 1) = "产品名称"
        .TextMatrix(0, 2) = "采购入库"
        .TextMatrix(0, 3) = "盘盈入库"
        .TextMatrix(0, 4) = "其它入库"
        .TextMatrix(0, 5) = " 盘溢"
        .TextMatrix(0, 6) = "退库"
        .TextMatrix(0, 7) = " 退货"
        .TextMatrix(0, 8) = " 报损"
        .TextMatrix(0, 9) = " 盘损"
        .TextMatrix(0, 10) = " 出库"
        .TextMatrix(0, 11) = "单价"
        .TextMatrix(0, 12) = "仓库库存"
        .TextMatrix(0, 13) = "金额"
        .TextMatrix(0, 14) = "超市库存"
        .TextMatrix(0, 15) = "金额"
        
        .ColAlignment(0) = 1
        .ColAlignment(1) = 1
        For i = 2 To 11
            .ColAlignment(i) = 4
        Next
        .FillStyle = flexFillRepeat
        .col = 0
        .row = 1
        .ColSel = .Cols - 1
        
        If cxScreen = 1024 Then
            .colWidth(0) = 650 * 1.2
            .colWidth(1) = 2000 * 1.2
            .colWidth(2) = 700 * 1.2
            .colWidth(3) = 700 * 1.2
            .colWidth(4) = 700 * 1.2
            .colWidth(5) = 500 * 1.2
            .colWidth(6) = 500 * 1.2
            .colWidth(7) = 500 * 1.2
            .colWidth(8) = 500 * 1.2
            .colWidth(9) = 500 * 1.2
            .colWidth(10) = 700 * 1.2
            .colWidth(11) = 700 * 1.2
            .colWidth(12) = 700 * 1.2
            .colWidth(13) = 900 * 1.2
            .colWidth(14) = 700 * 1.2
            .colWidth(15) = 900 * 1.2
        Else
            .colWidth(0) = 650
            .colWidth(1) = 1300
            .colWidth(2) = 700
            .colWidth(3) = 700
            .colWidth(4) = 700
            .colWidth(5) = 500
            .colWidth(6) = 500
            .colWidth(7) = 500
            .colWidth(8) = 500
            .colWidth(9) = 500
            .colWidth(10) = 900
            .colWidth(11) = 700
            .colWidth(12) = 900
            .colWidth(13) = 900
            .colWidth(14) = 700
            .colWidth(15) = 900
        End If


    End With
End Sub

Private Sub Form_Load()
    DTPicker2.Value = Now + 1
    DTPicker1.Value = "1999-1-1"
    pb.Visible = False
    showtitle
End Sub

Private Sub Form_Resize()
On Error Resume Next
Frame1.Left = (Me.width - Frame1.width) / 2
Label2.Left = (Me.width - Label2.width) / 2
msglist.width = Me.width - 150
msglist.height = Me.height - 2100



End Sub