/*
 * Xailer source code:
 *
 * SQLiteRecords.prg
 * Clase TSQLiteRecords()
 *
 * Copyright 2003, 2007 Jose F. Gimenez
 * Copyright 2003, 2007 Xailer.com
 * All rights reserved
 *
 */

#include "Xailer.ch"
#include "error.ch"
#include "Dataset.ch"
#include "SQLite.ch"


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

CLASS XSQLiteRecords FROM TRecords

PUBLIC:
   PROPERTY oDataSource READONLY READ INLINE ::oDataSet:oDataSource AS TDataSource

   METHOD Open() // --> lSuccess
   METHOD Close() // --> .T.

   METHOD CanEdit()     INLINE ::lIsTable // --> lResult

   METHOD AddNew() // --> lSuccess
   METHOD Cancel() // --> lSuccess
   METHOD Update() // --> lSuccess
   METHOD Delete() // --> lSuccess

   METHOD FieldCount()  INLINE ::FCount // --> nCount
   METHOD FieldInfo( nField, nType ) // --> aInfo
   METHOD FieldGet( n, lForceRecno ) // --> Value | Nil
   METHOD FieldPut( n, v ) // --> Value

   PROPERTY Recno       READONLY INIT 1
   PROPERTY RecCount    READONLY INIT 0
   PROPERTY Bof         READONLY INIT .T.
   PROPERTY Eof         READONLY INIT .T.
   PROPERTY FCount      READONLY INIT 0

   METHOD GoTop()       INLINE ::Goto( 1 ) // --> Nil
   METHOD GoBottom()    INLINE ::Goto( ::RecCount ) // --> Nil
   METHOD Goto( n ) // --> Nil
   METHOD GotoRowID( n ) INLINE ::Goto( AScan( ::aRowID, n ) ) // --> Nil
   METHOD RowID() // --> nRowId
   METHOD Skip( n ) // --> Nil
   METHOD nSkip( n ) // --> nSkipped

   METHOD KeyCount()    INLINE ::RecCount // --> nCount
   METHOD KeyNo()       INLINE ::Recno // --> nPosition
   METHOD KeyGoto( n )  INLINE ::Goto( n ) // --> Nil

   METHOD Refresh()        INLINE ::QueryAll() // --> lSuccess
   METHOD RefreshCurrent() INLINE ::QueryCurrent() // --> lSuccess

   METHOD Locate( cExp, lContinue ) // --> lSuccess
   METHOD Filter( cExp ) // --> cOldFilter
   METHOD Sort( cExp ) // --> .T.

   METHOD Clone() // --> oSQLQuery | oSQLTable

   METHOD GetRows()  INLINE ::aData // --> aRows
   METHOD GetRow( nRow, lMove ) // --> aRow

RESERVED:
   PROPERTY cTable   INIT ""
   PROPERTY cTableAlias
   PROPERTY cFields
   PROPERTY cFrom
   PROPERTY cWhere
   PROPERTY cGroupBy
   PROPERTY cHaving
   PROPERTY cOrder
   PROPERTY cLimit
   PROPERTY cOffset
   PROPERTY cFilter        INIT ""
   PROPERTY cFilterHaving  INIT ""

   PROPERTY aStruct
   PROPERTY aData
   PROPERTY aRowId
   PROPERTY aTypes


   METHOD QueryAll()
   METHOD QueryCurrent( aData )
   METHOD GetFilterExp( oDBBrowse )
   METHOD GetSeekExp( oDataField, cSeek )
   METHOD DbStruct( Handle, nFromField, nToField )
   METHOD Append()
   METHOD Replace()
   METHOD UtfToAnsi()

PROTECTED:
   DATA aLocate
   DATA nLocate   INIT -1
   DATA lOnAppend INIT .F.

ENDCLASS

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

METHOD Open() CLASS XSQLiteRecords

   LOCAL cSql, cName, n, lIsSelect

   // Comprobar que hay conexión

   IF ! ::oDataSource:lConnected
      RETURN .F.
   ENDIF

   lIsSelect := Left( Upper( ::oDataSet:BuildSQLSt() ), 7 ) == "SELECT " .OR. ::lIsTable

   ::cTable      := Nil
   ::cTableAlias := Nil
   ::cFrom       := Nil
   ::cWhere      := Nil
   ::cGroupBy    := Nil
   ::cHaving     := Nil
   ::cOrder      := Nil
   ::cLimit      := Nil
   ::cOffset     := Nil

   IF lIsSelect .AND. ::oDataSet:nCursorType != adOpenForwardOnly     // TQueryReport
      // Parsing del comando
      WITH OBJECT ::oDataSet
         cName := :BuildSQLSt()
         cSql  := Upper( cName )
         IF ( n := __XA_Sql_RAt( " OFFSET ", cSql ) ) > 0
            ::cOffset := LTrim( Substr( cName, n + 8 ) )
            cSql := Left( cSql, n - 1 )
         ENDIF
         IF ( n := __XA_Sql_RAt( " LIMIT ", cSql ) ) > 0
            ::cLimit := LTrim( Substr( cName, n + 7, Len( cSql ) - n - 6 ) )
            cSql := Left( cSql, n - 1 )
            ::oDataset:nMaxRecords := Val( ::cLimit )
         ENDIF
         IF ( n := __XA_Sql_RAt( " ORDER BY ", cSql ) ) > 0
            ::cOrder := LTrim( Substr( cName, n + 10, Len( cSql ) - n - 9 ) )
            cSql := Left( cSql, n - 1 )
         ENDIF
         IF ( n := __XA_Sql_RAt( " HAVING ", cSql ) ) > 0
            ::cHaving := LTrim( Substr( cName, n + 8, Len( cSql ) - n - 7 ) )
            cSql := Left( cSql, n - 1 )
         ENDIF
         IF ( n := __XA_Sql_RAt( " GROUP BY ", cSql ) ) > 0
            ::cGroupBy := LTrim( Substr( cName, n + 10, Len( cSql ) - n - 9 ) )
            cSql := Left( cSql, n - 1 )
            ::oDataset:lReadOnly := .T.
         ENDIF
         IF ( n := __XA_Sql_RAt( " WHERE ", cSql ) ) > 0
            ::cWhere := LTrim( Substr( cName, n + 7, Len( cSql ) - n - 6 ) )
            cSql := Left( cSql, n - 1 )
         ENDIF
         IF ( n := __XA_Sql_At( " FROM ", cSql ) ) > 0
            ::cFrom := AllTrim( Substr( cName, n + 6, Len( cSql ) - n - 5 ) )
            cSql := Left( cSql, n - 1 )
            IF ( n := __XA_Sql_At( "SELECT ", cSql ) ) > 0
               ::cFields := AllTrim( Substr( cName, n + 7, Len( cSql ) - n - 6 ) )
            ENDIF
            IF ( n := __XA_Sql_At( " AS ", ::cFrom ) ) > 0
               ::cTableAlias := Substr( ::cFrom, n + 4 )
               IF ( n := At( " ", ::cTableAlias ) ) > 0 .OR. ( n := At( ",", ::cTableAlias ) ) > 0
                  ::cTableAlias := Left( ::cTableAlias, n - 1 )
               ENDIF
            ENDIF
            IF ( n := At( " ", ::cFrom ) ) > 0 .OR. ( n := At( ",", ::cFrom ) ) > 0
               ::cTable := Left( ::cFrom, n - 1 )
            ELSE
               ::cTable := ::cFrom
            ENDIF
         ELSEIF ::lIsTable
            ::cFrom := Left( cName, Len( cSql ) )
            ::cTable := ::cFrom
         ENDIF
         IF !Empty( ::cTableAlias )
            ::cTableAlias := StrTran( ::cTableAlias, CRLF, "" )
         ENDIF
         ::cTable := StrTran( ::cTable, CRLF, "" )
         DEFAULT ::cTableAlias TO ::cTable
      END
   ENDIF

   // Apertura del recordset

