Simplemente sustituir el código de datacrud.prg y modeldata.prg de Datatables, por el sugerido a continuación, y podréis observar los resultados en el ejemplo numero 15 (CRUD) de Datatable.

Os muestro unas pantallas de los resultados, solo tenéis que adaptar a vuestras necesidades y a funcionar 100%.

 

DATACRUD.PRG

____________________________________________________________________

#include "Xailer.ch"
#include "Nefele.ch"

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

PROCEDURE DataCrud()

WITH OBJECT TWebPage():New()
:cClrPane := clWhite
:aBreadCrumbs := {{GetEnv("SCRIPT_NAME"),"🏠"},{GetEnv("SCRIPT_NAME") + "?maindatatables","📋"},"CRUD"}
AppMenu( :WO, "DataTable" )

// Si se van a utilizar sweet alerts, incluir el script
AAdd( :aHeadScript, '<script src="https://cdn.jsdelivr.net/npm/sweetalert2@10"></script>')

CreateTitle( :WO, "15. CRUD - Altas , Bajas , Modificaciones y Consultas" )

WITH OBJECT WDataTable():New(:WO)
:cId := "browse"
:CssClass:= "display" // efecto pijama y seleccion de columna ordenada
// Configurar opciones
WITH OBJECT :Configure()
:responsive := .T.
:processing := .T.
// :serverSide := .T.
// Opciones del control de paginación
:paging := .T.
:info := .T.
// Incluir combobox con el numero de filas a mostrar
:lengthChange := .T.
//Cambiar saltos de las filas a mostrar
:lengthMenu := { {10, 15, 20, -1}, {10, 15, 20, "Mostrar Todo"} }
// Opciones para ordenacion de columnas
:ordering := .T.
:order := { {0, 'asc'} } // Columna por defecto
// Opciones para la búsqueda
:searching := .T. // activa la barra de busqueda y busca en los campos :searchable := .T.
WITH OBJECT :AddColumn()
:title := "#id"
:data := "id"
:orderable := .T.
END

WITH OBJECT :AddColumn()
:title := "Nombre"
:data := "first"
:orderable := .T. // para que ordene
:searchable := .T. // para que realize la busqueda incluyendo este campo
END

WITH OBJECT :AddColumn()
:title := "Apellidos"
:data := "last"
:orderable := .T.
:searchable := .T. // para que realize la busqueda incluyendo este campo
END

WITH OBJECT :AddColumn()
:title := "Ciudad"
:data := "city"
:orderable := .T.
END

WITH OBJECT :AddColumn()
:title := "Edad"
:data := "age"
:orderable := .T.
END

:AddColumnButton( "add", "blue", "DialogAddRow" ) // boton para ALTAS

:AddColumnButton( "edit", "green", "DialogEditRow" ) // boton de EDICION

:AddColumnButton( "delete", "red", "MessageDeleteRow" ) // boton de BORRADO

// Vincular a modelo de datos
:ajax := { "cgi" => "GetSQLCrud" } // dentro del modeldata

END
:Create()

END

oCgi:SendPage( :Create() )
END

RETURN

//------------------------------------------------------------------------------
PROCEDURE DialogAddRow()

local hParams := oCGI:aParamsToHash(.T.)
local oDialog, oForm, oFooter
local cScript

WITH OBJECT oDialog := WModalPopup():New()
:cId := "dialog"
:cTitle := "Añadir datos"

WITH OBJECT oForm := WForm():New(:WO)
:cId := "dlgform"
:cFunction := "ActionEditRow"
:aParams := {{"id",""}} // Dejamos id vacio, para que lo detecte lNew = .t , controle que es un ALTA, y le asigne la siguiente id automaticamente

WITH OBJECT WEdit():New(:WO)
:cId := "first"
:cValue := ""
:cHint := "Nombre"
:cIcon := "person"
:SetRequired()
:Create()
END

WITH OBJECT WEdit():New(:WO)
:cId := "last"
:cValue := ""
:cHint := "Apellidos"
:cIcon := "person"
:SetRequired()
:Create()
END

WITH OBJECT WEdit():New(:WO)
:cId := "city"
:cValue := ""
:cHint := "Ciudad"
:cIcon := "home"
:SetRequired()
:Create()
END

