clsvchquerysqlbi.cls

用友U8财务软件VB源程序, 本版本为2002年版本

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "clsVchQuerySQLBI"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = True
'--------------------------------
'时间:2002.4.17
'版权:北京用友软件股份有限公司
'设计:章景峰
'编码:章景峰
'说明:U8资金管理---业务对象
'--------------------------------
Private m_DataSourceName As String
Private m_NodeKey        As Long

Public Property Get DataSourceName() As String
    DataSourceName = m_DataSourceName
End Property

Public Property Let DataSourceName(ByVal vData As String)
    m_DataSourceName = vData
End Property

Public Property Get NodeKey() As Long
    NodeKey = m_NodeKey
End Property

Public Property Let NodeKey(ByVal vData As Long)
    m_NodeKey = vData
End Property

Public Function CreateSQL() As String
    Dim i                As Integer
    Dim QryFldSqc()      As String
    Dim QryFldOrderBy(3) As String
    Dim objDataMgr       As New U8FDMgr.DataManager
    Dim objEO            As U8FDEso.EntityObject
    
    On Error GoTo lblHandle
    
    If Trim(DataSourceName) = "" Then Exit Function
    If NodeKey < 11 Or NodeKey > 80 Then Exit Function
    
    Set objEO = objDataMgr.LoadEOMetaData(DataSourceName, NodeKey)
    
    ReDim Preserve QryFldSqc(objEO.Fields.Count)
    For i = 1 To objEO.Fields.Count
        If objEO.Fields(i).QryFldSqc > 0 Then
            If objEO.Fields(i).DataType = U8FDEso.esoDate Then
                QryFldSqc(objEO.Fields(i).QryFldSqc) = "convert(varchar(10)," & objEO.Fields(i).Name & ",111)" & " as [" & objEO.Fields(i).Caption & "]"
            Else
                QryFldSqc(objEO.Fields(i).QryFldSqc) = objEO.Fields(i).Name & " as [" & objEO.Fields(i).Caption & "]"
            End If
        End If
        If objEO.Fields(i).RefFldSqc > 0 Then 'order by
            QryFldOrderBy(objEO.Fields(i).RefFldSqc) = objEO.Fields(i).Name
        End If
    Next
    
    CreateSQL = "Select "
    For i = 1 To UBound(QryFldSqc)
        If Not IsNull(QryFldSqc(i)) And QryFldSqc(i) <> "" Then
            If i = 1 Then
                CreateSQL = CreateSQL & QryFldSqc(i) & "," & objEO.SourceOIDField & " as 内部业务ID,"
            Else
                CreateSQL = CreateSQL & QryFldSqc(i) & ","
            End If
        End If
    Next
    
    CreateSQL = Mid(CreateSQL, 1, Len(CreateSQL) - 1)
    '---------------------------
    Dim objAccDefBI   As New U8FDBso.clsAccDefBI
    Dim objUnitBI     As New U8FDBso.clsAccUnitBI
    Dim objIRateBI    As New U8FDBso.clsIRateBI
    Dim objCadBI      As New U8FDBso.clsCadBI
    Dim objTranEO     As U8FDEso.EntityObject
    Dim DeriveBIType  As Long
    Dim SQLfrom       As String
    Dim sqlwhere      As String
    Dim RelationTable As String
    
    DeriveBIType = objEO.DeriveBIType
    If DeriveBIType = 0 Then DeriveBIType = NodeKey
    If DeriveBIType >= 51 Then
        CreateSQL3 objEO, CreateSQL, SQLfrom, sqlwhere
    ElseIf DeriveBIType >= 41 Then
        CreateSQL2 objEO, CreateSQL, SQLfrom, sqlwhere
    Else
        Select Case DeriveBIType
            Case 21
                SQLfrom = " from " & objEO.SourceTable
                sqlwhere = " where substring(" & objEO.SourceOIDField & ",1,2)=" & NodeKey
                
                If InStr(1, CreateSQL, "digest" & " as ") > 0 Then
                    CreateSQL = Mid(CreateSQL, 1, InStr(1, CreateSQL, "digest" & " as ") - 1) & objEO.SourceTable & "." & "digest" & Mid(CreateSQL, InStr(1, CreateSQL, "digest" & " as ") + Len("digest"))
                End If
                
                If InStr(1, CreateSQL, "capitalmoney_name" & " as ") > 0 Then
