Reordering invoice lines using jqGrid and TableDND extension

Wednesday, July 22, 2009

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.

jqGrid and 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

You lose points for: 1) Using " in JavaScript. This is a VERY poor practice. 2) Using "var" for non-anonymous types. This is a WORST practice. 3) Making all your properties virtual. That's a horrible architecture blasphemy. You should have your MVP removed.

Jeff Tsung - Wednesday, September 23, 2009 8:39:17 PM

Thanks for comments, Jeff. I completely disagree with you in every point. Can you explain your opinions or is it just flame? :)

Until you get done with opinions I kindly keep my MVP.

DigiMortal - Wednesday, September 23, 2009 9:10:24 PM

Thanks for this post, it helped me out a lot because I don't know jQuery well enough yet.

Try this for the re-ordering. It only changes those that need to be updated.
//get the line to change
var lineToChange = invoice.Lines.SingleOrDefault(line => line.LineNo == oldPosition;

if(oldPosition == newPosition)
return; // nothing to change
IEnumerable changingLines; // a list of all lines to be changed;
int offset = 0;
if (oldPosition (line.LineNo oldPosition));
offset = -1;
}
else
{
//new position is higher in the list (closer to 1)
//i.e. old pos was 8, new pos is 3 so we need to add 1 to everything from positions 3 to 7
changingLines = invoice.Lines.Where(line => (line.LineNo >= newPosition) && (line.LineNo line.LineNo += offset).ToArray();
// if these lines are reversed, due to deferred execution,
//the changingLines query will be affected.
lineToChange.LineNo = newPosition;

Greg Gorman - Thursday, October 1, 2009 6:25:50 PM

If i use below function I am getting Loading.......

gridComplete: function() {
jQuery("#list").tableDnDUpdate();
}

Please help me on this regard

Naresh - Friday, October 30, 2009 7:42:05 AM

Invoice lines

Grid

Adding support for lines reordering

Links

4 Comments