​x
 
1
​
2
    <div id="grid_sorting_remote" style="margin: 5px auto;">
3
    </div>
4
​

76
 
1
​
2
    $(function () {
3
        var colM = [
4
            { title: "ShipCountry", width: 100, dataIndx: "ShipCountry" },
5
            { title: "Customer Name", width: 130, dataIndx: "ContactName" },
6
            { title: "Shipping Via", width: 100, dataIndx: "ShipVia" },
7
            { title: "Order ID", width: 100, dataIndx: "OrderID", dataType: "integer" },
8
            { title: "Order Date", width: "100", dataIndx: "OrderDate", dataType: "date" },
9
            { title: "Required Date", width: 100, dataIndx: "RequiredDate", dataType: "date" },
10
            { title: "Shipped Date", width: 100, dataIndx: "ShippedDate", dataType: "date" },       
11
            { title: "Freight", width: 100, align: "right", dataType: "float", dataIndx: "Freight" },
12
            { title: "Shipping Name", width: 160, dataIndx: "ShipName" },
13
            { title: "Shipping Address", width: 200, dataIndx: "ShipAddress" },
14
            { title: "Shipping City", width: 100, dataIndx: "ShipCity" },
15
            { title: "Shipping Region", width: 130, dataIndx: "ShipRegion" },
16
            { title: "Shipping Postal Code", width: 135, dataIndx: "ShipPostalCode" }
17
        ];
18
        var dataModel = {
19
            location: "remote",                        
20
            method: "GET",
21
            url: "/pro/orders/get" //for ASP.NET
22
            //url: "orders.php", //for PHP
23
        }
24
​
25
        var gObj = {            
26
            dataModel: dataModel,
27
            sortModel: {
28
                type: 'remote',
29
                single: false,
30
                sorter: [{ dataIndx: 'ShipCountry', dir: 'up' }, { dataIndx: 'ContactName', dir: 'down'}],
31
                space: true,
32
                multiKey: null
33
            },
34
            flex: {one: true},
35
            wrap: false,
36
            hwrap: false,            
37
            showTitle: false,
38
            colModel: colM,            
39
            resizable: true,            
40
            virtualX: true, virtualY: true,
41
            freezeCols: 2,
42
            toolbar: {
43
                items: [
44
                    {
45
                        type: 'select',
46
                        label: 'Sorting Type:',
47
                        value: 'multi', //default value.
48
                        options: [
49
                            {'single': 'Single without shift key'},
50
                            {'singlemulti': 'Single with shift key for multiple'},
51
                            {'multi': 'Multiple columns'}
52
                        ],
53
                        listener: function(evt){                            
54
                            var val = $(evt.target).val(),
55
                                single = true,
56
                                multiKey = null;
57
​
58
                            if(val == 'singlemulti'){                                
59
                                multiKey = 'shiftKey';
60
                            }
61
                            else if(val == 'multi'){
62
                                single = false;
63
                            }    
64
​
65
                            this.option("sortModel.single", single);
66
                            this.option("sortModel.multiKey", multiKey);
67
                            this.sort(); //refresh sorting.                        
68
                        }
69
                    }
70
                ]
71
            }
72
        };
73
        var $grid = $("#grid_sorting_remote").pqGrid(gObj);
74
​
75
    });
76
​

100
 
