/*
 * $Id: scripts.prg 483 2023-05-09 06:22:18Z bedipritpal $
 */

/*
 * Harbour Project source code:
 *
 * Copyright 2013-2023 Pritpal Bedi <bedipritpal@hotmail.com>
 * http://harbour-project.org
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2, or (at your option)
 * any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this software; see the file COPYING.  If not, write to
 * the Free Software Foundation, Inc., 59 Temple Place, Suite 330,
 * Boston, MA 02111-1307 USA (or visit the web site http://www.gnu.org/).
 *
 * As a special exception, the Harbour Project gives permission for
 * additional uses of the text contained in its release of Harbour.
 *
 * The exception is that, if you link the Harbour libraries with other
 * files to produce an executable, this does not by itself cause the
 * resulting executable to be covered by the GNU General Public License.
 * Your use of that executable is in no way restricted on account of
 * linking the Harbour library code into it.
 *
 * This exception does not however invalidate any other reasons why
 * the executable file might be covered by the GNU General Public License.
 *
 * This exception applies only to the code released by the Harbour
 * Project under the name Harbour.  If you copy code from other
 * Harbour Project or Free Software Foundation releases into a copy of
 * Harbour, as the General Public License permits, the exception does
 * not apply to the code that you add in this way.  To avoid misleading
 * anyone as to the status of such modified files, you must delete
 * this exception notice from them.
 *
 * If you write modifications of your own for Harbour, it is your choice
 * whether to permit this exception to apply to your modifications.
 * If you do not wish that, delete this exception notice.
 *
 */
/*----------------------------------------------------------------------*/
/*
 *                               EkOnkar
 *                         ( The LORD is ONE )
 *
 *                             Pritpal Bedi
 *                              17Nov2016
 */
/*----------------------------------------------------------------------*/

#include "hbclass.ch"
#include "common.ch"
#include "inkey.ch"
#include "fileio.ch"
#include "hbgtinfo.ch"
#include "hbhrb.ch"

//--------------------------------------------------------------------//
//
// cBuffer := '#command SELECT <fld,...> FROM <from> [INTO <into>] [ORDER BY <order,...>] [GROUP BY <group,...>] [WHERE <*whr*>] ' + ;
//            ' => ' + ;
//            ' __hbqtExecSelect( #<fld>, <"from">, #<whr>, #<order>, <"into">, #<group> )' + ;
//            Chr( 10 ) + Chr( 10 ) + cBuffer
//--------------------------------------------------------------------//
//               Select Statement Parser & Executer
//--------------------------------------------------------------------//

FUNCTION __hbqtExecSelect( cFields, cFrom, cWhere, cOrder, cInto, cGroup )
   LOCAL oSQL
   SetColor( "N/W" )
   CLS

   DEFAULT cWhere TO ""
   DEFAULT cOrder TO ""
   DEFAULT cInto  TO ""
   DEFAULT cGroup TO ""

   oSQL := HbSQL():new():create( cFields, cFrom, cWhere, cOrder, cInto, cGroup )

   HB_SYMBOL_UNUSED( oSQL )
   RETURN NIL

//--------------------------------------------------------------------//

CLASS HbSQL

   DATA   cFields                                  INIT ""
   DATA   cFrom                                    INIT ""
   DATA   cWhere                                   INIT ""
   DATA   cOrder                                   INIT ""
   DATA   cGroup                                   INIT ""
   DATA   cInto                                    INIT ""

   DATA   aData                                    INIT {}
   DATA   aStruct                                  INIT {}
   DATA   aStructF                                 INIT {}
   DATA   aFields                                  INIT {}
   DATA   nFields                                  INIT {}
   DATA   aTags                                    INIT {}
   DATA   aInfo                                    INIT {}
   DATA   aWhere                                   INIT {}

   DATA   cAlias                                   INIT "__SOURCE__"
   DATA   cDriver
   DATA   cTable
   DATA   cPath
   DATA   cName
   DATA   cExt
   DATA   cMsg                                     INIT ""
   DATA   cSearch                                  INIT ""
   DATA   nIndex                                   INIT 0
   DATA   cFor                                     INIT ""
   DATA   bFor
   DATA   cSearchType                              INIT ""
   DATA   lAggregate                               INIT .F.

   METHOD init()
   METHOD create( cFields, cFrom, cWhere, cOrder, cInto, cGroup )
   METHOD openTable()
   METHOD parseFields()
   METHOD pullIndexes()
   METHOD closeAndAlert( cAlert )
   METHOD parseWhere()
   METHOD pullWheres()
   METHOD pullKeyValueOperator( cWhere, cOperator )
   METHOD collectData()
   METHOD collectFields()
   METHOD orderData( cOrderBy )
   METHOD saveData()
   METHOD consolidateData()
   METHOD browseData()
   METHOD parseParams( cFunc, aParam, aResult )

   ENDCLASS


METHOD init() CLASS HbSQL
   RETURN Self


