4
 
1
​
2
<div id="grid_editing" style="margin: auto;">
3
</div>
4
​

171
 
1
​
2
    $(function () {
3
        //called when save changes button is clicked.
4
        function saveChanges() {
5
            var grid = $grid.pqGrid('getInstance').grid;
6
            
7
            //attempt to save editing cell.
8
            if (grid.saveEditCell() === false) {
9
                return false;
10
            }
11
            
12
            if (grid.isDirty() && grid.isValidChange({ focusInvalid: true }).valid) {
13
​
14
                var changes = grid.getChanges({ format: "byVal" });
15
​
16
                //post changes to server 
17
                $.ajax({
18
                    dataType: "json",
19
                    type: "POST",
20
                    async: true,
21
                    beforeSend: function (jqXHR, settings) {
22
                        grid.showLoading();
23
                    },
24
                    url: "/pro/products/batch", //for ASP.NET, java   
25
                    //url:                                              
26
                    data: { list: JSON.stringify(changes) },
27
                    success: function (changes) {
28
                        //debugger;
29
                        grid.commit({ type: 'add', rows: changes.addList });
30
                        grid.commit({ type: 'update', rows: changes.updateList });
31
                        grid.commit({ type: 'delete', rows: changes.deleteList });
32
​
33
                        grid.history({ method: 'reset' });
34
                    },
35
                    complete: function () {
36
                        grid.hideLoading();
37
                    }
38
                });
39
            }
40
        }
41
        var obj = {
42
            hwrap: false,
43
            resizable: true,
44
            rowBorders: false,
45
            virtualX: true,
46
            filterModel: { header: true, type: 'local' },
47
            trackModel: { on: true }, //to turn on the track changes.            
48
            toolbar: {
49
                items: [
50
                    { type: 'button', icon: 'ui-icon-plus', label: 'New Product', listener: function () {
51
                            //append empty row at the end.                            
52
                            var rowData = { ProductID: 34, UnitPrice: 0.2 }; //empty row
53
                            var rowIndx = $grid.pqGrid("addRow", { rowData: rowData, checkEditable: true });
54
                            $grid.pqGrid("goToPage", { rowIndx: rowIndx });
55
                            $grid.pqGrid("editFirstCellInRow", { rowIndx: rowIndx });                        
56
                        }
57
                    },
58
                    { type: 'separator' },
59
                    { type: 'button', icon: 'ui-icon-disk', label: 'Save Changes', cls: 'changes', listener: function () {
60
                            saveChanges();                        
61
                        },
62
                        options: { disabled: true }
63
                    },
64
                    { type: 'button', icon: 'ui-icon-cancel', label: 'Reject Changes', cls: 'changes', listener: function () {
65
                            $grid.pqGrid("rollback");
66
                            $grid.pqGrid("history", { method: 'resetUndo' });                        
67
                        },
68
                        options: { disabled: true }
69
                    },
70
                    { type: 'separator' },
71
                    { type: 'button', icon: 'ui-icon-arrowreturn-1-s', label: 'Undo', cls: 'changes', listener: function () {
72
                            $grid.pqGrid("history", { method: 'undo' });                        
73
                        },
74
                        options: { disabled: true }
75
                    },
76
                    { type: 'button', icon: 'ui-icon-arrowrefresh-1-s', label: 'Redo', listener: function () {
77
                            $grid.pqGrid("history", { method: 'redo' });                        
78
                        },
79
                        options: { disabled: true }
80
                    }
81
                ]
82
            },
83
            scrollModel: { autoFit: true },
84
            swipeModel: { on: false },
85
            editModel: {
86
                saveKey: $.ui.keyCode.ENTER
87
            },
88
            editor: { select: true },
89
            title: "<b>Batch Editing & Filtering & Sorting Combined</b>",
90
            history: function (evt, ui) {
91
                var $grid = this.widget();
92
                if (ui.canUndo != null) {
93
                    $("button.changes", $grid).button("option", { disabled: !ui.canUndo });
94
                }
95
                if (ui.canRedo != null) {
96
                    $("button:contains('Redo')", $grid).button("option", "disabled", !ui.canRedo);
97
                }
98
                $("button:contains('Undo')", $grid).button("option", { label: 'Undo (' + ui.num_undo + ')' });
99
                $("button:contains('Redo')", $grid).button("option", { label: 'Redo (' + ui.num_redo + ')' });
100
            },
101
            colModel: [
102
                { title: "Product ID", dataType: "integer", dataIndx: "ProductID", editable: false, width: 80 },
103
                { title: "Product Name", width: 165, dataType: "string", dataIndx: "ProductName",
104
                    filter: { type: 'textbox', condition: 'begin', listeners: ['keyup'] },
105
                    validations: [
106
                        { type: 'minLen', value: 1, msg: "Required" },
107
                        { type: 'maxLen', value: 40, msg: "length should be <= 40" }
108
                    ]
109
                },
110
                { title: "Quantity Per Unit", width: 140, dataType: "string", dataIndx: "QuantityPerUnit",
111
                    validations: [
112
                        { type: 'minLen', value: 1, msg: "Required." },
113
                        { type: 'maxLen', value: 20, msg: "length should be <= 20" }
114
                    ]
115
                },
116
                { title: "Unit Price", width: 100, dataType: "float", dataIndx: "UnitPrice", format: '$##,###.00',
117
                    validations: [{ type: 'gt', value: 0.5, msg: "should be > 0.5"}]
118
                },
119
                //{ hidden: true },
120
                { title: "Units In Stock", width: 100, dataType: "integer", dataIndx: "UnitsInStock",
121
                    validations: [
122
                        { type: 'gte', value: 1, msg: "should be >= 1" },
123
                        { type: 'lte', value: 1000, msg: "should be <= 1000" }
124
                    ]
125
                },
126
                { title: "Discontinued", width: 100, dataType: "bool", align: "center", dataIndx: "Discontinued",
127
                    editor: { type: "checkbox", subtype: 'triple',
128
                        style: "margin:3px 5px;"
129
​
130
                    },
131
                    validations: [{ type: 'nonEmpty', msg: "Required"}]
132
                },
133
                { title: "", editable: false, minWidth: 83, sortable: false,
134
                    render: function (ui) {
135
                        return "<button type='button' class='delete_btn'>Delete</button>";
136
                    },
137
                    postRender: function (ui) {
138
                        var rowIndx = ui.rowIndx,
139
                            grid = this,
140
                            $cell = grid.getCell(ui);
141
​
142
                        $cell.find("button").button({ icons: { primary: 'ui-icon-scissors'} })
143
                        .bind("click", function () {
144
​
145
                            grid.addClass({ rowIndx: ui.rowIndx, cls: 'pq-row-delete' });
146
​
147
                            var ans = window.confirm("Are you sure to delete row No " + (rowIndx + 1) + "?");
148
                            grid.removeClass({ rowIndx: rowIndx, cls: 'pq-row-delete' });
149
                            if (ans) {
150
                                grid.deleteRow({ rowIndx: rowIndx });
151
                            }
152
                        });
153
                    }
154
                }
155
            ],
156
            postRenderInterval: -1, //call postRender synchronously.
157
            pageModel: { type: "local", rPP: 20 },
158
            dataModel: {
159
                dataType: "JSON",
160
                location: "remote",
161
                recIndx: "ProductID",
162
                url: "/pro/products/get", //for ASP.NET
163
                //url: "/pro/products.php", //for PHP
164
                getData: function (response) {
165
                    return { data: response.data };
166
                }
167
            }
168
        };
169
        var $grid = $("#grid_editing").pqGrid(obj);
170
    });