WITH OBJECT WEdit():New(:WO)
:cId := "age"
:cValue := 0
:cHint := "Edad"
:cIcon := "timer"
:SetRequired()
:Create()
END
:Create()
END

WITH OBJECT :AddFooter()

WITH OBJECT WButton():New(:WO)
:aWidth := {6,9,10}
:cText := "Aceptar"
:cIcon := "send"
:cPosition := xc_Right
:lFlat := .T.
//:cOnClick no necesario, lSubmit envia formulario segun id cSubmitForm
:cAjaxBevel := "nflcargo"
:cSubmitForm := "dlgform"
:lSubmit := .T.
:lModalClose := .T.
:Create()
END
WITH OBJECT WButton():New(:WO)
:cText := "Salir"
:cPosition := xc_Right
:aWidth := {6,3,2}
//:oStyle:cPadding=4
:cIcon := "cancel"
:lFlat := .T.
:lModalClose := .T.
:Create()
END
END

// Configurar opciones modal
WITH OBJECT :Configure()
:opacity := 0.3
:inDuration := 150
// :onOpenStart := "()=>window.alert('init popup')"
// :onCloseEnd := "()=>window.alert('end popup')"
END
:Create()
END

// Hack. Interceptar submit por defecto del form para nueva llamada Ajax
TEXT INTO cScript
<script>
document.querySelector('#dlgform').addEventListener('submit',(e)=>{
e.preventDefault();

var U_id= document.getElementById("id").value;
var U_fi= document.getElementById("first").value;
var U_la= document.getElementById("last").value;
var U_ci= document.getElementById("city").value;
var U_ag= document.getElementById("age").value;

$.ajax({
url: '?FUNCTION=ActionEditRow',
method:"POST",
data: {id:U_id,
first:U_fi,
last:U_la,
city:U_ci,
age:U_ag},
async: true,
cache: false
})
.done((r,s,x)=>{
console.log("ActionEditRow",s);
});
return false;
});
</script>
ENDTEXT

oCgi:SendPage( oDialog:FullHtml()+cScript )

RETURN

//------------------------------------------------------------------------------
PROCEDURE DialogEditRow()

local hParams := oCGI:aParamsToHash(.T.)
local oDialog, oForm, oFooter
local cScript

WITH OBJECT oDialog := WModalPopup():New()
:cId := "dialog"
:cTitle := "Editar datos"
:aWidth[xc_M] := 8 // Apartir del tamaño M (Tablet) solo ocupara 8/12 partes de la pantalla
:aOffset[xc_M] := 2 // Y la centramos desplzandola 2/12 desde la izquierda

WITH OBJECT oForm := WForm():New(:WO)
:cId := "dlgform"
:cFunction := "ActionEditRow"
:aParams := {{"id",hParams['ID']}}
WITH OBJECT WEdit():New(:WO)
:cId := "id"
:cValue := hParams['ID']
:cHint := "id"
:cIcon := "123"
:SetRequired()
:Create()
END

WITH OBJECT WEdit():New(:WO)
:cId := "first"
:cValue := hParams['FIRST']
:cHint := "Nombre"
:cIcon := "person"
:oMask:lUpperCase:= .T.
:SetRequired()
:Create()
END

WITH OBJECT WEdit():New(:WO)
:cId := "last"
:cValue := hParams['LAST']
:cHint := "Apellidos"
:cIcon := "person"
:SetRequired()
:Create()
END

WITH OBJECT WEdit():New(:WO)
:cId := "city"
:cValue := hParams['CITY']
:cHint := "Ciudad"
:cIcon := "home"
:SetRequired()
:Create()
END

WITH OBJECT WEdit():New(:WO)
:cId := "age"
:cValue := hParams['AGE']
:cHint := "Edad"
:cIcon := "timer"
// :oMask:lUpperCase:= "999"
:SetRequired()
:Create()
END
:Create()
END

WITH OBJECT :AddFooter()