RETURN ::QueryAll( .T. )

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

METHOD QueryAll( lOpening ) CLASS XSQLiteRecords

   LOCAL Handle, nRecno, nOld
   LOCAL cSql
   LOCAL lIsSelect, lUtf

   lIsSelect := Left( Upper( ::oDataSet:BuildSQLSt() ), 7 ) == "SELECT " .OR. ::lIsTable
   lUtf := ::UtfToAnsi()

   IF ::oDataSource:lAllowProcessMessages
      ProcessMessages()
   ENDIF

   IF ::oDataSet:lOpen .OR. ( lOpening != NIL .AND. lOpening )

      cSql := ::cFrom
      nOld := ::Recno

      IF lIsSelect
         IF ! Empty( ::cWhere )
            cSql += " WHERE " + ::cWhere
            IF !Empty( ::cFilter )
               cSql += " AND " + ::cFilter
            ENDIF
         ELSEIF ! Empty( ::cFilter )
            cSql += " WHERE " + ::cFilter
         ENDIF
         IF ! Empty( ::cGroupBy )
            cSql += " GROUP BY " + ::cGroupBy
         ENDIF
         IF ! Empty( ::cHaving )
            cSql += " HAVING " + ::cHaving
            IF !Empty( ::cFilterHaving )
               cSql += " AND " + ::cFilterHaving
            ENDIF
         ELSEIF ! Empty( ::cFilterHaving )
            cSql += " HAVING " + ::cFilterHaving
         ENDIF
         IF ! Empty( ::cOrder )
            cSql += " ORDER BY " + ::cOrder
         ENDIF
         IF ::oDataset:nMaxRecords > 0
            cSql += " LIMIT " + LTrim( Str( ::oDataset:nMaxRecords ) )
         ELSEIF ! Empty( ::cLimit )
            cSql += " LIMIT " + ::cLimit
         ENDIF
         IF ! Empty( ::cOffset )
            cSql += " OFFSET " + ::cOffset
         ENDIF
         cSql := "SELECT " + ::cTableAlias + "._RowId_ AS _RowId_," + IIF( Empty( ::cFields ), "*", ::cFields ) + " FROM " + cSql
      ELSE
         cSql := ::oDataset:BuildSqlSt()
         ::oDataSet:lReadOnly := .T.
      ENDIF

      ::aRowId := {}
      ::aTypes := {}
      ::aData  := {}

      WITH OBJECT ::oDataSource
         Handle := :Prepare( cSql, .T. )
         IF Handle != 0
            WHILE( :GetRows( Handle, ::aData, 1024, ::aRowId,, @nRecno, ::aTypes, .F., lUtf ) == SQLITE_ROW )
               ProcessMessages()
            ENDDO
            IF ::aStruct == Nil
               ::dbStruct( Handle )
            ENDIF
         ELSE
            Handle := :Prepare( ::oDataset:BuildSqlSt() )
            ::oDataset:lReadOnly := .T.
            IF Handle != 0
               WHILE( :GetRows( Handle, ::aData, 1024,,,,::aTypes, .F., lUtf ) == SQLITE_ROW )
                  ProcessMessages()
               ENDDO
               IF ::aStruct == Nil
                  ::dbStruct( Handle, 1 )
               ENDIF
            ENDIF
         END
         :Finalize( Handle )
      END

      ::RecCount := Len( ::aData )
      ::Bof      := .F.
      ::Eof      := ( ::RecCount == 0 )
   //   ::Recno    := nRecno
      IF !Empty( nOld ) .And. nOld <= ::RecCount
         ::Recno := nOld
      ELSE
         ::Recno := Min( 1, ::RecCount )
      ENDIF

   ENDIF

RETURN ( Handle != 0 )

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

