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

136
 
1
​
2
    $(function () {
3
​
4
        function pqDatePicker(ui) {
5
            var $this = $(this);
6
            $this
7
                //.css({ zIndex: 3, position: "relative" })
8
                .datepicker({
9
                    yearRange: "-25:+0", //25 years prior to present.
10
                    changeYear: true,
11
                    changeMonth: true,
12
                    showButtonPanel: true,
13
                    onClose: function (evt, ui) {
14
                        $(this).focus();
15
                    }
16
                });
17
            //default From date
18
            $this.filter(".pq-from").datepicker("option", "defaultDate", new Date("01/01/1996"));
19
            //default To date
20
            $this.filter(".pq-to").datepicker("option", "defaultDate", new Date("12/31/1998"));
21
        }
22
​
23
        //define colModel
24
        var colM = [
25
        { title: "ShipCountry", width: 100, dataIndx: "ShipCountry",
26
            filter: { type: 'textbox', condition: 'begin', listeners: ['change'] }
27
        },
28
        { title: "Customer Name", width: 120, dataIndx: "ContactName",
29
            filter: { type: 'textbox', condition: 'begin', listeners: ['change'] }
30
        },
31
        { title: "Order ID", minWidth: 130, dataIndx: "OrderID", dataType: "integer",
32
            filter: { type: 'textbox', condition: "between", listeners: ['change'] }
33
        },
34
        { title: "Order Date", minWidth: "190", dataIndx: "OrderDate", dataType: "date",
35
            filter: { type: 'textbox', condition: "between", init: pqDatePicker, listeners: ['change'] }
36
        },
37
        { title: "Shipping Region", width: 130, dataIndx: "ShipRegion",
38
            filter: { type: 'select',
39
                condition: 'equal', 
40
                //init: multiSelect,
41
                valueIndx: "ShipRegion",
42
                labelIndx: "ShipRegion",
43
                groupIndx: "ShipCountry",
44
                prepend: { '': '--Select--' },
45
                listeners: ['change']
46
            }
47
        },
48
        { title: "Paid", width: 100, dataIndx: "paid", dataType: "bool", align: "center", type: 'checkbox',
49
            filter: { type: "checkbox", subtype: 'triple', condition: "equal", listeners: ['click'] }
50
        },
51
        { title: "Shipping Via", width: 140, dataIndx: "ShipVia",
52
            filter: { type: "select",
53
                condition: 'equal',
54
                prepend: { '': '--Select--' },
55
                valueIndx: "ShipVia",
56
                labelIndx: "ShipVia",
57
                listeners: ['change']
58
            }
59
        },
60
        { title: "Required Date", width: 100, dataIndx: "RequiredDate", dataType: "date" },
61
        { title: "Shipped Date", width: 100, dataIndx: "ShippedDate", dataType: "date" },
62
        { title: "Freight", width: 100, align: "right", dataIndx: "Freight", dataType: "float" },
63
        { title: "Shipping Name", width: 150, dataIndx: "ShipName" },
64
        { title: "Shipping Address", width: 270, dataIndx: "ShipAddress" },
65
        { title: "Shipping City", width: 100, dataIndx: "ShipCity" },
66
        { title: "Shipping Postal Code", width: 180, dataIndx: "ShipPostalCode" }
67
​
68
        ];
69
        //define dataModel
70
        var dataModel = {
71
            location: "remote",            
72
            dataType: "JSON",
73
            method: "GET",
74
            url: "/pro/orders/get"
75
            //url: "/pro/orders.php",//for PHP
76
        }
77
        var obj = {
78
            height: 500,
79
            dataModel: dataModel,
80
            flex:{one: true},
81
            colModel: colM,
82
            //pageModel: { type: 'local', rPP: 20 },
83
            virtualX: true, virtualY: true,
84
            wrap: false,
85
            showBottom: false,            
86
            editable: false,            
87
            filterModel: { on: true, mode: "AND", header: true, type: 'remote' },
88
            title: "Shipping Orders",
89
            resizable: true,
90
            hwrap:false,
91
            freezeCols: 2,
92
            toolbar:{
93
                items:[
94
                    {
95
                        type:'button',
96
                        label: 'Toggle filter row',
97
                        listener: function(){
98
                            this.option('filterModel.header', !this.option('filterModel.header'));
99
                            this.refresh();
100
                        }
101
                    },
102
                    {
103
                        type:'button',
104
                        label: 'Reset filters',
105
                        listener: function(){
106
                            this.reset({filter: true});                         
107
                        }                        
108
                    }
109
                ]
110
            }
111
        };
112
        var grid = pq.grid( "#grid_filter", obj);
113
        
114
        //load shipregion and shipvia dropdowns in first load event.                
115
        grid.one("load", function (evt, ui) {
116
            var column = grid.getColumn({ dataIndx: "ShipRegion" });
117
            var filter = column.filter;
118
            filter.cache = null;
119
            filter.options = grid.getData({ dataIndx: ["ShipCountry", "ShipRegion"] });
120
​
121
            var column = grid.getColumn({ dataIndx: "ShipVia" });
122
            var filter = column.filter;
123
            filter.cache = null;
124
            filter.options = grid.getData({ dataIndx: ["ShipVia"] });
125
​
126
            //and apply initial filtering.
127
            grid.filter({
128
                oper: 'add',
129
                data: [
130
                    { dataIndx: 'ShipRegion', value: 'RJ' },
131
                    { dataIndx: 'ContactName', value: 'M' }
132
                ]
133
            });            
134
        });
135
    });
