Server side calculated fields best method

Datagaard

Hi Allan,

I am wanting to return a calculated data field from the server to populate an additional column in the dataTable, but is not used in the editor.

I have created a separate ajax request for the dataTable to return the required data, but can't seem to get the Editor side to populate the required dropdown.

So I was wondering what is the best practice for this type of scenario.

My Ajax data is as follows:

<?php
    header('Content-Type: application/json');
    
    include_once '../Includes/db.inc.php';

    $aResult = array();
    $data = array();
    if(PHP_SAPI === 'cli') {
        $partID = $argv[1];
    }
    else{
        $partID = $_REQUEST['ParticipantID'];
    }

    try
    {
        //$partID = $_POST['arguments'][0];
        
        $sql = "SELECT p.ID, p.ParticipantID, p.ReferralNumber, p.ReferralDoctor, p.ReferralDoctorNumber, p.ReferralDate, r.ReferralTypeDescription, p.ReferralPeriod, p.FirstSessionDate,
                    CASE LOWER(p.ReferralPeriodTypeID) WHEN 'n' THEN CONVERT(VARCHAR(10),DATEADD(month,COALESCE(p.ReferralPeriod,0),p.FirstSessionDate),120) WHEN 'i' THEN 'None' 
                        ELSE CONVERT(VARCHAR(10),DATEADD(month,12,p.FirstSessionDate),120) end AS ExpiryDate
                    FROM TelemedParticipantReferral AS p
                    LEFT OUTER JOIN ReferralPeriodType AS r ON r.ReferralPeriodTypeID = p.ReferralPeriodTypeID
                    WHERE ParticipantID = :partID";
        
            global $pdo;
            //echo $sql;            
            $s = $pdo->prepare($sql);
            $s->bindValue(':partID',$partID);
            $s->execute();
            
            while($row = $s->fetch())
            {
                $aResult[] = array('ID' => $row['ID'], 'ParticipantID' => $row['ParticipantID'], 'ReferralNumber' => $row['ReferralNumber'], 'ReferralDoctor' => $row['ReferralDoctor'], 'ReferralDoctorNumber' => $row['ReferralDoctorNumber'], 'ReferralDate' => $row['ReferralDate'], 'ReferralTypeDescription' => $row['ReferralTypeDescription'], 'ReferralPeriod' => $row['ReferralPeriod'], 'FirstSessionDate' => $row['FirstSessionDate'], 'ExpiryDate' => $row['ExpiryDate']);
            }   
    }
    catch (PDOException $e){$data['error'] = $e->getMessage();}
    $data['data'] = $aResult; 
    echo json_encode($data);

?>

The json data returned can be seen in the attached image.

Within my calling PHP I have this:

var table = $('#TelemedParticipantReferral').DataTable({
        dom: 'Bfrtip',
        ajax: {
            url: 'ajax/TelemedParticipantReferral.php',
            data: {
                ParticipantID: partId
            },
            dataSrc: 'data'
        },
        columns: [
            { "data": "ReferralNumber"  },
            { "data": "ReferralDoctor"  },
            { "data": "ReferralDoctorNumber"    },
            { "data": "ReferralDate" },
            { "data": "ReferralTypeDescription" },
            { "data": "ReferralPeriod"  },
            { "data": "FirstSessionDate" },
            { "data": "ExpiryDate" }
        ],
        select: true,
        lengthChange: false,
        responsive: true,
        buttons: [
            { extend: 'create', editor: editor },
            { extend: 'edit',   editor: editor },
            { extend: 'remove', editor: editor }
        ]
    });
    table
        .order( [ 1, 'desc' ] )
        .draw();

Data shows in the table. However no data shows in the dropdown for the Editor side.

Code below for Editor:

<?php
    ini_set("display_errors",1);
/*
 * Editor server script for DB table TelemedParticipantReferral
 * Created by http://editor.datatables.net/generator
 */