METHOD QueryCurrent( aData ) CLASS XSQLiteRecords

   LOCAL aResult, aTypes
   LOCAL cSql

   IF ::Recno > ::RecCount .or. Empty( ::cFrom ) .or. ::Recno > Len( ::aData )
      RETURN .F.
   ENDIF

   cSql := "SELECT " + IIF( Empty( ::cFields ), "*", ::cFields ) + ;
           " FROM " + ::cFrom + ;
           " WHERE " + ::cTableAlias + "._RowId_=" + LTrim( Str( ::aRowId[ ::Recno ] ) )

   IF ::oDataSource:Execute( cSql,, @aResult, @aTypes, .F., ::UtfToAnsi() ) .AND. ! Empty( aResult )
      IF Pcount() == 0
         ::aData[ ::Recno ] := aResult[ 1 ]
         ::aTypes[ ::Recno ] := aTypes[ 1 ]
      ELSE
         aData := aResult[ 1 ]
      ENDIF
      RETURN .T.
   ENDIF

RETURN .F.

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

METHOD DbStruct( Handle, nFromField, nToField ) CLASS XSQLiteRecords

   LOCAL n, cType, nLen, nDec, uDefValue, nAt
   LOCAL aName := ::oDataSource:GetColumnNames( Handle )
   LOCAL aType := ::oDataSource:GetColumnTypes( Handle )
   LOCAL aTables := ::oDataSource:GetColumnTables( Handle )
   LOCAL aDdl, aDat, cLine, cField, aPK, aTmp
   LOCAL lInc, lNul, lPK

   DEFAULT nFromField   TO 2
   DEFAULT nToField     TO Len( aName )

   ::aStruct := {}
   ::FCount  := nToField - nFromField + 1

   ::oDatasource:Execute( "SELECT sql FROM SQLITE_MASTER WHERE TYPE = 'table' and upper( tbl_name ) = '" +;
                           Upper( ::cTable ) + "'",, @aDdl )

   aDat := {}
   aPK  := {}

   // Buscamos la información de AutoIncrement y NotNull de la tabla primaria

   IF !Empty( aDdl )
      aDdl := ParseInfo( aDdl[ 1, 1 ] )

      FOR EACH cLine IN aDdl
         IF cLine != "PRIMARY KEY"
            nAt := At( "]", cLine )
            IF nAt == 0
               nAt := At( " ", cLine )
            ENDIF
            cField := Upper( Left( cLine, nAt -1 ) )
            IF Substr( cField, 1, 1 ) == "["
               cField := Substr( cField, 2 )
            ENDIF
            lInc := "AUTOINCREMENT" $ Upper( cLine )
            lNul := !( "NOT NULL" $ Upper( cLine ) )
            lPK  := lInc .OR. "PRIMARY KEY" $ Upper( cLine )
            Aadd( aDat, { cField, lInc, lNul } )
            IF lPK
               Aadd( aPK, cField )
            ENDIF
         ELSE
            cLine := Substr( cLine, At( "(", cLine ) + 1 )
            cLine := Alltrim( Left( cLine, At( ")", cLine ) - 1 ) )
            aTmp := hb_atokens( cLine, "," )
            FOR EACH cField IN aTmp
               cField := Upper( StrPattern( cField, "[]", .F. ) )
               Aadd( aPK , cField )
            NEXT
         ENDIF
      NEXT
   ENDIF

   FOR n := nFromField TO nToField
      aType[ n ] := Upper( aType[ n ] )
      IF Left( aType[ n ], 4 ) == "CHAR" .OR. ;
         Left( aType[ n ], 7 ) == "VARCHAR" .OR. ;
         Left( aType[ n ], 4 ) == "TEXT" .OR. ;
         Left( aType[ n ], 10 ) == "MEDIUMTEXT" .OR. ;
         Left( aType[ n ], 6 ) == "STRING"
         IF At( "(", aType[ n ] ) > 0
            cType := "C"
            nLen := 0
         ELSE
            cType := "M"
            nLen := 10
         ENDIF
         nDec := 0
         uDefValue := ""
      ELSEIF Left( aType[ n ], 4 ) = "JSON"
         cType := "H"
         nLen := 0
         nDec := 0
         uDefValue := ""
      ELSEIF Left( aType[ n ], 4 ) = "MEMO"
         cType := "M"
         nLen := 10
         nDec := 0
         uDefValue := ""
      ELSEIF Left( aType[ n ], 3 ) = "INT"
         cType := "N"
         nLen := 10
         nDec := 0
         uDefValue := 0
      ELSEIF Left( aType[ n ], 6 ) = "BIGINT"
         cType := "N"
         nLen := 19
         nDec := 0
         uDefValue := 0
      ELSEIF Left( aType[ n ], 8 ) = "CURRENCY"
         cType := "N"
         nLen := 16
         nDec := 2
         uDefValue := 0
      ELSEIF Left( aType[ n ], 5 ) = "FLOAT"
         cType := "N"
         nLen := 18
         nDec := 4
         uDefValue := 0
      ELSEIF Left( aType[ n ], 6 ) = "DOUBLE"
         cType := "N"
         nLen := 20
         nDec := 6
         uDefValue := 0
      ELSEIF Left( aType[ n ], 7 ) = "DECIMAL" .OR. Left( aType[ n ], 7 ) = "NUMERIC" .OR. Left( aType[ n ], 4 ) = "REAL"
         nLen := 20
         nDec := 6
         IF ( nAt := At( "(", aType[ n ] ) ) > 0
            cType := Substr( aType[ n ], nAt + 1 )
            nLen := Val( cType )
            IF ( nAt := At( ",", cType ) ) > 0
               nDec := Val( Substr( cType, nAt + 1 ) )
            ENDIF
         ENDIF
         cType := "N"
         uDefValue := 0
      ELSEIF Left( aType[ n ], 8 ) = "DATETIME" .OR. Left( aType[ n ], 9 ) = "TIMESTAMP"
         cType := "T"
         nLen := 19
         nDec := 0
         uDefValue := HB_CToT( "" )
      ELSEIF Left( aType[ n ], 4 ) = "DATE"
         cType := "D"
         nLen := 8
         nDec := 0
         uDefValue := CTOD( "" )
      ELSEIF Left( aType[ n ], 7 ) = "LOGICAL" .OR. Left( aType[ n ], 4 ) = "BOOL"
         cType := "L"
         nLen := 1
         nDec := 0
         uDefValue := .F.
      ELSEIF Left( aType[ n ], 4 ) = "BLOB"
         cType := "B"
         nLen := 0
         nDec := 0
         uDefValue := Nil
      ELSEIF Empty( aType[ n ] )
         cType     := "C"
         nLen      := 0
         nDec      := 0
         uDefValue := ""
         IF Len( ::aData ) > 0
            //LogDebug( "SQLite Field '" + aName[ n ] + "' with empty type.")
            SWITCH Valtype( ::aData[ 1, n + 1 - nFromField ] )
            CASE "N"
               cType := "N"
               nLen := 20
               nDec := 6
               uDefValue := 0
               EXIT
            CASE "D"
            CASE "T"
               cType := "D"
               nLen := 8
               nDec := 0
               uDefValue := CTOD( "" )
               EXIT
            CASE "L"
               cType := "L"
               nLen := 1
               nDec := 0
               uDefValue := .F.
               EXIT
            OTHERWISE
               cType := "U"
               nLen := 0
               nDec := 0
               uDefValue := NIL
            END SWITCH
         ENDIF
      ELSE
         cType := "C"
         nLen := 0
         nDec := 0
         uDefValue := ""
         LogDebug( "SQLite Field '" + aName[ n ] + "' with type (" + aType[ n ] +") undefined. Treated as string.")
      ENDIF

      CalcLenDec( aType[ n ], @nLen, @nDec )

      lInc := .F.
      lNul := .T.
      lPK  := .F.

      IF Upper( aTables[ n ] ) ==  Upper( ::cTable )
         IF ( nAt := Ascan( aDat, {|v| v[ 1 ] == Upper( aName[ n ] ) }  ) ) > 0
            lInc := aDat[ nAt, 2 ]
            lNul := aDat[ nAt, 3 ]
         ENDIF
         IF ( nAt := Ascan( aPK, {|v| v == Upper( aName[ n ] ) }  ) ) > 0
            lPK := .T.
         ENDIF
      ENDIF

      AAdd( ::aStruct, { aName[ n ], cType, nLen, nDec, uDefValue, aType[ n ], 0, aTables[ n ], lInc, lNul, lPK } )
   NEXT

