Send email on row edit with editor

davidjmorin · August 2020

Im trying to get an email to fire off when an edit is made.


include( "lib/DataTables.php" );

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;


    Editor::inst( $db, 'rma_submissions', 'ID' )
        ->fields(
            Field::inst( 'rma_submissions.id' ),
            Field::inst( 'rma_submissions.timestamp' )
                ->validator( Validate::dateFormat( 'Y-m-d H:i:s' ) )
                ->getFormatter( Format::datetime( 'Y-m-d H:i:s', 'Y-m-d H:i:s' ) )
                ->setFormatter( Format::datetime( 'Y-m-d H:i:s', 'Y-m-d H:i:s' ) ),
            Field::inst( 'rma_submissions.location' ),
            Field::inst( 'rma_submissions.orig_invoice' ),
            Field::inst( 'rma_submissions.all_accessories' ),
            Field::inst( 'rma_submissions.new_submission' ),
            Field::inst( 'rma_submissions.man_sku' ),
            Field::inst( 'rma_submissions.device_id' ),
            Field::inst( 'rma_submissions.failure_desc' ),
            Field::inst( 'rma_submissions.defective_oob' ),
            Field::inst( 'rma_submissions.original_sales_date' ),
            Field::inst( 'rma_submissions.vendor' ),
            Field::inst( 'rma_submissions.email' ),
            Field::inst( 'rma_submissions.device_man' ),
            Field::inst( 'rma_submissions.district' ),
            Field::inst( 'rma_submissions.rma_number' ),
            Field::inst( 'rma_submissions.paid' ),
            Field::inst( 'rma_submissions.paid_date' ),
            Field::inst( 'rma_reasons.reason_description' ),
        )
        ->on( 'postEdit', function ( $editor, $id, $values, $row ) {

            })

        ->leftJoin( 'rma_reasons', 'rma_reasons.reason_code', '=', 'rma_submissions.failure_desc' )
        ->on( 'postEdit', function ( $editor, $id, $values, $row ) {
            $to = "sku@mysite.net";
            $subject = "rmaUpdate Completed";

            $message = "
            <html>
            <head>
            <title>rmaUpdate Complete</title>
            </head>
            <body>
            <p>This is a copy of your rma Update Request</p>
            <table>
            <tr><td>Email: <b> $row['email']</b></td></tr>
            <tr><td>Notes: <b> $row['failure_desc']</b></td></tr>
            <tr><td>RQ Sku: <b> $row['man_sku']</b></td></tr>
            <tr><td>Completed?: <b> Yes </b></td></tr>
            </table>
            </body>
            </html>
            ";

            // Always set content-type when sending HTML email
            $headers = "MIME-Version: 1.0" . "\r\n";
            $headers .= "Content-type:text/html;charset=UTF-8" . "\r\n";

            // More headers
            $headers .= 'From: <support@mysite.net>' . "\r\n";
            $headers .= 'cc: <'.$row['email'].'>' . "\r\n";


            mail($to,$subject,$message,$headers);
    } )
        ->process( $_POST )
        ->json();

The above saves the data but gives me an error with my editor.

Any suggestions?

allan · August 2020

That error message means that the server has responded with invalid JSON. Hopefully there is an error message in there. What is that error message? If you click the “More information” link it will show you how to get that information.

Allan

davidjmorin · August 2020

Its returning the correct data it appears.

data[row_11][rma_submissions][rma_number]: Test2m666
data[row_11][rma_submissions][paid]: No
data[row_11][rma_submissions][paid_date]: 
data[row_11][rma_submissions][vendor]: Ice Mobility
data[row_11][rma_submissions][device_man]: Apple
data[row_11][rma_submissions][device_id]: 355982110016789
action: edit

davidjmorin · August 2020

I did update my script a bit.

<?php

// Using the sku submit email for testing only.  

function my_mail_function(){
    $to = "davidm@mysite.net";
    $subject = "RMAUpdate Completed";

    $values2 = $row['rma_submissions.paid']; //Just for testing purpose.

    $message = "
    <html>
    <head>
    <title>RMAUpdate Complete</title>
    </head>
    <body>
    <p>This is a copy of your SKU Update Request</p>
    <table>
      $values2
    <tr><td>Completed?: <b> Yes </b></td></tr>
    </table>
    </body>
    </html>
    ";

    // Always set content-type when sending HTML email
    $headers = "MIME-Version: 1.0" . "\r\n";
    $headers .= "Content-type:text/html;charset=UTF-8" . "\r\n";

    // More headers
    $headers .= 'From: <support@mysite.net>' . "\r\n";


    mail($to,$subject,$message,$headers);
}

