Drag-and-drop between dashboard elements

ASP.NET, PHP, Tutorials

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:

1. SQL query that creates required tables in the database

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.

3. The following code goes to Javascript OnLoad event of both orders and orders_archive table List Pages.

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 required

if( 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!

Drag-and-drop between dashboard elements

Leave a Reply

Sign up for the free email newsletter for news about our
database publishing software, tips, and more.

Resources

Company

Related posts:

Leave a Reply