Created
September 5, 2018 19:14
-
-
Save haskellcamargo/3df1f170688932632a2e64f6f3c02f1c to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#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