RETURN ::aStruct

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

METHOD FieldInfo( nField, nPos ) CLASS XSQLiteRecords

   LOCAL aInfo := Array( XA_FLDINFO_COUNT )

   aInfo[ XA_FLDINFO_NAME ]       := ::aStruct[ nField, 1 ]
   aInfo[ XA_FLDINFO_SQLTYPE ]    := ::aStruct[ nField, 6 ]
   aInfo[ XA_FLDINFO_BASICTYPE ]  := ::aStruct[ nField, 2 ]
   aInfo[ XA_FLDINFO_SQLLEN ]     := ::aStruct[ nField, 3 ]
   aInfo[ XA_FLDINFO_BASICLEN ]   := ::aStruct[ nField, 3 ]
   aInfo[ XA_FLDINFO_SQLDEC ]     := ::aStruct[ nField, 4 ]
   aInfo[ XA_FLDINFO_BASICDEC ]   := ::aStruct[ nField, 4 ]
   aInfo[ XA_FLDINFO_AUTOINC ]    := ::aStruct[ nField, 9 ]
   aInfo[ XA_FLDINFO_ALLOWSNULL ] := ::aStruct[ nField, 10 ]
   aInfo[ XA_FLDINFO_PK ]         := ::aStruct[ nField, 11 ]
   aInfo[ XA_FLDINFO_EDITABLE ]   := !Empty( ::cTable ) .AND. Upper( ::aStruct[ nField, 8 ] ) == Upper( ::cTable )
   aInfo[ XA_FLDINFO_DEFVALUE ]   := IIF( Empty( ::aStruct[ nField, 6 ] ) .OR. ::aStruct[ nField, 9 ], NIL, XA_GetDefValue( aInfo[ XA_FLDINFO_BASICTYPE ] ) )
   aInfo[ XA_FLDINFO_TABLE ]      := ::aStruct[ nField, 8 ]

   IF ! Empty( nPos )
      RETURN aInfo[ nPos ]
   ENDIF

RETURN aInfo

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

METHOD Close() CLASS XSQLiteRecords

   ::aData    := Nil
   ::aRowId   := Nil
   ::aTypes   := Nil
   ::aStruct  := Nil
   ::Recno    := 1
   ::RecCount := 0
   ::Bof      := .T.
   ::Eof      := .T.
   ::FCount   := 0

RETURN .T.

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

METHOD FieldGet( nField, lForceRecno ) CLASS XSQLiteRecords

   LOCAL nRecno

   IF nField > 0 .AND. nField <= ::FCount
      IF ::lOnAppend .AND. ( lForceRecno == NIL .OR. !lForceRecno )
         RETURN ::aRecord[ nField ]
      ENDIF
      nRecno := ::Recno
      IF nRecno > 0 .AND. nRecno <= ::RecCount
         RETURN ::aData[ nRecno, nField ]
      ENDIF
   ENDIF

RETURN Nil

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

METHOD FieldPut( nField, Value ) CLASS XSQLiteRecords

   LOCAL nRecno

   IF ::lOnAppend
      ::aRecord[ nField ] := Value
   ELSE
      nRecno := ::Recno
      IF nRecno > 0 .AND. nRecno <= ::RecCount
         ::aData[ nRecno, nField ] := Value
      ENDIF
   ENDIF

RETURN Value

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

METHOD AddNew() CLASS XSQLiteRecords

   ::lOnAppend := .T.

RETURN NIL

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

METHOD Cancel() CLASS XSQLiteRecords

   IF ::lOnAppend
      ::lOnAppend := .F.
   ELSEIF !::Eof()
      Aeval( ::aRecord, {|v,e| ::aData[ ::Recno, e ] := v } )
   ENDIF

RETURN NIL

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

