Performing calculations server side

ztaylor1990

I am trying to perform a calculation after I make an inline edit to a row called percent_margin. I want the next column in the table margin_flat to perform a calculation and update that value in the db. Here is the code that I am using right now. I am getting an error when I run it that says Uncaught ReferenceError: row is not defined. Any help would be appreciated.

allan

That looks like it should work - this is the code for postEdit. What version of the PHP libraries are you using please?

Also, I'm not clear on why you would attempt to set a value in postEdit? The value has already been written by that point.

Allan

ztaylor1990

I am using version 7.2. I am not getting any errors with that code it is just not performing the calculation the way I am needing it to. So when the user makes an inline edit to a row and then data is passed to the database I am then wanting to use that updated value to update the value of the next column. Similar to a formula in an excel spreadsheet.

allan

That's for the clarification - postEdit on the server-side isn't the correct place to do this.

There are two options:

1. If you are storing Margin flat in the database, then do the calculation in preEdit.
2. If you are not storing Margin flat in the database, then do the calculation client-side with a renderer.

Allan

ztaylor1990

Hey Allan,

I am really not sure why this isn't working. After I submit an inline edit to the table I get the following error.

Here is what I am doing server-side.

When I change the set value to a number like 1. it updates the table and the database but it is not working with calculations. So I am assuming I have some syntax wrong.

Thanks,

ztaylor1990

This is what the xhr in the network console says... Undefined index: avg_rpm

allan

Ah! By default inline() will only submit the values that have changed. So you would only have access to the value of the field that is being edited. Your preEdit however needs access to multiple values, so you need to tell Editor to submit the form's full data:

editor.inline( this, {
  submit: 'allIfChanged'
} );

Allan

ztaylor1990

I already have that in my index, I am still getting the same error. The logic from my server-side event is causing the error. (Undefined index: avg_rpm on line 62) When I remove that piece of code it removes the error and allows me to update the table. If I just put a static number it also works, it appears to just be the way that I am performing the calculations with $values. If I try $row it says Undefined index: row. I am honestly not sure why this isn't working. I have read a lot of documentation and forums and it seems like it should be working.

allan

