Reordering invoice lines using jqGrid and TableDND extension

In one of my ASP.NET MVC applications I needed flexible interface for inserting invoice lines. Sometimes invoice lines are inserted in incorrect order and it saves accountants some time if they are able to change the order of invoice lines quickly. In my application I used jqGrid with TableDND extension. Here’s how I got it work.

In the end of this posting I will provide you also links so you get all the required stuff and guiding materials to get started with jQuery and jqGrid in ASP.NET.

Invoice lines

As first thing let’s see invoice line class and example screenshot. The class given here is simple toy to illustrate row reordering and not to go deep to invoice line modeling and implementation questions. Let’s start with screenshot.

Invoice lines

And here is my simple InvoiceLine class that contains no complex business logic.


public class InvoiceLine
{
   
public virtual int Id { get; set
; }
   
public virtual decimal Amount { get; set
; }
   
public virtual string Description { get; set
; }
   
public virtual decimal DiscountPercent { get; set
; }
   
public virtual decimal DiscountSum { get; set
; }
   
public virtual int LineNo { get; set
; }
   
public virtual Invoice ParentInvoice { get; set
; }
   
public virtual string Text { get; set
; }
   
public virtual string Unit { get; set
; }
   
public virtual decimal UnitPrice { get; set
; }
   
public virtual decimal VatPercent { get; set
; }

   
public virtual decimal
Sum
    {
       
get
        {
           
return Amount * UnitPrice * (1 + VatPercent / 100);
        }
    }
}

Grid

Here is the definition of my grid (it is defined in invoices detail view). Who is not familiar with selectors stuff and AJAX I give a little explanation. First block, HTML with divs, defines table where data is show and pager. JavaScrtipt below attaches jqGrid to table and pager and defines some properties and functions. When grid is created it makes request to /accounting/invoice/INVOICE_ID/lines to get invoice lines in JSON format.


<div>
    <table id="list" class="scroll"></table>
    <div id="pager" class="scroll"></div
>
</
div>
 
<script type="text/javascript">
    $(document).ready(function
() {
        jQuery(
"#list"
).jqGrid({
            url:
'/accounting/invoice/<%= Model.Invoice.Id %>/lines'
,
    datatype:
'json'
,
    mtype:
'GET'
,
    colNames: [
'Id', 'No', 'Text', 'Amount', 'Unit', 'Unit Price'
,
             
'VAT %', 'Discount %', 'Discount Sum', 'Sum'
],
    colModel: [
      {
          name:
'Id', index: 'Id', editable: true
,
          width: 40, align:
'left'
      },
      {
          name:
'No', index: 'No', editable: true
,
          width: 40, align:
'left'
      },
      {
          name:
'Text', index: 'Text', editable: true
,
          width: 350, align:
'left'
      },
      {
          name:
'Amount', index: 'Amount', editable: true
,
          width: 50, align:
'left'
      },
      {
          name:
'Unit', index: 'Unit', editable: true
,
          width: 50, align:
'left'
      },
      {
          name:
'Unit Price', index: 'UnitPrice', editable: true
,
          width: 120, align:
'left'
      },
      {
          name:
'VAT %', index: 'VATPercent', editable: true
,
          width: 60, align:
'left'
      },
      {
          name:
'Discount %', index: 'DiscountPercent'
,
          editable:
true, width: 80, align: 'left'
      },
      {
          name:
'Discount Sum', index: 'DiscountSum', editable: true
,
          width: 80, align:
'left'
      },
      {
          name:
'Sum', index: 'Sum', editable: true
, width: 80,
          align:
'left'
      }
    ],
    pager: jQuery(
'#pager'
),
    rowNum: 10,
    rowList: [5, 10, 20, 50],
    sortname:
'Id'
,
    sortorder:
"desc"
,
    viewrecords:
true
,
    imgpath:
'/scripts/themes/steel/images'
,
    caption:
'Invoice Lines'
,
    editurl:
'/accounting/invoice/<%= Model.Invoice.Id %>/save'
,
  gridComplete:
function
() {
      jQuery(
"#list"
).tableDnDUpdate();
  }
}).navGrid(
'#pager'
,
      { view:
false, search: false, refresh: false }, //options
      {}, // edit options
      {}, // add options
      {}, // del options
      {} // search options
  );
});