METHOD create( cFields, cFrom, cWhere, cOrder, cInto, cGroup ) CLASS HbSQL

   DEFAULT cFields TO ::cFields
   DEFAULT cFrom   TO ::cFrom
   DEFAULT cWhere  TO ::cWhere
   DEFAULT cOrder  TO ::cOrder
   DEFAULT cInto   TO ::cInto
   DEFAULT cGroup  TO ::cGroup

   ::cFields := cFields
   ::cFrom   := cFrom
   ::cWhere  := cWhere
   ::cOrder  := cOrder
   ::cInto   := cInto
   ::cGroup  := cGroup

   IF ::openTable()
      IF ! ::parseFields()
         RETURN ::closeAndAlert( ::cMsg )
      ENDIF
      IF ! ::lAggregate .AND. Empty( ::aFields )
         RETURN ::closeAndAlert( "Fields requested are not present in the table!" )
      ENDIF
      ::pullIndexes()
      IF ::parseWhere()
         // Collect data from the source table based on WHERE clause
         //
         ::collectData()
         // We have pulled data, close the source table
         //
         Select( ::cAlias )
         dbCloseArea()
         //
         IF ! Empty( ::aData )
            IF ::lAggregate
               IF ! ::consolidateData()
                  RETURN ::closeAndAlert( ::cMsg )
               ENDIF
            ENDIF
            // Sort data per ORDER BY clause
            //
            ::orderData()

            // Save if INTO clause is present
            //
            ::saveData()

            // We are done, browse results
            //
            ::browseData()
         ENDIF
      ENDIF
   ENDIF
   RETURN NIL


METHOD collectData() CLASS HbSQL

   IF ! HB_ISBLOCK( ::bFor )
      IF ::nIndex > 0
         IF dbSeek( ::cSearch )
            IF ::cSearchType == "=="
               DO WHILE Trim( ( ::cAlias )->( &( IndexKey( ::nIndex ) ) ) ) == ::cSearch
                  ::collectFields()
                  ( ::cAlias )->( dbSkip() )
               ENDDO
            ELSE
               DO WHILE ( ::cAlias )->( &( IndexKey( ::nIndex ) ) ) = ::cSearch
                  ::collectFields()
                  ( ::cAlias )->( dbSkip() )
               ENDDO
            ENDIF
         ENDIF
      ELSE
         DO WHILE ! ( ::cAlias )->( Eof() )
            ::collectFields()
            ( ::cAlias )->( dbSkip() )
         ENDDO
      ENDIF
   ELSE
      IF ::nIndex > 0
         IF dbSeek( ::cSearch )
            IF ::cSearchType == "=="
               DO WHILE Trim( ( ::cAlias )->( &( IndexKey( ::nIndex ) ) ) ) == ::cSearch
                  IF Eval( ::bFor )
                     ::collectFields()
                  ENDIF
                  ( ::cAlias )->( dbSkip() )
               ENDDO
            ELSE
               DO WHILE ( ::cAlias )->( &( IndexKey( ::nIndex ) ) ) = ::cSearch
                  IF Eval( ::bFor )
                     ::collectFields()
                  ENDIF
                  ( ::cAlias )->( dbSkip() )
               ENDDO
            ENDIF
         ENDIF
      ELSE
         DO WHILE ! ( ::cAlias )->( Eof() )
            IF Eval( ::bFor )
               ::collectFields()
            ENDIF
            ( ::cAlias )->( dbSkip() )
         ENDDO
      ENDIF
   ENDIF
   RETURN NIL


METHOD collectFields() CLASS HbSQL
   LOCAL aTmp, aField

   aTmp := {}
   FOR EACH aField IN ::aInfo
      AAdd( aTmp, Eval( aField[ 6 ] ) )
   NEXT
   AAdd( ::aData, aTmp )
   RETURN NIL