WITH OBJECT WButton():New(:WO)
:aWidth := {6,9,10}
:cText := "Aceptar"
:cIcon := "send"
:oStyle:cColor := "green"
:cPosition := xc_Right
:lFlat := .T.
//:cOnClick no necesario, lSubmit envia formulario segun id cSubmitForm
:cAjaxBevel := "nflcargo"
:cSubmitForm := "dlgform"
:lSubmit := .T.
:lModalClose := .T.
:Create()
END
WITH OBJECT WButton():New(:WO)
:cText := "Salir"
:cPosition := xc_Right
:aWidth := {6,3,2}
//:oStyle:cPadding=4
:cIcon := "cancel"
:oStyle:cColor := "red"
:lFlat := .T.
:lModalClose := .T.
:Create()
END
END

// Configurar opciones modal
WITH OBJECT :Configure()
:opacity := 0.3
:inDuration := 150
// :onOpenStart := "()=>window.alert('init popup')"
// :onCloseEnd := "()=>window.alert('end popup')"
END
:Create()
END

// Hack. Interceptar submit por defecto del form para nueva llamada Ajax
TEXT INTO cScript
<script>
document.querySelector('#dlgform').addEventListener('submit',(e)=>{
e.preventDefault();

var U_id= document.getElementById("id").value;
var U_fi= document.getElementById("first").value;
var U_la= document.getElementById("last").value;
var U_ci= document.getElementById("city").value;
var U_ag= document.getElementById("age").value;

$.ajax({
url: '?FUNCTION=ActionEditRow',
method:"POST",
data: {id:U_id,
first:U_fi,
last:U_la,
city:U_ci,
age:U_ag},
async: true,
cache: false
})
.done((r,s,x)=>{
console.log("ActionEditRow",s);
});
return false;
});
</script>
ENDTEXT

oCgi:SendPage( oDialog:FullHtml()+cScript )

RETURN

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

PROCEDURE MessageDeleteRow()

local hParams := oCGI:aParamsToHash(.T.)
local oMsgAlert := WMsgAlert():New()

WITH OBJECT oMsgAlert
:cPosition := xc_Top
:cTitle := "Borrar "+ hParams['first']+" "+ hParams['last'] +" ? <BR>"
:cType := "info"
:cAjaxBevel := "nflcargo"
:cConfirmButton := 'Sí'
:cConfirmButtonColor := "green"
:cConfirmButtonIcon := 'exit_to_app'
:cOnConfirm := "ActionDeleteRow"
:aParams := { {"id", hParams['id']} }
:cCancelButton := "No"
:cCancelButtonColor := "red"
:cCancelButtonIcon := "cancel"
:Create()
END

//ocgi:Console( ocgi:aparams)
//oCgi:SendStatus( .T., "application/javascript" )
//oCgi:Send( "" )
//oCGI:Send( "window.alert('quim')" )
// Equivalente a :
// oCGI:SendScript( "window.alert('quim')" )

oCgi:SendScript( oMsgAlert:FullHtml() )

RETURN

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

PROCEDURE ActionEditRow(id,first,last,city, age)
LOCAL aParam := {}
local cScript := ""
local hParams := oCGI:aParamsToHash(.T.)

// 1) Llamada a modelo de datos y obtener respuesta hash
local hResponse := DataEditRow( hParams['id'],hParams['first'],hParams['last'],hParams['city'],hParams['age'] ) //DataEditRow( hParams['id'])

// 2) Crear mensaje de confirmación o de error
TEXT INTO cScript
Swal.mixin({
toast: true,
position: 'top-end',
showConfirmButton: false,
timer: 1000,
timerProgressBar: true,
didOpen: (toast) => {
toast.addEventListener('mouseenter', Swal.stopTimer)
toast.addEventListener('mouseleave', Swal.resumeTimer)
}
}).fire({
icon: 'success',
title: 'Registro modificado / insertado'
});
$('#browse').DataTable().ajax.reload();
ENDTEXT

// cScript := nfl_ParseScript( cScript, hResponse['message'] )

// 3) Devolver al cliente
oCGI:SendScript( cScript )

RETURN

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

PROCEDURE ActionDeleteRow()

local cScript := ""
local hParams := oCGI:aParamsToHash(.T.)

// 1) Llamada a modelo de datos y obtener respuesta hash
local hResponse := DataDeleteRow( hParams['id'] )

// 2) Crear mensaje de confirmación o de error
TEXT INTO cScript
Swal.mixin({
toast: true,
position: 'top-end',
showConfirmButton: false,
timer: 1000,
timerProgressBar: true,
didOpen: (toast) => {
toast.addEventListener('mouseenter', Swal.stopTimer),
toast.addEventListener('mouseleave', Swal.resumeTimer)
}
}).fire({
icon: 'success',
title:'%s'
});
$('#browse').DataTable().ajax.reload();
ENDTEXT

cScript := nfl_ParseScript( cScript, hResponse['message'] )

// 3) Devolver al cliente
oCGI:SendScript( cScript )

RETURN

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

 

MODELDATA.PRG

__________________________________________________________________________________

#include "Xailer.ch"
#include "Nefele.ch"

//------------------------------------------------------------------------------
// Modelo de datos en memoria
//------------------------------------------------------------------------------
function GetDataArray(nRows)

local aData

aData := {;
{ "Tiger Nixon", "System Architect", "Edinburgh", "5421", "2011/04/25", "$320,800" },;
{ "Garrett Winters", "Accountant", "Tokyo", "8422", "2011/07/25", "$170,750" },;
{ "Ashton Cox", "Junior Technical Author", "San Francisco", "1562", "2009/01/12", "$86,000" },;
{ "Cedric Kelly", "Senior Javascript Developer", "Edinburgh", "6224", "2012/03/29", "$433,060" },;
{ "Airi Satou", "Accountant", "Tokyo", "5407", "2008/11/28", "$162,700" },;
{ "Brielle Williamson", "Integration Specialist", "New York", "4804", "2012/12/02", "$372,000" },;
{ "Herrod Chandler", "Sales Assistant", "San Francisco", "9608", "2012/08/06", "$137,500" },;
{ "Rhona Davidson", "Integration Specialist", "Tokyo", "6200", "2010/10/14", "$327,900" },;
{ "Colleen Hurst", "Javascript Developer", "San Francisco", "2360", "2009/09/15", "$205,500" },;
{ "Sonya Frost", "Software Engineer", "Edinburgh", "1667", "2008/12/13", "$103,600" },;
{ "Jena Gaines", "Office Manager", "London", "3814", "2008/12/19", "$90,560" },;
{ "Quinn Flynn", "Support Lead", "Edinburgh", "9497", "2013/03/03", "$342,000" },;
{ "Charde Marshall", "Regional Director", "San Francisco", "6741", "2008/10/16", "$470,600" },;
{ "Haley Kennedy", "Senior Marketing Designer", "London", "3597", "2012/12/18", "$313,500" },;
{ "Tatyana Fitzpatrick", "Regional Director", "London", "1965", "2010/03/17", "$385,750" },;
{ "Michael Silva", "Marketing Designer", "London", "1581", "2012/11/27", "$198,500" }, ;
{ "Paul Byrd", "Chief Financial Officer (CFO)", "New York", "3059", "2010/06/09", "$725,000" },;
{ "Gloria Little", "Systems Administrator", "New York", "1721", "2009/04/10", "$237,500" },;
{ "Bradley Greer", "Software Engineer", "London", "2558", "2012/10/13", "$132,000" },;
{ "Dai Rios", "Personnel Lead", "Edinburgh", "2290", "2012/09/26", "$217,500" },;
{ "Jenette Caldwell", "Development Lead", "New York", "1937", "2011/09/03", "$345,000" },;
{ "Yuri Berry", "Chief Marketing Officer (CMO)", "New York", "6154", "2009/06/25", "$675,000" },;
{ "Caesar Vance", "Pre-Sales Support", "New York", "8330", "2011/12/12", "$106,450" },;
{ "Doris Wilder", "Sales Assistant", "Sydney", "3023", "2010/09/20", "$85,600" },;
{ "Angelica Ramos", "Chief Executive Officer (CEO)", "London", "5797", "2009/10/09", "$1,200,000" },;
{ "Gavin Joyce", "Developer", "Edinburgh", "8822", "2010/12/22", "$92,575" },;
{ "Jennifer Chang", "Regional Director", "Singapore", "9239", "2010/11/14", "$357,650" },;
{ "Brenden Wagner", "Software Engineer", "San Francisco", "1314", "2011/06/07", "$206,850" },;
{ "Fiona Green", "Chief Operating Officer (COO)", "San Francisco", "2947", "2010/03/11", "$850,000" },;
{ "Shou Itou", "Regional Marketing", "Tokyo", "8899", "2011/08/14", "$163,000" },;
{ "Michelle House", "Integration Specialist", "Sydney", "2769", "2011/06/02", "$95,400" },;
{ "Suki Burks", "Developer", "London", "6832", "2009/10/22", "$114,500" },;
{ "Prescott Bartlett", "Technical Author", "London", "3606", "2011/05/07", "$145,000" },;
{ "Gavin Cortez", "Team Leader", "San Francisco", "2860", "2008/10/26", "$235,500" },;
{ "Martena Mccray", "Post-Sales support", "Edinburgh", "8240", "2011/03/09", "$324,050" },;
{ "Unity Butler", "Marketing Designer", "San Francisco", "5384", "2009/12/09", "$85,675" };
}

