frmfi_zznew.frm

一个用VB写的财务软件源码

            sWhere = sWhere + " and bmdm like '" + sBm + "%'"
        End If
    End If
    
    sMonth = Format(sKJRQ, "mm")
    
    GetMyFS = 0
    If sQj = "月" Then          '按月取数
       If sFx = "借" Then       '按借
            If sMonth = "01" Then  '当为一月份时，发生额＝（一月份累计借）－（年初余额借）
               rstTmp.Open "select sum(ljj" & Trim(sFlag) & "01-ljj" & Trim(sFlag) & "00) from " + sTableName & glo.sOperateYear & " where " & _
               sWhere, glo.cnnMain, adOpenStatic, adLockReadOnly
               If rstTmp.EOF = False Then
                    If Not IsNull(rstTmp.Fields(0).value) Then
                         GetMyFS = rstTmp.Fields(0).value
                    End If
               End If
                Exit Function
            Else
               sPreMonth = Format(yfdel(sKJRQ), "mm")       '否则发生额＝本月累计借－上月累计借
               rstTmp.Open "select sum(ljj" & Trim(sFlag) & sMonth & "-ljj" & Trim(sFlag) & sPreMonth & ") from " + sTableName & glo.sOperateYear & _
                            " where " + sWhere, glo.cnnMain, adOpenStatic, adLockReadOnly
               If rstTmp.EOF = False Then
                    If Not IsNull(rstTmp.Fields(0).value) Then
                         GetMyFS = rstTmp.Fields(0).value
                    End If
               End If
               Exit Function
            End If
       End If
       
       If sFx = "贷" Then       '按贷
            If sMonth = "01" Then  '当为一月份时，发生额＝（一月份累计贷）－（年初余额贷）
               rstTmp.Open "select sum(ljd" & Trim(sFlag) & "01-ljd" & Trim(sFlag) & "00) from " + sTableName & glo.sOperateYear & " where " & _
               sWhere, glo.cnnMain, adOpenStatic, adLockReadOnly
               If rstTmp.EOF = False Then
                    If Not IsNull(rstTmp.Fields(0).value) Then
                         GetMyFS = rstTmp.Fields(0).value
                    End If
               End If
               Exit Function
            Else
               sPreMonth = Format(yfdel(sKJRQ), "mm")       '否则发生额＝本月累计贷－上月累计贷
               rstTmp.Open "select sum(ljd" & Trim(sFlag) & sMonth & "-ljd" & Trim(sFlag) & sPreMonth & ") from " + sTableName & glo.sOperateYear & _
                            " where " + sWhere, glo.cnnMain, adOpenStatic, adLockReadOnly
               If rstTmp.EOF = False Then
                    If Not IsNull(rstTmp.Fields(0).value) Then
                         GetMyFS = rstTmp.Fields(0).value
                    End If
               End If
               Exit Function
            End If
       End If
    End If
    '---------------------------年发生额（１２月累计数－年初数）
    If sQj = "年" Then
        If sFx = "借" Then             '12月累计借－年初数借
            rstTmp.Open "select sum(ljj" & Trim(sFlag) & "12-ljj" & Trim(sFlag) & "00) from " + sTableName & glo.sOperateYear & " where " & _
               sWhere, glo.cnnMain, adOpenStatic, adLockReadOnly
               If rstTmp.EOF = False Then
                        If Not IsNull(rstTmp.Fields(0).value) Then
                             GetMyFS = rstTmp.Fields(0).value
                        End If
               End If
               Exit Function
        End If
        
        If sFx = "贷" Then             '12月累计贷－年初数贷
            rstTmp.Open "select sum(ljd" & Trim(sFlag) & "12-ljd" & Trim(sFlag) & "00) from " + sTableName & glo.sOperateYear & " where " & _
               sWhere, glo.cnnMain, adOpenStatic, adLockReadOnly
               If rstTmp.EOF = False Then
               If Not IsNull(rstTmp.Fields(0).value) Then
                    GetMyFS = rstTmp.Fields(0).value
               End If
               End If
               Exit Function
        End If
        
    End If
    
End Function

