cte.prg

#include 'hbclass.ch'

#define CRLF Chr( 13 ) + Chr( 10 )

External AllTrim

Class CTE
    Hidden:
    Data cTable
    Data cIdField
    Data cParentField
    Data aFields
    Data aSelect
    Data aConditions
    Data cQuery
    Data lOrphan
    Data cRoot
    Data nAlias

    Exported:
    Method New( cTable ) Constructor
    Method SetRelation( cIdField, cParentField )
    Method SetRoot( cRoot, lOrphan )
    Method SetFields( aFields )
    Method Select( aSelect )
    Method AddCondition( cCondition )
    Method Compile()

    Hidden:
    Method NextTableAlias()
    Method CompileFields( cAlias, aSource, lBase, nInline )
    Method CompileConditions( cAlias )
EndClass

Method New( cTable ) Class CTE
    ::cTable      := cTable
    ::nAlias      := 0
    ::aFields     := {}
    ::aSelect     := {}
    ::aConditions := { "#.D_E_L_E_T <> '*'" }
Return Self

Method NextTableAlias() Class CTE
    Local cAlias := 'TB' + AllTrim( Str( ::nAlias ) )
    ::nAlias++
Return cAlias

Method SetRelation( cIdField, cParentField ) Class CTE
    ::cIdField := cIdField
    ::cParentField := cParentField
Return Self

Method SetRoot( cRoot, lOrphan ) Class CTE
    ::cRoot   := cRoot
    ::lOrphan := IIf( lOrphan == Nil, .F., lOrphan )
Return Self

Method SetFields( aFields ) Class CTE
    ::aFields := aFields
Return Self

Method Select( aSelect ) Class CTE
    ::aSelect := aSelect
Return Self

Method AddCondition( cCondition ) Class CTE
    aAdd( ::aConditions, cCondition )
Return Self

Method CompileFields( cAlias, aSource, lBase, nInline ) Class CTE
    Local nIndex
    Local cFields := ''
    Local nMax
    Local bField := { |cAlias, cField| cAlias + '.' + cField }

    If aSource == Nil
        If lBase == Nil
            lBase := .F.
        EndIf

        If lBase .And. ::lOrphan
            bField := { |cAlias, cField, cDef| ;
                'ISNULL(MAX(' + cAlias + '.' + cField + '), ' + cDef + ')';
            }
        EndIf

        aSource := ::aFields
        cFields += Eval( bField, cAlias, ::cIdField, 'NULL' ) + ' AS __ID__,' + CRLF
        cFields += '           ' + Eval( bField, cAlias, ::cParentField, "'" + ::cRoot + "'" )
        cFields += ' AS __PARENT__,' + CRLF
        cFields += '           ' + IIf( lBase, '0', '(__RECURSIVE__.__DEPTH__ + 1)' ) + ' AS __DEPTH__'

        If !Empty( aSource )
            cFields += ','
        EndIf

        cFields += CRLF
    EndIf

    nMax := Len( aSource )
    nInline := nInline != Nil .And. nInline

    For nIndex := 1 To nMax
        If !nInline
            cFields += '           '
        EndIf
        cFields += Eval( bField, cAlias, aSource[ nIndex ], "''" )
        If ::lOrphan
            cFields += ' AS ' + aSource[ nIndex ]
        EndIf
        If nIndex < nMax
            cFields += ','
        EndIf
        If !nInline
            cFields += CRLF
        EndIf
    Next
Return cFields

Method CompileConditions( cAlias )
    Local cSource := ''
    Local nIndex

    For nIndex := 1 To Len( ::aConditions )
        cSource += '      AND ' + StrTran( ::aConditions[ nIndex ], '#', cAlias ) + CRLF
    Next
Return cSource


Method Compile()
    Local cQuery := ''
    Local cTableAlias
    Local nIndex

    If ::cRoot != Nil
        cTableAlias := ::NextTableAlias()
        cQuery += 'WITH' + CRLF
        cQuery += '  __SQLTREE_PARENT__ AS (' + CRLF
        cQuery += '    SELECT ' + ::CompileFields( cTableAlias, , .T. )
        cQuery += '    FROM ' + ::cTable + ' ' + cTableAlias + CRLF
        cQuery += '    WHERE ' + cTableAlias + '.' + ::cIdField + ' = ' + "'" + ::cRoot + "'" + CRLF
        cQuery += ::CompileConditions( cTableAlias )
        cQuery += '    UNION ALL' + CRLF

        cTableAlias := ::NextTableAlias()
        cQuery += '    SELECT ' + ::CompileFields( cTableAlias )
        cQuery += '    FROM ' +  ::cTable + ' ' + cTableAlias + CRLF
        cQuery += '    INNER JOIN __SQLTREE_PARENT__ __RECURSIVE__' + CRLF
        cQuery += '       ON __RECURSIVE__.__PARENT__ = ' + cTableAlias + '.' + ::cParentField + CRLF
        cQuery += ::CompileConditions( cTableAlias )
        cQuery += '  )' + CRLF
    EndIf

    If !Empty( ::aSelect )
        cTableAlias := ::NextTableAlias()
        cQuery += 'SELECT ' + ::CompileFields( cTableAlias, ::aSelect, , .T. ) + CRLF
        cQuery += 'FROM __SQLTREE_PARENT__ ' + cTableAlias + CRLF
        cQuery += 'ORDER BY ' + cTableAlias + '.__DEPTH__ DESC' + CRLF
    EndIf

    ::cQuery := cQuery
Return cQuery

Function Main()
    Local oCTE

    Local cDateTime := '2018010419:00'

    oCTE := CTE():New( 'STZT10' )
    oCTE:SetRelation( 'TZ_CODBEM', 'TZ_BEMPAI' )
    oCTE:SetFields( { 'TZ_CODBEM', 'TZ_BEMPAI', 'TZ_DATASAI', 'TZ_HORASAI', 'TZ_DATAMOV', 'TZ_HORAENT' } )
    oCTE:SetRoot( 'BP00001' )
    oCTE:AddCondition( "#.TZ_DATAMOV + #.TZ_HORAENT < '" + cDateTime + "'" )

    oCTE:Select( { 'TZ_BEMPAI' } )

    ? oCTE:Compile()
Return 1