METHOD parseFields() CLASS HbSQL
   LOCAL xTmp, cField, nField, aToken, n, aParam, cFunc, cParams

   IF "*" == ::cFields
      FOR EACH xTmp IN ::aStruct
         n := xTmp:__enumIndex()
         AAdd( ::aFields, xTmp[ 1 ] )
         AAdd( ::nFields, n )
         AAdd( ::aInfo, { xTmp[ 1 ], xTmp[ 2 ], xTmp[ 3 ], xTmp[ 4 ], "FIELD", &( "{|| fieldget(" + hb_ntos( n ) + ") }" ) } )
      NEXT
      AEval( ::aStruct, {|e_,i| AAdd( ::aFields, e_[ 1 ] ), AAdd( ::nFields, i ) } )
   ELSE
      xTmp := __tokenizeList( ::cFields )
      FOR EACH aToken IN xTmp
         cField := Upper( aToken )
         IF ( n := At( "(", cField ) ) > 0
            cFunc := Left( cField, n - 1 )
            cParams := " " + SubStr( cField, n + 1 )
            cParams := Left( cParams, Len( cParams ) - 1 )

            SWITCH cFunc
            CASE "COUNT"
               ::lAggregate := .T.
               AAdd( ::aInfo, { cField, "N", 10, 0, cFunc, {|| 1 } } )
               EXIT
            CASE "SUM"
            CASE "AVG"
            CASE "MIN"
            CASE "MAX"
               ::lAggregate := .T.
            CASE "FUNC"
               aParam := __pullFieldsFromExp( cParams )
               FOR EACH xTmp IN ::aStruct
                  IF AScan( aParam, {|e| e == xTmp[ 1 ] } ) > 0
                     cParams := StrTran( cParams, xTmp[ 1 ], "fieldget(" + hb_ntos( xTmp:__enumIndex() ) + ")" )
                  ENDIF
               NEXT
               xTmp := NIL
               IF HB_ISHASH( xTmp := __evalAsIs( cParams ) )
                  AAdd( ::aInfo, { cField, xTmp[ "type" ], xTmp[ "length" ], xTmp[ "dec" ], cFunc, &( "{|| " + cParams + " }" ) } )
               ENDIF
               EXIT
            CASE "SUBSTR"
            CASE "LEFT"
            CASE "RIGHT"
            CASE "LOWER"
            CASE "UPPER"
               aParam := __pullFieldsFromExp( cParams )
               FOR EACH xTmp IN ::aStruct
                  IF AScan( aParam, {|e| e == xTmp[ 1 ] } ) > 0
                     cParams := StrTran( cParams, xTmp[ 1 ], "fieldget(" + hb_ntos( xTmp:__enumIndex() ) + ")" )
                  ENDIF
               NEXT
               cParams := cFunc + "( " + cParams + ")"
               xTmp := NIL
               IF HB_ISHASH( xTmp := __evalAsIs( cParams ) )
                  AAdd( ::aInfo, { cField, xTmp[ "type" ], xTmp[ "length" ], xTmp[ "dec" ], cFunc, &( "{|| " + cParams + " }" ) } )
               ENDIF
               EXIT
            CASE "RECNO"
               AAdd( ::aInfo, { cField, "N", 10, 0, cFunc, &( "{|| RecNo() }" ) } )
               EXIT
            ENDSWITCH
         ELSE
            IF ( nField := AScan( ::aStruct, {|e_| e_[ 1 ] == cField } ) ) > 0
               AAdd( ::aFields, cField )
               AAdd( ::nFields, nField )
               AAdd( ::aInfo, { cField, ::aStruct[ nField, 2 ], ::aStruct[ nField, 3 ], ::aStruct[ nField, 4 ], ;
                                "FIELD", &( "{|| FieldGet(" + hb_ntos( nField ) + ") }" ) } )
            ELSE
               ::aFields := {}
               ::cMsg := "Defined field does not exist in table!"
               RETURN .F.
            ENDIF
         ENDIF
      NEXT
   ENDIF
   IF ::lAggregate
      AAdd( ::aInfo, { "_$B$_", "N", 1, 0, "BASE", {|| 1 } } )
   ENDIF
   RETURN .T.


STATIC FUNCTION __tokenizeList( cList )
   LOCAL n, cChr, lFunc, nBraceOp, nBraceCl
   LOCAL a_:= {}

   n := 1
   nBraceOp := nBraceCl := 0
   lFunc := .F.
   FOR EACH cChr IN cList
      IF cChr == "("                               // function starts
         IF ! lFunc
            lFunc := .T.
         ENDIF
         nBraceOp++
      ELSEIF cChr == ")"
         IF lFunc
            nBraceCl++
            IF nBraceOp == nBraceCl
               nBraceOp := nBraceCl := 0
               lFunc := .F.
            ENDIF
         ENDIF
      ELSEIF cChr $ ",+-*/"
         IF ! lFunc
            AAdd( a_, AllTrim( SubStr( cList, n, cChr:__enumIndex() - n ) ) )
            n := cChr:__enumIndex() + 1
         ENDIF
      ENDIF
   NEXT
   IF n < Len( cList )
      AAdd( a_, AllTrim( SubStr( cList, n ) ) )
   ENDIF
   RETURN a_


STATIC FUNCTION __pullFieldsFromExp( cExp, aFlds )
   LOCAL aParams, cToken, n

   DEFAULT aFlds TO {}

   aParams := __tokenizeList( cExp )
   IF HB_ISARRAY( aParams )
      FOR EACH cToken IN aParams
         IF ( n := At( "(", cToken ) ) > 0
            __pullFieldsFromExp( SubStr( cToken, n + 1, Len( cToken ) - n - 1 ), @aFlds )
         ELSE
            AAdd( aFlds, cToken )
         ENDIF
      NEXT
   ENDIF

   // larger fields first - avoid errors if another field has subset of characters of another field
   // no_11, no_111
   //
   ASort( aFlds, NIL, NIL, {|e,f| Len( e ) > Len( f ) } )
   RETURN aFlds


