/*
 * Xailer source code:
 *
 * MySQLRecords.prg
 * Clase TMySQLRecords
 *
 * Copyright 2007 Jose F. Gimenez
 * Copyright 2007 Xailer.com
 * All rights reserved
 *
 */

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

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

#define AUTO_INCREMENT_FLAG   512
#define NOT_NULL_FLAG         1

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

CLASS XMySQLRecords 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 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 aPrimaryKey
   PROPERTY aDefValues     INIT {}
   PROPERTY cFilter        INIT ""
   PROPERTY cFilterHaving  INIT ""

   PROPERTY aStruct
   PROPERTY aData
   PROPERTY cCurrentRecord

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

PROTECTED:
   DATA aLocate
   DATA nLocate   INIT -1
   DATA lOnAppend INIT .F.
   DATA aPKFields INIT {}

ENDCLASS

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

METHOD Open() CLASS XMySQLRecords

   LOCAL cSql, cName, n
   LOCAL 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 )
            IF ( n := At( ",",::cLimit ) ) > 0 .AND. Empty( ::cOffset )// offset en limit
               ::cOffset := Substr( ::cLimit, n + 1 )
               ::cLimit  := Left( ::cLimit, n - 1 )
            ENDIF
            ::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 ) )
            IF Left( ::cFrom, 1 ) == "("
               ::cFrom := Nil
            ELSE
               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
            ENDIF
         ELSEIF ::lIsTable
            ::cFrom := Left( cName, Len( cSql ) )
            ::cTable := ::cFrom
         ENDIF
         IF !Empty( ::cTableAlias )
            ::cTableAlias := StrTran( ::cTableAlias, CRLF, "" )
         ENDIF
         IF !Empty( ::cTable )
            ::cTable := StrTran( ::cTable, CRLF, "" )
         ENDIF
         DEFAULT ::cTableAlias TO ::cTable
      END
   ENDIF

   // Apertura del recordset

RETURN ::QueryAll( .T. )

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