136
​

170
 
1
​
2
//model class
3
public class OrderCustomer
4
{
5
    [Key]
6
    public int OrderID { get; set; }
7
    public String ContactName { get; set; }
8
    public int EmployeeID { get; set; }
9
    public DateTime? OrderDate { get; set; }
10
    public DateTime? RequiredDate { get; set; }
11
    public DateTime? ShippedDate { get; set; }
12
    public String ShipVia { get; set; }
13
    public Decimal Freight { get; set; }//money
14
    public String ShipName { get; set; }
15
    public String ShipAddress { get; set; }
16
    public String ShipCity { get; set; }
17
    public String ShipRegion { get; set; }
18
    public String ShipPostalCode { get; set; }
19
    public String ShipCountry { get; set; }
20
    public bool paid { get; set; }
21
}
22
///to check valid column name.
23
public class ColumnHelper
24
{
25
    public static bool isValidColumn(String dataIndx)
26
    {            
27
        if (Regex.IsMatch(dataIndx, "^[a-z,A-Z]*$"))
28
        {
29
            return true;
30
        }
31
        else
32
        {
33
            return false;
34
        }
35
    }
36
}
37
//return type of FitlerHelper.deSerializedFilter
38
public struct deSerializedFilter
39
{
40
    public String query;
41
    public List<Object> param;
42
}
43
​
44
public class FilterHelper
45
{
46
    struct Filter
47
    {
48
        public String dataIndx;
49
        public String condition;
50
        public String value;
51
    }
52
    //map to json object posted by client
53
    struct FilterObj
54
    {
55
        public String mode;
56
        public List<Filter> data;
57
    }
58
    public static deSerializedFilter deSerializeFilter(String pq_filter)
59
    {
60
        JavaScriptSerializer js = new JavaScriptSerializer();        
61
​
62
        FilterObj filterObj = js.Deserialize<FilterObj>(pq_filter);
63
        String mode = filterObj.mode;
64
        List<Filter> filters = filterObj.data;
65
​
66
        List<String> fc = new List<String>();
67
​
68
        List<object> param = new List<object>();
69
​
70
        foreach (Filter filter in filters)
71
        {
72
            String dataIndx = filter.dataIndx;
73
            if (ColumnHelper.isValidColumn(dataIndx) == false)
74
            {
75
                throw new Exception("Invalid column name");
76
            }
77
            String text = filter.value;
78
            String condition = filter.condition;
79
            if (condition == "contain")
80
            {
81
                fc.Add(dataIndx + " like @" + dataIndx);
82
                param.Add(new SqlParameter(dataIndx, "%" + text + "%"));
83
            }
84
            else if (condition == "notcontain")
85
            {
86
                fc.Add(dataIndx + " not like @" + dataIndx);
87
                param.Add(new SqlParameter(dataIndx, "%" + text + "%"));
88
            }
89
            else if (condition == "begin")
90
            {
91
                fc.Add(dataIndx + " like @" + dataIndx);
92
                param.Add(new SqlParameter(dataIndx, text + "%"));
93
            }
94
            else if (condition == "end")
95
            {
96
                fc.Add(dataIndx + " like @" + dataIndx);
97
                param.Add(new SqlParameter(dataIndx, "%" + text));
98
            }
99
            else if (condition == "equal")
100
            {
101
                fc.Add(dataIndx + "=@" + dataIndx);
102
                param.Add(new SqlParameter(dataIndx, text));
103
            }
104
            else if (condition == "notequal")
105
            {
106
                fc.Add(dataIndx + "!=@" + dataIndx);
107
                param.Add(new SqlParameter(dataIndx, text));
108
            }
109
            else if (condition == "empty")
110
            {
111
                fc.Add("isnull(" + dataIndx + ",'')=''");
112
                //param.Add(new SqlParameter(dataIndx, text + "%"));
113
            }
114
            else if (condition == "notempty")
115
            {
116
                fc.Add("isnull(" + dataIndx + ",'')!=''");
117
            }
118
            else if (condition == "less")
119
            {
120
                fc.Add(dataIndx + "<@" + dataIndx);
121
                param.Add(new SqlParameter(dataIndx, text));
122
            }
123
            else if (condition == "great")
124
            {
125
                fc.Add(dataIndx + ">@" + dataIndx);
126
                param.Add(new SqlParameter(dataIndx, text));
127
            }
128
        }
129
        String query = "";
130
        if (filters.Count > 0)
131
        {
132
            query = " where " + String.Join(" " + mode + " ", fc.ToArray());
133
        }
134
​
135
        deSerializedFilter ds = new deSerializedFilter();
136
        ds.query = query;
137
        ds.param = param;
138
        return ds;
139
    }
140
}
141
public class ordersController : Controller
142
{
143
    public ActionResult get(String pq_filter)
144
    {
145
        pqTestContext db = new pqTestContext();
146
​
147
        //filter
148
        String filterQuery = "";
149
        List<object> filterParam = new List<object>();
150
        if (pq_filter != null && pq_filter.Length > 0)
151
        {
152
            deSerializedFilter dsf = FilterHelper.deSerializeFilter(pq_filter);
153
            filterQuery = dsf.query;
154
            filterParam = dsf.param;
155
        }
156
​
157
        List<OrderCustomer> orders = db.Database.SqlQuery<OrderCustomer>(@"select * from ordercustomers " + filterQuery, filterParam.ToArray()).ToList();
158
​
159
        StringBuilder sb = new StringBuilder(@"{""data"":");
160
​
161
        JavaScriptSerializer js = new JavaScriptSerializer();        
162
        String json = js.Serialize(orders);
163
        sb.Append(json);
164
            
165
        sb.Append("}");
166
            
167
        return this.Content(sb.ToString(), "text/text");
168
    }
169
}
170
​