Private Function GetMyLFS(ByVal sKm As String, ByVal sQj As String, ByVal sFx As String, sFlag As String, ByVal sBm As String, ByVal sXm As String) As Double   '取累计发生
    Dim rstTmp As New ADODB.Recordset
    Dim sMonth As String
    Dim sTableName As String
    Dim sWhere As String
    
    If Trim$(sBm + sXm) = "" Then
        sTableName = "tZW_Balance"
        sWhere = "kmdm='" & sKm & "'"
    Else
        sTableName = "tUSU_Fz"
        sWhere = "kmdm like '" & sKm & "%'"
        If Trim$(sXm) <> "" Then
            sXm = "↑" + sXm
            sWhere = sWhere + " and xmdm like '%" + sXm + "%'"
        End If
        If Trim$(sBm) <> "" Then
            sWhere = sWhere + " and bmdm like '" + sBm + "%'"
        End If
    End If
    sMonth = Format(sKJRQ, "mm")
    GetMyLFS = 0
    If sQj = "月" Then          '按月取数
       If sFx = "借" Then       '按借
                    '发生额＝本月累计借－年初累计借
               rstTmp.Open "select sum(ljj" & Trim(sFlag) & sMonth & "-ljj" & Trim(sFlag) & "00) from " + sTableName & glo.sOperateYear & _
                            " where " + sWhere, glo.cnnMain, adOpenStatic, adLockReadOnly
            If rstTmp.EOF Then Exit Function
               If Not IsNull(rstTmp.Fields(0).value) Then
                    GetMyLFS = rstTmp.Fields(0).value
               End If
               Exit Function
       End If
       
       If sFx = "贷" Then       '按贷
                  '发生额＝本月累计贷－年初累计贷
               rstTmp.Open "select sum(ljd" & Trim(sFlag) & sMonth & "-ljd" & Trim(sFlag) & "00) from " + sTableName & glo.sOperateYear & _
                            " where " + sWhere, glo.cnnMain, adOpenStatic, adLockReadOnly
            If rstTmp.EOF Then Exit Function
               If Not IsNull(rstTmp.Fields(0).value) Then
                    GetMyLFS = rstTmp.Fields(0).value
               End If
               Exit Function
       End If
    End If
'----------------------------按年(即全年累计发生额）
    If sQj = "年" Then
        If sFx = "借" Then             '12月累计借－年初数借
            rstTmp.Open "select sum(ljj" & Trim(sFlag) & "12-ljj" & Trim(sFlag) & "00) from " + sTableName & glo.sOperateYear & " where " & _
               sWhere, glo.cnnMain, adOpenStatic, adLockReadOnly
            If rstTmp.EOF Then Exit Function
               If Not IsNull(rstTmp.Fields(0).value) Then
                    GetMyLFS = rstTmp.Fields(0).value
               End If
               Exit Function
        End If
        
        If sFx = "贷" Then             '12月累计贷－年初数贷
            rstTmp.Open "select sum(ljd" & Trim(sFlag) & "12-ljd" & Trim(sFlag) & "00) from " + sTableName & glo.sOperateYear & " where " & _
               sWhere, glo.cnnMain, adOpenStatic, adLockReadOnly
            If rstTmp.EOF Then Exit Function
               If Not IsNull(rstTmp.Fields(0).value) Then
                    GetMyLFS = rstTmp.Fields(0).value
               End If
               Exit Function
        End If
        
    End If
End Function