include( "lib/DataTables.php" );

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;


    Editor::inst( $db, 'rma_submissions', 'ID' )
        ->fields(
            Field::inst( 'rma_submissions.id' ),
            Field::inst( 'rma_submissions.timestamp' )
                ->validator( Validate::dateFormat( 'Y-m-d H:i:s' ) )
                ->getFormatter( Format::datetime( 'Y-m-d H:i:s', 'Y-m-d H:i:s' ) )
                ->setFormatter( Format::datetime( 'Y-m-d H:i:s', 'Y-m-d H:i:s' ) ),
            Field::inst( 'rma_submissions.location' ),
            Field::inst( 'rma_submissions.orig_invoice' ),
            Field::inst( 'rma_submissions.all_accessories' ),
            Field::inst( 'rma_submissions.new_submission' ),
            Field::inst( 'rma_submissions.man_sku' ),
            Field::inst( 'rma_submissions.device_id' ),
            Field::inst( 'rma_submissions.failure_desc' ),
            Field::inst( 'rma_submissions.defective_oob' ),
            Field::inst( 'rma_submissions.original_sales_date' ),
            Field::inst( 'rma_submissions.vendor' ),
            Field::inst( 'rma_submissions.email' ),
            Field::inst( 'rma_submissions.device_man' ),
            Field::inst( 'rma_submissions.district' ),
            Field::inst( 'rma_submissions.rma_number' ),
            Field::inst( 'rma_submissions.paid' ),
            Field::inst( 'rma_submissions.paid_date' ),
            Field::inst( 'rma_reasons.reason_description' ),
        )
        ->on( 'postEdit', function ( $editor, $id, $values, $row ) {

            })

        ->leftJoin( 'rma_reasons', 'rma_reasons.reason_code', '=', 'rma_submissions.failure_desc' )

        ->on( 'postEdit', function ( $editor, $id, $values, $row ) {
                my_mail_function();

        } )
        #->on( 'submitError', function ( e, xhr, err, thrown, data ) { this.error( xhr.responseText ); } );
        ->process( $_POST )
        ->json();

davidjmorin · August 2020

I did remove the 1st instance of the ->on postedit in this. It doesnt allow me to edit my post.

also, if i remove the $values2 from the script it works fine. It must be trying to parse data that is not there. How would I get the information from the table and row that was edited?

davidjmorin · August 2020

@allan
How can I include js editor.on( 'submitError', function ( e, xhr, err, thrown, data ) { this.error( xhr.responseText ); } ); in this example? Ive tried a few different ways and it fails to load.

davidjmorin · August 2020

Ok so I kinda have this working but want to know why I have to store this in a session for it to work.

    ->on('postEdit', function ($editor, $id, $values, $row) {
            $_SESSION['values'] = $row;
            $values2 = $_SESSION['values']['rma_submissions']['location'];
            $mail = new PHPMailer;
            $mail->setFrom('david.morin@mysite.net', 'RMA Request');
            $mail->addReplyTo('noreply@mysite.net', 'No Reply');
            $mail->addCC('davidm@mystie.net', 'David Morin');
            $mail->Subject = 'RMA Request Information';
            $mail->msgHTML('<b>RMA');
            $mail->Body = "
            <html>
            <head>

            </head>
            <body>
            This is a copy of your RMA request.
            <p>
                     ".$_SESSION['values']['rma_submissions']['location']." <br />
                 ".$_SESSION['values']['rma_submissions']['id']."<br>
                 ".$_SESSION['values']['rma_submissions']['device_id']."
            </p>
            </body>
            </html>";

            $mail->send();


                })

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

