VB开发的ERP系统

End Sub

Private Sub SzToolbar_ButtonClick(ByVal Button As MSComctlLib.Button)
    
    Select Case Button.Key
        Case "ymsz"                                          '页面设置
            Dyymctbl.Show 1
        Case "yl"                                            '预 览
            Call bbyl(True)
        Case "dy"                                            '打 印
            Call bbyl(False)
        Case "cx"                                            '查 询
            Cg_OnRoadListQuery.Show 1
        Case "bz"                                            '帮 助
            Call F1bz
        Case "fh"                                            '退 出
           Unload Me
    End Select

End Sub

Private Sub Timer1_Timer()                                 '在窗体激活后调入查询程序
    
    Timer1.Enabled = False
    Xt_Wait.Show
    Xt_Wait.Refresh
   
    '加快显示速度
    CxbbGrid.Redraw = False
 
    '生成查询结果
    Call Sub_Query
   
    CxbbGrid.Redraw = True
    
    Xt_Wait.Hide

End Sub

Private Sub Sub_Query()                                     '生成查询结果(Define)
    
    

    Dim Rec_Query As New ADODB.Recordset        '查询结果动态集
    Dim Coljsq As Long                          '网格列计数器
    Dim jsqte As Long                           '临时动态计数器
    Dim Int_Year As Integer
    Dim Int_Period As Integer
    Dim Int_MYear As Integer
    Dim Int_MPeriod As Integer
    Dim Str_Temp As String
    Dim Rec_Temp As New ADODB.Recordset
    Dim Rec_Invoice As New ADODB.Recordset
    Dim Int_Invoice As Integer                  '查询方式
    Dim Bln_Start As Boolean                    '是否存在期初余额
    Dim Bln_Jsye As Boolean                     '是否存在结算余额
    Dim Rec_Kjqj As New ADODB.Recordset         '判断期初会计期间
    '以下为自定义部分[
    '没有结帐不显示数据
    
    Set Rec_Temp = Nothing
    
    With Cg_OnRoadListQuery
        '生成查询条件
        Str_QueryCondi = " where Checker<>'' "
        Str_QueryInvoice = " where Checker<>'' "
        For jsqte = 1 To 3
            Select Case jsqte
                Case 1  '发票号
                        Str_QueryCondi = Str_QueryCondi & " And (convert(char(4),kjyear)+convert(char(2),right((100+period),2)))>='" & Trim(Left(Trim(.Cmb_Kjqj1.Text), 4)) + Trim(Right(Trim(.Cmb_Kjqj1.Text), 2)) & _
                        "' And (convert(char(4),kjyear)+convert(char(2),right((100+period),2)))<='" & Trim(Left(Trim(.Cmb_Kjqj2.Text), 4)) + Trim(Right(Trim(.Cmb_Kjqj2.Text), 2)) & "'"
                        Str_QueryInvoice = Str_QueryInvoice & " And (convert(char(4),banlanKjyear)+convert(char(2),right((100+banlanperiod),2)))>='" & Trim(Left(Trim(.Cmb_Kjqj1.Text), 4)) + Trim(Right(Trim(.Cmb_Kjqj1.Text), 2)) & _
                        "' And (convert(char(4),banlanKjyear)+convert(char(2),right((100+banlanperiod),2)))<='" & Trim(Left(Trim(.Cmb_Kjqj2.Text), 4)) + Trim(Right(Trim(.Cmb_Kjqj2.Text), 2)) & "'"
                        Set Rec_Kjqj = Cw_DataEnvi.DataConnect.Execute("Select Max(Kjyear) as MKjyear,Max(Period) as MPeriod From Cg_Account")
                        If Not IsNull(Rec_Kjqj.Fields("MKjyear")) Then
                            If Rec_Kjqj.Fields("MKjyear") >= S2N(Left(Trim(.Cmb_Kjqj1.Text), 4)) Then
                                Int_MYear = S2N(Left(Trim(.Cmb_Kjqj1.Text), 4))
                                If Rec_Kjqj.Fields("MPeriod") >= S2N(Right(Trim(.Cmb_Kjqj1.Text), 2)) Then
                                    Int_MPeriod = S2N(Right(Trim(.Cmb_Kjqj1.Text), 2))
                                Else
                                    Int_MPeriod = Rec_Kjqj.Fields("MPeriod")
                                End If
                            Else
                                Int_MYear = Rec_Kjqj.Fields("MKjyear")
                                Int_MPeriod = Rec_Kjqj.Fields("MPeriod")
                            End If
                        Else
                            Int_MYear = S2N(Left(Trim(.Cmb_Kjqj1.Text), 4))
                            Int_MPeriod = S2N(Right(Trim(.Cmb_Kjqj1.Text), 2))
                        End If
                        
                        Str_Temp = " where Kjyear =" & Int_MYear & " and Period =" & Int_MPeriod
                        Me.Lbl_Xslabel(0).Caption = Trim(.Cmb_Kjqj1.Text)
                        Me.Lbl_Xslabel(1).Caption = Trim(.Cmb_Kjqj2.Text)
                Case 2  '供应商
                    If Trim(.LrText(0).Text) <> "" Then
                        Str_QueryCondi = Str_QueryCondi & " and SupplierCode = '" & Trim(.LrText(0).Tag) & "'"
                        Str_QueryInvoice = Str_QueryInvoice & " and SupplierCode = '" & Trim(.LrText(0).Tag) & "'"
                        Str_Temp = Str_Temp & " and SupplierCode ='" & Trim(.LrText(0).Tag) & "'"
                        Me.Lbl_Xslabel(2).Caption = Trim(.LrText(0).Text)
                    Else
                        Me.Lbl_Xslabel(2).Caption = ""
                    End If
                Case 3  '物料编码
                    If Trim(.LrText(1).Text) <> "" Then
                        Str_QueryCondi = Str_QueryCondi & " and MNumber =  '" & Trim(.LrText(1).Text) & "'"
                        Str_QueryInvoice = Str_QueryInvoice & " and MNumber =  '" & Trim(.LrText(1).Text) & "'"
                        Str_Temp = Str_Temp & " and MNumber ='" & Trim(.LrText(1).Text) & "'"
                        Me.Lbl_Xslabel(3).Caption = Trim(.LrText(1).Text)
                    Else
                        Me.Lbl_Xslabel(3).Caption = ""
                    End If
            
            End Select
        Next jsqte
    End With
    
    

    '读取数据
    
    Set Rec_Temp = Cw_DataEnvi.DataConnect.Execute("Select * from Cg_V_AccountList " & Str_Temp)
    
    Sqlstr = "select SupplierName,MNumber,sum(Quantity) as SumBalanceQuan,sum(TotalMoneyBb) as SumBalanceMoney," & _
            "sum(DistributeCharge) as SumBalanceCharge,sum(InreasonWasteQuan)+sum(notInreasonWasteQuan) as SumBalanceWastageQuan," & _
            "sum(NotInreasonWasteMoney) as SumBalanceWastageMoney from Cg_V_InvoiceBill  " & Str_QueryInvoice & _
            " Group by SupplierName,MNumber "
    Set Rec_Invoice = Cw_DataEnvi.DataConnect.Execute(Sqlstr)
    Sqlstr = "select SupplierName,MNumber,MName,Model,PurUnitName,sum(Quantity) as SumInvoiceQuan,sum(TotalMoneyBb) as SumInvoiceMoneyBb from Cg_V_InvoiceBill  " & Str_QueryCondi & _
            " Group by SupplierName,MNumber,MName,Model,PurUnitName "

    Set Rec_Query = Cw_DataEnvi.DataConnect.Execute(Sqlstr)
    
    With Rec_Query
        CxbbGrid.Rows = CxbbGrid.FixedRows
        jsqte = CxbbGrid.FixedRows
        Do While Not .EOF
            If jsqte >= CxbbGrid.Rows Then
                CxbbGrid.AddItem ""
            End If
            '[>>自定义填充内容
            
            '判断是否有期初余额
            If Not Rec_Temp.EOF Or Not Rec_Temp.BOF Then
                Rec_Temp.MoveFirst
                Do While Not Rec_Temp.EOF
                    Bln_Start = False
                    If Trim(Rec_Temp.Fields("MNumber")) = Trim(.Fields("MNumber")) And Trim(Rec_Temp.Fields("SupplierName")) = Trim(.Fields("SupplierName")) Then
                        Bln_Start = True
                        Exit Do
                    End If
                    Rec_Temp.MoveNext
                Loop
            Else
                Bln_Start = False
            End If
            '判断是否有本期发生数量和结算数量
            If Not Rec_Invoice.EOF Or Not Rec_Invoice.BOF Then
                Rec_Invoice.MoveFirst
                Do While Not Rec_Invoice.EOF
                    Bln_Jsye = False
                    If Trim(Rec_Invoice.Fields("MNumber")) = Trim(.Fields("MNumber")) And Trim(Rec_Invoice.Fields("SupplierName")) = Trim(.Fields("SupplierName")) Then
                        Bln_Jsye = True
                        Exit Do
                    End If
                    Rec_Invoice.MoveNext
                Loop
            Else
                Bln_Jsye = False
            End If
            CxbbGrid.TextMatrix(jsqte, Sydz("001", GridStr(), Szzls)) = Trim(.Fields("SupplierName"))                                           '供应商
            CxbbGrid.TextMatrix(jsqte, Sydz("002", GridStr(), Szzls)) = Trim(.Fields("MNumber") & "")                                           '物料编码
            CxbbGrid.TextMatrix(jsqte, Sydz("003", GridStr(), Szzls)) = Trim(.Fields("MName") & "")                                             '物料名称
            CxbbGrid.TextMatrix(jsqte, Sydz("004", GridStr(), Szzls)) = Trim(.Fields("Model") & "")                                             '规格型号
            CxbbGrid.TextMatrix(jsqte, Sydz("005", GridStr(), Szzls)) = Trim(.Fields("PurUnitName") & "")                                       '计量单位
            If Bln_Start = True Then
                If S2N(Rec_Temp.Fields("StartWayQuan") & "") <> 0 Then
                    CxbbGrid.TextMatrix(jsqte, Sydz("006", GridStr(), Szzls)) = Trim(Rec_Temp.Fields("StartWayQuan") & "")                      '上期结余数量
                Else
                    CxbbGrid.TextMatrix(jsqte, Sydz("006", GridStr(), Szzls)) = ""                                                              '上期结余数量
                End If
                If S2N(Rec_Temp.Fields("StartWayMoney") & "") <> 0 Then
                    CxbbGrid.TextMatrix(jsqte, Sydz("007", GridStr(), Szzls)) = Trim(Rec_Temp.Fields("StartWayMoney") & "")                     '上期结余金额
                Else
                    CxbbGrid.TextMatrix(jsqte, Sydz("007", GridStr(), Szzls)) = ""                                                              '上期结余金额
                End If
            Else
                CxbbGrid.TextMatrix(jsqte, Sydz("006", GridStr(), Szzls)) = ""
                CxbbGrid.TextMatrix(jsqte, Sydz("007", GridStr(), Szzls)) = ""
            End If
            CxbbGrid.TextMatrix(jsqte, Sydz("008", GridStr(), Szzls)) = Trim(.Fields("SumInvoiceQuan") & "")                                    '本期采购数量
            CxbbGrid.TextMatrix(jsqte, Sydz("009", GridStr(), Szzls)) = Trim(.Fields("SumInvoiceMoneyBb") & "")                                 '本期采购金额
            If Bln_Jsye = True Then
                CxbbGrid.TextMatrix(jsqte, Sydz("010", GridStr(), Szzls)) = Trim(Rec_Invoice.Fields("SumBalanceQuan") & "")                     '本期结算数量
                CxbbGrid.TextMatrix(jsqte, Sydz("011", GridStr(), Szzls)) = Trim(Rec_Invoice.Fields("SumBalanceMoney") & "")                    '本期结算金额
                CxbbGrid.TextMatrix(jsqte, Sydz("012", GridStr(), Szzls)) = Trim(Rec_Invoice.Fields("SumBalanceCharge") & "")                   '本期结算费用
                CxbbGrid.TextMatrix(jsqte, Sydz("013", GridStr(), Szzls)) = Trim(Rec_Invoice.Fields("SumBalanceWastageQuan") & "")              '本期结算损耗数量
                CxbbGrid.TextMatrix(jsqte, Sydz("014", GridStr(), Szzls)) = Trim(Rec_Invoice.Fields("SumBalanceWastageMoney") & "")             '本期结算损耗金额
                CxbbGrid.TextMatrix(jsqte, Sydz("015", GridStr(), Szzls)) = S2N(CxbbGrid.TextMatrix(jsqte, Sydz("006", GridStr(), Szzls))) + S2N(Trim(.Fields("SumInvoiceQuan") & "")) - S2N(Trim(Rec_Invoice.Fields("SumBalanceQuan") & "")) - S2N(Trim(Rec_Invoice.Fields("SumBalanceWastageQuan") & ""))
                CxbbGrid.TextMatrix(jsqte, Sydz("016", GridStr(), Szzls)) = S2N(CxbbGrid.TextMatrix(jsqte, Sydz("007", GridStr(), Szzls))) + S2N(Trim(.Fields("SumInvoiceMoneyBb") & "")) - S2N(Trim(Rec_Invoice.Fields("SumBalanceMoney") & "")) - S2N(Trim(Rec_Invoice.Fields("SumBalanceWastageMoney") & "")) - S2N(Trim(Rec_Invoice.Fields("SumBalanceCharge") & ""))
            Else
                CxbbGrid.TextMatrix(jsqte, Sydz("010", GridStr(), Szzls)) = ""                                  '本期结算数量
                CxbbGrid.TextMatrix(jsqte, Sydz("011", GridStr(), Szzls)) = ""                                  '本期结算金额
                CxbbGrid.TextMatrix(jsqte, Sydz("012", GridStr(), Szzls)) = ""                                  '本期结算费用
                CxbbGrid.TextMatrix(jsqte, Sydz("013", GridStr(), Szzls)) = ""                                  '本期结算损耗数量
                CxbbGrid.TextMatrix(jsqte, Sydz("014", GridStr(), Szzls)) = ""                                  '本期结算损耗金额
                CxbbGrid.TextMatrix(jsqte, Sydz("015", GridStr(), Szzls)) = S2N(CxbbGrid.TextMatrix(jsqte, Sydz("006", GridStr(), Szzls))) + S2N(Trim(.Fields("SumInvoiceQuan") & ""))          '期末数量
                CxbbGrid.TextMatrix(jsqte, Sydz("016", GridStr(), Szzls)) = S2N(CxbbGrid.TextMatrix(jsqte, Sydz("007", GridStr(), Szzls))) + S2N(Trim(.Fields("SumInvoiceMoneyBb") & ""))       '期末金额
            End If
            
            
            '<<]
            '设置数据行高度(Fixed)
            CxbbGrid.RowHeight(jsqte) = Sjhgd
     
            '动态集指针加1,同时将计数器加1(Fixed)
            .MoveNext
            jsqte = jsqte + 1
        Loop
    End With

    ']以上为用户自定义部分

