How do you control the database update using an EDIT function?

webpointz

I have the editor setup for a products table. I want to have a field that when a person edits a product, they can add stock. Is there a way to have a field called "Add Stock" and then control the table update so that it adds the new number to the "currentstock" table column?

webpointz

Just to add clarification.

I have a table displayed and when the user selects a row and clicks on the EDIT button, when the popup comes up it has all form fields for the products table.

One of the fields is "Current Stock" which shows the numeric value of current stock on hand which is readonly for display.

I want to have a form field called "Add Stock".

When the user adds a value in "Add Stock", when the update button is clicked I need to take the form field value submitted and ADD it to the currentstock value on update.

Example:

Product ID: XYZ
Current Stock: 150
Add Stock: 50

On update, the currentstock field for the product id will now be 200.

webpointz

Update:

I decided to add a custom button called "Add Stock". It opens an edit window that shows the current stock as "readonly" and another form field called "Add Stock".

My question is, when I add stock, on save, I want it to ADD the new amount to the readonly field to the database AND make an entry in another table called productstock which makes an entry for the new amount for transaction logging.

Any suggestions on how to do this?

allan

One option is to use the preSubmit option. Using that you can modify the data sent to the server, so you could, for example take the current stock value and just add the "add" value:

editor.on( 'preSubmit', function ( e, data, action ) {
  if ( action === 'edit' ) {
    data.data.stock += data.data.addStock;
  }
} );

The other option would be to do this on the server-side using the setValue option for the stock field. Again you would get the current value (possibly reading it form the db if you wanted to ensure there was no possibility of loosing information) add the 'add' value and then return the combination.

Regards,
Allan

webpointz

Thanks Allan

Two questions. Where does the "editor.on" code you put go?

Also, how would I handle "inserting" a row into the productstock transaction log table with the add stock value?

mkleinosky

Here is an example of an editor page using a "on" function which shows where code goes ...
http://editor.datatables.net/examples/api/confirmClose.html

webpointz

Thanks...I got it.

My only problem right now is when I add the stock with:

data.data.stock += data.data.addStock;

If the total was 500 and I add 50, it's updating the current stock to 50050 instead of 550.
I've been trying to wrap each value with parseInt() to no avail.

webpointz

First question figured out:

stockEditor.on( 'preSubmit', function ( e, data, action ) {
    
    var currStockval = parseInt(data.data.table_products.currentstock);
    var addStockval  = parseInt(data.data.addstock);
    var newStockval  = addStockval += currStockval;

      if ( action === 'edit' ) {
        data.data.table_products.currentstock = newStockval;
      }
    } );  

Now I just need the ability to perform an insert on another table with the productid to record the transaction in the productstock table.

allan

Now I just need the ability to perform an insert on another table with the productid to record the transaction in the productstock table

Two options:

1. If suitable, then try a leftJoin
2. If not, then you'll need to effectively 'post process' the request at the server-side outside of the Editor libraries and perform the db insert.

Allan

webpointz

So I would try a left join during the presubmit?

Just learning all this so sorry for the questions.

What would the left join look like?

webpointz

So, I have the code below that performs the adding of stock and it works perfectly.

I now need to take the following variables and pass them as an insert to another table:

In the editor, I have productid, description and addstock (hidden) that I can pass to the presubmit, but I also need to pass the current PHP $_SESSION['userid'], $_SESSION['customerid'] and the current date to the productstock table.

Sorry, I'm stumped as to how to do this insert.

    stockEditor.on( 'preSubmit', function ( e, data, action ) {
    
    var currStockval = parseInt(data.data.table_products.currentstock);
    var addStockval  = parseInt(data.data.addstock);
    var newStockval  = addStockval += currStockval;

      if ( action === 'edit' ) {
        data.data.table_products.currentstock = newStockval;
        
      }
    } );  

allan

So I would try a left join during the presubmit?

No - the Editor at the server-side would be set up for the leftJoin, as per the documentation. You may already have it doing a left join from the data structure?

What does your server side code look like? Are you using the PHP or .NET Editor libraries?

Allan

webpointz

I'm using PHP libraries. I'm doing a left join on two tables to populate two select boxes in the editor.

Editor::inst( $db, 'table_products', 'id' )
    ->fields(
        Field::inst( 'table_products.productid' )
            ->validator( 'Validate::notEmpty' )
            ->validator( 'Validate::unique' ),
        Field::inst( 'table_products.description' ),
        Field::inst( 'table_products.customerid' )
                    ->options( 'table_customers', 'id', 'customername' ),
        Field::inst( 'table_customers.customername' ),
        Field::inst( 'table_products.grouping' ),
        Field::inst( 'table_products.currentstock' ),
        Field::inst( 'table_products.equipment' ),
        Field::inst( 'table_products.miscellaneous' ),
        Field::inst( 'table_products.notes' ),
        Field::inst( 'table_products.unitcost' ),
        Field::inst( 'table_products.unitmeasurement' ),
        Field::inst( 'table_products.active' )  
            ->options( 'table_active', 'id', 'active' )     
    )
    ->leftJoin( 'table_customers', 'table_customers.id', '=', 'table_products.customerid' )
    ->leftJoin( 'table_active', 'table_active.id', '=', 'table_products.active' )   
    
    ->process( $_POST )
    ->json();

allan

Okay thanks - so the join table should already be updated. Reviewing your earlier comments I missed this before:

record the transaction

So you want a new row every time addstock is provided? If so, my apologies for misunderstanding. This is not something that Editor itself will do since that is a seperate transaction, however, it is something you can easily do using a splash of PHP:

if ( isset( $_POST['action'] ) && $_POST['action'] === 'edit' ) {
  $db->insert( 'myTable', [ 'myField' => $_POST['data']['addstock'] ] );
}

Documentation for the Database->insert() method is available here.

Allan

webpointz

Thanks Allan,

Yes. Only when addStock is done, I want an insert into the productstock table that for reporting purposes records each time a user adds stock to the products table.

Where would I put your code:

if ( isset( $_POST['action'] ) && $_POST['action'] === 'edit' ) {
  $db->insert( 'myTable', [ 'myField' => $_POST['data']['addstock'] ] );
}

webpointz

Got it. Added it in my PHP just after the include DataTables.php

One last thing, here is my code in the PHP page:

Editor::inst( $db, 'table_products', 'id' )
    ->fields(
        Field::inst( 'table_products.productid' )
            ->validator( 'Validate::notEmpty' )
            ->validator( 'Validate::unique' ),
        Field::inst( 'table_products.description' ),
        Field::inst( 'table_products.customerid' )
                    ->options( 'table_customers', 'id', 'customername' ),
        Field::inst( 'table_customers.customername' ),
        Field::inst( 'table_products.grouping' ),
        Field::inst( 'table_products.currentstock' ),
        Field::inst( 'table_products.equipment' ),
        Field::inst( 'table_products.miscellaneous' ),
        Field::inst( 'table_products.notes' ),
        Field::inst( 'table_products.unitcost' ),
        Field::inst( 'table_products.unitmeasurement' ),
        Field::inst( 'table_products.active' )  
            ->options( 'table_active', 'id', 'active' )     
    )
    ->leftJoin( 'table_customers', 'table_customers.id', '=', 'table_products.customerid' )
    ->leftJoin( 'table_active', 'table_active.id', '=', 'table_products.active' )   

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

I have a column that the leftJoin uses to match the customerid so that it displays the customername in a select list.

However, I also need the customerid to be passed as a variable during the addstock and I can't seem to get it to do that.

webpointz

I now have it doing an insert with everything EXCEPT the customerid from the row

webpointz

If I dump out the array coming out of the ADD STOCK it looks like this:

{"action":"edit","data":{"addstock":"5","table_products":{"currentstock":"120","productid":"CIB-CAM 02542E","description":"CAM Today's Income Decision EN (2012/11)"}},"id":"row_3064"}

webpointz

I will post all of my code:

PHP:

<?php

session_start();

/*
 * Editor server script for DB table table_products
 * Created by http://editor.datatables.net/generator
 */

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

if ( isset( $_POST['action'] ) && $_POST['action'] === 'edit' ) {
  $db->insert( 'table_productstock', [ 'stockcount' => $_POST['data']['addstock'] , 'productid' => $_POST['data']['table_products']['productid'], 'productname' => $_POST['data']['table_products']['description'], 'insertdate' => date('Y-m-d H:i:s'), 'userid' => $_SESSION['userid'], 'customerid' => $_POST['data']['table_products']['customerid'] ] );
}   


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


// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'table_products', 'id' )
    ->fields(
        Field::inst( 'table_products.productid' )
            ->validator( 'Validate::notEmpty' )
            ->validator( 'Validate::unique' ),
        Field::inst( 'table_products.description' ),
        Field::inst( 'table_products.customerid' )
                    ->options( 'table_customers', 'id', 'customername' ),
        Field::inst( 'table_customers.customername' ),
        Field::inst( 'table_products.grouping' ),
        Field::inst( 'table_products.currentstock' ),
        Field::inst( 'table_products.equipment' ),
        Field::inst( 'table_products.miscellaneous' ),
        Field::inst( 'table_products.notes' ),
        Field::inst( 'table_products.unitcost' ),
        Field::inst( 'table_products.unitmeasurement' ),
        Field::inst( 'table_products.active' )  
            ->options( 'table_active', 'id', 'active' )     
    )
    ->leftJoin( 'table_customers', 'table_customers.id', '=', 'table_products.customerid' )
    ->leftJoin( 'table_active', 'table_active.id', '=', 'table_products.active' )   
    
    ->process( $_POST )
    ->json();

JAVASCRIPT:

var editor; // use a global for the submit and return data rendering in the examples

(function($){

$(document).ready(function() {
    var editor = new $.fn.dataTable.Editor( {

        "ajax": "php/table.table_products.php",
        "table": "#table_products",
        
        "i18n": {
            "edit": {
            "button": "Edit",
            "title":  "Update Product",
            "submit": "Update"
            },
            "create": {
            "button": "Add",
            "title":  "Add New Product",
            "submit": "Add Product"
            }},     
        
        "fields": [
            {
                "label": "Product ID:",
                "name": "table_products.productid"
            },
            {
                "label": "Description:",
                "name": "table_products.description",
                "type": "textarea"
            },
            {
                "label": "Customer:",
                "name": "table_products.customerid",
                "type": "select"
            },
            {
                "label": "Grouping:",
                "name": "table_products.grouping"
            },
            {
                "label": "Stock:",
                "name": "table_products.currentstock",
                "type": "readonly"
            },
            {
                "label": "Equipment:",
                "name": "table_products.equipment"
            },
            {
                "label": "Miscellaneous:",
                "name": "table_products.miscellaneous"
            },
            {
                "label": "Notes:",
                "name": "table_products.notes",
                "type": "textarea"
            },
            {
                "label": "Unit Cost:",
                "name": "table_products.unitcost"
            },
            {
                "label": "Unit Measurement:",
                "name": "table_products.unitmeasurement"
            },
            {
                "label": "Active:",
                "name": "table_products.active",
                "type": "select",
                "def" : "1"
            }               
        ]
    } );

        var stockEditor = new $.fn.dataTable.Editor( {
        
            ajax: "php/table.table_products.php",
            table: "#table_products",
            fields: [ 
            {label: "Add Stock:",
                    name:  "addstock"
                },        
                {label: "Current Stock:",
                    name:  "table_products.currentstock",
                type: "hidden"
                },
                {label: "Product ID:",
                    name:  "table_products.productid",
                type: "hidden"
                },
                {label: "Description:",
                    name:  "table_products.description",
                type: "hidden"
                }                           
                ]
         
        });

    stockEditor.on( 'preSubmit', function ( e, data, action ) {
    
    var currStockval = parseInt(data.data.table_products.currentstock);
    var addStockval  = parseInt(data.data.addstock);
    var newStockval  = addStockval += currStockval;

      if ( action === 'edit' ) {
        data.data.table_products.currentstock = newStockval;
      }
    } );  
       

    var table = $('#table_products').DataTable( {
        "dom": "Tfrtip",
        "ajax": "php/table.table_products.php",
        
        "columnDefs": [ {
            "targets": 0,
            "data": null
            } ],            
        
        "columns": [
            {
                "data": "table_products.productid"
            },
            {
                "data": "table_products.description"
            },
            { 
                "data": "table_customers.customername" 
            },  
            {
                "data": "table_products.currentstock"
            }
        ],
        tableTools: {
            sRowSelect: "os",
            aButtons: [
                { sExtends: "editor_create", editor: editor },
                { sExtends: "editor_edit",   editor: editor },
                {
                    sExtends: 'select_single',
                    sButtonClass: 'marginLeft',
                    sButtonText: 'Add Stock',
                    fnClick: function () {
                        if ( table.row( '.selected' ).length !== 0 ) {
                            // Show the stock update form
                            stockEditor
                                .title( 'Add Stock' )
                                .buttons( { label: 'Add Stock', fn: function() { this.submit(); } } )
                                .edit( table.row( '.selected' ).node() );
                        }
                    }
                }              
            ]
    }
   } );
} );

}(jQuery));

HTML

<table cellpadding="0" cellspacing="0" border="0" class="display responsive" id="table_products" width="100%">
                <thead>
                    <tr>
                        <th>Product ID</th>
                        <th>Description</th>
                        <th>Customer</th>
                        <th>Stock</th>
                    </tr>
                </thead>
            </table>

webpointz

I found the issue

In the JS file, it has the following field which with the left join shows the select list with the products customername based on customerid value:

Field::inst( 'table_products.customerid' )
                    ->options( 'table_customers', 'id', 'customername' ),

Only, table_products.customerid is now unavaliable. Every other Field::inst() can be passed except the one above as customerid.

Is there a way around this?

webpointz

For now, inside the

if ( isset( $_POST['action'] ) && $_POST['action'] === 'edit' ) {
  $db->insert( 'table_productstock', [ 'stockcount' => $_POST['data']['addstock'] , 'productid' => $_POST['data']['table_products']['productid'], 'productname' => $_POST['data']['table_products']['description'], 'insertdate' => date('Y-m-d H:i:s'), 'userid' => $_SESSION['userid'], 'customerid' => $thisCustomerid ] );
}   

I'm just running a query against the products table to retrieve the customerid in a SELECT query passing the $_POST['data']['table_products']['productid'] and it works fine.

allan

Good to hear that works now :-)

Allan