METHOD QueryAll( lOpening ) CLASS XMySQLRecords

   LOCAL Handle, nRecno, nOld
   LOCAL cSql
   LOCAL aKeys, aPK := {}, cKey, n, aRows, aRow
   LOCAL lIsSelect

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

   IF ::oDataSource:lAllowProcessMessages
      ProcessMessages()
   ENDIF

   IF !Empty( ::oDataSet ) .AND. ( ::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 " + IIF( Empty( ::cFields ), "*", ::cFields ) + " FROM " + cSql
      ELSE
         cSql := ::oDataset:BuildSqlSt()
         ::oDataSet:lReadOnly := .T.
      ENDIF

      ::aData := {}

      WITH OBJECT ::oDataSource

         IF ::aStruct == Nil
            IF lIsSelect .AND. !Empty( ::cTable )
               // Obtener la PK de la tabla y los valores por defecto de los campos
               TRY
                  //::aPrimaryKey := aPK := :GetTablePrimaryKey( ::cTable )
                  aPK := {}
                  ::aPrimaryKey := {}
                  ::aDefValues := {}
                  :Execute( "SHOW COLUMNS FROM " + ::cTable,, @aRows )
                  IF !Empty( aRows )
                     FOR EACH aRow IN aRows
                        IF aRow[ 4 ] = "PRI"
                           AAdd( aPK, aRow[ 1 ] )
                        ENDIF
                        IF !Empty( aRow[ 5 ] )
                           AAdd( ::aDefValues, { Lower( aRow[ 1 ] ), aRow[ 5 ] } )
                        ENDIF
                     NEXT
                     ::aPrimaryKey := aPK
                  ENDIF
               CATCH
               END
            ENDIF
         ENDIF

         IF !Empty( Handle := :SQLPrepare( cSql ) )
            IF ::aStruct == Nil
               ::dbStruct( Handle )
               IF lIsSelect
                  FOR n := 1 TO Len( ::aStruct )
                     IF lAnd( ::aStruct[ n, 7 ], AUTO_INCREMENT_FLAG )
                        // Es un campo autoincremental
                        ::aPrimaryKey := { ::aStruct[ n, 1 ] }
                        ::aPKFields := { n }
                     ENDIF
                  NEXT
                  IF Empty( ::aPKFields )
                     // Montar los arrays con la PK
                     FOR EACH cKey IN aPK
                        IF ( n := AScan( ::aStruct, {|f| f[1] == cKey } ) ) ==  0
                           // La PK esta incompleta
                           ::aPrimaryKey := Nil
                           ::aPKFields := {}
                           EXIT
                        ELSE
                           // Añadir a los campos de la PK
                           AAdd( ::aPKFields, n )
                        ENDIF
                     NEXT
                  ENDIF
                  ::oDataSet:lReadOnly := Empty( ::aPKFields )
               ENDIF
            ENDIF

            WHILE( :SQLGetRows( Handle, ::aData, 1024,,, @nRecno ) == 0 )
               IF :lAllowProcessMessages
                  ProcessMessages()
               ENDIF
            ENDDO

            :SQLFinalize( Handle )
         ENDIF
      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 !Empty( Handle )

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

METHOD QueryCurrent( aData ) CLASS XMySQLRecords

   LOCAL aResult
   LOCAL cSql

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

   cSql := "SELECT " + IIF( Empty( ::cFields ), "*", ::cFields ) + ;
           " FROM " + ::cFrom + ;
           " WHERE " + ::CurrentWhere( .F. )

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

RETURN .F.

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

METHOD FieldInfo( nField, nPos ) CLASS XMySQLRecords

   LOCAL aInfo := Array( XA_FLDINFO_COUNT ), n

   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_ALLOWSNULL ] := !lAnd( ::aStruct[ nField, 7 ], NOT_NULL_FLAG )
   aInfo[ XA_FLDINFO_EDITABLE ]   := !Empty( ::cTable ) .AND. ( Upper( ::aStruct[ nField, 9 ] ) == Upper( ::cTable ) )
   aInfo[ XA_FLDINFO_AUTOINC ]    := lAnd( ::aStruct[ nField, 7 ], AUTO_INCREMENT_FLAG )
   //aInfo[ XA_FLDINFO_DEFVALUE ]   := IIF( aInfo[ XA_FLDINFO_AUTOINC ], NIL, XA_GetDefValue( aInfo[ XA_FLDINFO_BASICTYPE ] ) )
   IF !aInfo[ XA_FLDINFO_AUTOINC ]
      IF !Empty( ::cTable ) .AND. Lower( ::aStruct[ nField, 8 ] ) == Lower( ::cTable ) .AND. ( n := AScan( ::aDefValues, {|x| x[ 1 ] == Lower( aInfo[ XA_FLDINFO_NAME ] ) } ) ) > 0
         SWITCH aInfo[ XA_FLDINFO_BASICTYPE ]
         CASE "C"
         CASE "M"
            aInfo[ XA_FLDINFO_DEFVALUE ] := ::aDefValues[ n, 2 ]
            EXIT
         CASE "N"
            aInfo[ XA_FLDINFO_DEFVALUE ] := Val( ::aDefValues[ n, 2 ] )
            EXIT
         CASE "L"
            aInfo[ XA_FLDINFO_DEFVALUE ] := ( ::aDefValues[ n, 2 ] != "0" )
            EXIT
/* NOTA: Que se hace con CURRENT_TIMESTAMP y similares ???
         CASE "D"
            EXIT
         CASE "T"
            EXIT
*/
         OTHERWISE
            aInfo[ XA_FLDINFO_DEFVALUE ] := XA_GetDefValue( aInfo[ XA_FLDINFO_BASICTYPE ] )
         END SWITCH
      ELSEIF aInfo[ XA_FLDINFO_BASICTYPE ] == "B"
         aInfo[ XA_FLDINFO_DEFVALUE ] := Nil
      ELSE
         aInfo[ XA_FLDINFO_DEFVALUE ] := XA_GetDefValue( aInfo[ XA_FLDINFO_BASICTYPE ] )
      ENDIF
   ENDIF
   aInfo[ XA_FLDINFO_PK ]         := ( Ascan( ::aPrimaryKey, {| x | Lower( x ) == Lower( aInfo[ XA_FLDINFO_NAME ] ) } ) > 0 )
   aInfo[ XA_FLDINFO_TABLE ]      := ::aStruct[ nField, 8 ]

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