127
 
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 FilterHelper
17
{
18
    public static function deSerializeFilter($pq_filter)        
19
    {
20
        $filterObj = json_decode($pq_filter);
21
        
22
        $mode = $filterObj->mode;                
23
        $filters = $filterObj->data;
24
        
25
        $fc = array();
26
        $param= array();
27
​
28
        foreach ($filters as $filter)
29
        {            
30
            $dataIndx = $filter->dataIndx;            
31
            if (ColumnHelper::isValidColumn($dataIndx) == false)
32
            {
33
                throw new Exception("Invalid column name");
34
            }
35
            $text = $filter->value;
36
            $condition = $filter->condition;
37
            
38
            if ($condition == "contain")
39
            {
40
                $fc[] = $dataIndx . " like CONCAT('%', ?, '%')";
41
                $param[] = $text;
42
            }
43
            else if ($condition == "notcontain")
44
            {
45
                $fc[] = $dataIndx . " not like CONCAT('%', ?, '%')";
46
                $param[] = $text;                
47
            }
48
            else if ($condition == "begin")
49
            {
50
                $fc[] = $dataIndx . " like CONCAT( ?, '%')";
51
                $param[] = $text;                                
52
            }
53
            else if ($condition == "end")
54
            {
55
                $fc[] = $dataIndx . " like CONCAT('%', ?)";
56
                $param[] = $text;                                
57
            }
58
            else if ($condition == "equal")
59
            {
60
                $fc[] = $dataIndx . " = ?";
61
                $param[] = $text;                                
62
            }
63
            else if ($condition == "notequal")
64
            {
65
                $fc[] = $dataIndx . " != ?";
66
                $param[] = $text;                                
67
            }
68
            else if ($condition == "empty")
69
            {             
70
                $fc[] = "ifnull(" . $dataIndx . ",'')=''";                
71
            }
72
            else if ($condition == "notempty")
73
            {
74
                $fc[] = "ifnull(" . $dataIndx . ",'')!=''";                
75
            }
76
            else if ($condition == "less")
77
            {
78
                $fc[] = $dataIndx . " < ?";
79
                $param[] = $text;                                                
80
            }
81
            else if ($condition == "great")
82
            {
83
                $fc[] = $dataIndx . " > ?";
84
                $param[] = $text;                                                                
85
            }
86
        }
87
        $query = "";
88
        if (sizeof($filters) > 0)
89
        {
90
            $query = " where " . join(" ".$mode." ", $fc);
91
        }
92
​
93
        $ds = new stdClass();
94
        $ds->query = $query;
95
        $ds->param = $param;
96
        return $ds;
97
    }
98
}//end of class
99
​
100
​
101
//orders.php
102
$filterQuery = "";
103
$filterParam = array();
104
if ( isset($_GET["pq_filter"]))
105
{
106
    $pq_filter = $_GET["pq_filter"];
107
    $dsf = FilterHelper::deSerializeFilter($pq_filter);
108
    $filterQuery = $dsf->query;
109
    $filterParam = $dsf->param;
110
}    
111
​
112
$sql = "Select * from ordercustomers ".$filterQuery;
113
​
114
$dsn = 'mysql:host='.DB_HOSTNAME.';dbname='.DB_NAME;
115
$options = array(
116
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
117
); 
118
$dbh = new PDO($dsn, DB_USERNAME, DB_PASSWORD, $options);
119
​
120
$stmt = $dbh->prepare($sql);
121
​
122
$stmt->execute($filterParam);
123
​
124
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
125
​
126
echo "{\"data\":". json_encode( $rows ) ." }" ;
127
​

