How to inject column data into Email notification using 'postCreate' function?

koppanm

Dear Allan, the ->on( 'postCreate', function()) is working well, thanks for it. It would be great to inject column data into it. Perhaps, using $values might bear the solution to it, but I could not figure it out, how to get it done. Please, advice! Many thanks, Miklos

kthorngren

When you say column data are you referring to the data of the row that was just created? If yes then use the second parameter, json, of the postCreate event to access the returned row data.

If the new row contains other generated data that you need then use the fourth parameter, id, to access the the row data using row().data() with the selector as the string #id. I built a simple example that you can add a new row to and see the structure of the json response and using row().data() to get the new row.
https://live.datatables.net/guwafemu/623/edit

Is this what you are looking for? If not please provide more details of what column data you want to use.

Kevin

koppanm

Dear Kevin, thanks for your ind and quick reply. Yes, that is what I am looking for! I will go ahead and check what you have built and try to implement it. Many thanks in advance, will report back.
Miklos

koppanm

OK, now I see you have probably thought I would like to log changes. Indeed, what I would like is to send email notification with newly inserted data.
To clarify this for you I place my code below.

Many thanks!

<?php

/*
 * Editor server script for DB table dvoppriceoffer_joint
 * 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\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

// The following statement can be removed after the first run (i.e. the database
// table has been created). It is a good idea to do this to help improve
// performance.
$db->sql( "CREATE TABLE IF NOT EXISTS `dvoppriceoffer_joint` (
    `id` int(10) NOT NULL auto_increment,
    `name` varchar(255),
    `taj` varchar(255),
    `dg` varchar(255),
    `op` varchar(255),
    `surgeon` varchar(255),
    `assist` varchar(255),
    `date` date,
    `note` varchar(255),
    `state` varchar(255),
    `dv` numeric(9,2),
    `histo` numeric(9,2),
    `smart` numeric(9,2),
    `extra` numeric(9,2),
    `dv_sum` numeric(9,2),
    `orv` numeric(9,2),
    `price_sum` numeric(9,2),
    `optyp` numeric(9,2),
    `validfrom` varchar(255),
    `offernum` varchar(255),
    PRIMARY KEY( `id` )
);" );

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'dvoppriceoffer_joint', 'id' )
    ->fields(
        Field::inst( 'name' )
        ->options(Options::inst()
                ->table( 'dv_priceoffer_emails' )
                ->value( 'name' )
                ->label( 'name' )
            ),
        Field::inst( 'taj' ),
        Field::inst( 'op' ),
        Field::inst( 'surgeon' )
        ->options(Options::inst()
                ->table( 'surgeons' )
                ->value( 'name' )
                ->label( 'name' )
            ),
        Field::inst( 'assist' )
        ->options(Options::inst()
                ->table( 'assistants' )
                ->value( 'name' )
                ->label( 'name' )
            ),
        Field::inst( 'date' )
            ->validator( Validate::dateFormat( 'Y-m-d' ) )
            ->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) )
            ->setFormatter( Format::dateFormatToSql( 'Y-m-d' ) ),
        Field::inst( 'dg' ),
        Field::inst( 'note' ),
        Field::inst( 'state' )
        ->options(Options::inst()
                ->table( 'statuses' )
                ->value( 'status' )
                ->label( 'status' )
            ),
        Field::inst( 'dv' ),
        Field::inst( 'histo' ),
        Field::inst( 'smart' ),
        Field::inst( 'extra' ),
        Field::inst( 'dv_sum' ),
        Field::inst( 'orv' ),
        Field::inst( 'price_sum' ),
        Field::inst( 'optyp' )
        ->options(Options::inst()
                ->table( 'dvoppricelist' )
                ->value( 'id' )
                ->label( 'id' )
            ),
        Field::inst( 'validfrom' ),
        Field::inst( 'offernum' )
        ->options(Options::inst()
                ->table( 'dv_priceoffer_emails' )
                ->value( 'offernum' )
                ->label( 'offernum' )
            )
        
        

        )
        
        ->on( 'postEdit', function ( e, json, data, id ) {
    mail( 'koppan.miklos@icloud.com, holdraforgo@icloud.com, akoppan@laparo.hu', 'Előjegyzés módosítva!', 'Előjegyzés módosítva: '(table.row(id).data())' sorszámú előjegyzésnél módosítás történt.' );
        } )
        ->on( 'postCreate', function ( $editor, $id, $values, $row ) {
    mail( 'koppan.miklos@icloud.com, holdraforgo@icloud.com, akoppan@laparo.hu', 'Új előjegyzés történt!', 'Új előjegyzés: '.$id.' sorszámmal rögzítve.' );
        } )
        ->on( 'postRemove', function ( $editor, $id, $values ) {
    mail( 'koppan.miklos@icloud.com, holdraforgo@icloud.com, akoppan@laparo.hu', 'Előjegyzés törölve!', 'Törölve: '.$id.' sorszámú előjegyzés törölve.' );
        } )


        

    ->process( $_POST )
    
    ->json();

kthorngren

Right, sorry I misunderstood. I don't use the Editor server side libraries but I believe you will want to refer to this postCreate event doc. Looks like the first parameter is data which is the data read from the database. I suspect that is what you want to use. I might be wrong but I think you have the incorrect parameters listed for the events in your above code.

Kevin

allan

Also:

->on( 'postEdit', function ( e, json, data, id ) {

Isn't valid PHP. That looks like the client-side postEdit function, not the server-side one by the same name (which I can see how it is confusing!).

Your postCreate and postRemove look fine, but check the manual for postEdit.

The final parameter ($row) passed to the event handler is what you should use to create your email.

Allan

koppanm

Many thanks to both of you for your kind and valuable help!
I have followed your instruction, Allan, but it does not work unfortunately.

->on( 'postEdit', function ( $editor, $id, $values, $row ) {
mail( 'myself@icloud.com, herself@icloud.com, herself@laparo.hu', 'Elojegyzes modositva!', 'Előjegyzés módosítva: '.$id.' sorszámú előjegyzésnél módosítás történt.' );
} )
->on( 'postCreate', function ( $editor, $id, $values, $row ) {
mail( 'myself@icloud.com, herself@icloud.com, herself@laparo.hu', 'Uj elojegyzes!', 'Új előjegyzés '.$row.' sorszámmal rögzítve.' );
} )
->on( 'postRemove', function ( $editor, $id, $values ) {
mail( 'myself@icloud.com, herself@icloud.com, herself@laparo.hu', 'Elojegyzes torolve!', 'Törölve: a(z) '.$id.' sorszámú előjegyzés törölve.' );
} )

kthorngren

Without knowing the details of what is not working and any error messages you might be getting. My guess is that you need to access the specific properties (fields) of the $row object. I don't use PHP so the syntax might not be correct but maybe something like $row->name.

Kevin

koppanm

Dear Kevin, I appreciate you efforts! I got it working partially with the code below:

->on( 'postEdit', function ( $editor, $id, $values, $row ) {
mail( '@icloud.com, @icloud.com, @laparo.hu', 'Elojegyzese modositva!', 'Előjegyzés módosítva: '.$row['name'].' előjegyzésénél módosítás történt.' );
} )

Now data from column "name" is injected into the body of the email. However, I would still need more data from additional columns to be inserted, that I have not been able to achieve so far. Any idea would be highly appreciated.
Best regards,
Miklos

kthorngren

Looks like the PHP email() method expects a string for the third message parameter. You will need to build a string but concatenating the different fields with the text you want. For example:

'Dear  '.$row['name'].' your surgeon is '.$row['surgeon'].' more text....'

Kevin

koppanm

Dear Kevin,
I am grateful to you for your continuous support! It helped me a lot to get to the current state of my code - almost perfect.
The only tiny thing I have not been able to solve so far is the missing whitespace between data injected into the body of the email message. I have thoroughly checked the available descriptions about PHP syntax to solve it - no luck so far. The problem is minor, it can stay as is, but if you have any idea it would be awesome.
Once more, many-many thanks for all your suggestions and support.
Yours sincerely,
Miklos
- below you may kindly find my code

->on( 'postCreate', function ( $editor, $id, $values, $row ) {
mail( '@icloud.com, @icloud.com, @laparo.hu', 'DV GYN Team - Elfogadta, bejegyezve!', $row['op'].$row['date'].', operatőr: '.$row['surgeon'], 'Új műtét előjegyezve: '.$row['name'] );
} )

kthorngren

Are you asking about adding new lines? If yes then see if this SO thread helps.

Kevin

allan

One thing, if you are sending email from PHP, I'd very much suggest using something like PHPMailer, rather than the built in mail() if you are sending to external addresses and deliverability is important.

Allan