STATIC FUNCTION __evalAsIs( cParams )
   LOCAL hRet, xTmp
   LOCAL bError := ErrorBlock( {|| Break() } )

   BEGIN SEQUENCE
      xTmp := Eval( &( "{|| " + cParams + " }" ) )
      hRet := {=>}
      hRet[ "type"   ] := ValType( xTmp )
      hRet[ "length" ] := iif( hRet[ "type" ] == "C", Len( xTmp ), iif( hRet[ "type" ] == "N", 15, iif( hRet[ "type" ] == "D", 8, 1 ) ) )
      hRet[ "dec"    ] := iif( hRet[ "type" ] == "N", 3, 0 )
   RECOVER
      // nothing to do
   END SEQUENCE
   ErrorBlock( bError )
   RETURN hRet


METHOD parseParams( cFunc, aParam, aResult ) CLASS HbSQL
   LOCAL lError := .F.
   LOCAL cF, aF, nField, nWid, nDec, cN

   nWid := nDec := 0
   cN := ""
   IF Len( aParam ) >= 1 .AND. ! ( Len( aParam ) % 2 == 0 )
      FOR EACH aF IN aParam
         cF := aF[ 1 ]
         cN += cF
         IF ! cF $ "+*-/"
            IF ( nField := AScan( ::aStruct, {|e_| e_[ 1 ] == cF } ) ) > 0
               IF ::aStruct[ nField, 2 ] == "N"
                  aF[ 2 ] := "fieldget(" + hb_ntos( nField ) + ")"
                  nWid := Max( nWid, ::aStruct[ nField, 3 ] )
                  nDec := Max( nDec, ::aStruct[ nField, 4 ] )
               ELSE
                  lError := .T.
               ENDIF
            ELSE
               lError := .T.
            ENDIF
         ENDIF
         IF lError
            ::cMsg := "Defined field does not exist in table!"
            RETURN .F.
         ENDIF
      NEXT
      IF cFunc == "COUNT"
         aResult := { cFunc + "(" + cN + ")", "N", 8, 0, cFunc, &( "{|| 1 }" ) }
      ELSE
         IF Len( aParam ) == 5
            cF := aParam[ 1, 2 ] + aParam[ 2, 1 ] + aParam[ 3,2 ] + aParam[ 4, 1 ] + aParam[ 5,2 ]
         ELSEIF Len( aParam ) == 3
            cF := aParam[ 1, 2 ] + aParam[ 2, 1 ] + aParam[ 3,2 ]
         ELSEIF Len( aParam ) == 1
            cF := aParam[ 1, 2 ]
         ENDIF
         aResult := { cFunc + "(" + cN + ")", "N", 12, nDec, cFunc,  &( "{|| " + cF + "}" ) }
      ENDIF
   ELSE
      ::cMsg := "Defined field does not exist in table!"
      RETURN .F.
   ENDIF
   RETURN .T.


METHOD consolidateData() CLASS HbSQL
   LOCAL aGroup, cGroup, aData, ele_, nAdd_, aInfo, n, aOpr_

   IF ! Empty( ::cGroup )
      aGroup := hb_ATokens( Upper( ::cGroup ), "," )
      FOR EACH cGroup IN aGroup
         cGroup := AllTrim( cGroup )
         IF AScan( ::aFields, {|e| e == cGroup } ) == 0
            ::cMsg := "Group By field is not included in SELECT clause!"
            RETURN .F.
         ENDIF
      NEXT

      ele_:= {}
      FOR EACH cGroup IN aGroup
         IF ( n := AScan( ::aInfo, {|e_| e_[ 1 ] == cGroup } ) ) > 0
            AAdd( ele_, n )
         ENDIF
      NEXT
      nAdd_:= {} ; aOpr_:= {}
      FOR EACH aInfo IN ::aInfo
         IF aInfo[ 5 ] $ "SUM,AVG,MIN,MAX,COUNT"
            AAdd( nAdd_, aInfo:__enumIndex() )
            AAdd( aOpr_, aInfo[ 5 ] )
         ENDIF
      NEXT
      //
      aData := __hbqtAProcessUnique( ::aData, ele_, nAdd_, aOpr_ )

      ::aData := aData
   ELSE
      ele_:= { Len( ::aInfo ) }
      nAdd_:= {} ; aOpr_:= {}
      FOR EACH aInfo IN ::aInfo
         IF aInfo[ 5 ] $ "SUM,AVG,MIN,MAX,COUNT"
            AAdd( nAdd_, aInfo:__enumIndex() )
            AAdd( aOpr_, aInfo[ 5 ] )
         ENDIF
      NEXT
      aData := __hbqtAProcessUnique( ::aData, ele_, nAdd_, aOpr_ )
      ::aData := aData
   ENDIF
   RETURN .T.