</script
>

Invoice lines grid gets its data from InvoiceController. This is my MVC controller that provides actions for invoices. Here is the controller action InvoiceLines that returns invoice lines to invoice detail view. This method is for the same request that jqGrid makes to get data. The code here is not nice one but it works.


public ActionResult InvoiceLines(int id)
{
   
var repository = Resolver.Resolve<IInvoiceRepository
>();
   
var
invoice = repository.GetById(id);


   
var
lines = invoice.Lines;
   
if (lines == null
)
        lines =
new List<InvoiceLine
>();

   
var rowArray = new object
[lines.Count];
   
var
i = 0;

   
foreach (var line in
lines)
    {
        i++;

       
var values = new
[] {
                    line.Id.ToString(),
                    line.LineNo.ToString(),
                    line.Text,
                    line.Amount.ToString(
"0.00"
),
                   
"h"
,
                    line.UnitPrice.ToString(
"c"
),
                   
"20"
,
                   
"0"
,
                   
"0"
,
                    line.Sum.ToString(
"c"
)
                };
       
var row = new
{ id = i, cell = values };
        rowArray[i - 1] = row;
    }

   
var jsonData = new
    {
        total = 1,
        page = 1,
        records = 3,
        rows = rowArray
    };
   
return Json(jsonData);
}

Adding support for lines reordering

You saw some code that is behind this nice invoice lines grid. Let’s add now lines reordering functionality. We use TableDND extension for jqGrid. It is possible that this extension is not included in jquery.jqGrid.js file by default. It it is not there just add this line to this file (same place where other extensions are defined):

{ include: true, incfile: ‘jquery.tablednd.js’, minfile: ‘min/grid.tablednd-min.js’ }

Now let’s go back to our invoice details view. Now we add the following javaScript to $(document).ready function, right before jQuery("#list").jqGrid() call.


jQuery("#list").tableDnD({
        onDrop:
function
(table, row) {
           
var
oldIndex = row.id;
           
var
newIndex = row.rowIndex;
           
if
(oldIndex == newIndex)
               
return
;

            $.post(
'/accounting/invoice/<%= Model.Invoice.Id %>/changeroworder'
,
        {
            oldPosition: oldIndex,
            newPosition: newIndex
        },
       
function
(result) {
            $(
"#list").trigger("reloadGrid");
        });
    }
});

Here we are doing some little mystery. We define onDrop function that is triggered when row is dropped. In this function we control if row was moved and if it is we send this information to server. In server we have controller action that takes old and new positions of row and reorders invoice lines.

Last piece of code is function that checks the results of controller action call. This way or other we have to reload rows to get same state as in server. Of course, we can optimize our code and avoid another request by controlling the returned result. If it is okay then we can change line numbers on client side.

Controller action that reorders invoice lines is as follows.


[AcceptVerbs(HttpVerbs.Post)]
public ActionResult ChangeRowOrder(int invoiceId, int oldPosition, int
newPosition)
{
   
var repository = Resolver.Resolve<IInvoiceRepository
>();
    repository.ChangeLineOrder(invoiceId, oldPosition, newPosition);
    Response.Write(
"OK"
);
   
return null;
}

And here is invoice repository ChangeLineOrder() method.


public void ChangeLineOrder(int oldPosition, int newPosition)
{
   
var invoice = GetById(1); // changedLine.ParentInvoice;

   
var
moveUp = (newPosition < oldPosition);
   
var
transaction = _session.BeginTransaction();

   
foreach (var line in
invoice.Lines)
    {
       
if
(line.LineNo == oldPosition)
        {
            line.LineNo = newPosition;
           
continue
;
        }
       
if
(moveUp)
           
if
(line.LineNo < newPosition || line.LineNo > oldPosition)
               
continue
;
           
else
                line.LineNo++;
       
else
            if
(line.LineNo > newPosition || line.LineNo < oldPosition)
               
continue
;
           
else
                line.LineNo--;
    }
    Save(invoice);
    transaction.Commit();
}

So, that’s it guys. You can change the order of invoice lines by mouse now and new numbers are assigned to invoice lines behind the curtains. For me, the most valuable part here is the last method that reorders invoice lines. All that is above it was pretty simple to get work.

Links


Leave a Reply

Your email address will not be published. Required fields are marked *