mdlzz.bas

u8

Attribute VB_Name = "mdlZZ"
'软件著作权: 北京用友软件集团有限公司
'系统名称: 资金计息8.0
'功能说明: 从账务取数函数集
'作者: 赵春立

'CuiDong Efficiency-A 2000/06/19 效率优化A

Option Explicit

'********************************************************************
'*函数说明: 取出科目期初余额-带辅助账                                  *
'*参    数: cCode         科目编码　                                  *
'*          blnPrpty      科目属性　                                  *
'*返回值  : 科目期初余额                                              *
'*********************************************************************
'Public Function GetKmQC_Fz(cCode As String, bProperty As Boolean, cdeptcode As Variant, cPersonCode As Variant, cCusCode As Variant, cSupCode As Variant, cItem_id As Variant, citem_class As Variant) As Double                                            'cuidong 对账单+外币 2001.12.04
Public Function GetKmQC_Fz(cCode As String, bProperty As Boolean, cdeptcode As Variant, cPersonCode As Variant, cCusCode As Variant, cSupCode As Variant, cItem_id As Variant, citem_class As Variant, Optional ByVal bMoney_F As Boolean = False) As Double 'cuidong 对账单+外币 2001.12.04
   '此函数已经过大规模修改，旧版程序请参照备份
   'CuiDong Efficiency-A 2000/06/19 效率优化A
   Dim blnBysyf As Boolean
   Dim rsBysyf As New UfRecordset
   Dim sqlQc As String
   Dim rsQc As New UfRecordset
   
   Dim SQL_AP As String
   Dim SQL_AR As String
   
   GetKmQC_Fz = 0
   If Not IsNull(cCusCode) Then
'     Set rsBysyf = dbsZJ.OpenRecordset("AccInformation", dbOpenDynaset)  'CuiDong Efficiency-A 2000/06/19 效率优化A
      Set rsBysyf = dbsZJ.OpenRecordset("Select * From AccInformation Where cID = '24'", dbOpenDynaset)  'CuiDong Efficiency-A 2000/06/19 效率优化A
      With rsBysyf
'         .FindFirst "cID = '24'"                                         'CuiDong Efficiency-A 2000/06/19 效率优化A
'         If Not .NoMatch Then                                            'CuiDong Efficiency-A 2000/06/19 效率优化A
         If Not (.EOF Or .BOF) Then                                       'CuiDong Efficiency-A 2000/06/19 效率优化A
            If !cValue = "False" Then
'               sqlQc = "SELECT Sum(iBAmount) AS QcMb FROM AP_Sum " & _
'                  "WHERE ccode LIKE '" & cCode & "%' AND " & _
'                  "IIf(" & IsNull(cdeptcode) & ", True, cDeptCode LIKE '" & cdeptcode & "%') AND " & _
'                  "IIf(" & IsNull(cPersonCode) & ", True, cPerson = '" & cPersonCode & "') AND " & _
'                  "IIf(" & IsNull(cCusCode) & ", True, cDwCode = '" & cCusCode & "') AND " & _
'                  "IIf(" & IsNull(cItem_id) & ", True, cItemCode = '" & cItem_id & "') AND " & _
'                  "IIf(" & IsNull(citem_class) & ", True, cItem_Class = '" & citem_class & "')"
'
'               sqlQc = "SELECT Sum(iBAmount) AS QcMb FROM AP_Sum WHERE ccode LIKE '" & cCode & "%'"
               If bMoney_F Then 'cuidong 对账单+外币 2001.12.04
                  SQL_AP = "SELECT Sum(iDAmount_f-iCAmount_f) AS QcMb FROM AP_Detail WHERE iPeriod =0 And cFlag='AP' And cCode LIKE '" & cCode & "%'" 'cuidong 对账单+外币 2001.12.04
                  SQL_AR = "SELECT Sum(iCAmount_f-iDAmount_f) AS QcMb FROM AP_Detail WHERE iPeriod =0 And cFlag='AR' And cCode LIKE '" & cCode & "%'" 'cuidong 对账单+外币 2001.12.04
               Else             'cuidong 对账单+外币 2001.12.04
                  SQL_AP = "SELECT Sum(iDAmount-iCAmount) AS QcMb FROM AP_Detail WHERE iPeriod =0 And cFlag='AP' And cCode LIKE '" & cCode & "%'"
                  SQL_AR = "SELECT Sum(iCAmount-iDAmount) AS QcMb FROM AP_Detail WHERE iPeriod =0 And cFlag='AR' And cCode LIKE '" & cCode & "%'"
               End If           'cuidong 对账单+外币 2001.12.04
               sqlQc = ""
               If Not IsNull(cdeptcode) Then