METHOD orderData( cOrderBy ) CLASS HbSQL
   LOCAL aOrder, cOrder, xTmp, nS, n, cE, nE, i, cFor, bFor, nLastOrder

   DEFAULT cOrderBy TO ::cOrder

   IF ! Empty( cOrderBy )
      cOrderBy := Upper( cOrderBy )
      aOrder := hb_ATokens( cOrderBy, "," )
      FOR EACH cOrder IN aOrder
         IF Right( cOrder, 4 ) == "-ASC"
            cOrder := Left( cOrder, Len( cOrder ) - 4 )
         ENDIF
         xTmp := Right( cOrder, 5 ) == "-DESC"
         IF xTmp
            cOrder := Left( cOrder, Len( cOrder ) - 5 )
         ENDIF
         nS := 1
         IF ( n := AScan( ::aInfo, {|e_| e_[ 1 ] == cOrder } ) ) > 0
            cFor := "e_[" + hb_ntos( n ) + "]" + iif( xTmp, ">", "<" ) +  "f_[" + hb_ntos( n ) + "]"
            bFor := &( "{|e_,f_| " + cFor + " }" )

            IF cOrder:__enumIndex() == 1
               ASort( ::aData, NIL, NIL, bFor )
            ELSE
               cE := ::aData[ nS, nLastOrder ]
               nE := 0
               DO WHILE .T.
                  FOR i := nS TO Len( ::aData )
                     IF ::aData[ i, nLastOrder ] != cE
                        ASort( ::aData, nS, nE, bFor )
                        cE := ::aData[ i, nLastOrder ]
                        nS := i
                        nE := 0
                        EXIT
                     ENDIF
                     nE++
                  NEXT
                  IF nE >= Len( ::aData )
                     EXIT
                  ENDIF
               ENDDO
               IF nS < Len( ::aData )
                  ASort( ::aData, nS, NIL, bFor )
               ENDIF
            ENDIF
            nLastOrder := n
         ENDIF
      NEXT
   ENDIF
   RETURN NIL


METHOD saveData() CLASS HbSQL
   LOCAL xTmp, cPath, cName, cExt, aStruct, aField, x, s, s1
   LOCAL cTarget := "__TARGET__"
   LOCAL nArea := Select()
   LOCAL cDlm := ","

   IF ! Empty( ::cInto )
      aStruct := {}
      FOR EACH aField IN ::aInfo
         IF ! aField[ 1 ] == "_$B$_"
            AAdd( aStruct, { __normalizeFieldName( aField[ 1 ] ), aField[ 2 ], aField[ 3 ], aField[ 4 ] } )
         ENDIF
      NEXT

      hb_FNameSplit( ::cInto, @cPath, @cName, @cExt )

      SWITCH Lower( cExt )
      CASE ".csv"
      CASE ".xls"
         s := ""
         FOR EACH aField IN aStruct
            s += aField[ 1 ] + cDlm
         NEXT
         s := SubStr( s, 1, Len( s ) - 1 ) + Chr( 13 )+ Chr( 10 )
         FOR EACH xTmp IN ::aData
            s1 := ""
            FOR EACH aField IN aStruct
               s1 += __hbqtXtoS( xTmp[ aField:__enumIndex() ] ) + cDlm
            NEXT
            IF Right( s1, 1 ) == cDlm
               s1 := SubStr( s1, 1, Len( s1 ) - 1 )
            ENDIF
            s += s1 + Chr( 13 ) + Chr( 10 )
         NEXT
         hb_MemoWrit( ::cInto, s )
         EXIT
      OTHERWISE
         dbCreate( ::cInto, aStruct, "DBFCDX" )
         IF ! NetErr() .AND. hb_FileExists( ::cInto )
            USE ( ::cInto ) ALIAS ( cTarget ) EXCLUSIVE NEW VIA "DBFCDX"
            IF ! NetErr()
               FOR EACH xTmp IN ::aData
                  dbAppend()
                  FOR EACH x IN aStruct
                     FieldPut( x:__enumIndex(), xTmp[ x:__enumIndex() ] )
                  NEXT
               NEXT
               dbCommit()
            ENDIF
            dbCloseArea()
         ENDIF
         EXIT
      ENDSWITCH
   ENDIF
   Select( nArea )
   RETURN NIL


STATIC FUNCTION __normalizeFieldName( cName )
   cName := Upper( cName )
   cName := iif( Left( cName, 4 ) == "FUNC", SubStr( cName, 6 ), cName )

   cName := StrTran( cName, "(", "_" )
   cName := StrTran( cName, ")", "_" )
   cName := StrTran( cName, "*", "_" )
   cName := StrTran( cName, "+", "_" )
   cName := StrTran( cName, "-", "_" )
   cName := StrTran( cName, "/", "_" )
   cName := StrTran( cName, ",", "_" )
   cName := StrTran( cName, ",", "_" )

   cName := StrTran( cName, "____", "_" )
   cName := StrTran( cName, "___", "_" )
   cName := StrTran( cName, "__", "_" )
   DO WHILE .T.
      IF ! Left( cName, 1 ) == "_"
         EXIT
      ENDIF
      cName := SubStr( cName, 2 )
   ENDDO
   DO WHILE .T.
      IF ! Right( cName, 1 ) == "_"
         EXIT
      ENDIF
      cName := SubStr( cName, 1, Len( cName ) - 1 )
   ENDDO
   RETURN cName