''                    CreateSQL = Mid(CreateSQL, 1, InStr(1, CreateSQL, "capitalmoney_name" & " as ") - 1) & Rmbdx(objEO.Fields.Item("sum_mny").Value) & Mid(CreateSQL, InStr(1, CreateSQL, "capitalmoney_name" & " as ") + Len("capitalmoney_name"))
                End If
                
                If InStr(1, CreateSQL, "naturalmoney_name" & " as ") > 0 Then
                    CreateSQL = Mid(CreateSQL, 1, InStr(1, CreateSQL, "naturalmoney_name" & " as ") - 1) & objEO.Fields.Item("sum_mny").SourceField & "*" & objEO.Fields.Item("exchange_rate").SourceField & Mid(CreateSQL, InStr(1, CreateSQL, "naturalmoney_name" & " as ") + Len("naturalmoney_name"))
                End If
                
                Set objTranEO = objAccDefBI.Init(DataSourceName)
                If InStr(1, CreateSQL, "rcv_acc_code" & " as ") > 0 Then
                    CreateSQL = Mid(CreateSQL, 1, InStr(1, CreateSQL, "rcv_acc_code" & " as ") - 1) & objTranEO.SourceTable & "." & objTranEO("accdef_code").SourceField & Mid(CreateSQL, InStr(1, CreateSQL, "rcv_acc_code" & " as ") + Len("rcv_acc_code"))
                End If
                If InStr(1, CreateSQL, "accdef_name" & " as ") > 0 Then
                    CreateSQL = Mid(CreateSQL, 1, InStr(1, CreateSQL, "accdef_name" & " as ") - 1) & objTranEO.SourceTable & "." & objTranEO("accdef_name").SourceField & Mid(CreateSQL, InStr(1, CreateSQL, "accdef_name" & " as ") + Len("accdef_name"))
                End If
                If InStr(1, CreateSQL, "money_name" & " as ") > 0 Then
                    CreateSQL = Mid(CreateSQL, 1, InStr(1, CreateSQL, "money_name" & " as ") - 1) & objTranEO.SourceTable & "." & objTranEO("money_name").SourceField & Mid(CreateSQL, InStr(1, CreateSQL, "money_name" & " as ") + Len("money_name"))
                End If
                SQLfrom = SQLfrom & " left join " & objTranEO.SourceTable
                SQLfrom = SQLfrom & " on " & objEO.SourceTable & "." & objEO("rcv_acc_id").SourceField & "=" & objTranEO.SourceTable & "." & objTranEO.SourceOIDField
                
                RelationTable = objTranEO.SourceTable & "." & objTranEO("accunit_id").SourceField
                Set objTranEO = objUnitBI.Init(DataSourceName)
                If InStr(1, CreateSQL, "accunit_name" & " as ") > 0 Then
                    CreateSQL = Mid(CreateSQL, 1, InStr(1, CreateSQL, "accunit_name" & " as ") - 1) & objTranEO.SourceTable & "." & objTranEO("accunit_name").SourceField & Mid(CreateSQL, InStr(1, CreateSQL, "accunit_name" & " as ") + Len("accunit_name"))
                    SQLfrom = SQLfrom & " left join " & objTranEO.SourceTable
                    SQLfrom = SQLfrom & " on " & RelationTable & "=" & objTranEO.SourceTable & "." & objTranEO.SourceOIDField
                End If
                
                If InStr(1, CreateSQL, "settle_name" & " as ") > 0 Then
                    CreateSQL = Mid(CreateSQL, 1, InStr(1, CreateSQL, "settle_name" & " as ") - 1) & "SettleStyle." & "cSSName" & Mid(CreateSQL, InStr(1, CreateSQL, "settle_name" & " as ") + Len("settle_name"))
                    SQLfrom = SQLfrom & " left join " & "SettleStyle"
                    SQLfrom = SQLfrom & " on " & objEO.SourceTable & "." & objEO("settle_code").SourceField & "=" & "SettleStyle" & "." & "cSSCode"
                End If
                
                If InStr(1, CreateSQL, "equalsubject_name" & " as ") > 0 Then
                    CreateSQL = Mid(CreateSQL, 1, InStr(1, CreateSQL, "equalsubject_name" & " as ") - 1) & "code." & "ccode_name" & Mid(CreateSQL, InStr(1, CreateSQL, "equalsubject_name" & " as ") + Len("equalsubject_name"))
                    SQLfrom = SQLfrom & " left join " & "code"
                    SQLfrom = SQLfrom & " on " & objEO.SourceTable & "." & objEO("equalsubject_code").SourceField & "=" & "code" & "." & "ccode"
                End If
                
                Set objTranEO = Nothing
            Case 22
                SQLfrom = " from " & objEO.SourceTable
                sqlwhere = " where substring(" & objEO.SourceOIDField & ",1,2)=" & NodeKey
                
                If InStr(1, CreateSQL, "digest" & " as ") > 0 Then