Private Function GetMyJFS(ByVal sKm As String, ByVal sQj As String, ByVal sFx As String, sFlag As String, ByVal sBm As String, ByVal sXm As String) As Double '取净发生
    Dim rstTmp As New ADODB.Recordset
    Dim sMonth As String
    Dim sPreMonth As String     '保存上一月份
    Dim sTableName As String
    Dim sWhere As String
    
    If Trim$(sBm + sXm) = "" Then
        sTableName = "tZW_Balance"
        sWhere = "kmdm='" & sKm & "'"
    Else
        sTableName = "tUSU_Fz"
        sWhere = "kmdm like '" & sKm & "%'"
        If Trim$(sXm) <> "" Then
            sXm = "↑" + sXm
            sWhere = sWhere + " and xmdm like '%" + sXm + "%'"
        End If
        If Trim$(sBm) <> "" Then
            sWhere = sWhere + " and bmdm like '" + sBm + "%'"
        End If
    End If
    sMonth = Format(sKJRQ, "mm")
    
    GetMyJFS = 0
    If sQj = "月" Then          '按月取数
       If sFx = "借" Then       '按借
                    '发生净额＝(本月累计借－上月累计借）－（本月累计贷－上月累计贷）
               If sMonth = "01" Then
                    rstTmp.Open "select sum(ljj" & Trim(sFlag) & "01-ljj" & Trim(sFlag) & "00-ljd" & Trim(sFlag) & _
                    "01+ljd" & Trim(sFlag) & "00) from " + sTableName & glo.sOperateYear & _
                            " where " + sWhere, glo.cnnMain, adOpenStatic, adLockReadOnly
               Else
                    sPreMonth = Format(yfdel(sKJRQ), "mm")
                    rstTmp.Open "select sum(ljj" & Trim(sFlag) & sMonth & "-ljj" & Trim(sFlag) & _
                              sPreMonth & "-ljd" & Trim(sFlag) & sMonth & _
                               "+ljd" & Trim(sFlag) & sPreMonth & ") from " + sTableName & glo.sOperateYear & _
                            " where " + sWhere, glo.cnnMain, adOpenStatic, adLockReadOnly
               End If
            If rstTmp.EOF Then Exit Function
               If Not IsNull(rstTmp.Fields(0).value) Then
                    GetMyJFS = rstTmp.Fields(0).value
               End If
               Exit Function
       End If
       
       If sFx = "贷" Then       '按贷
                  '发生净额＝（本月累计贷－上月累计贷）－（本月累计借－上月累计借）
              If sMonth = "01" Then
                    rstTmp.Open "select sum(ljd" & Trim(sFlag) & "01-ljd" & Trim(sFlag) & "00-ljj" & Trim(sFlag) & _
                          "01+ljj" & Trim(sFlag) & "00) from " + sTableName & glo.sOperateYear & _
                            " where " + sWhere, glo.cnnMain, adOpenStatic, adLockReadOnly
               Else
                    sPreMonth = Format(yfdel(sKJRQ), "mm")
                    rstTmp.Open "select sum(ljd" & Trim(sFlag) & sMonth & "-ljd" & Trim(sFlag) & sPreMonth & "-ljj" & Trim(sFlag) & _
                              sMonth & "+ljj" & Trim(sFlag) & sPreMonth & ") from " + sTableName & glo.sOperateYear & _
                            " where " + sWhere, glo.cnnMain, adOpenStatic, adLockReadOnly
               End If
            If rstTmp.EOF Then Exit Function
               If Not IsNull(rstTmp.Fields(0).value) Then
                    GetMyJFS = rstTmp.Fields(0).value
               End If
               Exit Function
       End If
    End If
'-----------------------------按年(即全年净发生额）
    
   If sQj = "年" Then
   
        If sFx = "借" Then       '按借
                        '净发生额＝(12月累计借－年初数借）－（12月累计贷－年初数贷）
            rstTmp.Open "select sum(ljj" & Trim(sFlag) & "12-ljj" & Trim(sFlag) & "00-ljd" & _
            Trim(sFlag) & "12+ljd" & Trim(sFlag) & "00) from " + sTableName & glo.sOperateYear & _
                         " where " + sWhere, glo.cnnMain, adOpenStatic, adLockReadOnly
            If rstTmp.EOF Then Exit Function
            If Not IsNull(rstTmp.Fields(0).value) Then
                 GetMyJFS = rstTmp.Fields(0).value
            End If
            Exit Function
        End If
        
        If sFx = "贷" Then           '净发生额＝(12月累计借－年初数借）－（12月累计贷－年初数贷）
            rstTmp.Open "select sum(ljd" & Trim(sFlag) & "12-ljd" & Trim(sFlag) & "00-ljj" & Trim(sFlag) & _
                        "12+ljj" & Trim(sFlag) & "00) from " + sTableName & glo.sOperateYear & _
                         " where " + sWhere, glo.cnnMain, adOpenStatic, adLockReadOnly
            If rstTmp.EOF Then Exit Function
            If Not IsNull(rstTmp.Fields(0).value) Then
                 GetMyJFS = rstTmp.Fields(0).value
            End If
            Exit Function
        End If
        
    End If
End Function