METHOD Update() CLASS XSQLiteRecords

   LOCAL lSuccess

   IF ::lOnAppend
      lSuccess := ::Append()
      IF lSuccess
         ::lOnAppend := .F.  // IOZ (09/03/19): Cuando es error es el método Cancel() es que lo pone a .F.
      ENDIF
   ELSE
      lSuccess := ::Replace()
   ENDIF

RETURN lSuccess

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

METHOD Append() CLASS XSQLiteRecords

   LOCAL oField
   LOCAL aResult, aData
   LOCAL cSql, cFields, cValues
   LOCAL nRecno, nRowId
   LOCAL lUtf

   cSql    := "INSERT INTO " + ::cTable
   cFields := ""
   cValues := ""
   lUtf    := ::UtfToAnsi()

   FOR EACH oField IN ::oDataset:aFields
      IF oField:lModified .AND. oField:lEditable
         IF !Empty( cFields )
            cFields += ", "
            cValues += ", "
         ENDIF
         cFields += oField:cName
         IF ::aRecord[ oField:__EnumIndex() ] == Nil
            cValues += "NULL"
         ELSEIF oField:cType == "H"
            cValues += "'" + StrSQLite( HB_JsonEncode( ::aRecord[ oField:__EnumIndex() ] ) ) + "'"
         ELSEIF oField:cType == "B"
            cValues += "X'" + StringToHex( ::aRecord[ oField:__EnumIndex() ] ) + "'"
         ELSE
            cValues += ValToStr( ::aRecord[ oField:__EnumIndex() ], lUtf )
         ENDIF
      ENDIF
   NEXT

   cSql += "( " + cFields + " ) VALUES( " + cValues + " )"

   IF ! ::oDataSource:Execute( cSql,, @aResult )
      RETURN .F.
   ENDIF

   nRowId := ::oDataSource:LastInsertRowId()

   // Esto es para localizar en que posicion se ha insertado el registro

   cSql := ::cFrom

   IF ! Empty( ::cWhere )
      cSql += " WHERE " + ::cWhere
      IF ! Empty( ::cFilter )
         cSql += " AND " + ::cFilter
      ENDIF
   ELSEIF ! Empty( ::cFilter )
      cSql += " WHERE " + ::cFilter
   ENDIF
   IF ! Empty( ::cHaving )
      cSql += " HAVING " + ::cHaving
      IF !Empty( ::cFilterHaving )
         cSql += " AND " + ::cFilterHaving
      ENDIF
   ELSEIF ! Empty( ::cFilterHaving )
      cSql += " HAVING " + ::cFilterHaving
   ENDIF
   IF ! Empty( ::cOrder )
      cSql += " ORDER BY " + ::cOrder
   ENDIF

   ::oDataSource:Execute( "SELECT " + ::cTableAlias + "._RowId_ FROM " + cSql,, @aData )

   Aadd( ::aData, Nil )

	IF Empty( aData ) .OR. aData[ Len( aData ), 1 ] == nRowId .OR. ( nRecno := AScan( aData, {| x | x[ 1 ] == nRowId } ) ) == 0
		AAdd( ::aRowId, nRowId )
      AAdd( ::aTypes, Nil )
      ::Recno := Len( ::aData )
   ELSE
      AIns( ::aData, nRecno )
      AAdd( ::aRowId, Nil )
      AAdd( ::aTypes, Nil )
      AIns( ::aRowId, nRecno )
      AIns( ::aTypes, nRecno )
      ::aRowId[ nRecno ] := nRowId
      ::Recno := nRecno
   ENDIF

   ::RecCount := Len( ::aData )

   ::QueryCurrent() // carga el registro añadido

RETURN .T.

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

METHOD Replace() CLASS XSQLiteRecords

   LOCAL oField
   LOCAL hData
   LOCAL aData
   LOCAL cSql, cMod, n
   LOCAL nRecno := ::Recno
   LOCAL lUtf := ::UtfToAnsi()

   IF ::oDataset:nLockType == adLockOptimistic

      IF ! ::QueryCurrent( @aData )
         ::oDataSource:NewError( "Could not retrieve current row values, 'Update' canceled",, ::oDataset )
         RETURN .F.
      ENDIF

      IF Len( aData ) == 0
         ::oDataSource:NewError( "Row deleted after the 'Edit' operation, 'Update' canceled",, ::oDataset )
         RETURN .F.
      ENDIF

      cMod := ""
      FOR n := 1 TO Len( aData )
         IF !( aData[ n ] == ::aRecord[ n ] )
            IF ! Empty( cMod )
               cMod += ", "
            ENDIF
            cMod += ::oDataset:aFields[ n ]:cName
         ENDIF
      NEXT

      IF ! Empty( cMod )
         ::oDataSource:NewError( "The fields:" + cMod + " have changed after the 'Edit' operation, 'Update' canceled",, ::oDataset )
         RETURN .F.
      ENDIF

   ENDIF

   cSql := ""
   FOR EACH oField IN ::oDataset:aFields
      IF oField:lModified .AND. oField:lEditable
         IF !Empty( cSql )
            cSql += ", "
         ENDIF
         cSql += oField:cName + "="
         IF ::aData[ nRecno, oField:__EnumIndex() ] == Nil
            cSql += "NULL"
         ELSEIF oField:cType == "H"
            hData := ::aData[ nRecno, oField:__EnumIndex() ]
            IF !Empty( hData )
               cSql += "'" + StrSQLite( HB_JsonEncode( hData ) ) + "'"
            ELSE
               cSql += "NULL"
            ENDIF
         ELSEIF oField:cType == "B"
            cSql += "X'" + StringToHex( ::aData[ nRecno, oField:__EnumIndex() ] ) + "'"
         ELSE
            cSql += ValToStr( ::aData[ nRecno, oField:__EnumIndex() ], lUtf )
         ENDIF
      ENDIF
   NEXT

   IF Empty( cSql )
      RETURN .T.
   ENDIF

   cSql := "UPDATE " + ::cTable + " SET " + cSql +;
           " WHERE _RowId_=" + LTrim( Str( ::aRowId[ ::Recno ] ) )

   WITH OBJECT ::oDataSource
      IF :Execute( cSql )
         IF :nAffectedRows == 1
            ::QueryCurrent() // Actualiza el registro editado por si acaso
         ENDIF
         RETURN .T.
      ENDIF
   END WITH