// DataTables PHP library and database connection
include( "lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;

if(PHP_SAPI === 'cli') {
    $partID = intval($argv[1]);
}
else{
    $partID = intval($_REQUEST['ParticipantID']);
}

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'TelemedParticipantReferral', 'ID' )
    ->fields(
        Field::inst( 'TelemedParticipantReferral.ParticipantID' )
            ->validator( 'Validate::notEmpty' )
            ->validator( 'Validate::numeric' ),
        Field::inst( 'TelemedParticipantReferral.ReferralNumber' )
            ->validator( 'Validate::notEmpty' ),
        Field::inst( 'TelemedParticipantReferral.ReferralDoctor' ),
        Field::inst( 'TelemedParticipantReferral.ReferralDoctorNumber' ),
        Field::inst( 'TelemedParticipantReferral.ReferralDate' )
            ->validator( 'Validate::dateFormat', array( 'format'=>'D, j M y' ) )
            ->getFormatter( 'Format::date_sql_to_format', 'D, j M y' )
            ->setFormatter( 'Format::date_format_to_sql', 'D, j M y' ),
        Field::inst( 'TelemedParticipantReferral.ReferralPeriodTypeID' )
            ->options( 'ReferralPeriodType','ReferralPeriodTypeID', 'ReferralTypeDescription')
            ->validator( 'Validate::dbValues' ),
        Field::inst( 'ReferralPeriodType.ReferralTypeDescription' ),
        Field::inst( 'TelemedParticipantReferral.ReferralPeriod' ),
        Field::inst( 'TelemedParticipantReferral.FirstSessionDate' )
            ->validator( 'Validate::dateFormat', array( 'format'=>'D, j M y' ) )
            ->getFormatter( 'Format::date_sql_to_format', 'D, j M y' )
            ->setFormatter( 'Format::date_format_to_sql', 'D, j M y' )
    )
    ->leftJoin( 'ReferralPeriodType', 'ReferralPeriodType.ReferralPeriodTypeID', '=', 'TelemedParticipantReferral.ReferralPeriodTypeID' )
    ->where( $key = "TelemedParticipantReferral.ParticipantID", $value = $partID, $op = '=' )
    ->process( $_POST )
    ->json();
?>

Calling PHP Code:

var partId = <?php echo $data_ParticipantID; ?>;
        var editor = new $.fn.dataTable.Editor({
            ajax: {
                url: 'php/table.TelemedParticipantReferral.php',
                data: {
                    ParticipantID: partId       
                }       
            },
            table: '#TelemedParticipantReferral',
            fields: [
                {
                    "label": "ParticipantID:",
                    "name": "TelemedParticipantReferral.ParticipantID"
                },
                {
                    "label": "Referral No:",
                    "name": "TelemedParticipantReferral.ReferralNumber"
                },
                {
                    "label": "Referral Doctor:",
                    "name": "TelemedParticipantReferral.ReferralDoctor"
                },
                {
                    "label": "Referral Doctor No:",
                    "name": "TelemedParticipantReferral.ReferralDoctorNumber"
                },
                {
                    "label": "Referral Date:",
                    "name": "TelemedParticipantReferral.ReferralDate",
                    "type": "datetime",
                    "format": "ddd, D MMM YY"
                },
                {
                    "label": "Referral Period Type:",
                    "name": "TelemedParticipantReferral.ReferralPeriodTypeID",
                    "type": "select"
                },
                {
                    "label": "Referral Period:",
                    "name": "TelemedParticipantReferral.ReferralPeriod"
                },
                {
                    "label": "First Session Date:",
                    "name": "TelemedParticipantReferral.FirstSessionDate",
                    "type": "datetime",
                    "format": "ddd, D MMM YY"
                }
            ]
    });

Edit doesn't work, in as much as clicking the Edit button doesn't bring any screen up, is this because I don't have a row ID?

allan

is this because I don't have a row ID?

No - that shouldn't be the case. You have specified ID in the Editor constructor's third parameter - that should be all that is needed.

Is there a Javascript error occurring when you click one of the buttons?

Allan

Datagaard

Hi Allan,

Apologies for delay in getting back to this, was on holidays in USA.
To clarify the issue, the popup appears when edit is clicked, but only the participantid field in the editor populates with data.

I can see no Javascript errors being reported by firebug.

allan

Hope you had a good holiday .

I'd really need a link to the page showing the issue to be able to debug it.

Allan

Datagaard

Hi Allan.

I will get you access. Can you reply to my email address so I can grant you access.

Had a great time in New York and Boston, Paying for it now with very bad cold hitting me around quite a bit.

Cheers