RETURN aInfo

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

METHOD Close() CLASS XMySQLRecords

   ::aData     := Nil
   ::aStruct   := Nil
   ::Recno     := 1
   ::RecCount  := 0
   ::Bof       := .T.
   ::Eof       := .T.
   ::FCount    := 0
   ::aPKFields := {}

RETURN .T.

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

METHOD CurrentWhere( lOrgTable ) CLASS XMySQLRecords

   LOCAL nField, cWhere := ""

   FOR EACH nField IN ::aPKFields
      IF !Empty( cWhere )
         cWhere += " AND "
      ENDIF
      IF lOrgTable
         IF !Empty( ::cTable )
            cWhere += ::cTable + "."
         ENDIF
      ELSEIF !Empty( ::aStruct[ nField, 8 ] )
         cWhere += ::aStruct[ nField, 8 ] + "."
      ENDIF
      WITH OBJECT ::oDataset
         IF :lOnEdit
            cWhere += ::aStruct[ nField, 1 ] + "=" + ValToStr( ::aRecord[ nField ] )
         ELSE
            cWhere += ::aStruct[ nField, 1 ] + "=" + ValToStr( ::aData[ ::Recno, nField ] )
         ENDIF
      END WITH
   NEXT

RETURN cWhere

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

METHOD FieldGet( nField, lForceRecno ) CLASS XMySQLRecords

   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 XMySQLRecords

   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 XMySQLRecords

   ::lOnAppend := .T.

RETURN NIL

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

METHOD Cancel() CLASS XMySQLRecords

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

RETURN NIL

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

METHOD Update() CLASS XMySQLRecords

   LOCAL lSuccess

   IF ::lOnAppend
      lSuccess := ::Append()
      IF lSuccess
         ::lOnAppend := .F.
      ENDIF
   ELSE
      lSuccess := ::Replace()
   ENDIF

RETURN lSuccess

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