RETURN .F.

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

METHOD Delete() CLASS XSQLiteRecords

   IF Len( ::aRowId ) < ::Recno
      RETURN .F.
   ENDIF

   WITH OBJECT ::oDataSource
      IF :Execute( "DELETE FROM " + ::cTable + " WHERE _RowId_=" + LTrim( Str( ::aRowId[ ::Recno ] ) ) )
         IF :nAffectedRows == 1
            ::RecCount := Len( ::aData ) - 1
            ADel( ::aData, ::Recno )
            ASize( ::aData, ::RecCount )
            ADel( ::aRowId, ::Recno )
            ADel( ::aTypes, ::Recno )
            ASize( ::aRowId, ::RecCount )
            ASize( ::aTypes, ::RecCount )
            IF ::Recno > ::RecCount
               ::Eof := .T.
            ENDIF
            RETURN .T.
         ENDIF
      ENDIF
   END WITH

RETURN .F.

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

METHOD Locate( cExp, lContinue )  CLASS XSQLiteRecords

   LOCAL cSql, nRecno

   DEFAULT lContinue TO .F.

   IF ! lContinue
      cSql := "SELECT " + ::cTableAlias + "._RowId_ AS __ID__ FROM " + ::cFrom
      IF ! Empty( ::cWhere )
         cSql += " WHERE " + ::cWhere
         IF ! Empty( ::cFilter )
            cSql += " AND " + ::cFilter
         ENDIF
         cSql += " AND " + cExp
      ELSEIF !Empty( ::cFilter )
         cSql += " WHERE " + ::cFilter + " AND " + cExp
      ELSE
         cSql += " WHERE " + cExp
      ENDIF
      IF ! Empty( ::cGroupBy )
         cSql += " GROUP BY " + ::cGroupBy
      ENDIF
      IF ! Empty( ::cHaving )
         cSql += " HAVING " + ::cHaving
         IF !Empty( ::cFilterHaving )
            cSql += " AND " + ::cFilterHaving
         ENDIF
      ELSEIF ! Empty( ::cFilterHaving )
         cSql += " HAVING " + ::cFilterHaving
      ENDIF
      IF ! Empty( ::cOrder )
         cSql += " ORDER BY " + ::cOrder
      ENDIF
      ::aLocate := {}
      TRY
         ::oDataSource:Execute( cSql,, ::aLocate )
      CATCH
         LogDebug( "Xailer-TSQlite Locate error: " + cSql )
      END
      ::nLocate := 0
   ENDIF
   IF ::nLocate >= 0
      IF ::nLocate++ < Len( ::aLocate )
         nRecno := Ascan( ::aRowID, ::aLocate[ ::nLocate, 1 ] )
         IF nRecno > 0
            ::oDataset:Goto( nRecno )
            RETURN .T.
         ENDIF
      ELSE
         ::nLocate := -1
         ::aLocate := Nil
         ::oDataset:Goto( ::RecCount() + 1 )
      ENDIF
   ENDIF

RETURN .F.

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

METHOD Filter( cExp ) CLASS XSQLiteRecords

   LOCAL cOldFilter := ::cFilter, n

   IF !Empty( ::cFilterHaving )
      cOldFilter += " HAVING " + ::cFilterHaving
   ENDIF

   IF PCount() > 0
      IF Left( Upper( cExp ), 7 ) == "HAVING "
         ::cFilter := ""
         ::cFilterHaving := SubStr( cExp, 8 )
      ELSEIF ( n := At( " HAVING ", Upper( cExp ) ) ) > 0
         ::cFilter := Left( cExp, n - 1 )
         ::cFilterHaving := SubStr( cExp, n + 8 )
      ELSE
         ::cFilter := cExp
         ::cFilterHaving := ""
      ENDIF
      ::QueryAll()
   ENDIF

RETURN cOldFilter

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

METHOD Sort( cExp ) CLASS XSQLiteRecords

   IF PCount() > 0
      ::cOrder := cExp
      ::QueryAll()
   ENDIF

RETURN .T.

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

METHOD Clone() CLASS XSQLiteRecords

   LOCAL oDs, oField
   LOCAL cFilter

   IF ::lIsTable
      oDs := TSqlTable():Create()
   ELSE
      oDs := TSqlQuery():Create()
   ENDIF

   WITH OBJECT oDs
      :oDataSource     := ::oDataSource
      :cProcess        := ::oDataSet:cProcess
      :cName           := ::oDataSet:cName
		:aSQLParams      := ::oDataset:aSQLParams
      :nCursorLocation := ::oDataSet:nCursorLocation
      :nCursorType     := ::oDataSet:nCursorType
      :nLockType       := ::oDataSet:nLockType
      :nMaxRecords     := ::oDataSet:nMaxRecords
      :lDisplayErrors  := ::oDataSet:lDisplayErrors
      :lUpdLinked      := ::oDataSet:lUpdLinked
      :lReadOnly       := ::oDataSet:lReadOnly
      :oRecords        := TSQLiteRecords():Create( ::oDataSet, ::lIsTable )
      :lOpen           := ::oDataSet:lOpen
      FOR EACH oField IN ::oDataSet:aUserFields
         :AddCalcField( oField:cName, oField:bGetValue )
      NEXT
      IF ! Empty( cFilter := ::Filter() )
         :SetFilter( cFilter )
      ENDIF
      :Goto( ::Recno )
   END WITH

RETURN oDs

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

METHOD GetRow( nRow, lMove ) CLASS XSQLiteRecords

   DEFAULT nRow TO ::Recno, lMove TO .T.

   IF lMove .AND. nRow != ::Recno
      ::Goto( nRow )
   ENDIF

   IF nRow > 0 .AND. nRow <= Len( ::aData )
      RETURN ::aData[ nRow ]
   ENDIF

RETURN {}

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