METHOD parseWhere() CLASS HbSQL
   LOCAL xTmp, nWhere, n, nField, cValue

   IF ! Empty( ::cWhere )
      IF ! ::pullWheres()
         RETURN ::closeAndAlert( ::cMsg )
      ENDIF

      IF ! Empty( ::aWhere )
         nWhere := 0
         IF ! Empty( ::aTags )
            FOR EACH xTmp IN ::aWhere
               IF xTmp[ 3 ] == "=" .OR. xTmp[ 3 ] == "LIKE"
                  n := Len( xTmp[ 1 ] )
                  IF ( ::nIndex := AScan( ::aTags, {|e| Left( e, n ) == xTmp[ 1 ] } ) ) > 0
                     dbSetOrder( ::nIndex )
                     nWhere := xTmp:__enumIndex()
                     ::cSearch := xTmp[ 2 ]
                     IF Left( ::cSearch, 1 ) == '"'
                        ::cSearch := SubStr( ::cSearch, 2, Len( ::cSearch ) - 2 )
                     ENDIF
                     IF Right( ::cSearch, 1 ) == "%"
                        ::cSearch := Left( ::cSearch, Len( ::cSearch ) - 1 )
                        ::cSearchType := "="
                     ELSE
                        ::cSearchType := "=="
                     ENDIF
                     xTmp[ 4 ] := ::cSearch
                     EXIT
                  ENDIF
               ENDIF
            NEXT
         ENDIF
         //
         ::cFor := ""
         FOR EACH xTmp IN ::aWhere
            n := xTmp:__enumIndex()
            IF n != nWhere       // we already processed it as seek field
               nField := AScan( ::aStruct, {|e_| e_[ 1 ] == xTmp[ 1 ] } )
               cValue := xTmp[ 2 ]
               IF Left( cValue, 1 ) == '"'
                  cValue := SubStr( cValue, 2, Len( cValue ) - 2 )
               ENDIF
               xTmp[ 2 ] := cValue
               SWITCH Left( ::aStruct[ nField, 2 ], 1 )
               CASE "C"
                  cValue := '"' + cValue + '"'
                  EXIT
               CASE "D"
                  cValue := StrTran( cValue, "-", "" )
                  cValue := "StoD('" + cValue + "')"
                  EXIT
               CASE "N"
                  cValue := Val( cValue )
                  cValue := hb_ntos( cValue )
                  EXIT
               ENDSWITCH
               xTmp[ 2 ] := cValue
               xTmp[ 5 ] := nField
               //                                                                operator                             value
               ::cFor += "fieldget(" + hb_ntos( nField ) + ") " + iif( xTmp[ 3 ] == "LIKE", "=", xTmp[ 3 ] ) + " " + xTmp[ 2 ] + " .AND. "
            ENDIF
         NEXT
         IF Right( ::cFor,7 ) == " .AND. "
            ::cFor := Left( ::cFor, Len( ::cFor ) - 7 )
         ENDIF
         IF ! Empty( ::cFor )
            ::bFor := &( "{|| " + ::cFor + "}" )
         ENDIF
      ENDIF
   ENDIF
   RETURN .T.


METHOD pullWheres() CLASS HbSQL
   LOCAL n, cClone, cField, cWhere
   LOCAL a_:={}

   cClone := StrTran( ::cWhere, " and ", " AND " )
   DO WHILE .T.
      IF ( n := At( " AND ", cClone ) ) > 0
         AAdd( a_, AllTrim( SubStr( cClone, 1, n - 1 ) ) )
         cClone := SubStr( cClone, n + 5 )
      ELSE
         EXIT
      ENDIF
   ENDDO
   IF ! Empty( cClone )
      AAdd( a_, cClone )
   ENDIF

   FOR EACH cWhere IN a_
      cWhere := StrTran( cWhere, " like ", " LIKE " )

      DO CASE
      CASE At( ">=", cWhere ) > 0
         AAdd( ::aWhere, ::pullKeyValueOperator( cWhere, ">=" ) )
      CASE At( "<=", cWhere ) > 0
         AAdd( ::aWhere, ::pullKeyValueOperator( cWhere, "<=" ) )
      CASE At( "!=", cWhere ) > 0
         AAdd( ::aWhere, ::pullKeyValueOperator( cWhere, "!=" ) )
      CASE At( "<>", cWhere ) > 0
         AAdd( ::aWhere, ::pullKeyValueOperator( cWhere, "<>" ) )
      CASE At( "=", cWhere ) > 0
         AAdd( ::aWhere, ::pullKeyValueOperator( cWhere, "=" ) )
      CASE At( ">", cWhere ) > 0
         AAdd( ::aWhere, ::pullKeyValueOperator( cWhere, ">" ) )
      CASE At( "<", cWhere ) > 0
         AAdd( ::aWhere, ::pullKeyValueOperator( cWhere, "<" ) )
      CASE At( "LIKE", cWhere ) > 0
         AAdd( ::aWhere, ::pullKeyValueOperator( cWhere, "LIKE" ) )
      ENDCASE
   NEXT

   FOR EACH a_ IN ::aWhere
      IF ! HB_ISARRAY( a_ )
         ::cMsg := "WHERE clause - mal-formed!"
         RETURN .F.
      ENDIF
      cField := a_[ 1 ]
      IF AScan( ::aStruct, {|e_| e_[ 1 ] == cField } ) == 0
         ::cMsg := "WHERE clause - field does not exists!"
         RETURN .F.
      ENDIF
   NEXT
   RETURN .T.


