Skip to content

Instantly share code, notes, and snippets.

@haskellcamargo
Created September 5, 2018 19:14
Show Gist options
  • Save haskellcamargo/3df1f170688932632a2e64f6f3c02f1c to your computer and use it in GitHub Desktop.
Save haskellcamargo/3df1f170688932632a2e64f6f3c02f1c to your computer and use it in GitHub Desktop.
#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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment