How do I do my own processing witht the DataTables Editor?

Eduardo Almeida

Good afternoon from Brazil, Community!

I need help with the DataTables Editor.

CASE: I have 3 (three) SQL Server 2012 tables that I'm showing by using the leftJoin() method. I need to update only two of the tables, but the problem is that they are not "database related", that means that the tables don't have constraints linking each other, they just have the same "primary-key" (yeah! The database is a mess, the field that holds the "primary-key" doesn't have that constraint implemented) and each table holds a different information to the same thing. What I need to do is to update two tables and the third is a readonly table.

#-------------------#           #-------------------#           #-------------------#
|       Table_1     |           |       Table_2     |           |       Table_3     |
#-------------------#           #-------------------#           #-------------------#
| id |  info_1      |           | id |   info_2     |           | id |   info_3     |
#-------------------#           #-------------------#           #-------------------#
| 1 | bla bla bla   |           |  1 | bla bla_2    |           |  1 | bla bla_3    |
#-------------------#          # -------------------#           # ------------------#

On the illustration above, I tried to show the 3 (three) tables with only one record each and the same id or "primary-key", refering to the same object with info in three different tables.

Because of this, when I update the database, I first need to check if the record already exists, if it does exist, I update, if not, I need to insert data into it. I can't make any changes on the database, unfortunatelly.

Here is my code:

<?php
include '../Editor/php/DataTables.php';

/*
 * 
 * 
 * namespaces do plugin of the Editor DataTables
 */
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Join,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;

/*
 * 
 * 
 * creates a new instance of the editor class
 */

$editor = Editor::inst( $db, 'Esteira_Credito as diurb', 'numero_operacao' )
    ->fields(
        /*
         * 
         * read all fields of the OCE tables
         */
        Field::inst( 'diurb.unidade' ),
        Field::inst( 'diurb.macrossegmento' ),
        Field::inst( 'diurb.setor' ),
        Field::inst( 'diurb.fonte_recursos' ),
        Field::inst( 'diurb.programa' ),
        Field::inst( 'diurb.selecao' ),
        Field::inst( 'diurb.pacnpac' ),
        Field::inst( 'diurb.uf' ),
        Field::inst( 'diurb.regiao' ),
        Field::inst( 'diurb.tomador' ), # <---\\ dozen
        Field::inst( 'diurb.municipio' ),
        Field::inst( 'diurb.empreendimento' ),
        Field::inst( 'diurb.modalidade' ),
        Field::inst( 'diurb.numero_operacao' ),
        Field::inst( 'diurb.investimento' ),
        Field::inst( 'diurb.financiamento' ),
        Field::inst( 'diurb.contrapartida' ),
        Field::inst( 'diurb.situacao_operacao' ),
        Field::inst( 'diurb.previsao' ),
        Field::inst( 'diurb.observacoes' ), # <---\\ dozen
        Field::inst( 'diurb.risco_tomador' ),
        Field::inst( 'diurb.risco_operacao' ),
        Field::inst( 'diurb.analise_juridica' ),
        Field::inst( 'diurb.analise_engenharia' ),
        Field::inst( 'diurb.precificacao' ),
        Field::inst( 'diurb.emissao_proposta_firme_stn' ),
        Field::inst( 'diurb.autorizacao_stn' ),
        Field::inst( 'diurb.impacto_patrimonio_referencia' ),
        Field::inst( 'diurb.comite_credito_sr' ),
        Field::inst( 'diurb.comite_credito_matriz' ), # <---\\ dozen
        Field::inst( 'diurb.conselho_diretor' ),
        Field::inst( 'diurb.termo_habilitacao' ),
        // ^^^^ first table readonly
        Field::inst( 'acompanhamento.tipo' ),
        Field::inst( 'acompanhamento.priorizada' ),
        // ^^^^ second table read and update
        Field::inst( 'complemento.observacoes_complementares' ),
        Field::inst( 'complemento.condicoes_operacao_taxa_prazo_garantias' ),
        Field::inst( 'complemento.complementacao_informacoes_garantia_observacoes' ),
        Field::inst( 'complemento.reciprocidade' ),
        Field::inst( 'complemento.montate_fee' ),
        Field::inst( 'complemento.ppp_concessoes' ), # <---\\ dozen
        Field::inst( 'complemento.tarifa' ),
        Field::inst( 'complemento.estruturacao' ),
        Field::inst( 'complemento.voto' ),
        Field::inst( 'complemento.gerente' ),
        Field::inst( 'complemento.analista' ),
        Field::inst( 'complemento.estagio_etapa' ),
        Field::inst( 'complemento.situacao_estagio_etapa' ),
        Field::inst( 'complemento.estagio_tarefa' ),
        Field::inst( 'complemento.situacao_estagio_tarefa' ),
        Field::inst( 'complemento.usuario' ), # <---\\ dozen
        Field::inst( 'complemento.data_hora' )
        // ^^^^ third table read
    )
    ->where('diurb.situacao_operacao', 'Contratada', '<>')
    /*
     * 
     * JOIN with the "aux" tables
     */
    ->leftJoin( 'Esteira as acompanhamento', 'left(acompanhamento.numero_operacao, 7)', '=', 'left(diurb.numero_operacao, 7)' )
    ->leftJoin( 'Esteira_Inf_Compl as complemento', 'acompanhamento.numero_operacao', '=', 'diurb.numero_operacao' )
    /*
     * 
     * 
     * here is where reside my problems: how to workaround this
     */
    ->process( $_POST )
    /**
     * 
     * it brings the whole data in JSOn format
     */
    ->json();

I need to do sth like this:

if( isset($_POST['action']) AND $_POST['action'] == 'edit' ){
    // here is my code to select, check and update or insert into the database
}

The problem is that the process( $_POST ) is being executed everytime I submit the edit form, and a SQL Server 2012 error (excpetion) is thrown (because of the database mess which doesn't allow me to do things correctly) by the process() method:

{"error":"SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'as'.","data":[]}

But when I remove the process( $_POST ) part the following error is shown:

{"fieldErrors":[],"error":"","data":[],"ipOpts":[]}

Could anyone help me on this? Any thoughts are very welcomed.

Thanks in advance!!!

Eduardo Almeida

My illustration went wrong somehow. I'm sorry!!!

allan

Hi,

I wonder if the best approach here would be to use server-side events. The way I would suggest approaching it is let Editor update the data in the first table, but then for the subsequent two tables you could update them using a preEdit event. The event is given the data from the client-side so you can write it to the server.

Allan