171
​

​x
 
1
​
2
div.pq-toolbar button
3
{
4
    margin:0px 5px;
5
}
6
button.delete_btn
7
{
8
    margin:-3px 0px;
9
}
10
tr.pq-row-delete
11
{
12
    text-decoration:line-through;         
13
}
14
tr.pq-row-delete td
15
{
16
    background-color:pink;   
17
}
18
​

103
 
1
​
2
//Model class
3
public class Product
4
{
5
    public int ProductID { get; set; }
6
    public String ProductName { get; set; }
7
    public String QuantityPerUnit { get; set; }
8
    public decimal UnitPrice { get; set; }
9
    public short UnitsInStock { get; set; }
10
    public short UnitsOnOrder { get; set; }
11
    public short ReorderLevel { get; set; }
12
    public bool Discontinued { get; set; }
13
}
14
public partial class productsController : Controller
15
{
16
    private void addList(List<Product> addList)
17
    {
18
        List<Product> products = ((List<Product>)Session["Products"]);
19
        int max;
20
        if (products.Count == 0)
21
        {
22
            max = 0;
23
        }
24
        else
25
        {
26
            max = products.OrderByDescending(x => x.ProductID).First().ProductID;
27
        }
28
​
29
        for (int i = 0; i < addList.Count; i++)
30
        {                
31
            Product product = addList[i];
32
            product.ProductID = max + 1 + i;
33
            products.Add(product);
34
        }
35
    }        
36
    private void updateList(List<Product> updateList)
37
    {
38
        List<Product> products = ((List<Product>)Session["Products"]);
39
​
40
        for (int i = 0; i < updateList.Count; i++)
41
        {
42
            Product product2 = updateList[i];
43
​
44
            Product product = products.Find(Product => Product.ProductID == product2.ProductID);
45
            if (product == null)
46
            {
47
                products.Add(product2);
48
            }
49
            else
50
            {
51
                product.ProductID = product2.ProductID;
52
                product.ProductName = product2.ProductName;
53
                product.QuantityPerUnit = product2.QuantityPerUnit;
54
                product.UnitPrice = product2.UnitPrice;
55
                product.UnitsInStock = product2.UnitsInStock;
56
                product.UnitsOnOrder = product2.UnitsOnOrder;
57
                product.Discontinued = product2.Discontinued;
58
            }
59
        }            
60
    }                
61
    private void deleteList(List<Product> deleteList)
62
    {
63
        List<Product> products = ((List<Product>)Session["Products"]);
64
​
65
        for (int i = 0; i < deleteList.Count; i++)
66
        {
67
            Product product2 = deleteList[i];
68
            Product product = products.Find(Product => Product.ProductID == product2.ProductID);
69
            products.Remove(product);
70
        }        
71
    }
72
    [HttpPost]
73
    public String batch(String list)
74
    {
75
        fillInSession();
76
​
77
        JavaScriptSerializer js = new JavaScriptSerializer();
78
        Dictionary<String, List<Product>> dlist = js.Deserialize<Dictionary<String, List<Product>>>(list);
79
​
80
        this.updateList(dlist["updateList"]);
81
        this.addList(dlist["addList"]);
82
        this.deleteList(dlist["deleteList"]);
83
        return js.Serialize(dlist);
84
    }
85
    public ActionResult get()
86
    {
87
        fillInSession();
88
        var products2 = (from product in (List<Product>)Session["Products"]
89
                            orderby product.ProductName
90
                            select product);
91
​
92
        StringBuilder sb = new StringBuilder(@"{""data"":");
93
​
94
        JavaScriptSerializer js = new JavaScriptSerializer();
95
​
96
        string json = js.Serialize(products2);
97
        sb.Append(json);
98
        sb.Append("}");
99
​
100
        return this.Content(sb.ToString(), "text/text");
101
    }
102
}
103
​

