datatables and editor.. Serverside manupulation

datatables and editor.. Serverside manupulation

iahammediahammed Posts: 10Questions: 0Answers: 0
edited March 2013 in Editor
DataTables is very nice I love this, I am new in programming.. I'll be grateful if any body can help with example..

I need to filter data on the basis of user session(I have user authentication module), how do I pass session data to generate

for example:
SELECT section : SELECT * FROM sales WHERE user_id = $session_id;

in the same way
INSERT INTO sales (x,y,z, user_id) VALUES (a,b,c,$session_id);
UPDATE sales SET column1=value, column2=value2,... WHERE user_id=$session_id AND some_column=some_value;
DELETE FROM table_name WHERE some_column=some_value AND user_id=$session_id;


Best Regards
M. Iftakher Ahammed

Replies

  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin
    If you are using the Editor PHP classes then your use the `where` method: http://editor.datatables.net/docs/current/php/class-DataTables.Editor.html#_where . Just add the condition to your initialisation of the Editor instance (before you call `process()` ) and that will add the conditions required.

    Allan
  • iahammediahammed Posts: 10Questions: 0Answers: 0
    edited March 2013
    Thanks for your help.. It works perfect for select, update and delete,

    But how to insert new row

    Like : INSERT INTO sales (x,y,z, user_id) VALUES (a,b,c,$session_id);

    Thanks again for your help..
  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin
    Hi,

    You'd currently need to detect when the request made is for a `create` request and modify both the Editor PHP instance to add the extra field, and the data to add the extra data.

    In code this might look something like:

    [code]
    $data = $_POST;

    $editor = Editor::inst( $db, 'users' )
    ->field(
    Field::inst( 'first_name' ),
    Field::inst( 'last_name' )
    );

    // On `create`, add an extra field to the instance and modify the data to add
    // the required session id
    if ( isset($_POST['action']) && $_POST['action'] === 'create' ) {
    $data['data']['session_id'] = $session_id;

    $editor->field( Field::inst( 'session_id' ) );
    }

    $editor
    ->process( $data )
    ->json();
    [/code]

    Regards,
    Allan
  • iahammediahammed Posts: 10Questions: 0Answers: 0
    Dear Sir, thanks for your last comment, that works perfect,
    I am using join Tables my Data for DataTables from server: is as follows:
    {
    "id":-1,
    "error":"",
    "fieldErrors":[],
    "data":[],
    "aaData":[
    {
    "DT_RowId":"row_24",
    "sales_total":"726.00",
    "sales_net":"664.49",
    "sales_VAT":"61.51",
    "sales_card":"1000.00",
    "sales_cash":"-274.00",
    "sales_date":"Thu, 28 Feb 13",
    "sales_invoice":[
    {
    "id":"25",
    "s_item_name":"Takeaway",
    "s_g_price":"15.00"
    },
    {
    "id":"26",
    "s_item_name":"Collection",
    "s_g_price":"15.00"
    },
    {
    "id":"27",
    "s_item_name":"Delevery",
    "s_g_price":"15.00"
    },
    {
    "id":"28",
    "s_item_name":"Drink\/Wine",
    "s_g_price":"15.00"
    },
    {
    "id":"30",
    "s_item_name":"Restaurant",
    "s_g_price":"515.00"
    }]
    },
    {
    "DT_RowId":"row_25",
    "sales_total":"583.00",
    "sales_net":"533.30",
    "sales_VAT":"49.70",
    "sales_card":"60.00",
    "sales_cash":"523.00",
    "sales_date":"Tue, 19 Feb 13",
    "sales_invoice":[
    {
    "id":"31",
    "s_item_name":"Takeaway",
    "s_g_price":"250.00"
    },
    {
    "id":"32",
    "s_item_name":"Collection",
    "s_g_price":"20.00"
    },
    {
    "id":"33",
    "s_item_name":"Delevery",
    "s_g_price":"23.00"
    },
    {
    "id":"34",
    "s_item_name":"Drink\/Wine",
    "s_g_price":"15.00"
    },
    {
    "id":"36",
    "s_item_name":"Restaurant",
    "s_g_price":"250.00"
    }
    ]
    },
    {
    "DT_RowId":"row_26",
    "sales_total":"6000.00",
    "sales_net":"5400.16",
    "sales_VAT":"599.84",
    "sales_card":"5000.00",
    "sales_cash":"1000.00",
    "sales_date":"Fri, 01 Feb 13",
    "sales_invoice":[
    {
    "id":"37",
    "s_item_name":"Takeaway",
    "s_g_price":"1000.00"
    },
    {
    "id":"38",
    "s_item_name":"Collection",
    "s_g_price":"1000.00"
    },
    {
    "id":"39",
    "s_item_name":"Delevery",
    "s_g_price":"1000.00"
    },
    {
    "id":"40",
    "s_item_name":"Drink\/Wine",
    "s_g_price":"1000.00"
    },
    {
    "id":"42",
    "s_item_name":"Restaurant",
    "s_g_price":"1000.00"
    }
    ]
    }

    My php code is as follows :
    <?php
    require_once '../../bootstrap.php';
    secure_page();
    $c = get_data();
    $mem_id = $c->id;
    /*
    * Example PHP implementation used for the index.html example
    */
    // DataTables PHP library
    include( "lib/DataTables.php" );
    // 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

    $data = $_POST;
    $editor = Editor::inst( $db, 'sales' )
    ->field(
    Field::inst( 'sales_total' )->validator( 'Validate::required' ),
    Field::inst( 'sales_net' )->validator( 'Validate::required' ),
    Field::inst( 'sales_VAT' )->validator( 'Validate::required' ),
    Field::inst( 'sales_card' )->validator( 'Validate::required' ),
    Field::inst( 'sales_cash' )->validator( 'Validate::required' ),
    Field::inst( 'sales_date' )->validator( 'Validate::required' )
    ->validator( 'Validate::dateFormat', 'D, d M y' )
    ->getFormatter( 'Format::date_sql_to_format', 'D, d M y' )
    ->setFormatter( 'Format::date_format_to_sql', 'D, d M y' )
    )
    ->join(
    Join::inst( 'sales_invoice', 'array' )
    ->join( 'id','sale_id' )
    ->field(
    Field::inst( 'id' )->validator( 'Validate::required' ),
    Field::inst( 's_item_name' ),
    Field::inst( 's_g_price' )
    )
    );
    if ( isset($_POST['action']) && $_POST['action'] === 'create' ) {
    $data['data']['mem_id'] = $mem_id;
    $editor->fields( Field::inst( 'mem_id' ) );
    }

    $editor
    ->where( $key = 'mem_id', $value = $mem_id, $op = '=' )
    ->process( $data )
    ->json();



    Javascript:

    var editor; // use a global for the submit and return data rendering in the examples
    $(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
    "ajaxUrl": "../editor/php/sales_join.php",
    "domTable": "#example",
    "fields": [
    {
    "label": "Sales date:",
    "name": "sales_date",
    "type": "date",
    "dateFormat": 'D, d M y'
    },{
    "label": "Invoice Detail",
    "name":"sales_invoice[].s_g_price",
    "type": "select"
    },{
    "label": "Net Sale:",
    "name": "sales_net"
    },{
    "label": "VAT:",
    "name": "sales_VAT"
    },{
    "label": "Total:",
    "name": "sales_total"
    }

    ]
    } );

    $('#example').dataTable( {
    "bJQueryUI": true,
    "sPaginationType": "full_numbers",
    "sDom": '<"H"Tfr>t<"F"ip>',
    "sAjaxSource": "../editor/php/sales_join.php",
    //"bServerSide": true,
    //"bProcessing":true,
    "sServerMethod": 'POST',
    "aoColumns": [
    { "mData": "sales_date" },
    { "mData": "sales_net" , "sClass": "right"},
    { "mData": "sales_VAT" , "sClass": "right"},
    { "mData": "sales_total", "sClass": "right" },
    { "mData": "sales_invoice", "mRender": "[ ].s_item_name" },
    { "mData": "sales_invoice", "mRender": "[ ].s_g_price" },
    { "mData": "sales_cash" , "sClass": "right"},
    { "mData": "sales_card", "sClass": "right" }
    ],
    "oTableTools": {
    "sRowSelect": "multi",
    "aButtons": [
    { "sExtends": "editor_create", "editor": editor },
    { "sExtends": "editor_edit", "editor": editor },
    { "sExtends": "editor_remove", "editor": editor }
    ]
    }
    } );
    } );


    I need a editor like that will be grateful if you help me

    Date :

    Net Sale :

    VAT :

    Total :

    Take Away :

    Collection :

    Delevery :

    Drink/Wine :

    Restaurant :

    Card Sale :


    Regards Ivan
  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin
    Hi Ivan,

    I'm afraid I don't quite understand. What do you mean by needing an Editor like that? Is the code you pasted above not working? If not, are you able to link me to the page you are working on so I can debug it?

    Allan
  • iahammediahammed Posts: 10Questions: 0Answers: 0
    Dear Sir,

    Link is : http://www.chefville.ie/apps/

    I need help to edit Itemize sale amount, please help..

    Regards Ivan
  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin
    If it is a join, are the items / amounts limited? If so, then you might want to use a checkbox like in this example: http://editor.datatables.net/release/DataTables/extras/Editor/examples/join.html . It would also be possible for you to create a field type plugin which displays those options as a table or a multi-select element.

    Allan
  • iahammediahammed Posts: 10Questions: 0Answers: 0
    Dear Sir,
    My live link is : http://www.chefville.ie/apps/
    I use Join Table AJAX Data is like:
    {
    "id":-1,
    "error":"",
    "fieldErrors":[],
    "data":[],
    "aaData":[
    {
    "DT_RowId":"row_24",
    "sales_total":"726.00",
    "sales_net":"664.49",
    "sales_VAT":"61.51",
    "sales_card":"1000.00",
    "sales_cash":"-274.00",
    "sales_date":"Thu, 28 Feb 13",
    "sales_invoice":[
    {
    "id":"25",
    "s_item_name":"Takeaway",
    "s_g_price":"15.00"
    },
    {
    "id":"26",
    "s_item_name":"Collection",
    "s_g_price":"15.00"
    },
    {
    "id":"27",
    "s_item_name":"Delevery",
    "s_g_price":"15.00"
    },
    {
    "id":"28",
    "s_item_name":"Drink\/Wine",
    "s_g_price":"15.00"
    },
    {
    "id":"30",
    "s_item_name":"Restaurant",
    "s_g_price":"515.00"
    }]
    }

    Is that possible to have editor to change sales_invoice sections().

    s_item_name as Label and s_g_price as Value

    I am new, Can you please send me live example if possible.

    Thanks in advance for your help.

    Best regards
    Ivan
  • iahammediahammed Posts: 10Questions: 0Answers: 0
    Dear Sir,
    I am stuck in this position, Is it possible to make an editor to change the value of s_g_price of the above AJAX data mentioned earlier post, If possible let me know I will buy the support for your time spend.. I am looking forward for your kind reply..
    Best Regards
    Ivan
  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin
    I'm still not quite understanding what you are trying to do - are you looking to have an inner 'table' for the item / sale which itself allows items to be added, edited and removed?

    If so, I think you have two options:

    1. Probably the easier option - link to another page for each invoice which will show an itemised list for that invoice with its own add / edit and delete controls. This is the approach I tend to take myself.

    2. Create a plug-in field type which allows the name and sale price to be edited (create, edit and delete) inline with the current form. This is a more complete solution since it is integrated with the current UI, but as I say, it will be a bit more difficult. There is a tutorial on how to create new field types here: https://editor.datatables.net/tutorials/field_types

    Allan
  • iahammediahammed Posts: 10Questions: 0Answers: 0
    Dear Allan,
    Thanks for your time, I read the links you send. I am not expert in programming, Is that possible for you to create the plug-in field(text type) for me if i buy support from your site, Otherwise I have to think for other package.
    Best Regards
    Ivan
  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin
    I'm afraid I'm fully booked for contract work just at the moment and it will be next month before I can take on any new work.

    Allan
  • iahammediahammed Posts: 10Questions: 0Answers: 0
    Dear Sir,
    I am waiting for your time, Just let me know the date I'll contract you.
    Best Regards
    Ivan..
  • iahammediahammed Posts: 10Questions: 0Answers: 0
    Dear Sir,
    Sorry to disturb you that much.
    how do i link to another page for ( "DT_RowId":"row_24") I need to pass only 24 not "row_24"..

    Edit

    please help

    Thanks Ivan
  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin
    Use mRender - that can be used to format an `A` tag link for example with the id source. You'd also just use a simple `replace` to remove the 'row_' part.

    Allan
  • iahammediahammed Posts: 10Questions: 0Answers: 0
    Thanks for your help.
    Regards
    Ivan
This discussion has been closed.