VB开发的ERP系统

     
    Xt_Wait.Hide

End Sub

Private Sub Sub_Query()                                    '生成查询结果
    
    Dim Rec_Query As New ADODB.Recordset        '查询结果动态集
    Dim Sqlstr As String                        '查询字符串
    Dim SqlStr1 As String
    Dim Coljsq As Long                          '网格列计数器
    Dim Jsqte As Integer                        '临时动态计数器

    '以下为自定义部分[
    With Book_CardList_Search
        '生成查询条件
        Card_Str = " where 1=1 "
        For Jsqte = 1 To 12
            Select Case Jsqte
                Case 1      '卡片开始录入日期
                    If Trim(.LrText(0).Text) <> "" Then
                        Card_Str = Card_Str & " And Gdzc_Card.writedate>=' " & Trim(.LrText(0).Text) & "'"
                    End If
                Case 2      '卡片录入终止日期
                    If Trim(.LrText(1).Text) <> "" Then
                        Card_Str = Card_Str & " and Gdzc_Card.writedate <= '" & Trim(.LrText(1).Text) & "'"
                    End If
                Case 3      '卡片编号（开始）
                    If Trim(.LrText(2).Text) <> "" Then
                        Card_Str = Card_Str & " and Gdzc_Card.Cardcode>='" & Trim(.LrText(2).Text) & "'"
                    End If
                Case 4      '卡片编号（终止）
                    If Trim(.LrText(3).Text) <> "" Then
                        Card_Str = Card_Str & " and Gdzc_Card.cardcode<='" & Trim(.LrText(3).Text) & "'"
                    End If
                Case 5      '资产编号
                    If Trim(.LrText(4).Text) <> "" Then
                        Card_Str = Card_Str & " and Gdzc_Card.FACode>='" & (Trim(.LrText(4).Text)) & "'"
                    End If
                Case 6      '资产编号
                    If Trim(.LrText(5).Text) <> "" Then
                        Card_Str = Card_Str & " and Gdzc_Card.FACode<='" & (Trim(.LrText(5).Text)) & "'"
                    End If
                Case 7      '资产类别编号
                    If Trim(.LrText(6).Text) <> "" Then
                        Card_Str = Card_Str & " and Gdzc_Card.FASortCode like '" & Trim(.LrText(6).Tag) & "%'"
                    End If
                Case 8      '部门编号
                    If Trim(.LrText(7).Text) <> "" Then
                        Card_Str = Card_Str & " and Gdzc_Card.DeptCode like '" & Trim(.LrText(7).Tag) & "%'"
                    End If
                Case 9      '设备位号
                    If Trim(.LrText(8).Text) <> "" Then
                        Card_Str = Card_Str & " and Gdzc_Card.setlocaNum='" & Trim(.LrText(8).Text) & "'"
                    End If
                Case 11     '只查询新增资产
                    If .Opt_New.Value = True Then
                        Card_Str = Card_Str & " and Gdzc_Card.whethernew='1' "
                    End If
            End Select
        Next Jsqte
    End With
 
    If Book_CardList_Search.Opt_Lessen.Value = True Then                '单独处理减少资产
        Sqlstr = "SELECT DISTINCT Gdzc_Variation.CardCode, Gy_Department.DeptName, Gdzc_Card.FACode, " _
            & "Gdzc_Card.FAName, Gdzc_Card.SpecificationMode, Gdzc_Card.MeasureUnit, " _
            & "Gdzc_Variation.FAQuantityOld, Gdzc_Variation.FAQuantityNew, " _
            & "Gdzc_Variation.VariationReason, Gdzc_Variation.FAValueOld, " _
            & "Gdzc_Variation.FAValueNew, Gdzc_Card.UseYears, Gdzc_Variation.DeprMethOld, " _
            & "Gdzc_Card.BeginUseDate, Gdzc_Card.SetLocaNum, Gdzc_Variation.VariDate, " _
            & "Gdzc_Sort.FASortName , Gdzc_VariationMode.FAVariName FROM Gdzc_Sort RIGHT OUTER JOIN " _
            & "Gdzc_Card ON Gdzc_Sort.FASortCode = Gdzc_Card.FASortCode RIGHT OUTER JOIN " _
            & "Gdzc_Variation LEFT OUTER JOIN Gy_Department ON Gdzc_Variation.DeptOld = Gy_Department.DeptCode ON " _
            & "Gdzc_Card.CardCode = Gdzc_Variation.CardCode LEFT OUTER JOIN " _
            & "Gdzc_VariationMode ON Gdzc_Variation.FAVariCode = Gdzc_VariationMode.FAVariCode " _
            & "WHERE (Gdzc_VariationMode.VariSort = 2)"
            CxbbGrid.TextMatrix(TopRow, 7) = "减少数量"
            CxbbGrid.TextMatrix(TopRow, 8) = "减少原因"
            CxbbGrid.TextMatrix(TopRow, 16) = "减少日期"
    Else
            Sqlstr = "SELECT Gdzc_Card.CardCode AS CardCode, Gdzc_Sort.FASortName, " _
                & "Gy_Department.DeptName, Gdzc_Card.FACode, Gdzc_Card.FAName, " _
                & "Gdzc_Card.SpecificationMode, Gdzc_Card.MeasureUnit, " _
                & "Gdzc_Card.FAQuantity, Gdzc_VariationMode.FAVariName, " _
                & "Gdzc_Card.FAValue, Gdzc_Card.UseYears, Gdzc_Card.DeprSum, " _
                & "Gdzc_Card.FactValue, Gdzc_Card.DeprMethod,Gdzc_Card.BeginUseDate, Gdzc_Card.SetLocaNum, " _
                & "Gdzc_Card.WriteDate FROM Gdzc_Card LEFT OUTER JOIN " _
                & "Gdzc_VariationMode ON Gdzc_Card.FAVariCode = Gdzc_VariationMode.FAVariCode LEFT OUTER JOIN " _
                & "Gy_Department ON Gdzc_Card.DeptCode = Gy_Department.DeptCode LEFT OUTER JOIN " _
                & "Gdzc_Sort ON Gdzc_Card.FASortCode = Gdzc_Sort.FASortCode"
            Sqlstr = Sqlstr & Card_Str & " and FactValue>0 ORDER BY CardCode"
            CxbbGrid.TextMatrix(TopRow, 7) = "资产数量"
            CxbbGrid.TextMatrix(TopRow, 8) = "增加方式"
            CxbbGrid.TextMatrix(TopRow, 16) = "录入日期"
    End If
    Set Rec_Query = Cw_DataEnvi.DataConnect.Execute(Sqlstr)
    With Rec_Query
        CxbbGrid.Rows = CxbbGrid.FixedRows
        CxbbGrid.Rows = CxbbGrid.FixedRows + .RecordCount
        Jsqte = CxbbGrid.FixedRows
        
        Do While Not .EOF
            If Jsqte >= CxbbGrid.Rows Then
                CxbbGrid.AddItem ""
            End If
            
            CxbbGrid.TextMatrix(Jsqte, Sydz("001", GridStr(), Szzls)) = Trim(.Fields("CardCode") & "")                                          '卡片编号
            CxbbGrid.TextMatrix(Jsqte, Sydz("002", GridStr(), Szzls)) = Trim(.Fields("FASortName") & "")                                        '资产类别
            CxbbGrid.TextMatrix(Jsqte, Sydz("003", GridStr(), Szzls)) = Trim(.Fields("DeptName") & "")                                          '部门名称
            CxbbGrid.TextMatrix(Jsqte, Sydz("004", GridStr(), Szzls)) = Trim(.Fields("FACode") & "")                                            '资产编号
            CxbbGrid.TextMatrix(Jsqte, Sydz("005", GridStr(), Szzls)) = Trim(.Fields("FAName") & "")                                            '资产名称
            CxbbGrid.TextMatrix(Jsqte, Sydz("006", GridStr(), Szzls)) = Trim(.Fields("SpecificationMode") & "")                                 '设备位号
            CxbbGrid.TextMatrix(Jsqte, Sydz("007", GridStr(), Szzls)) = Trim(.Fields("MeasureUnit"))                                            '计量单位
            If Book_CardList_Search.Opt_Lessen.Value = True Then
                CxbbGrid.TextMatrix(Jsqte, Sydz("008", GridStr(), Szzls)) = Trim(Val(!FAQuantityOld & "") - Val(!FAQuantityNew & ""))           '资产数量
            Else
                CxbbGrid.TextMatrix(Jsqte, Sydz("008", GridStr(), Szzls)) = Trim(.Fields("FAQuantity"))
            End If
            If Book_CardList_Search.Opt_Lessen.Value = True Then
                CxbbGrid.TextMatrix(Jsqte, Sydz("009", GridStr(), Szzls)) = Trim(!VariationReason & "")                                         '增加方式
            Else
                CxbbGrid.TextMatrix(Jsqte, Sydz("009", GridStr(), Szzls)) = Trim(.Fields("FAVariName"))
            End If
            If Book_CardList_Search.Opt_Lessen.Value = True Then
                If Val(Val(!FAValueOld & "") - Val(!FAValueNew & "")) <> 0 Then CxbbGrid.TextMatrix(Jsqte, Sydz("010", GridStr(), Szzls)) = Trim(Val(!FAValueOld & "") - Val(!FAValueNew & ""))
            Else
                If Val(.Fields("FAValue")) <> 0 Then CxbbGrid.TextMatrix(Jsqte, Sydz("010", GridStr(), Szzls)) = Trim(.Fields("FAValue") & "")  '资产原值
            End If
            If Val(.Fields("UseYears")) <> 0 Then CxbbGrid.TextMatrix(Jsqte, Sydz("011", GridStr(), Szzls)) = Trim(.Fields("UseYears") & "")    '使用年限
            If Book_CardList_Search.Opt_Lessen.Value = True Then
                If Val(Val(!FAValueOld & "") - Val(!FAValueNew & "")) <> 0 Then CxbbGrid.TextMatrix(Jsqte, Sydz("012", GridStr(), Szzls)) = Trim(Val(!FAValueOld & "") - Val(!FAValueNew & ""))
            Else
                If Val(.Fields("DeprSum")) <> 0 Then CxbbGrid.TextMatrix(Jsqte, Sydz("012", GridStr(), Szzls)) = Trim(.Fields("DeprSum"))       '累计折旧
            End If
            If Book_CardList_Search.Opt_Lessen.Value = True Then
                CxbbGrid.TextMatrix(Jsqte, Sydz("013", GridStr(), Szzls)) = ""                                                                  '净资产
            Else
                CxbbGrid.TextMatrix(Jsqte, Sydz("013", GridStr(), Szzls)) = Trim(.Fields("FactValue"))
            End If
            If Book_CardList_Search.Opt_Lessen.Value = True Then
                Select Case !DeprMethOld                                                                                                        '折旧方法
                    Case "01"
                        CxbbGrid.TextMatrix(Jsqte, Sydz("014", GridStr(), Szzls)) = "不计提折旧"
                    Case "02"
                        CxbbGrid.TextMatrix(Jsqte, Sydz("014", GridStr(), Szzls)) = "平均年限法(依净资产计提折旧)"
                    Case "03"
                        CxbbGrid.TextMatrix(Jsqte, Sydz("014", GridStr(), Szzls)) = "平均年限法(依帐面原值计提折旧)"
                    Case "04"
                        CxbbGrid.TextMatrix(Jsqte, Sydz("014", GridStr(), Szzls)) = "工作量法"
                    Case "05"
                        CxbbGrid.TextMatrix(Jsqte, Sydz("014", GridStr(), Szzls)) = "固定折旧额折旧法"
                    Case "06"
                        CxbbGrid.TextMatrix(Jsqte, Sydz("014", GridStr(), Szzls)) = "年数总和法"
                    Case "07"
                        CxbbGrid.TextMatrix(Jsqte, Sydz("014", GridStr(), Szzls)) = "双倍余额法"
                End Select
            Else
                Select Case .Fields("DeprMethod")                                                                                               '折旧方法
                    Case "01"
                        CxbbGrid.TextMatrix(Jsqte, Sydz("014", GridStr(), Szzls)) = "不计提折旧"
                    Case "02"
                        CxbbGrid.TextMatrix(Jsqte, Sydz("014", GridStr(), Szzls)) = "平均年限法(依净资产计提折旧)"
                    Case "03"
                        CxbbGrid.TextMatrix(Jsqte, Sydz("014", GridStr(), Szzls)) = "平均年限法(依帐面原值计提折旧)"
                    Case "04"
                        CxbbGrid.TextMatrix(Jsqte, Sydz("014", GridStr(), Szzls)) = "工作量法"
                    Case "05"
                        CxbbGrid.TextMatrix(Jsqte, Sydz("014", GridStr(), Szzls)) = "固定折旧额折旧法"
                    Case "06"
                        CxbbGrid.TextMatrix(Jsqte, Sydz("014", GridStr(), Szzls)) = "年数总和法"
                    Case "07"
                        CxbbGrid.TextMatrix(Jsqte, Sydz("014", GridStr(), Szzls)) = "双倍余额法"
                End Select
            End If
            CxbbGrid.TextMatrix(Jsqte, Sydz("015", GridStr(), Szzls)) = Format(.Fields("BeginUseDate"), "yyyy-mm-dd")                           '开始使用日期
            CxbbGrid.TextMatrix(Jsqte, Sydz("016", GridStr(), Szzls)) = Trim(.Fields("SetLocaNum") & "")                                        '设备位号
            If Book_CardList_Search.Opt_Lessen.Value = True Then
                CxbbGrid.TextMatrix(Jsqte, Sydz("017", GridStr(), Szzls)) = Format(!varidate, "yyyy-mm-dd")                                     '录入日期
            Else
                CxbbGrid.TextMatrix(Jsqte, Sydz("017", GridStr(), Szzls)) = Format(.Fields("WriteDate"), "yyyy-mm-dd")                          '录入日期
            End If
            CxbbGrid.RowHeight(Jsqte) = Sjhgd
            .MoveNext
            Jsqte = Jsqte + 1
        Loop
    End With
    
    '合计处理
    If CxbbGrid.Rows > CxbbGrid.FixedRows Then
        Set rstemp = New ADODB.Recordset
        SqlStr1 = "select sum(FAValue) as FAValue,sum(DeprSum) as DeprSum,sum(FactValue) as FactValue from Gdzc_Card "
        SqlStr1 = SqlStr1 & Card_Str
        rstemp.Open SqlStr1, Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
        With CxbbGrid
            .Rows = .Rows + 1
            .TextMatrix(.Rows - 1, 0) = "合  计"
            .TextMatrix(.Rows - 1, 9) = Trim(rstemp!FAValue & "")                                                                           '资产原值
            .TextMatrix(.Rows - 1, 11) = Trim(rstemp!DeprSum & "")                                                                          '累计折旧
            .TextMatrix(.Rows - 1, 12) = Trim(rstemp!FactValue & "")                                                                        '净资产
            For Row_Num = 0 To .Cols - 1
                .Cell(flexcpBackColor, .Rows - 1, Row_Num) = &HFFFF00
            Next Row_Num
        End With
    End If
    
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 = 1                                          '报 表 小 标 题 行 数
    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) = " "
    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