1
​
2
public class OrderCustomer
3
{
4
    [Key]
5
    public int OrderID { get; set; }
6
    public String ContactName { get; set; }
7
    public int EmployeeID { get; set; }
8
    public DateTime? OrderDate { get; set; }
9
    public DateTime? RequiredDate { get; set; }
10
    public DateTime? ShippedDate { get; set; }
11
    public String ShipVia { get; set; }
12
    public Decimal Freight { get; set; }//money
13
    public String ShipName { get; set; }
14
    public String ShipAddress { get; set; }
15
    public String ShipCity { get; set; }
16
    public String ShipRegion { get; set; }
17
    public String ShipPostalCode { get; set; }
18
    public String ShipCountry { get; set; }
19
    public bool paid { get; set; }
20
}
21
public class SortHelper
22
{
23
    private struct sortobj
24
    {
25
        public String dataIndx;
26
        public String dir;
27
    }
28
    public static String deSerializeSort(String pq_sort)
29
    {
30
        JavaScriptSerializer js = new JavaScriptSerializer();
31
        List<sortobj> sorters = js.Deserialize<List<sortobj>>(pq_sort);
32
        List<String> columns = new List<string>();
33
        String sortby = "";
34
        foreach (sortobj sorter in sorters)
35
        {
36
            var dataIndx = sorter.dataIndx;
37
            var dir = sorter.dir;
38
            if (dir == "up")
39
            {
40
                dir = "asc";
41
            }
42
            else
43
            {
44
                dir = "desc";
45
            }
46
            if (ColumnHelper.isValidColumn(dataIndx))
47
            {
48
                columns.Add(dataIndx + " " + dir);
49
            }
50
            //columns.Add(
51
        }
52
​
53
        if (columns.Count > 0)
54
        {
55
            sortby = " order by " + String.Join(", ", columns);
56
        }
57
        return sortby;
58
    }
59
}
60
public class ColumnHelper
61
{
62
    public static bool isValidColumn(String dataIndx)
63
    {            
64
        if (Regex.IsMatch(dataIndx, "^[a-z,A-Z]*$"))
65
        {
66
            return true;
67
        }
68
        else
69
        {
70
            return false;
71
        }
72
    }
73
}
74
public class ordersController : Controller
75
{
76
    public ActionResult get(String pq_sort)
77
    {
78
        pqTestContext db = new pqTestContext();
79
​
80
        //sort
81
        String sortQuery = "";            
82
        if (pq_sort != null && pq_sort.Length > 0)
83
        {                  
84
            sortQuery = SortHelper.deSerializeSort(pq_sort);
85
        }
86
​
87
        List<OrderCustomer> orders = db.Database.SqlQuery<OrderCustomer>(@"select * from ordercustomers " + sortQuery).ToList();
88
​
89
        StringBuilder sb = new StringBuilder(@"{""data"":");
90
​
91
        JavaScriptSerializer js = new JavaScriptSerializer();
92
        String json = js.Serialize(orders);
93
        sb.Append(json);
94
            
95
        sb.Append("}");
96
            
97
        return this.Content(sb.ToString(), "text/text");
98
    }
99
}
100
​

73
 
1
​
2
class ColumnHelper
3
{
4
    public static function isValidColumn($dataIndx)
5
    {            
6
        if (preg_match('/^[a-z,A-Z]*$/', $dataIndx))
7
        {
8
            return true;
9
        }
10
        else
11
        {
12
            return false;
13
        }
14
    }
15
}
16
class SortHelper
17
{
18
    public static function deSerializeSort($pq_sort)
19
    {
20
        $sorters = json_decode($pq_sort);
21
        $columns = array();
22
        $sortby = "";
23
        foreach ($sorters as $sorter){
24
            $dataIndx = $sorter->dataIndx;
25
            $dir = $sorter->dir;
26
            if ($dir == "up")
27
            {
28
                $dir = "asc";
29
            }
30
            else
31
            {
32
                $dir = "desc";
33
            }
34
            if (ColumnHelper::isValidColumn($dataIndx))
35
            {
36
                $columns[] = $dataIndx . " " . $dir;
37
            }
38
            else{
39
                throw new Exception("invalid column ".$dataIndx);
40
            }
41
        }
42
        if (sizeof($columns) > 0)
43
        {
44
            $sortby = " order by " . join(", ", $columns);
45
        }
46
        return $sortby;
47
    }
48
}
49
​
50
//orders.php
51
$sortQuery = "";
52
if(isset($_GET["pq_sort"]))
53
{
54
    $pq_sort = $_GET["pq_sort"];
55
    $sortQuery = SortHelper::deSerializeSort($pq_sort);
56
}
57
​
58
$sql = "Select * from ordercustomers ".$sortQuery;
59
​
60
$dsn = 'mysql:host='.DB_HOSTNAME.';dbname='.DB_NAME;
61
$options = array(
62
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
63
); 
64
$dbh = new PDO($dsn, DB_USERNAME, DB_PASSWORD, $options);
65
​
66
$stmt = $dbh->prepare($sql);
67
​
68
$stmt->execute();
69
​
70
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
71
​
72
echo "{\"data\":". json_encode( $rows ) ." }" ;
73
​