163
 
1
​
2
//products.php
3
<?php
4
require_once 'conf.php';
5
​
6
function addList($addList)    
7
{
8
    $sproducts = json_decode($_SESSION["Products"], true);         
9
    //sort by product ID to get max ProductID
10
    usort($sproducts, function($a, $b)
11
    {
12
        return ($a["ProductID"] > $b["ProductID"]);
13
    });    
14
    $max = $sproducts[sizeof($sproducts)-1]["ProductID"];            
15
    
16
    //get $product by reference.
17
    foreach ($addList as $i=> &$product)
18
    {
19
        $product["ProductID"] = $max + 1 + $i;
20
​
21
        $sproduct = array();
22
        copyproduct($sproduct, $product);
23
        $sproducts[]=$sproduct;
24
        
25
        //$products[]=$product;//add new product
26
    }
27
    $_SESSION["Products"]= json_encode($sproducts);        
28
    return $addList;
29
}
30
function copyproduct(&$sproduct, $uproduct){    
31
    $sproduct["ProductID"] = $uproduct["ProductID"];
32
    $sproduct["ProductName"] = $uproduct["ProductName"];
33
    $sproduct["QuantityPerUnit"] = $uproduct["QuantityPerUnit"];
34
    $sproduct["UnitPrice"] = $uproduct["UnitPrice"];
35
    $sproduct["UnitsInStock"] = $uproduct["UnitsInStock"];
36
    //$product["UnitsOnOrder"] = $product2["UnitsOnOrder"];
37
    $sproduct["Discontinued"] = $uproduct["Discontinued"];     
38
}
39
function updateList($updateList)
40
{
41
    $sproducts = json_decode($_SESSION["Products"], true);         
42
​
43
    foreach ($updateList as $uproduct)
44
    {
45
        $ProductID = $uproduct["ProductID"];
46
        $found=false;
47
        foreach($sproducts as $i => &$sproduct){
48
            if($sproduct["ProductID"] == $ProductID){            
49
                //$products[$i] = $product2;
50
                copyproduct($sproduct, $uproduct);
51
                $found=true;
52
                break;
53
            }        
54
        }
55
        if(!$found){
56
            //add new product if not found.
57
            $sproduct = array();
58
            copyproduct($sproduct, $uproduct);
59
            $sproducts[]=$sproduct;
60
        }
61
    }
62
    $_SESSION["Products"]= json_encode($sproducts);        
63
    return $updateList;
64
}
65
function deleteList($deleteList)
66
{
67
    $products = json_decode($_SESSION["Products"], true);         
68
​
69
    foreach ($deleteList as $product)
70
    {
71
        $ProductID = $product["ProductID"];
72
        foreach($products as $i => $product2){
73
            if($product2["ProductID"] == $ProductID){            
74
                unset($products[$i]);
75
                break;
76
            }        
77
        }
78
    }
79
    $_SESSION["Products"]= json_encode($products);        
80
    return $deleteList;
81
}
82
if( isset($_GET["pq_batch"]))    
83
{
84
    session_start();
85
    $dlist = $_POST['list'];
86
    
87
    if(isset($dlist["updateList"])){
88
        $dlist["updateList"] = updateList($dlist["updateList"]);
89
    }
90
    if(isset($dlist["addList"])){
91
        $dlist["addList"] = addList($dlist["addList"]);
92
    }
93
    if(isset($dlist["deleteList"])){
94
        $dlist["deleteList"] = deleteList($dlist["deleteList"]);
95
    }
96
    
97
    echo json_encode($dlist);
98
}  
99
else{
100
    session_start();
101
    $_SESSION["Products"]=null;
102
    if (isset($_SESSION["Products"])==false)
103
    {
104
        //add in session["Products"];
105
        $sql = "Select ProductID, ProductName, QuantityPerUnit, UnitPrice, UnitsInStock, Discontinued from products";
106
        $dsn = 'mysql:host='.DB_HOSTNAME.';dbname='.DB_NAME;
107
        $options = array(
108
            PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
109
        ); 
110
        $dbh = new PDO($dsn, DB_USERNAME, DB_PASSWORD, $options);
111
        $stmt = $dbh->prepare($sql);
112
        $stmt->execute();
113
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
114
        foreach ($rows as $i=> &$row)
115
        {
116
            if($row['Discontinued']=='0'){
117
                $row['Discontinued']=false;
118
            }
119
            else{
120
                $row['Discontinued']=true;
121
            }
122
        }
123
​
124
        $_SESSION["Products"]= json_encode($rows);
125
    }    
126
    $products = json_decode($_SESSION["Products"], true);
127
    if(isset($_GET["pq_curpage"]) && isset($_GET["pq_rpp"]) )
128
    {
129
        $pq_curPage = $_GET["pq_curpage"];
130
        $pq_rPP=$_GET["pq_rpp"];
131
        
132
        $total_Records = sizeof($products);
133
​
134
        $skip = ($pq_rPP * ($pq_curPage - 1));
135
​
136
        if ($skip >= $total_Records)
137
        {            
138
            $pq_curPage = ceil($total_Records / $pq_rPP);
139
            $skip = ($pq_rPP * ($pq_curPage - 1));
140
        }    
141
        //sort by Product name
142
        usort($products, function($a, $b)
143
        {
144
            return strcmp($a["ProductName"], $b["ProductName"]);
145
        });
146
        $products2=array();
147
        for( $i=$skip; $i<($skip+$pq_rPP); $i++ ){
148
            if($i>=$total_Records){
149
                break;
150
            }
151
            $products2[]=$products[$i];
152
        }
153
        $sb = "{\"totalRecords\":" . $total_Records . ",\"curPage\":" . $pq_curPage . ",\"data\":".json_encode($products2)."}";
154
        echo $sb;
155
    }    
156
    else{
157
        $sb = "{\"data\":".json_encode($products)."}";
158
        echo $sb;
159
    }
160
}
161
​
162
?>    
163
​

156
 
1
​
2
package controller;
3
​
4
import domain.Product;
5
import flexjson.JSONSerializer;
6
import flexjson.JSONDeserializer;
7
import java.util.ArrayList;
8
import java.util.List;
9
import java.util.Map;
10
import javax.servlet.http.HttpServletRequest;
11
import javax.servlet.http.HttpSession;
12
import org.apache.commons.logging.Log;
13
import org.apache.commons.logging.LogFactory;
14
import org.springframework.jdbc.core.BeanPropertyRowMapper;
15
import org.springframework.jdbc.core.support.JdbcDaoSupport;
16
import org.springframework.stereotype.Controller;
17
import org.springframework.ui.ModelMap;
18
import org.springframework.web.bind.annotation.*;
19
​
20
/**
21
 * @author paramvir
22
 * batch editing & instant editing demo
23
 */
24
@Controller
25
public class ProductsListController extends JdbcDaoSupport {
26
    
27
    protected final Log logger = LogFactory.getLog(getClass());
28
​
29
    private HttpSession fillInSession(HttpServletRequest request){
30
        HttpSession ses = request.getSession(true);
31
        //ses.setAttribute("Products", null);
32
        if(ses.getAttribute("Products")==null){     
33
            logger.info("ses is null");            
34
            List<Product> products = getJdbcTemplate().query(
35
                 "Select productid, ProductName, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued from products", 
36
                  new BeanPropertyRowMapper(Product.class));
37
                        
38
            ses.setAttribute("Products", products);
39
        }    
40
        return ses;
41
    }
42
    private String serialize(List<Product> products){
43
        JSONSerializer serializer = new JSONSerializer().exclude("*.class");        
44
        String str = serializer.serialize(products);
45
        return str;
46
    }
47
    private String serializeMap(Map<String, List<Product>> map){
48
        JSONSerializer serializer = new JSONSerializer().exclude("*.class");        
49
        String str = serializer.deepSerialize(map);
50
        return str;
51
    }
52
    private Map deserializeMap(String str){
53
        JSONDeserializer<Map<String, List<Product>>> deserializer = new JSONDeserializer<Map<String, List<Product>>>();
54
        Map<String, List<Product>> map = deserializer.use( "values.values", Product.class ).deserialize(str);                        
55
        
56
        return map;
57
    }        
58
    @RequestMapping(value="/products/clear", method=RequestMethod.GET)
59
    public @ResponseBody String clear(HttpServletRequest request ){        
60
        HttpSession ses = fillInSession(request);
61
        //ses.setAttribute("Products", "");
62
        List<Product> products = (List<Product>)ses.getAttribute("Products");        
63
        products.clear();
64
        return "";
65
    }    
66
    private void updateList(ArrayList updateList, HttpSession ses ){        
67
​
68
        List<Product> products = (List<Product>)ses.getAttribute("Products");
69
        
70
        for (int i = 0; i < updateList.size(); i++)
71
        {
72
            Product updateProduct = (Product)updateList.get(i);
73
            int ProductID = updateProduct.getProductID();                            
74
            for(int j = 0 ; j < products.size() ; j++ ){
75
                Product sessionProduct = products.get(j);
76
                if(ProductID == sessionProduct.getProductID()){
77
                    products.set(j, updateProduct);                    
78
                    break;
79
                }
80
            }
81
        }  
82
        ses.setAttribute("Products", products);        
83
    }    
84
    private int getMaxProductID(List<Product> products){
85
        int max=0;
86
        for(int j = 0 ; j < products.size() ; j++ ){
87
            Product product = products.get(j);
88
            int ProductID = product.getProductID();
89
            if(ProductID > max){
90
                max = ProductID;
91
            }
92
        }        
93
        return max;
94
    }
95
    public void addList(ArrayList addList, HttpSession ses ){            
96
​
97
        List<Product> products = (List<Product>)ses.getAttribute("Products");
98
        int max = getMaxProductID(products);
99
        
100
        for (int i = 0; i < addList.size(); i++)
101
        {
102
            Product addProduct = (Product)addList.get(i);
103
            addProduct.setProductID(max+1+i);
104
            products.add(addProduct);
105
        }          
106
    }    
107
    private void deleteList(ArrayList deleteList, HttpSession ses ){        
108
​
109
        List<Product> products = (List<Product>)ses.getAttribute("Products");
110
        
111
        for (int i = 0; i < deleteList.size(); i++)
112
        {
113
            Product deleteProduct = (Product)deleteList.get(i);
114
            int ProductID = deleteProduct.getProductID();                
115
            
116
            for(int j = 0 ; j < products.size() ; j++ ){
117
                Product sessionProduct = products.get(j);
118
                if(ProductID == sessionProduct.getProductID()){
119
                    products.remove(sessionProduct);
120
                    break;
121
                }
122
            }
123
        }  
124
        ses.setAttribute("Products", products);        
125
    }
126
    @RequestMapping(value="/products/batch", method=RequestMethod.POST)
127
    public @ResponseBody String batch(@RequestParam String list, HttpServletRequest request ){        
128
        HttpSession ses = fillInSession(request);
129
        
130
        Map dlist = deserializeMap(list);
131
        
132
        ArrayList updateList = (ArrayList)dlist.get("updateList");
133
        ArrayList addList = (ArrayList)dlist.get("addList");
134
        ArrayList deleteList = (ArrayList)dlist.get("deleteList");
135
        
136
        this.updateList(updateList, ses);
137
        this.addList(addList, ses);
138
        this.deleteList(deleteList, ses);        
139
        
140
        return serializeMap(dlist);
141
    }
142
    //get all products
143
    @RequestMapping(value="/products/get",method=RequestMethod.GET)
144
    public @ResponseBody String products(ModelMap model, HttpServletRequest request ){
145
        
146
        HttpSession ses = fillInSession(request);        
147
        
148
        List<Product> products = (List<Product>)ses.getAttribute("Products");
149
​
150
        String serializedProducts = serialize(products);
151
        
152
        String responseStr = "{\"data\":" + serializedProducts + "}";        
153
        return responseStr;        
154
    }               
155
}
156
​