Get remote data sorted with grand total in jQuery using Java

Question

I am using jQuery for UI and my programming language is Java. Now I want to get remote data using an Ajax call to Java servlet and get the records from remote site in sorted order with also grand total. How can I do that?

I found many examples, but that was in PHP only. I want to implement it in Java, and I am not able to understand PHP.

Example which I found is as below.

JavaScript code

jQuery("#48remote2").jqGrid({
    url:'server.php?q=2',
    datatype: "json",
    colNames:['Inv No','Date', 'Client', 'Amount','Tax','Total','Notes'],
    colModel:[
        {name:'id',index:'id', width:55, editable:true, sorttype:'int',summaryType:'count', summaryTpl : '({0}) total'},
        {name:'invdate',index:'invdate', width:90, sorttype:'date', formatter:'date', datefmt:'d/m/Y'},
        {name:'name',index:'name', width:100},
        {name:'amount',index:'amount', width:80, align:"right", sorttype:'number',formatter:'number',summaryType:'sum'},
        {name:'tax',index:'tax', width:80, align:"right",sorttype:'number',formatter:'number',summaryType:'sum'},
        {name:'total',index:'total', width:80,align:"right",sorttype:'number',formatter:'number', summaryType:'sum'},
        {name:'note',index:'note', width:150, sortable:false,editable:true}
    ],
    rowNum:10,
    rowList:[10,20,30],
    height: 'auto',
    pager: '#p48remote2',
    sortname: 'invdate',
    viewrecords: true,
    sortorder: "desc",
    caption:"Grouping with remote data",
    grouping: true,
       groupingView : {
           groupField : ['name'],
           groupColumnShow : [true],
           groupText : ['<b>{0}</b>'],
           groupCollapse : false,
        groupOrder: ['asc'],
        groupSummary : [true],
        groupDataSorted : true
       },
    footerrow: true,
    userDataOnFooter: true
});
jQuery("#48remote2").jqGrid('navGrid','#p48remote',{add:false,edit:false,del:false});

And PHP code is as below,

PHP MySQL code

examp = $_REQUEST["q"]; //Query number

$page = $_REQUEST['page'];  // Get the requested page
$limit = $_REQUEST['rows']; // Get how many rows we want to have into the grid
$sidx = $_REQUEST['sidx'];  // Get index row - i.e. user click to sort
$sord = $_REQUEST['sord'];  // Get the direction
if(!$sidx) $sidx =1;
...

$result = mysql_query("SELECT COUNT(*) AS count FROM invheader a, clients b WHERE a.client_id=b.client_id".$wh);
$row = mysql_fetch_array($result,MYSQL_ASSOC);
$count = $row['count'];
if( $count >0 ) {
    $total_pages = ceil($count/$limit);
}
else {
    $total_pages = 0;
}
if ($page > $total_pages)
    $page=$total_pages;
$start = $limit*$page - $limit; // Do not put $limit*($page - 1)
if ($start<0)
    $start = 0;
$SQL = "SELECT a.id, a.invdate, b.name, a.amount,a.tax,a.total,a.note FROM invheader a, clients b WHERE a.client_id=b.client_id".$wh." ORDER BY ".$sidx." ".$sord. " LIMIT ".$start." , ".$limit;
$result = mysql_query( $SQL ) or die("Could not execute query.".mysql_error());
$responce->page = $page;
$responce->total = $total_pages;
$responce->records = $count;
$i=0; $amttot=0; $taxtot=0; $total=0;
while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
    $amttot += $row[amount];
    $taxtot += $row[tax];
    $total += $row[total];
    $responce->rows[$i]['id']=$row[id];
    $responce->rows[$i]['cell']=array($row[id],$row[invdate],$row[name],$row[amount],$row[tax],$row[total],$row[note]);
    $i++;
}
$responce->userdata['amount'] = $amttot;
$responce->userdata['tax'] = $taxtot;
$responce->userdata['total'] = $total;
$responce->userdata['name'] = 'Totals:';
echo json_encode($responce);

But I am not able to understand the code in PHP. How can I do that?

score 0 · Accepted Answer · edited May 23 '17 at 12:03

0

If I understand you correct you need include summary line in the bottom of the grid. To do this you don't need the grouping part from the posted example. The only parameters which are important in your case are:

footerrow: true,
userDataOnFooter: true

If you use both from the jqGrid options you need just calculate the sum for the columns where you need it has and include "userdata" part in the JSON which produce the servlet. See here, here and here for more information.

edited May 23 '17 at 12:03

Community

1
1

answered Feb 06 '12 at 10:56

Oleg

220,925
34
403
798

I want to implement paging in jquery and Java, e.g. when user press next/prev then at that time call should be made to server and data should be collected from server using ajax and also total of the data . – Bhavik Ambani Feb 06 '12 at 11:00
@BhavikAmbani: It's not a problem. If you use `datatype: "json"` and don't use `loadonce: true` all requests (inclusive paging) will be processed by your servlet. Nevertheless you can include in the server response the "userdata" part and jqGrid will just place the information from "userdata" in the summary line. So you will have full control of the grid body (inclusive the summary row) on the server. – Oleg Feb 06 '12 at 11:32
But I want the coding of server side in Java language, because what problem is wherever I go I found the PHP codes. – Bhavik Ambani Feb 06 '12 at 12:35
@BhavikAmbani: Sorry, but I don't full understand your problem. The servlet will be called with the parameters `page` (the requested page), `rows` (the number of rows per page), `sidx` (the name of the column by which the data should be sorted) and the `sord` ("asc" or "desc" - the sort direction). So you should just implement in Java the access to the Database where you hold the data and return back the requested page of data in the JSON form (like in the example from [here](http://stackoverflow.com/a/3128934/315935)). I personally use neither PHP nor Java. – Oleg Feb 06 '12 at 12:44
Can You please provide the complete example of that which I can download and understand. – Bhavik Ambani Feb 06 '12 at 13:01
@BhavikAmbani: I wrote you before that I don't use Java or PHP. I can't provide you any Java code. I use mostly C#, but even C# code can be very different from the *technology* which one uses. For example the codes of ASMX web service, WCF service, ASP.NET MVC and old ASP.NET ASHX handler are absolute different. In all the above C# variants one can use additionally different database access like LINQ to SQL, Entity Framework, `SQLCommand` and so on. I suppose there are the same differences if you write the implementation in Java code. – Oleg Feb 06 '12 at 13:11

Get remote data sorted with grand total in jQuery using Java

JavaScript code

PHP MySQL code

1 Answers1