Shouldnt I be able to just use $row[rma_submissions']['id']?

If i use the above without session it sends the email and saves to DB but still shows me an error. So its working but throwing an error somewhere. How and where can i diagnose this? Console and networking show no errors and 200 status.

allan · August 2020

Shouldnt I be able to just use $row[rma_submissions']['id']?

Yes.

If i use the above without session it sends the email and saves to DB but still shows me an error.

As I mentioned, the error you are seeing means that invalid JSON has been returned by the server. It might well contain some valid JSON, but there is something in there that it making the message as a whole invalid. So the key is to use the browser’s network inspector and go to the “Response” tab (not “Preview”) for the request and see what is there.

Allan

davidjmorin · August 2020

@allan Thank you for the response. So this is the response that I got.

{"data":[{"DT_RowId":"row_11","rma_submissions":{"id":"11","timestamp":"2020-07-27 10:55:03","location":"Andover MA","orig_invoice":"Yes","all_accessories":"Yes","new_submission":"New","man_sku":"SMG988UZKV","device_id":"3732847439847398","failure_desc":"18","defective_oob":"yes","original_sales_date":null,"vendor":"Ice Mobility","email":"davidm@mysite.net","device_man":"Apple","district":"District Manny","rma_number":"29384u239","paid":"Yes","paid_date":"Tue, 11 Aug 2020"},"rma_reasons":{"reason_description":"Headset Jack"}}]}

allan · August 2020

That’s valid JSON, which would not result in the “system error” message.

Can you give me a link to the page so I can check it out please? You can send me a PM by clicking my name above and then “Send message”, if you don’t want to make the url public.

Allan

davidjmorin · August 2020

@allan I got it working and it is now sending emails. I am not sure what was the issue but it is now working. One more issue though. If i set a where clause it does not send email.
See code below

    ->where( 'completed', 0, '=' )

        ->on('postEdit', function ($editor, $id, $values, $row) {
            $emailAddress = $row['requester'];
            $mail = new PHPMailer;
            $mail->setFrom('support@mysite.net', 'RMA Request');
            $mail->addReplyTo('noreply@mysite.net', 'No Reply');
            #$mail->addAddress(strval($emailAddress));
            $mail->addCC('davidm@mysite.net', 'David Morin');
            $mail->Subject = 'RMA Request Information';
            $mail->msgHTML('<b>Sku Request completed');
            if ($row['completed'] == '1') {


                $mail->Body = "
                <html>
                 <head>
                        <style>
                             .alnright { text-align: right; }
                        </style>
                 </head>
                 <body>
                        <img src='https://dev.mysite.net/assets/logo.png'><br />
                        <p>
                        SKU Request Complete
                        </p>
                        <p>
                        Item: ".$row['item_name']."<br />
                        Vendor: ".$row['vendor']."<br />
                        Barcode: ".$row['barcode']."
                 </body>
            </html>";


            $mail->send();
}

                        })


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

davidjmorin · August 2020

Here is the response

{
  "data": [],
  "debug": [
    {
      "query": "SELECT  `id` as 'id' FROM  `sku_request` WHERE `id` = :where_0 ",
      "bindings": [
        {
          "name": ":where_0",
          "value": "37",
          "type": null
        }
      ]
    },
    {
      "query": "UPDATE  `sku_request` SET  `completed` = :completed WHERE `id` = :where_0 ",
      "bindings": [
        {
          "name": ":completed",
          "value": "1",
          "type": null
        },
        {
          "name": ":where_0",
          "value": "37",
          "type": null
        }
      ]
    },
    {
      "query": "SELECT  `id` as 'id', `ID` as 'ID', `dateAdded` as 'dateAdded', `ID` as 'ID', `requester` as 'requester', `district` as 'district', `item_name` as 'item_name', `vendor_cost` as 'vendor_cost', `default_price` as 'default_price', `vendor` as 'vendor', `vendor_sku` as 'vendor_sku', `manufacturer` as 'manufacturer', `man_sku` as 'man_sku', `barcode` as 'barcode', `category` as 'category', `device_man` as 'device_man', `device_model` as 'device_model', `color` as 'color', `other_vendor` as 'other_vendor', `new_man` as 'new_man', `amazon_asin` as 'amazon_asin', `update_sku` as 'update_sku', `completed` as 'completed', `update_new` as 'update_new', `dateAdded` as 'dateAdded', `date_modified` as 'date_modified' FROM  `sku_request` WHERE `completed` = :where_0 AND  `id` = :where_1 ",
      "bindings": [
        {
          "name": ":where_0",
          "value": 0,
          "type": null
        },
        {
          "name": ":where_1",
          "value": "37",
          "type": null
        }
      ]
    }
  ]
}

allan · August 2020

It wouldn't . Your condition on the where statement is:

->where( 'completed', 0, '=' )

And in your postEdit:

if ($row['completed'] == '1') {

It can't ever be 1 if your where condition is restricting it to 0.

Allan

Send email on row edit with editor

Send email on row edit with editor

Answers

Howdy, Stranger!

Categories

DataTables

Send email on row edit with editor

Send email on row edit with editor

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables