datatables and editor.. Serverside manupulation

iahammed

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

allan

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

iahammed

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..

allan

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

iahammed

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

allan

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

iahammed

Dear Sir,

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

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

Regards Ivan

allan

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

iahammed

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

iahammed

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

allan

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

iahammed

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

allan

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

iahammed

Dear Sir,
I am waiting for your time, Just let me know the date I'll contract you.
Best Regards
Ivan..

iahammed

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

allan

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

iahammed

Thanks for your help.
Regards
Ivan