Is avg_rpm in the field list for the client-side Editor? It sounds like it might not be (you could use hidden if you don't want the user to see it, but include it in the submitted data).

Failing that, could you show me your Editor client-side initialisation please?

Allan

ztaylor1990

This is how I have it set up. Yes, avg_rpm is in the field list. And it has to be shown to the end-user so it can not be hidden. I got this to work client-side with render but I could not get the data to send to the db with render.

ztaylor1990

Here is my client side code in my index.php

function rfpworking() {
  $(document).ready(function() {
    var val = $('#project_name').val();
    var editor = new $.fn.dataTable.Editor( {
      "processing": true,
      "serverSide": true,
      "scrollX": true,
      ajax: {
        url: '../dependencies/datatables/php/rfpworking_table_serverside.php',
        "type": "POST",
        data: function ( d ) {
          d.val = val;
        }
      },
      "table": "#rfpworking",
      fields: [ {
        label: "Origin City",
        name: "origin_city"
      }, {
        label: "Origin State",
        name: "origin_state"
      }, {
        label: "Destination City",
        name: "destination_city"
      }, {
        label: "Destination State",
        name: "destination_state"
      }, {
        label: "Truck Type",
        name: "truck_type"
      }, {
        label: "Companies",
        name: "spot_num_companies"
      }, {
        label: "Reports",
        name: "spot_num_reports"
      }, {
        label: "Our Avg LH",
        name: "our_avg_lh"
      }, {
        label: "Our Num Reports",
        name: "our_num_reports"
      }, {
        label: "Ann Volume",
        name: "ann_volume"
      }, {
        label: "Cust Miles",
        name: "cust_miles"
      }, {
        label: "Market Avg LH Dat",
        name: "market_avg_lh_dat"
      }, {
        label: "DAT Miles",
        name: "dat_miles"
      },{
        label: "Avg RPM",
        name: "avg_rpm"
      }, {
        label: "Carrier Fuel",
        name: "carrier_fuel"
      }, {
        label: "Cust Fuel",
        name: "cust_fuel"
      }, {
        label: "Margin %",
        name: "percent_margin"
      }, {
        label: "Margin Flat:",
        name: "margin_flat"
      }, {
        label: "Cust Flat",
        name: "cust_flat"
      }, {
        label: "Cust All In",
        name: "cust_all_in"
      }, {
        label: "Carrier Flat",
        name: "carrier_flat"
      }, {
        label: "Carrier All In",
        name: "carrier_all_in"
      }, {
        label: "All In GP",
        name: "all_in_gp"
      },{
        label: "GP %",
        name: "gp_percent"
      },{
        label: "Spot Low",
        name: "spot_low"
      }, {
        label: "Spot High",
        name: "spot_high"
      }, {
        label: "STD",
        name: "std"
      }, {
        label: "Time",
        name: "time"
      }, {
        label: "Hazmat (Yes)",
        name: "hazmat"
      },{
        label: "Lane ID",
        name: "lane_id"
      },{
        label: "extra1",
        name: "extra1"
      },{
        label: "extra2",
        name: "extra2"
      },{
        label: "extra3",
        name: "extra3"
      },{
        label: "extra4",
        name: "extra4"
      }
      
      ]
    } );
    
    $('#rfpworking').on( 'click', 'tbody td', function (e) {
      editor.inline(this), {
        submit: 'allIfChanged'
      } ;
    } );
    
    

    var rfpworking = $('#rfpworking').DataTable({
      dom: "Bfrtip",
      destroy: true,
      ajax: {
        url: '../dependencies/datatables/php/rfpworking_table_serverside.php',
        "type": "POST",
        data: function ( d ) {
          d.val = val;
        }
      },
      serverSide: true,
      columns: [
        { data: "origin_city"},
        { data: "origin_state" },
        { data: "destination_city" },
        { data: "destination_state" },
        { data: "truck_type" },
        { data: "spot_num_companies" },
        { data: "spot_num_reports" },
        { data: "our_avg_lh" },
        { data: "our_num_reports" },
        { data: "ann_volume" },
        { data: "cust_miles" },
        { data: "market_avg_lh_dat" },
        { data: "dat_miles" },
        { data: "avg_rpm" },
        { data: "carrier_fuel" },
        { data: "cust_fuel" },
        { data: "percent_margin" },
        { data: "margin_flat" },
        { data: "cust_flat" },
        { data: "cust_all_in" },
        { data: "carrier_flat" },
        { data: "carrier_all_in" },
        { data: "all_in_gp" },
        { data: "gp_percent" },
        { data: "spot_low" },
        { data: "spot_high" },
        { data: "std" },
        { data: "time" },
        { data: "hazmat" }, 
        { data: "lane_id" }, 
        { data: "extra1" }, 
        { data: "extra2" }, 
        { data: "extra3" },
        { data: "extra4" }
      ],
      select: {
        style:    'os',
        selector: 'td:first-child'
      },
      
    });
  });
};

Here is my code from my server side php file.
```
<?php
include( "../../../Editor-1.9.2/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 = Editor::inst( $db, $var_value )
->fields(
Field::inst( 'id' ),
Field::inst( 'origin_city' ),
Field::inst( 'origin_state' ),
Field::inst( 'destination_city' ),
Field::inst( 'destination_state' ),
Field::inst( 'truck_type' ),
Field::inst( 'spot_num_companies' ),
Field::inst( 'spot_num_reports' ),
Field::inst( 'our_avg_lh' ),
Field::inst( 'our_num_reports' ),
Field::inst( 'ann_volume' ),
Field::inst( 'cust_miles' ),
Field::inst( 'market_avg_lh_dat' ),
Field::inst( 'dat_miles' ),
Field::inst( 'avg_rpm' ),
Field::inst( 'carrier_fuel' ),
Field::inst( 'cust_fuel' ),
Field::inst( 'percent_margin' ),
Field::inst( 'margin_flat' ),
Field::inst( 'cust_flat' ),
Field::inst( 'cust_all_in' ),
Field::inst( 'carrier_flat' ),
Field::inst( 'carrier_all_in' ),
Field::inst( 'all_in_gp' ),
Field::inst( 'gp_percent' ),
Field::inst( 'spot_low' ),
Field::inst( 'spot_high' ),
Field::inst( 'std' ),
Field::inst( 'time' ),
Field::inst( 'hazmat' ),
Field::inst( 'lane_id' ),
Field::inst( 'extra1' ),
Field::inst( 'extra2' ),
Field::inst( 'extra3' ),
Field::inst( 'extra4' )
)

->on( 'preEdit', function ( $editor, $id, $values) {
    $editor
        ->field( 'margin_flat' )
        ->setValue($values['avg_rpm']/(1-$values['percent_margin']));
} )

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

<?php > ``` ?>

allan

I honestly don't know why that isn't working! Could you add:

print_r( $values );

into your preEdit event handler (in between lines 55 and 56 in the above PHP)?

Then trigger an edit action. It will cause an error to be shown on the client-side, but just ignore that - what is the response from the server (shown in the browser's network inspector)?

Thanks,
Allan

ztaylor1990

Here is the response in the network inspector. (I made an inline edit to avg_rpm field). There was the same error shown in the datatable row but upon refresh the data was updated and sent to the db. So theUndefined index: error is happening from the field that the edit event takes place at. But the calculation is still not taking place. When I made the change to avg_rpm it just updated the margin_flat with the avg_rpm value.
The calculation should be 1.72/(1-.13)=1.98. So I think it doesn't like the division sign possibly? Because If I make a change to the percent_margin field it changes the margin flat value to 0.
Array
(
[avg_rpm] => 1.72
)
<br />
<b>Notice</b>: Undefined index: percent_margin in <b>/var/www/vhosts/giltnersolutions.com/httpdocs/dependencies/datatables/php/rfpworking_table_serverside.php</b> on line <b>69</b><br />
{"data":[{"DT_RowId":"row_113279","id":"113279","origin_city":"ACWORTH","origin_state":"GA","destination_city":"LANCASTER","destination_state":"TX","truck_type":"R","spot_num_companies":"32","spot_num_reports":"223","our_avg_lh":"","our_num_reports":"","ann_volume":"28","cust_miles":"781","market_avg_lh_dat":"1314.29","dat_miles":"","avg_rpm":"1.72","carrier_fuel":"0.33","cust_fuel":"0.26","percent_margin":"0.13","margin_flat":"1.72","cust_flat":"1554.19","cust_all_in":"2183.06","carrier_flat":"1335.51","carrier_all_in":"1593.24","all_in_gp":"589.82","gp_percent":"0.27018038899526","spot_low":"","spot_high":"","std":"","time":"","hazmat":"","lane_id":"","extra1":"","extra2":"","extra3":"","extra4":""}]}

ztaylor1990

Here is another test that I just ran and the errors that occurred.

I added another calculation called cust_flat.. I made a change to the percent_margin field and this is the error I get.

I am still confused as to why I am getting the A system error has occurred (More information) on the client side under the field I make and edit to.

113279<br />
<b>Warning</b>: Illegal string offset 'avg_rpm' in <b>/var/www/vhosts/giltnersolutions.com/httpdocs/dependencies/datatables/php/rfpworking_table_serverside.php</b> on line <b>69</b><br />
<br />
<b>Warning</b>: Illegal string offset 'percent_margin' in <b>/var/www/vhosts/giltnersolutions.com/httpdocs/dependencies/datatables/php/rfpworking_table_serverside.php</b> on line <b>69</b><br />
<br />
<b>Warning</b>: Division by zero in <b>/var/www/vhosts/giltnersolutions.com/httpdocs/dependencies/datatables/php/rfpworking_table_serverside.php</b> on line <b>69</b><br />
113279<br />
<b>Warning</b>: Illegal string offset 'cust_miles' in <b>/var/www/vhosts/giltnersolutions.com/httpdocs/dependencies/datatables/php/rfpworking_table_serverside.php</b> on line <b>75</b><br />
<br />
<b>Warning</b>: Illegal string offset 'margin_flat' in <b>/var/www/vhosts/giltnersolutions.com/httpdocs/dependencies/datatables/php/rfpworking_table_serverside.php</b> on line <b>75</b><br />
{"data":[{"DT_RowId":"row_113279","id":"113279","origin_city":"ACWORTH","origin_state":"GA","destination_city":"LANCASTER","destination_state":"TX","truck_type":"R","spot_num_companies":"32","spot_num_reports":"223","our_avg_lh":"","our_num_reports":"","ann_volume":"28","cust_miles":"781","market_avg_lh_dat":"1314.29","dat_miles":"","avg_rpm":"1.76","carrier_fuel":"0.33","cust_fuel":"0.26","percent_margin":"0.14","margin_flat":"INF","cust_flat":"1","cust_all_in":"2183.06","carrier_flat":"1335.51","carrier_all_in":"1593.24","all_in_gp":"589.82","gp_percent":"0.27018038899526","spot_low":"","spot_high":"","std":"","time":"","hazmat":"","lane_id":"","extra1":"","extra2":"","extra3":"","extra4":""}]}
```
$editor = Editor::inst( $db, $var_value )
->fields(
Field::inst( 'id' ),
Field::inst( 'origin_city' ),
Field::inst( 'origin_state' ),
Field::inst( 'destination_city' ),
Field::inst( 'destination_state' ),
Field::inst( 'truck_type' ),
Field::inst( 'spot_num_companies' ),
Field::inst( 'spot_num_reports' ),
Field::inst( 'our_avg_lh' ),
Field::inst( 'our_num_reports' ),
Field::inst( 'ann_volume' ),
Field::inst( 'cust_miles' ),
Field::inst( 'market_avg_lh_dat' ),
Field::inst( 'dat_miles' ),
Field::inst( 'avg_rpm' ),
Field::inst( 'carrier_fuel' ),
Field::inst( 'cust_fuel' ),
Field::inst( 'percent_margin' ),
Field::inst( 'margin_flat' ),
Field::inst( 'cust_flat' ),
Field::inst( 'cust_all_in' ),
Field::inst( 'carrier_flat' ),
Field::inst( 'carrier_all_in' ),
Field::inst( 'all_in_gp' ),
Field::inst( 'gp_percent' ),
Field::inst( 'spot_low' ),
Field::inst( 'spot_high' ),
Field::inst( 'std' ),
Field::inst( 'time' ),
Field::inst( 'hazmat' ),
Field::inst( 'lane_id' ),
Field::inst( 'extra1' ),
Field::inst( 'extra2' ),
Field::inst( 'extra3' ),
Field::inst( 'extra4' )
)

->on( 'preEdit', function ( $editor, $values) {
    print_r( $values );
    $editor
        ->field( 'margin_flat' )
        ->setValue($values['avg_rpm']/(1-$values['percent_margin']));
} )
    ->on( 'preEdit', function ( $editor, $values) {
    print_r( $values );
    $editor
        ->field( 'cust_flat' )
        ->setValue($values['cust_miles']*$values['margin_flat']);
} )
->process( $_POST )
->json();

<?php > ``` ?>

ztaylor1990

Disregard the last comment it was because I removed the $id from the preEdit...
This is the error I am getting with the new calculation added. I am still confused as to why I am getting the A system error has occurred (More information) on the client side under the field I make and edit to.

allan

The "system error" message is what I was referring to above when I said:

It will cause an error to be shown on the client-side

It's just because the return from the server is not valid JSON.

Back to the problem:

Array
(
[avg_rpm] => 1.72
)

Means that only the value that was edited is being submitted. Which took me back to your inline() call:

      editor.inline(this), {
        submit: 'allIfChanged'
      } ;

Its difficult to see like that, but if you rewrite that code we can see the object isn't actually doing anything due to a misplaced paraenthisis:

      editor.inline(this);
      { submit: 'allIfChanged' };

So it should be changed to be:

      editor.inline(this, {
        submit: 'allIfChanged'
      });

Then it should work .

Allan

ztaylor1990

Nice, it worked!! Thank you so much. All of that because of a misplaced parenthesis!