HB_Default( @nRows, Len(aData) )

ASize( aData, nRows )

return aData

//------------------------------------------------------------------------------
// Modelo de datos en un array ( Llamada al CGI por Ajax )
//------------------------------------------------------------------------------

function GetDataAjax()

local cJson
local aSource, aData

aSource := GetDataArray()

// Modelo de datos para DataTable
aData := {=>}
aData['draw'] := 1
aData['recordsTotal'] := Len(aSource)
aData['recordsFiltered'] := Len(aSource)
aData['data'] := aSource

// (T)Adjunta cadena milisegundos <!-- 0.0020000000004075 --> a la respuesta
// Fix
// invalidando Json devuelto, (F) no adjunta cadena
//oCGI:lSendTTFB := .F.

// Devolver respuesta JSON

return oCGI:SendJson( aData )

//------------------------------------------------------------------------------
// Modelo de datos en un array hash
//------------------------------------------------------------------------------

function GetDataHash(nRows, lAjax)

local aRecords, aRow, hRow
local aData := {=>}
local aSource := {}

hb_Default( @nRows, 10 )
hb_Default( @lAjax, .T. )

aRecords := GetDataArray(nRows)

for each aRow in aRecords
hRow := {=>}
hRow["id"] := aRow:__enumindex
hRow["name"] := aRow[1]
hRow["position"] := aRow[2]
hRow["office"] := aRow[3]
hRow["extn"] := aRow[4]
hRow["start_date"]:= aRow[5]
hRow["salary"] := aRow[6]
AAdd( aSource, hRow )
next

// Preparar modelo de datos para DataTable
aData['data'] := aSource

// Devolver respuesta JSON o sólo datos

return IIF( lAjax, oCGI:SendJson(aData), aSource )

//------------------------------------------------------------------------------
// Modelo de datos con fotos via URL ( Llamada al CGI por Ajax )
//------------------------------------------------------------------------------

function GetCgiFotos()

local item
local cJson
local aSource, aData

aSource := {;
{ "accusamus beatae", 1, "accusamus", "https://picsum.photos/id/1/80/50",{{1, "Basico"}, {2, "Oferta"}},10.50 },;
{ "reprehenderit est", 2, "reprehenderit", "https://picsum.photos/id/1014/80/50",{{1, "London"}, {2, "Tokyo"}},23 },;
{ "officia porro iure", 3, "officia", "https://picsum.photos/id/1015/80/50",{{1, "Paris"}, {2, "Lyon"}},123.65 },;
{ "culpa odio esse", 4, "culpa", "https://picsum.photos/id/1016/80/50",{{1, "Barcelona"}, {2, "Bilbao"}, {3, "Madrid"}, {4, "Zaragoza"}},13.35 },;
{ "natus nisi omnis", 5, "natus", "https://picsum.photos/id/1018/80/50",{{1, "London"}, {2, "Tokyo"}}, 8.75 } ;
}

/* Es posible devolver directamente una columna ya montada con HTML,
sin utilizar el render de la columna de su tipo
En este caso, definir la columna en la vista con AddColumn()

for each item in aSource
item[3] := ''
o...
item[3] := ''+ item[5] +''
next
*/

// Modelo de datos para DataTable
aData := {=>}
aData['draw'] := 1
aData['recordsTotal'] := Len(aSource)
aData['recordsFiltered'] := Len(aSource)
aData['data'] := aSource

// Devolver respuesta JSON

return oCGI:SendJson( aData )

//------------------------------------------------------------------------------
// Modelo de datos en una DBF ( Llamada al CGI por Ajax )
//------------------------------------------------------------------------------

