In this article we will show how you can implement drag-n-drop between two dashboard elements. It makes more sense to drag and drop records between two grids. In this example we will be using two tables named orders and orders_archive.
This is how it is going to look in the generated application:
CREATE TABLE `orders`(`OrderID` int NOT NULL AUTO\_INCREMENT, `CustomerID` varchar(5) NULL DEFAULT NULL, `EmployeeID` int NULL DEFAULT NULL, `OrderDate` datetime NULL DEFAULT NULL, `RequiredDate` datetime NULL DEFAULT NULL, `ShippedDate` datetime NULL DEFAULT NULL, `ShipVia` int NULL DEFAULT NULL, `Freight` decimal(12,2) NULL DEFAULT 0.00, `ShipName` varchar(40) NULL DEFAULT NULL, `ShipAddress` varchar(60) NULL DEFAULT NULL, `ShipCity` varchar(15) NULL DEFAULT NULL, `ShipRegion` varchar(15) NULL DEFAULT NULL, `ShipPostalCode` varchar(10) NULL DEFAULT NULL, `ShipCountry` varchar(15) NULL DEFAULT NULL, `Complited` tinyint NOT NULL DEFAULT 0, `order` int NULL DEFAULT NULL, PRIMARY KEY (`OrderID`))CHARACTER SET utf8;CREATE TABLE `orders\_archive`(`OrderID` int NOT NULL AUTO\_INCREMENT, `CustomerID` varchar(5) NULL DEFAULT NULL, `EmployeeID` int NULL DEFAULT NULL, `OrderDate` datetime NULL DEFAULT NULL, `RequiredDate` datetime NULL DEFAULT NULL, `ShippedDate` datetime NULL DEFAULT NULL, `ShipVia` int NULL DEFAULT NULL, `Freight` decimal(12,2) NULL DEFAULT 0.00, `ShipName` varchar(40) NULL DEFAULT NULL, `ShipAddress` varchar(60) NULL DEFAULT NULL, `ShipCity` varchar(15) NULL DEFAULT NULL, `ShipRegion` varchar(15) NULL DEFAULT NULL, `ShipPostalCode` varchar(10) NULL DEFAULT NULL, `ShipCountry` varchar(15) NULL DEFAULT NULL, `Complited` tinyint NOT NULL DEFAULT 0, `order` int NULL DEFAULT NULL, PRIMARY KEY (`OrderID`))CHARACTER SET utf8;
2. Create a dashboard in PHPRunner or ASPRunner.NET and add those two tables there.
Things to change:
– var connectTable should contain the name of another table. In orders table code it should say “orders_archive” and vice versa.
– “dashboard_dashboard.php” URL of the dashboard page. In ASPRunner.NET it will be something like dashboard
– “OrderID” – name of the key column of the current table.
$("[data-grid-message]").hide();$("[data-location='grid']").show();var connectTable = "orders\_archive";if(pageObj.dashboard){ // to initialize a plugin we will use a container where there grid is placed on the dashboard var panel = $( " > tbody",pageObj.gridElem ).parents(".panel-body"); panel.sortable({ connectWith: "#dashelement\_"+connectTable+"\_grid"+pageObj.dashboard.id+" .panel-body", items: 'tbody .r-gridrow', helper: 'clone', opacity: 0.6, stop:function(e,ui){ if ($(e.target).has(ui.item).length) $(e.target).sortable("cancel"); }, receive: function( e, ui ) { // we moved a row from another grid var OrderID = ui.item.find("[data-fieldname='OrderID']").find("span").html(),order = []; // make a post with from and to table names and also with the OrderID that needs to be updated $.post("dashboard\_dashboard.php",{a:"replaceRow",from:connectTable, to:pageObj.tName,OrderID:OrderID},function(response){console.log(response); }); // Update sort order as well. Not necessary but it looks better this way $.each($(" > tbody",pageObj.gridElem).find(".r-gridrow"),function(i,row){ var orderId = $(row).find("[data-fieldname='OrderID']").find("span").html(); order.push({OrderID:orderId,order:(i+1)}); }); $.post("dashboard\_dashboard.php",{order:order,table:pageObj.tName}); /* update 'Displaying n - n of n' for both tables */ /* decrease count */ updateDetails( $("#dashelement\_"+connectTable+"\_grid"+ pageObj.dashboard.id+" [data-itemid='details\_found']") ,-1); /* increase count */ updateDetails( $("#dashelement\_"+pageObj.tName+"\_grid"+ pageObj.dashboard.id+" [data-itemid='details\_found']") ,1); } }); function updateDetails(details,number){ var html\_details = details.html(), result\_details = /Displaying (\d+) - (\d+) of (\d+)/g.exec(html\_details), dispay = result\_details[2],of = result\_details[3]; html\_details = html\_details.replace(/- (\d+)/g,"- "+(parseInt(dispay) + number)); html\_details = html\_details.replace(/of (\d+)/g,"of "+(parseInt(of) + number)); details.html(html\_details); }};
4. Dashboard page: BeforeProcess event
In this event we add a new record to the target table and delete the same in the source table. The second section of this code helps us to update the sort order so
PHP code
if( postvalue("a") === "replaceRow"){$fromRs = DB::Select(postvalue("from"),array("OrderID" => postvalue("OrderID")));$selectError = DB::lastError();$fromRow = $fromRs->fetchAssoc();if($fromRow){DB::Insert(postvalue("to"),$fromRow);DB::Delete(postvalue("from"),array("OrderID" => postvalue("OrderID")));}exit();}// update sort order if requiredif( postvalue("order") ){$order = postvalue("order") ;foreach($order as $orderInfo){DB::Update(postvalue("table"),array("order" => $orderInfo["order"]), array("OrderID" => $orderInfo["OrderID"]));}exit();}
C# code
if(XVar.Equals(XVar.Pack(MVCFunctions.postvalue(new XVar("a"))), XVar.Pack("replaceRow"))){dynamic fromRow = null, fromRs = null, selectError = null;fromRs = XVar.Clone(DB.Select((XVar)(MVCFunctions.postvalue(new XVar("from"))), (XVar)(new XVar("OrderID", MVCFunctions.postvalue(new XVar("OrderID"))))));selectError = XVar.Clone(DB.lastError());fromRow = XVar.Clone(fromRs.fetchAssoc());if(XVar.Pack(fromRow)){DB.Insert((XVar)(MVCFunctions.postvalue(new XVar("to"))), (XVar)(fromRow));DB.Delete((XVar)(MVCFunctions.postvalue(new XVar("from"))), (XVar)(new XVar("OrderID", MVCFunctions.postvalue(new XVar("OrderID")))));}MVCFunctions.ob\_flush();HttpContext.Current.Response.End();throw new RunnerInlineOutputException();}if(XVar.Pack(MVCFunctions.postvalue(new XVar("order")))){dynamic order = XVar.Array();order = XVar.Clone(MVCFunctions.postvalue(new XVar("order")));foreach (KeyValuePair<XVar, dynamic> orderInfo in order.GetEnumerator()){DB.Update((XVar)(MVCFunctions.postvalue(new XVar("table"))), (XVar)(new XVar("order", orderInfo.Value["order"])), (XVar)(new XVar("OrderID", orderInfo.Value["OrderID"])));}MVCFunctions.ob\_flush();HttpContext.Current.Response.End();throw new RunnerInlineOutputException();}return null;
Enjoy!