'                  sqlQc = sqlQc & " And cdept_id LIKE '" & cdeptcode & "%'"    'Cuidong 2000/06/22
                  sqlQc = sqlQc & " And cDeptCode LIKE '" & cdeptcode & "%'"    'Cuidong 2000/06/22
               End If
               If Not IsNull(cPersonCode) Then
'                  sqlQc = sqlQc & " And cPerson_id = '" & cPersonCode & "'"    'Cuidong 2000/06/22
                  sqlQc = sqlQc & " And cPerson = '" & cPersonCode & "'"        'Cuidong 2000/06/22
               End If
               If Not IsNull(cCusCode) Then
'                  sqlQc = sqlQc & " And ccus_id = '" & cCusCode & "'"          'Cuidong 2000/06/22
                  sqlQc = sqlQc & " And cDwCode = '" & cCusCode & "'"           'Cuidong 2000/06/22
               End If
               If Not IsNull(cSupCode) Then
'                  sqlQc = sqlQc & " And csup_id = '" & cSupCode & "'"          'Cuidong 2000/06/22
''                  sqlQc = sqlQc & " And cDwCode = '" & cSupCode & "'"          'Cuidong 2000/06/22
               End If
               If Not IsNull(cItem_id) Then
'                  sqlQc = sqlQc & " And citem_id = '" & cItem_id & "'"         'Cuidong 2000/06/22
                  sqlQc = sqlQc & " And cItemCode = '" & cItem_id & "'"         'Cuidong 2000/06/22
               End If
               If Not IsNull(citem_class) Then
'                  sqlQc = sqlQc & " And citem_class = '" & citem_class & "'"   'Cuidong 2000/06/22
                  sqlQc = sqlQc & " And cItem_Class = '" & citem_class & "'"    'Cuidong 2000/06/22
               End If
               
               Set rsQc = dbsZJ.OpenRecordset(SQL_AP & sqlQc, dbOpenSnapshot)
               If Not rsQc.EOF Then
                  GetKmQC_Fz = IIf(IsNull(rsQc!QcMb), 0, rsQc!QcMb)
               End If
               rsQc.oClose
               
               Set rsQc = dbsZJ.OpenRecordset(SQL_AR & sqlQc, dbOpenSnapshot)
               If Not rsQc.EOF Then
                  GetKmQC_Fz = GetKmQC_Fz + IIf(IsNull(rsQc!QcMb), 0, rsQc!QcMb)
               End If
               rsQc.oClose
               
               blnBysyf = True
            End If
         End If
      End With
      rsBysyf.oClose
   End If
   
   If Not IsNull(cSupCode) Then
'      Set rsBysyf = dbsZJ.OpenRecordset("AccInformation", dbOpenDynaset)  'CuiDong Efficiency-A 2000/06/19 效率优化A
      Set rsBysyf = dbsZJ.OpenRecordset("Select * From AccInformation Where cID = '25'", dbOpenDynaset)  'CuiDong Efficiency-A 2000/06/19 效率优化A
      With rsBysyf
'         .FindFirst "cID = '25'"                                          'CuiDong Efficiency-A 2000/06/19 效率优化A
'         If Not .NoMatch Then                                             'CuiDong Efficiency-A 2000/06/19 效率优化A
         If Not (.EOF Or .BOF) Then                                        'CuiDong Efficiency-A 2000/06/19 效率优化A
            If !cValue = "False" Then
'               sqlQc = "SELECT Sum(iBAmount) AS QcMb FROM AP_Sum " & _
'                  "WHERE ccode LIKE '" & cCode & "%' AND " & _
'                  "IIf(" & IsNull(cdeptcode) & ", True, cDeptCode LIKE '" & cdeptcode & "%') AND " & _
'                  "IIf(" & IsNull(cPersonCode) & ", True, cPerson = '" & cPersonCode & "') AND " & _
'                  "IIf(" & IsNull(cSupCode) & ", True, cDwCode = '" & cSupCode & "') AND " & _
'                  "IIf(" & IsNull(cItem_id) & ", True, cItemCode = '" & cItem_id & "') AND " & _
'                  "IIf(" & IsNull(citem_class) & ", True, cItem_Class = '" & citem_class & "')"
'
'               sqlQc = "SELECT Sum(iBAmount) AS QcMb FROM AP_Sum WHERE ccode LIKE '" & cCode & "%'"
               If bMoney_F Then 'cuidong 对账单+外币 2001.12.04
                  SQL_AP = "SELECT Sum(iDAmount_f-iCAmount_f) AS QcMb FROM AP_Detail WHERE iPeriod =0 And cFlag='AP' And cCode LIKE '" & cCode & "%'" 'cuidong 对账单+外币 2001.12.04
                  SQL_AR = "SELECT Sum(iCAmount_f-iDAmount_f) AS QcMb FROM AP_Detail WHERE iPeriod =0 And cFlag='AR' And cCode LIKE '" & cCode & "%'" 'cuidong 对账单+外币 2001.12.04
               Else             'cuidong 对账单+外币 2001.12.04
                  SQL_AP = "SELECT Sum(iDAmount-iCAmount) AS QcMb FROM AP_Detail WHERE iPeriod =0 And cFlag='AP' And cCode LIKE '" & cCode & "%'"
                  SQL_AR = "SELECT Sum(iCAmount-iDAmount) AS QcMb FROM AP_Detail WHERE iPeriod =0 And cFlag='AR' And cCode LIKE '" & cCode & "%'"
               End If           'cuidong 对账单+外币 2001.12.04
               sqlQc = ""
               If Not IsNull(cdeptcode) Then