METHOD pullIndexes() CLASS HbSQL
   LOCAL n, xTmp

   FOR n := 1 TO 50
      IF ( xTmp := ( ::cAlias )->( IndexKey( n ) ) ) == ""
         EXIT
      ENDIF
      AAdd( ::aTags, Upper( xTmp ) )
   NEXT
   RETURN Self


METHOD openTable() CLASS HbSQL
   LOCAL lTableExists, n

   IF Empty( ::cFrom )
      Alert( "FROM clause missing!" ) ; RETURN .F.
   ENDIF
   IF ( n := At( "|", ::cFrom ) ) > 0
      ::cDriver := SubStr( ::cFrom, 1, n - 1 )
      ::cTable := SubStr( ::cFrom, n + 1 )
   ELSE
      ::cDriver := "DBFCDX"
      ::cTable := ::cFrom
   ENDIF
   hb_FNameSplit( ::cTable, @::cPath, @::cName, @::cExt )

   SWITCH Upper( ::cDriver )
   CASE "DBFCDX"
   CASE "DBFNTX"
   CASE "DBFNSX"
   CASE "ADS"
      IF Empty( ::cExt )
         ::cTable := ::cTable + ".dbf"
      ENDIF
      lTableExists := hb_FileExists( ::cTable )
      EXIT
   CASE "CACHERDD"
      lTableExists := .T.
      EXIT
   ENDSWITCH

   IF ! lTableExists
      Alert( "Table;" + ::cTable + ";" + "does not exists!" )
      RETURN .F.
   ENDIF

   USE ( ::cTable ) VIA ( ::cDriver ) Alias ( ::cAlias ) SHARED NEW
   IF NetErr()
      Alert( "Some error in opening ;" + ::cTable )
      RETURN .F.
   ENDIF
   ::aStruct:= dbStruct()
   AEval( ::aStruct, {|e_| e_[ 2 ] := Left( e_[ 2 ], 1 ) } )
   RETURN .T.


METHOD closeAndAlert( cAlert ) CLASS HbSQL
   IF Select( ::cAlias ) > 0
      Select( ::cAlias )
      dbCloseArea()
   ENDIF
   RETURN Alert( cAlert ) == 99                    // always return false


METHOD pullKeyValueOperator( cWhere, cOperator ) CLASS HbSQL
   LOCAL cField, cValue
   LOCAL n := hb_At( cOperator, cWhere )
   IF n > 0
      cField := Upper( AllTrim( SubStr( cWhere, 1, n - 1 ) ) )
      cValue := AllTrim( SubStr( cWhere, n + Len( cOperator ) ) )
      RETURN { cField, cValue, cOperator, NIL, NIL, NIL }
   ENDIF
   RETURN NIL


METHOD browseData() CLASS HbSQL
   LOCAL aField, nColumns, nDTCols
   LOCAL aStr := {}

   nColumns := 0
   FOR EACH aField IN ::aInfo
      IF aField[ 1 ] != "_$B$_"
         AAdd( aStr, { aField[ 1 ], aField[ 2 ], aField[ 3 ], aField[ 4 ] } )
         nColumns += Max( aField[ 3 ], Len( aField[ 1 ] ) )
      ENDIF
   NEXT
   nColumns += ( 3 * Len( ::aInfo ) ) + 3
   nDTCols := hb_gtInfo( HB_GTI_DESKTOPCOLS )
   SetMode( 25, Max( 80, Min( nColumns, nDTCols ) ) )
   //
   hb_gtInfo( HB_GTI_SCREENHEIGHT, hb_gtInfo( HB_GTI_SCREENHEIGHT ) + 1 )

*
*  Show a new window with SQL result
*
*  __browseData( ::aData, aStr )
*

   RETURN NIL