132
 
1
​
2
//ColumnHelper.java
3
package helper;
4
​
5
import java.util.regex.*;
6
​
7
/**
8
 * @author paramvir
9
 */
10
public class ColumnHelper {
11
​
12
    public static boolean isValidColumn(String dataIndx) {
13
        String pattern = "^[a-z,A-Z]*$";
14
​
15
        Pattern r = Pattern.compile(pattern);
16
​
17
        Matcher m = r.matcher(dataIndx);
18
        if (m.find()) {
19
            return true;
20
        } else {
21
            return false;
22
        }
23
    }
24
}
25
//SortHelper.java
26
package helper;
27
​
28
import flexjson.JSONDeserializer;
29
import java.util.ArrayList;
30
import java.util.List;
31
import java.util.Map;
32
​
33
/**
34
 *
35
 * @author paramvir
36
 */
37
public class SortHelper {
38
    
39
    public static String deSerialize(String pq_sort){        
40
        
41
        JSONDeserializer<ArrayList<Map>> deserializer = new JSONDeserializer<ArrayList<Map>>();         
42
        ArrayList<Map> sorters = deserializer.deserialize(pq_sort);        
43
        
44
        ArrayList<String> columns = new ArrayList<String>();
45
        String sortby = "";
46
        for (Map sorter : sorters)
47
        {
48
            String dataIndx = (String) sorter.get("dataIndx");
49
            String dir = (String) sorter.get("dir");
50
            if (dir.equals("up"))
51
            {
52
                dir = "asc";
53
            }
54
            else
55
            {
56
                dir = "desc";
57
            }
58
            if (ColumnHelper.isValidColumn(dataIndx))
59
            {
60
                columns.add(dataIndx + " " + dir);
61
            }            
62
        }
63
​
64
        if (columns.size() > 0)
65
        {       
66
            sortby = " order by " + stringJoin(", ", columns); 
67
        }
68
        return sortby;                
69
    }
70
    private static String stringJoin (String connector, List<String> list){
71
        String str = "";
72
        for(int i=0, len = list.size(); i< len; i++){                
73
            str += list.get(i);
74
            if(i<len-1){
75
                str += connector;
76
            }
77
        }
78
        return str;
79
    }    
80
}
81
//OrdersController.java
82
package controller;
83
​
84
import domain.OrderCustomer;
85
import flexjson.JSONSerializer;
86
import flexjson.transformer.DateTransformer;
87
import helper.FilterHelper;
88
import helper.SortHelper;
89
import java.util.*;
90
import org.apache.commons.logging.Log;
91
import org.apache.commons.logging.LogFactory;
92
import org.springframework.jdbc.core.BeanPropertyRowMapper;
93
import org.springframework.jdbc.core.support.JdbcDaoSupport;
94
import org.springframework.stereotype.Controller;
95
import org.springframework.web.bind.annotation.*;
96
​
97
/** 
98
 * @author paramvir
99
 */
100
@Controller
101
public class OrdersController extends JdbcDaoSupport{
102
    
103
    protected final Log logger = LogFactory.getLog(getClass());
104
    private String formatDate = "MM/dd/yyyy";
105
    
106
    @RequestMapping(value="/orders/get",method=RequestMethod.GET)  
107
    public @ResponseBody String get(@RequestParam(required=false) String pq_sort)
108
    {
109
        String sortQuery = "";            
110
        if (pq_sort != null && pq_sort.length() > 0)
111
        {                  
112
            sortQuery = SortHelper.deSerialize(pq_sort);
113
        }
114
        logger.info("pq_sort is " + pq_sort + "sortQuery is "+ sortQuery);
115
​
116
        List<OrderCustomer> orders = getJdbcTemplate().query(
117
                "Select * from ordercustomers " + sortQuery,                
118
                       new BeanPropertyRowMapper(OrderCustomer.class)                
119
                );
120
        
121
        
122
        JSONSerializer serializer = new JSONSerializer().exclude("*.class");        
123
        String json = serializer
124
                .transform(new DateTransformer(formatDate), Date.class)
125
                .serialize(orders);
126
​
127
        String sb="{\"data\":"+json+"}";
128
​
129
        return sb;
130
    }    
131
}
132
​