METHOD Append() CLASS XMySQLRecords

   LOCAL oField
   LOCAL aData
   LOCAL cSql, cFields, cValues, aParams
   LOCAL nField, cWhere, nRecno, nId

   cSql    := "INSERT INTO " + ::cTable
   cFields := ""
   cValues := ""
   aParams := {}

   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 == "B"
            cValues += "?"
            AAdd( aParams, ::aRecord[ oField:__EnumIndex() ] )
         ELSE
            cValues += ValToStr( ::aRecord[ oField:__EnumIndex() ] )
         ENDIF
      ENDIF
   NEXT

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

   IF CheckSQLInjection( cSql ) .OR. !IIF( Empty( aParams ), ::oDataSource:Execute( cSql ), ::oDataSource:Execute2( cSql,, aParams ) )
      RETURN .F.
   ENDIF

   IF !Empty( ::cOrder ) .AND. Len( ::aPKFields ) == 1 .AND. ::oDataset:nMaxRecords == 0 .AND. ;
      lAnd( ::aStruct[ ::aPKFields[ 1 ], 7 ], AUTO_INCREMENT_FLAG )
      // La PK es un autoincremental. Localizar el lugar donde se ha insertado el nuevo registro
      // NOTA: Si no hay 'ORDER BY', MySQL devuelve la consulta en el orden que le da la gana cada vez  :-(
      nId := ::oDataSource:LastInsertId()
      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 " + IIF( Empty( ::aStruct[ ::aPKFields[ 1 ], 8 ] ), "", ::aStruct[ ::aPKFields[ 1 ], 8 ] + "." ) + ::aStruct[ ::aPKFields[ 1 ], 1 ] + " FROM " + cSql,, @aData )

      IF Empty( aData ) .OR. aData[ Len( aData ), 1 ] == nId .OR. ( nRecno := AScan( aData, {| x | x[ 1 ] == nId } ) ) == 0
         AAdd( ::aData, Nil )
         ::Recno := Len( ::aData )
      ELSE
         AAdd( ::aData, Nil )
         AIns( ::aData, nRecno )
         ::Recno := nRecno
      ENDIF
      IF ::Recno <= Len( ::aData )
         ::aData[ ::Recno ] := Array( Len( ::aStruct ) )
         ::aData[ ::Recno, ::aPKFields[ 1 ] ] := nId
         ::aRecord[ ::aPKFields[ 1 ] ] := nId
      ENDIF
      IF ::oDataSet:oDBBrowse != Nil
         ::oDataSet:oDBBrowse:Refresh( .T. )
      ENDIF

   ELSE
      // Añadir el registro al final del array
      cWhere := ""
      FOR EACH nField IN ::aPKFields
         IF !Empty( cWhere )
            cWhere += " AND "
         ENDIF
         cWhere += ::aStruct[ nField, 1 ] + "=" + ValToStr( ::aRecord[ nField ] )
      NEXT
      AAdd( ::aData, {} )
      ::Recno := Len( ::aData )
      FOR EACH oField IN ::oDataset:aFields
         AAdd( ::aData[ ::Recno ], oField:Value )
      NEXT
      IF Len( ::aPKFields ) == 1 .AND. lAnd( ::aStruct[ ::aPKFields[ 1 ], 7 ], AUTO_INCREMENT_FLAG )
         ::aData[ ::Recno, ::aPKFields[ 1 ] ] := ::oDataSource:LastInsertId()
      ENDIF
   ENDIF

   ::RecCount := Len( ::aData )

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

RETURN .T.

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

METHOD Replace() CLASS XMySQLRecords

   LOCAL oField
   LOCAL aData
   LOCAL cSql, cMod, n, aParams
   LOCAL nRecno := ::Recno

   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 ] ) .AND. ::oDataset:aFields[ n ]:lEditable
            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 := ""
   aParams := {}

   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 == "B"
            cSql += "?"
            AAdd( aParams, ::aData[ nRecno, oField:__EnumIndex() ] )
         ELSE
            cSql += ValToStr( ::aData[ nRecno, oField:__EnumIndex() ] )
         ENDIF
      ENDIF
   NEXT

   IF Empty( cSql )
      RETURN .T.
   ENDIF

   IF !CheckSQLInjection( cSql )
      cSql := "UPDATE " + ::cTable + " SET " + cSql + " WHERE " + ::CurrentWhere( .T. )
      WITH OBJECT ::oDataSource
         IF IIF( Empty( aParams ), :Execute( cSql ), :Execute2( cSql,, aParams ) )
            IF :nAffectedRows == 1
               ::QueryCurrent() // Actualiza el registro editado por si acaso
            ENDIF
            RETURN .T.
         ENDIF
      END WITH
   ENDIF

RETURN .F.

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

METHOD Delete() CLASS XMySQLRecords

   LOCAL n

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

RETURN .F.

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

METHOD Locate( cExp, lContinue )  CLASS XMySQLRecords

   LOCAL cSql, nRecno

   DEFAULT lContinue TO .F.

   IF !Empty( ::cFrom )
      IF ! lContinue
         cSql := "SELECT IF( " + cExp + ", 1, 0 ) FROM " + ::cFrom
         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
         ::aLocate := {}
         TRY
            ::oDataSource:Execute( cSql,, ::aLocate )
         CATCH
            LogDebug( "Xailer-TMySql Locate error: " + cSql )
         END
         ::nLocate := 0
      ENDIF

      IF ::nLocate >= 0
         ::nLocate := Ascan( ::aLocate, {|v| v[ 1 ] == 1 }, ::nLocate + 1 )
         IF ::nLocate != 0
            ::oDataset:Goto( ::nLocate )
            RETURN .T.
         ELSE
            ::nLocate := -1
            ::aLocate := Nil
            ::oDataset:Goto( ::RecCount() + 1 )
         ENDIF
      ENDIF
   ENDIF

