VB开发的ERP系统

            Else
                Year = !Year
                Period = !Period - 1
            End If
            
            SqlStr1 = "SELECT Gdzc_Total.[Year], Gdzc_Total.Period, Gdzc_Sort.FASortName, " _
                & "SUM(Gdzc_Total.FAValueEndM) AS FAValueEndM, " _
                & "SUM(Gdzc_DetailedForm.DeprValue) AS DeprValue, SUM(Gdzc_Total.FAValueInM) " _
                & "AS FAValueInM, SUM(Gdzc_Total.FAValueOutM) AS FAValueOutM " _
                & "FROM Gdzc_Total LEFT OUTER JOIN " _
                & "Gdzc_DetailedForm ON Gdzc_Total.Period = Gdzc_DetailedForm.Period AND " _
                & "Gdzc_Total.[Year] = Gdzc_DetailedForm.[Year] AND " _
                & "Gdzc_Total.FASortCode = Gdzc_DetailedForm.FASortCode LEFT OUTER JOIN " _
                & "Gdzc_Sort ON Gdzc_Total.FASortCode = Gdzc_Sort.FASortCode " _
                & "where Gdzc_Total.Year=" & Val(Year) & " and Gdzc_Total.Period=" & Val(Period) _
                & " and Gdzc_Sort.FASortName='" & Trim(!FASortName) & "'" _
                & " GROUP BY Gdzc_Total.[Year], Gdzc_Total.Period, Gdzc_Sort.FASortName"
            Set rstemp = Cw_DataEnvi.DataConnect.Execute(SqlStr1)
            If Not rstemp.EOF Then
                CxbbGrid.TextMatrix(Jsqte, Sydz("004", GridStr(), Szzls)) = Trim(rstemp!DeprValue & "")                                 '上月折旧额
                CxbbGrid.TextMatrix(Jsqte, Sydz("005", GridStr(), Szzls)) = Trim(rstemp!FAValueInM & "")                                '上月增加原值
                CxbbGrid.TextMatrix(Jsqte, Sydz("006", GridStr(), Szzls)) = Trim(rstemp!FAValueOutM & "")                               '上月减少原值
                CxbbGrid.TextMatrix(Jsqte, Sydz("009", GridStr(), Szzls)) = Trim(rstemp!FAvalueEndM & "")                               '上月资产原值
                CxbbGrid.TextMatrix(Jsqte, Sydz("003", GridStr(), Szzls)) = Trim(Format(Val(Val(rstemp!DeprValue) / Val(rstemp!FAvalueEndM)) * 100, "##0.00") & "")         '月分类折旧率
            End If
            rstemp.Close
            Set rstemp = Nothing
            CxbbGrid.TextMatrix(Jsqte, Sydz("001", GridStr(), Szzls)) = Trim(!Year & "." & Format(!Period, "00") & "")                               '会计期间
            CxbbGrid.TextMatrix(Jsqte, Sydz("008", GridStr(), Szzls)) = Trim(!DeprValue & "")                                           '本月折旧额
            CxbbGrid.TextMatrix(Jsqte, Sydz("010", GridStr(), Szzls)) = Trim(!FAvalueEndM & "")                                         '本月资产原值
            CxbbGrid.TextMatrix(Jsqte, Sydz("002", GridStr(), Szzls)) = Trim(!FASortName & "")                                          '类别名称
            CxbbGrid.TextMatrix(Jsqte, Sydz("007", GridStr(), Szzls)) = Trim(Val(Val(!DeprValue) - Val(CxbbGrid.TextMatrix(Jsqte, Sydz("004", GridStr(), Szzls)))) & "")    '应增减折旧额
            CxbbGrid.RowHeight(Jsqte) = Sjhgd
            .MoveNext
            Jsqte = Jsqte + 1
            
            '合计行处理
            If Not .EOF Then
                If Trim(!Year & "." & Format(!Period, "00") & "") <> Trim(CxbbGrid.TextMatrix(Jsqte - 1, Sydz("001", GridStr(), Szzls))) Then
                    year1 = LeftChar(CxbbGrid.TextMatrix(Jsqte - 1, Sydz("001", GridStr(), Szzls)))
                    period1 = RightChar(CxbbGrid.TextMatrix(Jsqte - 1, Sydz("001", GridStr(), Szzls)))
                    
                    '上月原值和本月原值合计
                    SqlStr1 = "SELECT Year, Period, SUM(FAValueEndM) AS FAValueEndM, SUM(FAValueStartM) " _
                        & "AS FAValueStartM From Gdzc_Total where Year=" & Val(year1) & " and Period=" & Val(period1) _
                        & "GROUP BY Year,Period"
                    Set rstemp = Cw_DataEnvi.DataConnect.Execute(SqlStr1)
                    If Not rstemp.EOF Then
                        CxbbGrid.TextMatrix(Jsqte, Sydz("001", GridStr(), Szzls)) = "本期合计"                                          '会计期间
                        CxbbGrid.TextMatrix(Jsqte, Sydz("009", GridStr(), Szzls)) = Trim(rstemp!FAValueStartM & "")                     '上月资产原值
                        CxbbGrid.TextMatrix(Jsqte, Sydz("010", GridStr(), Szzls)) = Trim(rstemp!FAvalueEndM & "")                       '本月资产原值
                    End If
                    rstemp.Close
                    Set rstemp = Nothing
                    
                    '本月折旧
                    Set rstemp = New ADODB.Recordset
                    rstemp.Open "SELECT SUM(DeprValue) AS deprValue From Gdzc_DetailedForm where year=" & Val(year1) & " and period=" & Val(period1) & " GROUP BY [Year], Period", Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
                    If Not rstemp.EOF Then
                        CxbbGrid.TextMatrix(Jsqte, Sydz("008", GridStr(), Szzls)) = Trim(rstemp!DeprValue & "")                         '本月折旧额
                    End If
                    rstemp.Close
                    Set rstemp = Nothing
                    
                    '上个期间
                    If Val(period1) = 1 Then
                        year1 = year1 - 1
                        period1 = 12
                    Else
                        period1 = period1 - 1
                    End If
                    
                    '上月折旧额
                    Set rstemp = New ADODB.Recordset
                    rstemp.Open "SELECT SUM(DeprValue) AS deprValue From Gdzc_DetailedForm where year=" & Val(year1) & " and period=" & Val(period1) & " GROUP BY [Year], Period", Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
                    If Not rstemp.EOF Then
                        CxbbGrid.TextMatrix(Jsqte, Sydz("004", GridStr(), Szzls)) = Trim(rstemp!DeprValue & "")                         '上月折旧额
                    End If
                    rstemp.Close
                    Set rstemp = Nothing
                    
                    '上月原值增加和上月原值减少
                    Set rstemp = New ADODB.Recordset
                    rstemp.Open "SELECT SUM(FAValueInM) AS FAValueInM, SUM(FAValueOutM) AS FAValueOutM " _
                        & "From Gdzc_Total where year=" & Val(year1) & " and Period=" & Val(period1) & "GROUP BY [Year], Period", _
                        Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
                    If Not rstemp.EOF Then
                        CxbbGrid.TextMatrix(Jsqte, Sydz("005", GridStr(), Szzls)) = Trim(rstemp!FAValueInM & "")                        '上月增加原值
                        CxbbGrid.TextMatrix(Jsqte, Sydz("006", GridStr(), Szzls)) = Trim(rstemp!FAValueOutM & "")                       '上月减少原值
                    End If
                    rstemp.Close
                    Set rstemp = Nothing
                    
                    '应增减折旧额
                    CxbbGrid.TextMatrix(Jsqte, Sydz("007", GridStr(), Szzls)) = Trim(Val(Val(CxbbGrid.TextMatrix(Jsqte, Sydz("008", GridStr(), Szzls))) - Val(CxbbGrid.TextMatrix(Jsqte, Sydz("004", GridStr(), Szzls)))) & "")     '应增减折旧额
                    
                    For Row_Num = 0 To CxbbGrid.Cols - 1
                        CxbbGrid.Cell(flexcpBackColor, Jsqte, Row_Num) = &HFFFF00
                    Next Row_Num
                            
                    Jsqte = Jsqte + 1
                End If
            Else
                year1 = LeftChar(CxbbGrid.TextMatrix(Jsqte - 1, Sydz("001", GridStr(), Szzls)))
                period1 = RightChar(CxbbGrid.TextMatrix(Jsqte - 1, Sydz("001", GridStr(), Szzls)))
                
                '上月原值和本月原值合计
                SqlStr1 = "SELECT [Year], Period, SUM(FAValueEndM) AS FAValueEndM,SUM(FAValueStartM) " _
                    & "AS FAValueStartM From Gdzc_Total where Year=" & Val(year1) & " and Period=" & Val(period1) _
                    & "GROUP BY Year,Period"
                Set rstemp = Cw_DataEnvi.DataConnect.Execute(SqlStr1)
                If Not rstemp.EOF Then
                    CxbbGrid.TextMatrix(Jsqte, Sydz("001", GridStr(), Szzls)) = "本期合计"                                              '会计期间
                    CxbbGrid.TextMatrix(Jsqte, Sydz("009", GridStr(), Szzls)) = Trim(rstemp!FAValueStartM & "")                         '上月资产原值
                    CxbbGrid.TextMatrix(Jsqte, Sydz("010", GridStr(), Szzls)) = Trim(rstemp!FAvalueEndM & "")                           '本月资产原值
                End If
                rstemp.Close
                Set rstemp = Nothing
                
                '本月折旧
                Set rstemp = New ADODB.Recordset
                rstemp.Open "SELECT SUM(DeprValue) AS deprValue From Gdzc_DetailedForm where year=" & Val(year1) & " and period=" & Val(period1) & " GROUP BY [Year], Period", Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
                If Not rstemp.EOF Then
                    CxbbGrid.TextMatrix(Jsqte, Sydz("008", GridStr(), Szzls)) = Trim(rstemp!DeprValue & "")                             '本月折旧额
                End If
                rstemp.Close
                Set rstemp = Nothing
                
                
                '上个期间
                If Val(period1) = 1 Then
                    year1 = year1 - 1
                    period1 = 12
                Else
                    period1 = period1 - 1
                End If
                
                '上月折旧额
                Set rstemp = New ADODB.Recordset
                rstemp.Open "SELECT SUM(DeprValue) AS deprValue From Gdzc_DetailedForm where year=" & Val(year1) & " and period=" & Val(period1) & " GROUP BY [Year], Period", Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
                If Not rstemp.EOF Then
                    CxbbGrid.TextMatrix(Jsqte, Sydz("004", GridStr(), Szzls)) = Trim(rstemp!DeprValue & "")                             '上月折旧额
                End If
                rstemp.Close
                Set rstemp = Nothing
                
                '上月原值增加和上月原值减少
                Set rstemp = New ADODB.Recordset
                rstemp.Open "SELECT SUM(FAValueInM) AS FAValueInM, SUM(FAValueOutM) AS FAValueOutM " _
                    & "From Gdzc_Total where year=" & Val(year1) & " and Period=" & Val(period1) & "GROUP BY [Year], Period", _
                    Cw_DataEnvi.DataConnect, adOpenDynamic, adLockOptimistic
                If Not rstemp.EOF Then
                    CxbbGrid.TextMatrix(Jsqte, Sydz("005", GridStr(), Szzls)) = Trim(rstemp!FAValueInM & "")                            '上月增加原值
                    CxbbGrid.TextMatrix(Jsqte, Sydz("006", GridStr(), Szzls)) = Trim(rstemp!FAValueOutM & "")                           '上月减少原值
                End If
                rstemp.Close
                Set rstemp = Nothing
                
                '应增减折旧额
                CxbbGrid.TextMatrix(Jsqte, Sydz("007", GridStr(), Szzls)) = Trim(Val(Val(CxbbGrid.TextMatrix(Jsqte, Sydz("008", GridStr(), Szzls))) - Val(CxbbGrid.TextMatrix(Jsqte, Sydz("004", GridStr(), Szzls)))) & "")     '应增减折旧额
                
                For Row_Num = 0 To CxbbGrid.Cols - 1
                    CxbbGrid.Cell(flexcpBackColor, Jsqte, Row_Num) = &HFFFF00
                Next Row_Num
                        
                Jsqte = Jsqte + 1
            End If
        Loop
    End With
    
    '为零时清空单元格
    Call Txt_Clear

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

'*****************************************************
'[>>自定义函数
'取右字符串
Function RightChar(str As String) As String
    
    If str = "" Then Exit Function
    Dim i As Integer
    i = InStrRev(str, ".")
    RightChar = Mid(str, i + 1)

End Function

'取左字符串
Function LeftChar(str As String) As String
    
    If str = "" Then Exit Function
    Dim i As Integer
    i = InStr(str, ".")
    LeftChar = Mid(str, 1, i - 1)

End Function

'为零时清空单元格
Function Txt_Clear()
    
    Dim Row_Integer, Col_Integer As Integer         '行数和列数变量
    
    For Row_Integer = CxbbGrid.FixedRows To CxbbGrid.Rows - 1
        For Col_Integer = 2 To CxbbGrid.Cols - 1
            If Val(CxbbGrid.TextMatrix(Row_Integer, Col_Integer)) = 0 Then
                CxbbGrid.TextMatrix(Row_Integer, Col_Integer) = ""
            End If
        Next Col_Integer
    Next Row_Integer

End Function
'<<]
'****************************************************