End Sub

Private Sub bbyl(bbylte As Boolean)                    '报表打印预览
    
    Dim Bbzbt$, Bbxbt() As String, bbxbtzzxs() As Integer, Bbxbtgs As Integer
    Dim Bbbwh() As String, Bbbwhzzxs() As Integer, Bbbwhgs As Integer
    Bbxbtgs = 2                                          '报 表 小 标 题 行 数
    Bbbwhgs = 0                                          '报 表 表 尾 行 数
    ReDim Bbxbt(1 To Bbxbtgs)
    ReDim bbxbtzzxs(1 To Bbxbtgs)
    If Bbbwhgs <> 0 Then
        ReDim Bbbwh(1 To Bbbwhgs)
        ReDim Bbbwhzzxs(1 To Bbbwhgs)
    End If
    Bbzbt = ReportTitle
    Bbxbt(1) = ""
    Bbxbt(2) = Space(4) + Fun_FormatOutPut(Me.Lbl_Tslabel(0) + Me.Lbl_Xslabel(0), 10)
    Bbxbt(2) = Bbxbt(2) + Fun_FormatOutPut(Me.Lbl_Tslabel(1) + Me.Lbl_Xslabel(1), 20) + Fun_FormatOutPut(Me.Lbl_Tslabel(2) + Me.Lbl_Xslabel(2), 20)
    Bbxbt(2) = Bbxbt(2) + Fun_FormatOutPut(Me.Lbl_Tslabel(3) + Me.Lbl_Xslabel(3), 20)
    bbxbtzzxs(1) = 0                                     '报表行组织形式(0-居左 1-居中 2-居右)
    Call Scyxsjb(CxbbGrid)                               '生成报表数据
    Call Scdybb(Dyymctbl, Bbzbt, Bbxbt(), bbxbtzzxs(), Bbxbtgs, Bbbwh(), Bbbwhzzxs(), Bbbwhgs, bbylte)
    If Not bbylte Then
        Unload DY_Tybbyldy
    End If

End Sub