RETURN .F.

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

METHOD Filter( cExp ) CLASS XMySQLRecords

   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 XMySQLRecords

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

RETURN .T.

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

METHOD Clone() CLASS XMySQLRecords

   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        := TMySQLRecords():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 XMySQLRecords

   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 XMySQLRecords

   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 Skip( n ) CLASS XMySQLRecords

   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 XMySQLRecords

   LOCAL nRecno := ::Recno

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

RETURN ::Recno - nRecno

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

METHOD GetFilterExp( oDBBrowse ) CLASS XMySQLRecords

   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 '%" + StrMySql( cVal ) + "'"
                     ELSEIF !lComBef .AND. lComAft
                        cVal := Substr( cVal, 1, Len( cVal ) - 1 )
                        cExp :=  :cFullName + " LIKE '" + StrMySql( cVal ) + "%'"
                     ELSE
                        IF lComBef .AND. lComAft
                           cVal := Substr( cVal, 2, Len( cVal ) - 2 )
                        ENDIF
                        cExp :=  :cFullName + " LIKE '%" + StrMySql( cVal ) + "%'"
                     ENDIF
                  ENDIF
               CASE :cType == "N"
                  IF Empty( cExp )
                     cExp := :cFullName + IIF( !Left( cVal, 1 ) $ "<>", " = " , " " ) + cVal
                  ENDIF
               CASE :cType == "D" .OR. :cType == "T"
                  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 ) ) $ "1TYSO", " <> 0", " = 0" )
                  ENDIF
            END CASE
         END WITH
         cFil += IIF( ! Empty( cFil ), " AND ", "" ) + cExp
      ENDIF
   NEXT

RETURN cFil

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

METHOD GetSeekExp( oDataField, cSeek ) CLASS XMySQLRecords

   LOCAL cExp

   cSeek := Upper( cSeek )

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

RETURN cExp

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

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 )

   SWITCH ValType( x )
      CASE 'C'
         x := "'" + StrMySql( x ) + "'"
         EXIT
      CASE 'N'
         x := ToString( x )
         EXIT
      CASE 'D'
         x := IIF( Empty( x ), "'0000-00-00'", "'" + DToSQL( x ) + "'" )
         EXIT
      CASE 'T'
         x := IIF( Empty( x ), "'0000-00-00 00:00:00'", "'" + DTToSQL( x ) + "'" )
         EXIT
      CASE 'L'
         x := IIF( x, "1", "0" )
         EXIT
      OTHERWISE
         x := "NULL"
   END

RETURN x

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

CLASS XMySQLRecordsReport FROM TMySQLRecords

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 XMySQLRecordsReport

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

RETURN ::QueryAll( .T. )

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

METHOD Close() CLASS XMySQLRecordsReport

   ::oDataSource:SQLFinalize( ::Handle )
   ::Handle := Nil

RETURN ::Super:Close()

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

METHOD QueryAll( lOpening ) CLASS XMySQLRecordsReport

   LOCAL cSql, aData

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

      IF ::oDataSource:lAllowProcessMessages
         ProcessMessages()
      ENDIF

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

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

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

   ENDIF

RETURN .T.

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

METHOD Skip() CLASS XMySQLRecordsReport

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

RETURN ::Recno

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

METHOD nSkip( n ) CLASS XMySQLRecordsReport

   LOCAL nRecno := ::Recno

RETURN ::Skip() - nRecno

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

METHOD FieldGet( nField ) CLASS XMySQLRecordsReport

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

RETURN Nil

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

METHOD GetRow( nRow ) CLASS XMySQLRecordsReport

   IF ::Eof
      RETURN {}
   ENDIF

RETURN ::aData[ 1 ]

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