function GetDataDbf()

local aSource, aData
local cPathData
local n, nStart, nLimit
local hParams

cPathData := "data\"
aData := {=>}
aSource := {}

hParams := oCGI:aParamsToHash(.T.)
nStart := Val(hParams['START']) +1
nLimit := Val(hParams['LENGTH'])

if File( cPathData +"Articulo.DBF" )

USE (cPathData +"Articulo") ;
ALIAS "Articulos" NEW //SHARED

Articulos->( dbgoto( nStart ) )
n := 1

do while !Articulos->( eof() )

if n > nLimit
EXIT
else
AAdd( aSource, {;
Articulos->(RecNo()), ;
Articulos->Codigo, ;
Articulos->Nombre, ;
transform(Articulos->PVenta, '@ze 9999.99') ;
})
endif

Articulos->( DBSkip() )
n++
enddo

// Preparar modelo de datos para DataTable
aData['draw'] := hParams['DRAW']
aData['recordsTotal'] := OrdKeyCount()
aData['recordsFiltered'] := OrdKeyCount()
aData['data'] := aSource

// Informacion devuelta para debug en navegador
aData['aParams'] := oCgi:aParams
aData['hParams'] := hParams

Articulos->( DBCloseArea() )
else
aData['recordsTotal'] := 0
aData['data'] := {}
aData['error'] := "No se encuentran datos"
endif

// Devolver respuesta JSON

return oCGI:SendJson( aData )

//------------------------------------------------------------------------------
// Modelo de datos en una DBF para búsquedas ( Llamada al CGI por Ajax )
//------------------------------------------------------------------------------

function GetDbfSeek()

local cSeek
local aSource, aData
local cPathData
local n, nLimit
local hParams

cPathData := "data\"
aData := {=>}
aSource := {}

hParams := oCGI:aParamsToHash(.T.)
cSeek := Upper( hParams['SEARCH[VALUE]'] )
nLimit := Val(hParams['LENGTH'])
nLimit := IF( nLimit > 0, nLimit, 10 )

if File( cPathData +"Articulo.DBF" )

USE (cPathData +"Articulo") ;
INDEX (cPathData +"Articulo") ;
ALIAS "Articulos" NEW

Articulos->( ordsetfocus('nombre') )
Articulos->( dbseek( cSeek, .T. ) )
n := 1

do while !Articulos->( eof() )

if n > nLimit
EXIT
else
AAdd( aSource, {;
Articulos->(RecNo()), ;
Articulos->Codigo, ;
Articulos->Nombre, ;
transform(Articulos->PVenta, '@ze 9999.99') ;
})
endif

Articulos->( DBSkip() )
n++
enddo

// Preparar modelo de datos para DataTable
aData['draw'] := hParams['DRAW']
aData['recordsTotal'] := OrdKeyCount()
aData['recordsFiltered'] := OrdKeyCount()
aData['data'] := aSource

// Informacion devuelta para debug en navegador
aData['aParams'] := oCgi:aParams
aData['hParams'] := hParams

Articulos->( DBCloseArea() )
else
aData['recordsTotal'] := 0
aData['data'] := {}
aData['error'] := "No se encuentran datos"
endif

// Devolver respuesta JSON

return oCGI:SendJson( aData )

//------------------------------------------------------------------------------
// Modelo de datos DBF con ordenacion ( Llamada al CGI por Ajax )
//------------------------------------------------------------------------------

function GetDbfToHash()

local aSource, aRow, aData
local cPathData
local n, nOrder, nOffset, nLimit
local hParams

cPathData := "data\"
aData := {=>}
aSource := {}

hParams := oCGI:aParamsToHash(.T.)
nOrder := Val(hParams['ORDER[0][COLUMN]']) +1
nOffset := Val(hParams['START']) +1
nLimit := Val(hParams['LENGTH'])

if File( cPathData +"Articulo.DBF" )

USE (cPathData +"Articulo") ;
INDEX (cPathData +"Articulo") ;
ALIAS "Articulos" NEW //SHARED

Articulos->( ordsetfocus(nOrder) )
Articulos->( dbgotop() )
Articulos->( dbskip(nOffset) )
n := 1

do while !Articulos->( eof() )