Static FUNCTION __hbqtAProcessUnique( dat_, ele_, nAdd_, aOpr_ )
   LOCAL i, v, v1, d_, nCounter
   LOCAL v_:= {}
   LOCAL dd_:= {}
   LOCAL sum_:= AFill( Array( Len( nAdd_ ) ), 0 )

   d_:= __aSortEle( AClone( dat_ ), ele_ )

   AEval( ele_, {|e| AAdd( v_, d_[ 1, e ] ) } )
   v := __aIndexCmp( v_ )
   nCounter := 0
   FOR i := 1 TO Len( d_ )
      v_:= {}
      AEval( ele_, {|e| AAdd( v_, d_[ i, e ] ) } )
      IF ! ( v == ( v1 := __aIndexCmp( v_ ) ) )
         __aAdjustForAvg( sum_, nCounter, aOpr_ )
         AAdd( dd_, d_[ i - 1 ] )
         AEval( nAdd_, {|e, j| dd_[ Len( dd_ ), e ] := sum_[ j ] } )
         sum_:= AFill( sum_, 0 )
         v := v1
         nCounter := 0
      ENDIF
      nCounter++
      __aApplyOperation( nAdd_, sum_, d_[ i ], nCounter, aOpr_ )
   NEXT
   __aAdjustForAvg( sum_, nCounter, aOpr_ )
   AAdd( dd_, d_[ i - 1 ] )
   AEval( nAdd_, {|e, j| dd_[ Len( dd_ ), e ] := sum_[ j ] } )
   RETURN dd_

Static FUNCTION __hbqtXToS( xVrb )
   SWITCH ValType( xVrb )
   CASE "C" ; RETURN xVrb
   CASE "D" ; RETURN DToC( xVrb )
   CASE "N" ; RETURN LTrim( Str( xVrb ) )
   CASE "L" ; RETURN iif( xVrb, "Yes", "No" )
   CASE "A" ; RETURN hb_ValToExp( xVrb )
   CASE "B" ; RETURN "< block >"
   CASE "O" ; RETURN "< object >"
   ENDSWITCH
   RETURN ""

STATIC FUNCTION __aSortEle( ddd_, ele_ )
   LOCAL i, s, j, k
   LOCAL dum_:= {}
   LOCAL dat_:= {}
   LOCAL nRecs := Len( ddd_ )
   LOCAL nEle := Len( ele_ )
   LOCAL typ_:= Array( nEle )

   AEval( ele_, {|e,i| typ_[ i ] := ValType( ddd_[ 1, e ] ) } )

   FOR i := 1 TO nRecs
       s := ""
       FOR j := 1 TO nEle
          k := ele_[ j ]
          SWITCH typ_[ j ]
          CASE "C" ; s += ddd_[ i, k ] ; EXIT
          CASE "D" ; s += DToS( ddd_[ i, k ] ) ; EXIT
          CASE "N" ; s += Str( ddd_[ i, k ], 17, 4 ) ; EXIT
          CASE "L" ; s += iif( ddd_[ i, k ], "T", "F" ) ; EXIT
          ENDSWITCH
       NEXT
       AAdd( dum_, { s, i } )
   NEXT

   ASort( dum_, NIL, NIL, {|e_, f_| e_[ 1 ] < f_[ 1 ] } )

   FOR i := 1 TO Len( dum_ )
      AAdd( dat_, ddd_[ dum_[ i, 2 ] ] )
   NEXT
   RETURN dat_

STATIC FUNCTION __aIndexCmp( a_ )
   LOCAL i
   LOCAL s := ''
   FOR i := 1 TO Len( a_ )
      s := __aIndexKey( s, a_[ i ] )
   NEXT
   RETURN s

STATIC FUNCTION __aAdjustForAvg( sum_, nCounter, aOpr_ )
   LOCAL j
   FOR j := 1 TO Len( aOpr_ )
      IF aOpr_[ j ] == "AVG"
         sum_[ j ] := sum_[ j ] / nCounter
      ENDIF
   NEXT
   RETURN NIL

STATIC FUNCTION __aApplyOperation( nAdd_, sum_, d_, nCounter, aOpr_ )
   LOCAL j

   FOR j := 1 TO Len( aOpr_ )
      SWITCH aOpr_[ j ]
      CASE "COUNT"
      CASE "SUM"
      CASE "AVG"
         sum_[ j ] += d_[ nAdd_[ j ] ]
         EXIT
      CASE "MIN"
         sum_[ j ] := iif( nCounter == 1, d_[ nAdd_[ j ] ], Min( sum_[ j ], d_[ nAdd_[ j ] ] ) )
         EXIT
      CASE "MAX"
         sum_[ j ] := Max( sum_[ j ], d_[ nAdd_[ j ] ] )
         EXIT
      ENDSWITCH
   NEXT
   RETURN NIL

STATIC FUNCTION __aIndexKey( s, v )
   SWITCH ValType( v )
   CASE "C" ; RETURN s += v
   CASE "N" ; RETURN s += Str( v, 17, 4 )
   CASE "D" ; RETURN s += DToS( v )
   CASE "L" ; RETURN s += iif( v, "T", "F" )
   ENDSWITCH
   RETURN s