METHOD Goto( nRecno ) CLASS XSQLiteRecords

   IF nRecno > 0 .AND. nRecno <= ::RecCount
      ::Recno := nRecno
      ::Bof := .F.
      ::Eof := .F.
   ELSEIF nRecno == ::RecCount + 1
      ::Recno := nRecno
      ::Bof := .F.
      ::Eof := .T.
   ENDIF

RETURN Nil

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

METHOD RowID() CLASS XSQLiteRecords

   IF ::Recno == 0 .OR. ::Recno > ::RecCount
      RETURN 0
   ENDIF

RETURN ::aRowId[ ::Recno ]

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

METHOD Skip( n ) CLASS XSQLiteRecords

   DEFAULT n TO 1

   IF ::Recno + n < 1
      ::Recno := 1
      ::Bof := .T.
      ::Eof := ( ::RecCount == 0 )
   ELSEIF ::Recno + n > ::RecCount
      ::Recno := ::RecCount + 1
      ::Bof := .F.
      ::Eof := .T.
   ELSE
      ::Recno += n
      ::Bof := .F.
      ::Eof := .F.
   ENDIF

RETURN ::Recno

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

METHOD nSkip( n ) CLASS XSQLiteRecords

   LOCAL nRecno := ::Recno

   IF nRecno + n > ::RecCount
      n := ::RecCount - nRecno
   ENDIF
   ::Skip( n )

RETURN ::Recno - nRecno

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

METHOD GetFilterExp( oDBBrowse ) CLASS XSQLiteRecords

   LOCAL oCol
   LOCAL cExp, cVal, cFil
   LOCAL nAt
   LOCAL lComBef, lComAft

   IF ! Empty( oDBBrowse:cOrgFilter )
      cFil := "( " + oDBBrowse:cOrgFilter + ")"
   ELSE
      cFil := ""
   ENDIF

   FOR EACH oCol IN oDBBrowse:aCols
      cVal := oCol:cFilter
      IF ! Empty( cVal )
         cExp := oCol:FilterEval
         WITH OBJECT oCol:oDataField
            DO CASE
               CASE :cType == "C" .OR. :cType == "M"
                  IF Empty( cExp )
                     lComBef := ( Left( cVal, 1 ) == "%" )
                     lComAft := ( Right( cVal, 1 ) == "%" )
                     IF lComBef .AND. !lComAft
                        cVal := Substr( cVal, 2 )
                        cExp :=  :cFullName + " LIKE '%" + StrSqlite( cVal ) + "'"
                     ELSEIF !lComBef .AND. lComAft
                        cVal := Substr( cVal, 1, Len( cVal ) - 1 )
                        cExp :=  :cFullName + " LIKE '" + StrSqlite( cVal ) + "%'"
                     ELSE
                        IF lComBef .AND. lComAft
                           cVal := Substr( cVal, 2, Len( cVal ) - 2 )
                        ENDIF
                        cExp :=  :cFullName + " LIKE '%" + StrSqlite( cVal ) + "%'"
                     ENDIF
                  ENDIF
               CASE :cType == "N"
                  IF Empty( cExp )
                     cExp := :cFullName + IIF( !Left( cVal, 1 ) $ "<>", " = " , " " ) + cVal
                  ENDIF
               CASE :cType == "D"
                  IF Left( cVal, 1 ) $ "<>"
                     nAt := 1
                     IF Substr( cVal, 2, 1 ) == "="
                        nAt ++
                     ENDIF
                     cExp := :cFullName + " " + Left( cVal, nAt ) + " '" + DToSql( CTOD( Substr( cVal, nAt + 1 ) ) ) + "'"
                  ELSE
                     cExp := :cFullName + " = '" + DToSql( CTOD( cVal ) ) + "'"
                  ENDIF
               CASE :cType == "L"
                  IF Empty( cExp )
                     cExp := :cFullName + " = " + IIF( Upper( Left( cVal, 1 ) ) $ "YSTO1", "1" , "0" )
                  ENDIF
            END CASE
         END WITH
         cFil += IIF( ! Empty( cFil ), " AND ", "" ) + cExp
      ENDIF
   NEXT

RETURN cFil

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

METHOD GetSeekExp( oDataField, cSeek ) CLASS XSQLiteRecords

   LOCAL cExp

   cSeek := Upper( cSeek )

   WITH OBJECT oDataField
      ::cOrder := :cFullname
      DO CASE
      CASE :cType == "C" .or. :cType == "M"
         cExp := :cFullName + " LIKE '" + StrSQLite( cSeek ) + "%'"
      CASE :cType == "N"
         cExp := "CAST(" + :cFullname + " AS CHAR) LIKE '" + cSeek + "%'"
      CASE :cType == "D"
         cExp := "Dtoc( " + :cFullName + " ) LIKE '" + cSeek + "%'"
      CASE :cType == "L"
         cExp := :cFullName + IIF( Left( cSeek, 1 ) $ "1TYSO", " <> 0", " = 0" )
      END CASE
   END WITH

RETURN cExp

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

METHOD UtfToAnsi() CLASS XSQLiteRecords

   IF ::oDataSet:lUtfToAnsi != NIL
      RETURN ::oDataSet:lUtfToAnsi
   ENDIF

RETURN ::oDataSource:lUtfToAnsi

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

STATIC PROCEDURE CalcLenDec( cType, nLen, nDec )

   LOCAL nAt := At( "(", cType )

   IF nAt > 0
      cType := Substr( cType, nAt + 1 )
      nLen := Val( cType )
      IF ( nAt := At( ",", cType ) ) > 0
         nDec := Val( Substr( cType, nAt + 1 ) )
      ENDIF
   ENDIF

RETURN

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

STATIC FUNCTION ValToStr( x, lUtf )

   SWITCH ValType( x )
      CASE 'C'
         IF lUtf
            x := AnsiToUTF8( x )
         ENDIF
         x := "'" + StrSqlite( x ) + "'"
         EXIT
      CASE 'N'
         x := ToString( x )
         EXIT
      CASE 'D'
         x := IIF( Day( x ) == 0, "NULL", "'" + DToSQL( x ) + "'" )
         EXIT
      CASE 'T'
         x := IIF( Day( x ) == 0, "NULL", "'" + DTToSQL( x ) + "'" )
         EXIT
      CASE 'L'
         x := IIF( x, "1", "0" )
         EXIT
      OTHERWISE
         x := "NULL"
   END

