Gunnar Peipman's ASP.NET blog

ASP.NET, C#, SharePoint, SQL Server and general software development topics.

Sponsors

News

 
 
 
DZone MVB

Links

Social

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.

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


kick it on DotNetKicks.com pimp it vote it on WebDevVote.com Progg it Shout it

Comments

Jeff Tsung said:

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.

# September 23, 2009 4:39 PM

DigiMortal said:

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.

# September 23, 2009 5:10 PM

Greg Gorman said:

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<InvoiceLine> changingLines; // a list of all lines to be changed;

int offset = 0;  

if (oldPosition < newPosition)

{

//Position 1 being at the top, new position is lower in the list (closer to Count())

//i.e. old pos is 3, new pos is 8 so we need to subtract 1 from everything from positions 4 to 8

changingLines = invoice.Lines.Where(line => (line.LineNo <= newPosition) && (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 < oldPosition));

offset = 1;

}

if (changingAssignments == null)

return;  //really?  maybe throw an exception

//set the new order

//handle deferred execution by calling ToArray

changingLines.Select(line => line.LineNo += offset).ToArray();

// if these lines are reversed, due to deferred execution,

//the changingLines query will be affected.

lineToChange.LineNo = newPosition;

# October 1, 2009 2:25 PM

Naresh said:

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

gridComplete: function() {

     jQuery("#list").tableDnDUpdate();

 }

Please help me on this regard

# October 30, 2009 3:42 AM