'                  sqlQc = sqlQc & " And cdept_id LIKE '" & cdeptcode & "%'"
                  sqlQc = sqlQc & " And cDeptCode LIKE '" & cdeptcode & "%'"
               End If
               If Not IsNull(cPersonCode) Then
'                  sqlQc = sqlQc & " And cperson_id = '" & cPersonCode & "'"
                  sqlQc = sqlQc & " And cPerson = '" & cPersonCode & "'"
               End If
               If Not IsNull(cCusCode) Then
'                  sqlQc = sqlQc & " And ccus_id = '" & cCusCode & "'"
''                  sqlQc = sqlQc & " And cDwCode = '" & cCusCode & "'"
               End If
               If Not IsNull(cSupCode) Then
'                  sqlQc = sqlQc & " And csup_id = '" & cSupCode & "'"
                  sqlQc = sqlQc & " And cDwCode = '" & cSupCode & "'"
               End If
               If Not IsNull(cItem_id) Then
'                  sqlQc = sqlQc & " And citem_id = '" & cItem_id & "'"
                  sqlQc = sqlQc & " And cItemCode = '" & cItem_id & "'"
               End If
               If Not IsNull(citem_class) Then
'                  sqlQc = sqlQc & " And citem_class = '" & citem_class & "'"
                  sqlQc = sqlQc & " And cItem_Class = '" & citem_class & "'"
               End If
               
               blnBysyf = True
               
               Set rsQc = dbsZJ.OpenRecordset(SQL_AP & sqlQc, dbOpenSnapshot)
               If Not rsQc.EOF Then
'                  GetKmQC_Fz = GetKmQC_Fz + IIf(IsNull(rsQc!QcMb), 0, rsQc!QcMb) '
                  GetKmQC_Fz = IIf(IsNull(rsQc!QcMb), 0, rsQc!QcMb)
               End If
               rsQc.oClose
               
               Set rsQc = dbsZJ.OpenRecordset(SQL_AR & sqlQc, dbOpenSnapshot)
               If Not rsQc.EOF Then
                  GetKmQC_Fz = GetKmQC_Fz + IIf(IsNull(rsQc!QcMb), 0, rsQc!QcMb)
               End If
               rsQc.oClose
            End If
         End If
      End With
      rsBysyf.oClose
   End If
   
   If blnBysyf Then
   Else
         If bMoney_F Then 'cuidong 对账单+外币 2001.12.04
            sqlQc = "SELECT Sum(Case When cbegind_c = '借' Then mb_f Else -mb_f End) AS QcMb FROM GL_accass WHERE ccode LIKE '" & cCode & "%' And iPeriod = " & ZjAccInfo.GLBeginPeriod 'cuidong 对账单+外币 2001.12.04
         Else             'cuidong 对账单+外币 2001.12.04
            sqlQc = "SELECT Sum(Case When cbegind_c = '借' Then mb Else -mb End) AS QcMb FROM GL_accass WHERE ccode LIKE '" & cCode & "%' And iPeriod = " & ZjAccInfo.GLBeginPeriod
         End If           'cuidong 对账单+外币 2001.12.04
         If Not IsNull(cdeptcode) Then
            sqlQc = sqlQc & " And cdept_id LIKE '" & cdeptcode & "%'"
         End If
         If Not IsNull(cPersonCode) Then
            sqlQc = sqlQc & " And cperson_id = '" & cPersonCode & "'"
         End If
         If Not IsNull(cCusCode) Then
            sqlQc = sqlQc & " And ccus_id = '" & cCusCode & "'"
         End If
         If Not IsNull(cSupCode) Then
            sqlQc = sqlQc & " And csup_id = '" & cSupCode & "'"
         End If
         If Not IsNull(cItem_id) Then
            sqlQc = sqlQc & " And citem_id = '" & cItem_id & "'"
         End If