RETURN x

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

CLASS XSQLiteRecordsReport FROM TSQLiteRecords

PUBLIC:
   METHOD Open() // --> lSuccess
   METHOD Close() // --> lSuccess

   METHOD CanEdit()     INLINE .F. // --> lResult

   METHOD FieldGet( n ) // --> Value | Nil

   METHOD Skip( n ) // --> Nil
   METHOD nSkip( n ) // --> nSkipped

   METHOD Refresh()        INLINE ::QueryAll() // --> lSuccess
   METHOD RefreshCurrent() INLINE ::QueryCurrent() // --> lSuccess

   METHOD GetRows()        INLINE ::aData // --> aRows
   METHOD GetRow( nRow ) // --> aRow

RESERVED:
   DATA Handle

   METHOD QueryAll()
   METHOD QueryCurrent( aData )  INLINE !::Eof

ENDCLASS

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

METHOD Open() CLASS XSQLiteRecordsReport

   // Comprobar que hay conexión
   IF ! ::oDataSource:lConnected
      RETURN .F.
   ENDIF

RETURN ::QueryAll( .T. )

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

METHOD Close() CLASS XSQLiteRecordsReport

   ::oDataSource:Finalize( ::Handle )
   ::Handle := Nil

RETURN ::Super:Close()

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

METHOD QueryAll( lOpening ) CLASS XSQLiteRecordsReport

   LOCAL cSql, aData

   IF ::oDataSource:lAllowProcessMessages
      ProcessMessages()
   ENDIF

   IF !Empty( ::oDataSet ) .AND. ( ::oDataSet:lOpen .OR. ( lOpening != NIL .AND. lOpening ) )

      cSql := ::oDataSet:BuildSQLSt()
      ::aData  := {}

      WITH OBJECT ::oDataSource
         :Execute( "SELECT Count() FROM (" + cSql + ")",, @aData )
         ::RecCount := IIF( !Empty( aData ), aData[ 1, 1 ], 0 )
         IF :nLastError == 0 .OR. :nLastError >= 100
            ::Handle := :Prepare( cSql )
            IF ::aStruct == Nil
               ::dbStruct( ::Handle, 1 )
            ENDIF
            :GetRows( ::Handle, ::aData,,,,,,, :lUtfToAnsi )
         ENDIF
      END

      ::Bof      := .F.
      ::Eof      := ( ::RecCount == 0 )
      ::Recno    := 1

   ENDIF

RETURN .T.

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

METHOD Skip() CLASS XSQLiteRecordsReport

   ::Recno++
   ::Bof := .F.
   ::aData  := {}
   IF ::Eof := ( ::Recno > ::RecCount )
      ::Recno := ::RecCount + 1
   ELSE
      ::oDataSource:GetRows( ::Handle, ::aData,,,,,,, ::oDataSource:lUtfToAnsi )
   ENDIF

RETURN ::Recno

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

METHOD nSkip( n ) CLASS XSQLiteRecordsReport

   LOCAL nRecno := ::Recno

RETURN ::Skip() - nRecno

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

METHOD FieldGet( nField ) CLASS XSQLiteRecordsReport

   IF nField > 0 .AND. nField <= ::FCount .AND. !::Eof
      RETURN ::aData[ 1, nField ]
   ENDIF

RETURN Nil

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

METHOD GetRow( nRow ) CLASS XSQLiteRecordsReport

   IF ::Eof
      RETURN {}
   ENDIF

RETURN ::aData[ 1 ]

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

STATIC FUNCTION ParseInfo( cSql )

   LOCAL aData
   LOCAL cField, cChar
   LOCAL nAt, nLen
   LOCAL lInPar

   aData  := {}
   cField := ""
   nAt    := At( "(", cSql )
   nLen   := Len( cSql )
   lInPar := .F.

   IF nAt == 0
      RETURN {}
   ENDIF

   nAt ++

   DO WHILE nAt < nLen
      cChar := Substr( cSql, nAt, 1 )
      SWITCH cChar
      CASE "("
         lInPar := .T.
         cField += cChar
         EXIT
      CASE ")"
         lInPar := .F.
         cField += cChar
         EXIT
      CASE ","
         IF !lInPar
            Aadd( aData, Alltrim( cField ) )
            cField := ""
            EXIT
         ENDIF
      OTHERWISE
         cField += cChar
      END
      nAt ++
   ENDDO

   IF !Empty( cField )
      Aadd( aData, Alltrim( cField ) )
   ENDIF

RETURN aData

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

#pragma BEGINDUMP

#include "Xailer.h"

static int sql_at( BOOL lReverse )
{
   LPCSTR cSearch = hb_parc( 1 ), cSql = hb_parc( 2 );
   int nLen = hb_parclen( 1 ), nParentesis = 0, nFoundPos = 0;
   char c, *p = (char *) cSql, cComillas = 0;

   while( ( c = *p ) != 0 )
      {
      if( cComillas )
         {
         if( c == '\\' )
            p++;
         else if( c == cComillas )
            cComillas = 0;
         }
      else if( c == '(' )
         nParentesis++;
      else if( c == ')' )
         nParentesis--;
      else if( nParentesis == 0 )
         {
         if( c == *cSearch )
            if( strncmp( p, cSearch, nLen ) == 0 )
               {
               nFoundPos = p - cSql + 1;
               if( !lReverse )
                  break;
               }
         }
      p++;
      }

   return nFoundPos;
}

HB_FUNC( __XA_SQL_AT )
{
   hb_retnl( sql_at( FALSE ) );
}

HB_FUNC( __XA_SQL_RAT )
{
   hb_retnl( sql_at( TRUE ) );
}

#pragma ENDDUMP

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