232
 
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
//FilterHelper.java
26
package helper;
27
​
28
import flexjson.JSONDeserializer;
29
import java.text.ParseException;
30
import java.text.SimpleDateFormat;
31
import java.util.*;
32
import java.util.logging.Level;
33
import java.util.logging.Logger;
34
​
35
/**
36
 *
37
 * @author paramvir
38
 */
39
public class FilterHelper {
40
​
41
    public static Map deSerialize(String pq_filter, String formatDate) {
42
        JSONDeserializer<Map> deserializer = new JSONDeserializer<Map>();
43
​
44
        Map filterObj = deserializer
45
                .deserialize(pq_filter);
46
​
47
        ArrayList<Map> filters = (ArrayList<Map>) filterObj.get("data");
48
        String mode = (String) filterObj.get("mode");
49
​
50
        List<String> fc = new ArrayList<String>();
51
​
52
        List<Object> param = new ArrayList<Object>();
53
​
54
        for (Map filter : filters) {
55
            String dataIndx = (String) filter.get("dataIndx");
56
            if (ColumnHelper.isValidColumn(dataIndx) == false) {
57
                continue;
58
            }
59
            String dataType = (String) filter.get("dataType");
60
​
61
            String value = "";
62
            String value2 = "";
63
​
64
            String condition = (String) filter.get("condition");
65
​
66
            if (condition.equals("range") == false) {
67
                value = (String) filter.get("value");
68
                value2 = (String) filter.get("value2");
69
                
70
                value = (value!=null)?value.trim():"";                
71
                value2 = (value2!=null)?value2.trim():"";
72
                
73
                if (dataType.equals("bool")) {
74
                    if (value.equals("true")) {
75
                        value = "1";
76
                    } else {
77
                        value = "0";
78
                    }
79
                }
80
                else if (dataType.equals("date")) {
81
                    if(value.length()>1){
82
                        value =  dateFormat(value, formatDate);
83
                    }
84
                    if(value2.length()>1){
85
                        value2 = dateFormat(value2, formatDate);
86
                    }
87
                }                            
88
            }
89
            if (condition.equals("contain")) {
90
                fc.add(dataIndx + " like ?");
91
                param.add("%" + value + "%");
92
            } else if (condition.equals("notcontain")) {
93
                fc.add(dataIndx + " not like ?");
94
                param.add("%" + value + "%");
95
            } else if (condition.equals("begin")) {
96
                fc.add(dataIndx + " like ?");
97
                param.add(value + "%");
98
            } else if (condition.equals("end")) {
99
                fc.add(dataIndx + " like ?");
100
                param.add("%" + value);
101
            } else if (condition.equals("equal")) {
102
                fc.add(dataIndx + "= ?");
103
                param.add(value);
104
            } else if (condition.equals("notequal")) {
105
                fc.add(dataIndx + "!= ?");
106
                param.add(value);
107
            } else if (condition.equals("empty")) {
108
                fc.add("isnull(" + dataIndx + ",'')=''");
109
            } else if (condition.equals("notempty")) {
110
                fc.add("isnull(" + dataIndx + ",'')!=''");
111
            } else if (condition.equals("less")) {
112
                fc.add(dataIndx + "< ?");
113
                param.add(value);
114
            } else if (condition.equals("lte")) {
115
                fc.add(dataIndx + "<= ?" + dataIndx);
116
                param.add(value);
117
            } else if (condition.equals("great")) {
118
                fc.add(dataIndx + "> ?");
119
                param.add(value);
120
            } else if (condition.equals("gte")) {
121
                fc.add(dataIndx + ">= ?");
122
                param.add(value);
123
            } else if (condition.equals("between")) {
124
                fc.add("(" + dataIndx + ">= ? AND " + dataIndx + "<= ? )");
125
                param.add(value);
126
                param.add(value2);
127
            } else if (condition.equals("range")) {
128
                List<Object> arrValue = (List<Object>) filter.get("value");
129
                List<String> fcRange = new ArrayList<String>();
130
                for (Object val : arrValue) {
131
                    String strVal = (String) val;                    
132
                    if (strVal == null || strVal.isEmpty()) {
133
                        continue;
134
                    }
135
                    strVal = strVal.trim();
136
                    if (dataType.equals("date")) {
137
                        strVal =  dateFormat(strVal, formatDate);
138
                    }                                                
139
                    fcRange.add(dataIndx + "= ?");
140
                    param.add(strVal);
141
                }
142
                if (fcRange.size() > 0) {
143
                    fc.add("(" + stringJoin(" OR ", fcRange) + ")");
144
                }
145
            }
146
        }
147
        String query = "";
148
        if (fc.size() > 0) {
149
            query = " where " + stringJoin(" " + mode + " ", fc);
150
        }
151
        
152
        Map ds = new HashMap();
153
        ds.put("query", query);
154
        ds.put("param", param);
155
        return ds;
156
    }
157
    private static String dateFormat(String strDate, String format) {
158
        String newStr ="";
159
        try {
160
            Date date = new SimpleDateFormat(format).parse(strDate);
161
            newStr = new SimpleDateFormat("yyyy-MM-dd").format(date);
162
        } catch (ParseException ex) {
163
            Logger.getLogger(FilterHelper.class.getName()).log(Level.SEVERE, null, ex);
164
        }
165
        return newStr;
166
    }
167
    private static String stringJoin(String connector, List<String> list) {
168
        String str = "";
169
        for (int i = 0, len = list.size(); i < len; i++) {
170
            str += list.get(i);
171
            if (i < len - 1) {
172
                str += connector;
173
            }
174
        }
175
        return str;
176
    }
177
}
178
//OrdersController.java
179
package controller;
180
​
181
import domain.OrderCustomer;
182
import flexjson.JSONSerializer;
183
import flexjson.transformer.DateTransformer;
184
import helper.FilterHelper;
185
import helper.SortHelper;
186
import java.util.*;
187
import org.apache.commons.logging.Log;
188
import org.apache.commons.logging.LogFactory;
189
import org.springframework.jdbc.core.BeanPropertyRowMapper;
190
import org.springframework.jdbc.core.support.JdbcDaoSupport;
191
import org.springframework.stereotype.Controller;
192
import org.springframework.web.bind.annotation.*;
193
​
194
/** 
195
 * @author paramvir
196
 */