if n > nLimit
EXIT
else
aRow := {=>}
aRow['id'] := Articulos->( RecNo() )
aRow['codigo'] := Articulos->Codigo
aRow['nombre'] := Articulos->Nombre
aRow['precio'] := IF(Articulos->PVenta==0, '', ;
transform(Articulos->PVenta, '@ze 9999.99') )
AAdd( aSource, aRow )
endif

Articulos->( DBSkip() )
n++
enddo

// Preparar modelo de datos para DataTable
aData['draw'] := hParams['DRAW']
aData['recordsTotal'] := OrdKeyCount()
aData['recordsFiltered'] := OrdKeyCount()
aData['data'] := aSource

// Informacion devuelta para debug en navegador
aData['aParams'] := oCgi:aParams
aData['hParams'] := hParams

Articulos->( DBCloseArea() )
else
aData['data'] := {}
aData['recordsTotal'] := 0
aData['error'] := "No se encuentran datos"
endif

// Devolver respuesta JSON
oCGI:Console(aData)
return oCGI:SendJson( aData )

//------------------------------------------------------------------------------
// Modelo de datos MariaDb via TDolphin ( Llamada al CGI por Ajax )
//------------------------------------------------------------------------------

function GetSQLDolphin()

local cSql
local aSource
local oServer, oError, oQry
local aData := {=>}

TEXT INTO cSql
SELECT first, last, city, street, zip
FROM customer
ORDER BY first
LIMIT 100
ENDTEXT

TRY
oServer := _DbSqlConnex()
oQry := oServer:Query( cSql )
aSource := {}

// Recuperar filas en un array de hash
//while !oQry:Eof()
// aadd( aSource, oQry:FillHRow() )
// oQry:Skip()
//enddo

// Recuperar filas en un array
// Sintaxis FillArray( bOnFillArray, aColumns )
// aSource := oQry:FillArray(, { "first", "last", "city", "street", "zip" } )

// Recuperar filas al estilo DBF
do while !oQry:Eof()

AAdd( aSource, {;
Str(oQry:RecNo(),6), ;
AllTrim(oQry:First), ;
AllTrim(oQry:Last), ;
AllTrim(oQry:City), ;
AllTrim(oQry:Street),;
AllTrim(oQry:Zip) ;
})

oQry:Skip()
enddo

aData['recordsTotal'] := oQry:nRecCount()
aData['recordsFiltered'] := oQry:nRecCount()
aData['data'] := aSource

oQry:End()
oServer:End()

CATCH oError
aData['recordsTotal'] := 0
aData['data'] := {}
aData['error'] := oError:Description
END

// Devolver respuesta JSON

return oCGI:SendJson( aData )

//------------------------------------------------------------------------------
// Modelo de datos MariaDb via TDolphin con ordenacion ( Llamada al CGI por Ajax )
//------------------------------------------------------------------------------

function GetSQLOrderPage()

local cSql
local oError
local aSource, aData
local hParams
local nRows
local cTable, cOrder, cOffset, cLimit

aData := {=>}
cTable := 'customer'
hParams := oCGI:aParamsToHash(.T.)
cLimit := hParams['LENGTH']
cOffset := hParams['START']

cOrder := hParams['ORDER[0][COLUMN]']
// Obtener columna por posicion (+1)
cOrder := LTrim(Str(Val(cOrder)+1))
// Obtener columna por nombre
//cOrder := hParams['COLUMNS['+cOrder+'][DATA]']

cSql := [SELECT city,street,last,first,age FROM &1 ORDER BY &2 LIMIT &3 OFFSET &4]

TRY
WITH OBJECT _DbSqlConnex()

nRows := :GetRowsFromTable( cTable )

WITH OBJECT :Query( cSql, {cTable,cOrder,cLimit,cOffset} )

// Recuperar filas en un array de hash
aSource := {}
while ! :Eof()
aadd( aSource, :FillHRow() )
:Skip()
enddo

aData['data'] := aSource
aData['recordsTotal'] := nRows
aData['recordsFiltered'] := nRows

// Informacion devuelta para debug en navegador
aData['sql'] := {'query'=>:cQuery}

:End() // <- oQuery:End()
END
:End() // <- oServer:End()
END

CATCH oError
aData['recordsTotal'] := 0
aData['data'] := {}
aData['error'] := oError:Description
END