Private Function yfdel(sTmp As String) As String            '日期-1
Dim Y As String
Dim m As String
        Y = Format(sTmp, "yyyy")
        m = Format(sTmp, "mm")
        If Val(m) = 1 Then
            Y = str(Val(Y) - 1)
            m = "12"
        Else
            m = str(Val(m) - 1)
            If Val(m) < 10 Then
            m = "0" & Trim(m)
            End If
        End If
        yfdel = Trim(Y) & "-" & Trim(m)

End Function

Private Sub InitHead()
    Dim i As Integer
         mfgDisplay.Rows = 1
'        cllZzNew.SetCols 4, cllZzNew.GetCurSheet
End Sub

Private Sub GetData(ByVal Code As String)
Dim iTmp As Long
Dim rstPzSet As New ADODB.Recordset
Dim sOldID As String
Dim dJS As Double
'lijian**
Dim lRow1 As Long
Dim lRow2 As Long
Dim i As Integer
Dim j As Integer
lRow2 = 1
'end**

sOldID = ""
rstPzSet.CursorLocation = adUseClient
rstPzSet.Open "select * from tzw_zzpzset" & glo.sOperateYear & " where cPzType='" & m_sPzType & "' and id='" + Trim$(Code) + "'  order by id,sijlhm", glo.cnnMain, adOpenStatic, adLockReadOnly
cllZzNew.ResetContent
cllZzNew.SetRows rstPzSet.RecordCount + 1, cllZzNew.GetCurSheet
i = 1
bStart = False
cllZzNew.SetFormula 0, 1, 0, ""
cllZzNew.SetFormula 0, 2, 0, ""
While Not rstPzSet.EOF
    cllZzNew.SetCellString COL_ID, i, 0, Trim(rstPzSet.Fields("id").value)
    cllZzNew.SetCellString COL_SUMMARY, i, 0, IIf(IsNull(rstPzSet.Fields("czy").value), "", Trim(rstPzSet.Fields("czy").value))
    cllZzNew.SetCellString COL_SUBJECT, i, 0, Trim(rstPzSet.Fields("ckmdm").value)
    cllZzNew.SetCellString COL_ITEM, i, 0, FormatToString(rstPzSet.Fields("xmdm").value)
    cllZzNew.SetCellString COL_DEPARTMENT, i, 0, FormatToString(rstPzSet.Fields("bmdm").value)
    cllZzNew.SetCellString COL_FX, i, 0, Trim(rstPzSet.Fields("cfx").value)
    If Left$(FormatToString(rstPzSet.Fields("cfx").value), 1) = "借" Then
        cllZzNew.SetFormula COL_DEBITJE, i, 0, rstPzSet.Fields("cjegs").value   '计算公式
    Else
        cllZzNew.SetFormula COL_CREDITJE, i, 0, rstPzSet.Fields("cjegs").value   '计算公式
    End If
    If InStr(1, Trim(cllZzNew.GetFormula(COL_CREDITJE, iTmp, 0)), "JFHJ", vbTextCompare) < 1 Or InStr(1, Trim(cllZzNew.GetFormula(COL_DEBITJE, iTmp, 0)), "DFHJ", vbTextCompare) < 1 Then
        cllZzNew.SetFormula 0, 1, 0, cllZzNew.GetFormula(0, 1, 0) + "+" + cllZzNew.CellToLabel(COL_DEBITJE, i)
        cllZzNew.SetFormula 0, 2, 0, cllZzNew.GetFormula(0, 2, 0) + "+" + cllZzNew.CellToLabel(COL_CREDITJE, i)
    End If
    rstPzSet.MoveNext
    i = i + 1
Wend
bStart = True
cllZzNew.CalculateAll
cllZzNew.Redraw
For i = 1 To cllZzNew.GetRows(0)
    If cllZzNew.IsFormulaCell(COL_DEBITJE, i, 0) > 0 Then
        Call ChangeData(COL_DEBITJE, i)
        cllZzNew.SetCellString COL_ERROR, i, 0, CheckUserFormulaErr
    Else
        Call ChangeData(COL_CREDITJE, i)
        cllZzNew.SetCellString COL_ERROR, i, 0, CheckUserFormulaErr
    End If
Next
End Sub

Private Sub FillMfg()
        Dim iTmp As Long
        Dim rstPzSet As New ADODB.Recordset