197
@Controller
198
public class OrdersController extends JdbcDaoSupport{
199
    
200
    protected final Log logger = LogFactory.getLog(getClass());
201
    private String formatDate = "MM/dd/yyyy";
202
    
203
    @RequestMapping(value="/orders/get",method=RequestMethod.GET)  
204
    public @ResponseBody String get( @RequestParam(required=false) String pq_filter)
205
    {
206
        //filter
207
        String filterQuery = "";
208
        
209
        List<Object> filterParam = new ArrayList<Object>();
210
        if (pq_filter != null && pq_filter.length() > 0)
211
        {
212
            Map filterMap = FilterHelper.deSerialize(pq_filter, formatDate);
213
            filterQuery = (String) filterMap.get("query");
214
            filterParam = (List<Object>) filterMap.get("param");
215
        }
216
        List<OrderCustomer> orders = getJdbcTemplate().query(
217
                "Select * from ordercustomers " + filterQuery,                
218
                       new BeanPropertyRowMapper(OrderCustomer.class)
219
                       , filterParam.toArray()
220
                );        
221
        
222
        JSONSerializer serializer = new JSONSerializer().exclude("*.class");        
223
        String json = serializer
224
                .transform(new DateTransformer(formatDate), Date.class)
225
                .serialize(orders);
226
​
227
        String sb="{\"data\":"+json+"}";
228
​
229
        return sb;
230
    }    
231
}
232
​