// Informacion devuelta para debug en navegador
aData['hParams'] := hParams

// Devolver respuesta JSON

return oCGI:SendJson( aData )

//------------------------------------------------------------------------------
// Modelo de datos MariaDb via TDolphin para CRUD ( Llamada al CGI por Ajax )
//------------------------------------------------------------------------------

function GetSQLCrud()

local cSql
local oError
local aSource, aData
local hParams
local nRows
local cTable, cOrder, cOffset, cLimit

aData := {=>}
cTable := 'customer'
hParams := oCGI:aParamsToHash(.T.)
cLimit := IF( hb_hHaskey(hParams, 'LENGTH'), hParams['LENGTH'], "10" )

cSql := [SELECT * FROM &1 ] //LIMIT &2]

TRY
WITH OBJECT _DbSqlConnex()

WITH OBJECT :Query( cSql, {cTable, cLimit} )

// Recuperar filas en un array de hash
aSource := {}
while ! :Eof()
aadd( aSource, :FillHRow() )
:Skip()
enddo

aData['data'] := aSource
aData['recordsTotal'] := :nRecCount
aData['recordsFiltered'] := :nRecCount

// Informacion devuelta para debug en navegador
aData['sql'] := {'query'=>:cQuery}

:End() // <- oQuery:End()
END
:End() // <- oServer:End()
END

CATCH oError
aData['recordsTotal'] := 0
aData['data'] := {}
aData['error'] := oError:Description
END

// Informacion devuelta para debug en navegador
aData['hParams'] := hParams

// Devolver respuesta JSON

return oCGI:SendJson( aData )

//------------------------------------------------------------------------------
// Operaciones sobre registros
//------------------------------------------------------------------------------

function DataEditRow(pid,pfi,pla,pci,pag)

local cTable, csQL
local oError
local hResponse //:= {=>}
local lNew := .F.
hResponse := {=>}
cTable := 'customer'

// 1) Determinar tipo de operacion
if ( lNew := empty(pid ))
csQL := [INSERT INTO ]+cTable+[ SET first=]+nfl_Comillasd(pfi)+[ ,last=]+nfl_Comillasd(pla)+[ ,city=]+nfl_Comillasd(pci)+[ ,age=]+pag
else
csQL := [UPDATE ]+cTable+[ SET first=]+nfl_Comillasd(pfi)+[ ,last=]+nfl_Comillasd(pla)+[ ,city=]+nfl_Comillasd(pci)+[ ,age=]+pag+[ WHERE id=]+pid
endif

// 2) Añadir o actualizar registro
TRY
WITH OBJECT _DbSqlConnex()
:Execute( csQL )
:End()
END
hResponse['error'] := .F.
hResponse['message'] := "Registro "+ IF( lNew, "Insertado", "Modificado" )

CATCH oError
hResponse['error'] := .T.
hResponse['message'] := oError:Description
END
// 3) Devolver respuesta hash con el resultado de la operacion
hResponse['error'] := .T.
hResponse['message'] := "response"

return hResponse

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

function DataDeleteRow(pid)

local cTable, csQL
local oError
local hResponse

hResponse := {=>}
cTable := 'customer'
csQL := [DELETE FROM ]+cTable+[ WHERE id=]+pid

// 1) Eliminar registro
TRY
WITH OBJECT _DbSqlConnex()
:Execute( csQL )
:End()
END
hResponse['error'] := .F.
hResponse['message'] := "Registro borrado"

CATCH oError
hResponse['error'] := .T.
hResponse['message'] := oError:Description
END

// 2) Devolver respuesta hash con el resultado de la operacion

return hResponse

//------------------------------------------------------------------------------
// Conexion MariaDb via TDolphin
//------------------------------------------------------------------------------

#include "tdolphin.ch"

static function _DbSqlConnex()

local oServer
local hIni := hb_ReadIni( "connex.ini" )

CONNECT oServer ;
HOST hIni["mysql"]["host"] ;
USER hIni["mysql"]["usr"] ;
PASSWORD hIni["mysql"]["pwd"] ;
PORT hIni["mysql"]["port"] ;
FLAGS hIni["mysql"]["flags"] ;
DATABASE hIni["mysql"]["dbname"]

return oServer

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