5.10.1. Controllers for Invoices

The controller of the invoice module must be able to return data for both invoice headers and the associated invoice lines. The same applies to the methods for adding, editing and deleting records.

  1. [Authorize(Roles = "manager")]
  2. public class InvoiceController : Controller
  3. {
  4.   private DbModel db = new DbModel();
  6.   // display view
  7.   public ActionResult Index()
  8.   {
  9.     return View();
  10.   }
  12.   // Receiving data in the JSON format for the main grid
  13.   public ActionResult GetData(int? rows, int? page, string sidx, string sord,
  14. string searchField, string searchString, string searchOper)
  15.   {
  16.     // get the page number, the number of data displayed
  17.     int pageNo = page ?? 1;
  18.     int limit = rows ?? 20;
  19.     // calculate offset
  20.     int offset = (pageNo - 1) * limit;
  21.     // building a request for receipt of invoices
  22.     var invoicesQuery =
  23.         from invoice in db.INVOICES
  24.         where (invoice.INVOICE_DATE >= AppVariables.StartDate) &&
  25.               (invoice.INVOICE_DATE <= AppVariables.FinishDate)
  26.         select new
  27.         {
  28.           INVOICE_ID = invoice.INVOICE_ID,
  29.           CUSTOMER_ID = invoice.CUSTOMER_ID,
  30.           CUSTOMER_NAME = invoice.CUSTOMER.NAME,
  31.           INVOICE_DATE = invoice.INVOICE_DATE,
  32.           TOTAL_SALE = invoice.TOTAL_SALE,
  33.           PAID = invoice.PAID
  34.         };
  35.     // adding a search condition to the query, if it is produced
  36.     // for different fields, different comparison operators
  37.     // are available when searching
  38.     if (searchField == "CUSTOMER_NAME")
  39.     {
  40.       switch (searchOper)
  41.       {
  42.         case "eq": // equal
  43.           invoicesQuery = invoicesQuery.Where(
  44.           c => c.CUSTOMER_NAME == searchString);
  45.           break;
  46.         case "bw": // starting with
  47.           invoicesQuery = invoicesQuery.Where(
  48.           c => c.CUSTOMER_NAME.StartsWith(searchString));
  49.           break;
  50.         case "cn": // containing
  51.           invoicesQuery = invoicesQuery.Where(
  52.           c => c.CUSTOMER_NAME.Contains(searchString));
  53.           break;
  54.       }
  55.     }
  56.     if (searchField == "INVOICE_DATE")
  57.     {
  58.       var dateValue = DateTime.Parse(searchString);
  59.       switch (searchOper)
  60.       {
  61.         case "eq": // =
  62.           invoicesQuery = invoicesQuery.Where(
  63.           c => c.INVOICE_DATE == dateValue);
  64.           break;
  65.         case "lt": // <
  66.           invoicesQuery = invoicesQuery.Where(
  67.           c => c.INVOICE_DATE < dateValue);
  68.           break;
  69.         case "le": // <=
  70.           invoicesQuery = invoicesQuery.Where(
  71.           c => c.INVOICE_DATE <= dateValue);
  72.           break;
  73.         case "gt": // >
  74.           invoicesQuery = invoicesQuery.Where(
  75.           c => c.INVOICE_DATE > dateValue);
  76.           break;
  77.         case "ge": // >=
  78.           invoicesQuery = invoicesQuery.Where(
  79.           c => c.INVOICE_DATE >= dateValue);
  80.           break;
  81.       }
  82.     }
  83.     if (searchField == "PAID")
  84.     {
  85.       int iVal = (searchString == "on") ? 1 : 0;
  86.       invoicesQuery = invoicesQuery.Where(c => c.PAID == iVal);
  87.     }
  88.     // get the total number of invoices
  89.     int totalRows = invoicesQuery.Count();
  90.     // add sorting
  91.     switch (sord)
  92.     {
  93.       case "asc":
  94.         invoicesQuery = invoicesQuery.OrderBy(
  95.         invoice => invoice.INVOICE_DATE);
  96.         break;
  97.       case "desc":
  98.         invoicesQuery = invoicesQuery.OrderByDescending(
  99.         invoice => invoice.INVOICE_DATE);
  100.         break;
  101.     }
  102.     // get invoice list
  103.     var invoices = invoicesQuery
  104.        .Skip(offset)
  105.        .Take(limit)
  106.        .ToList();
  107.     // calculate the total number of pages
  108.     int totalPages = totalRows / limit + 1;
  109.     // create the result for jqGrid
  110.     var result = new
  111.       {
  112.         page = pageNo,
  113.         total = totalPages,
  114.         records = totalRows,
  115.         rows = invoices
  116.       };
  117.     // convert the result to JSON
  118.     return Json(result, JsonRequestBehavior.AllowGet);
  119.   }
  121.   // Receiving data in the form of JSON for the detail grid
  122.   public ActionResult GetDetailData(int? invoice_id)
  123.   {
  124.     // build a LINQ query for receiving invoice items
  125.     // filtered by invoice id
  126.     var lines =
  127.         from line in db.INVOICE_LINES
  128.         where line.INVOICE_ID == invoice_id
  129.         select new
  130.       {
  131.         INVOICE_LINE_ID = line.INVOICE_LINE_ID,
  132.         INVOICE_ID = line.INVOICE_ID,
  133.         PRODUCT_ID = line.PRODUCT_ID,
  134.         Product = line.PRODUCT.NAME,
  135.         Quantity = line.QUANTITY,
  136.         Price = line.SALE_PRICE,
  137.         Total = line.QUANTITY * line.SALE_PRICE
  138.       };
  139.     // get invoice position list
  140.     var invoices = lines
  141.         .ToList();
  142.     // create the result for jqGrid
  143.     var result = new
  144.     {
  145.       rows = invoices
  146.     };
  147.     // convert the result to JSON
  148.     return Json(result, JsonRequestBehavior.AllowGet);
  149.   }
  151.   // Add new invoice
  152.   [HttpPost]
  153.   [ValidateAntiForgeryToken]
  154.   public ActionResult Create(
  155.   [Bind(Include = "CUSTOMER_ID,INVOICE_DATE")] INVOICE invoice)
  156.   {
  157.     // check the correctness of the model
  158.     if (ModelState.IsValid)
  159.     {
  160.       try
  161.       {
  162.         var INVOICE_ID = new FbParameter("INVOICE_ID", FbDbType.Integer);
  163.         var CUSTOMER_ID = new FbParameter("CUSTOMER_ID", FbDbType.Integer);
  164.         var INVOICE_DATE = new FbParameter("INVOICE_DATE",
  165.                                FbDbType.TimeStamp);
  166.         // initialize parameters query
  167.         INVOICE_ID.Value = db.NextValueFor("GEN_INVOICE_ID");
  168.         CUSTOMER_ID.Value = invoice.CUSTOMER_ID;
  169.         INVOICE_DATE.Value = invoice.INVOICE_DATE;
  170.         // execute stored procedure
  171.         db.Database.ExecuteSqlCommand(
  172.           "EXECUTE PROCEDURE SP_ADD_INVOICE(@INVOICE_ID, @CUSTOMER_ID, @INVOICE_DATE)",
  173.           INVOICE_ID,
  174.           CUSTOMER_ID,
  175.           INVOICE_DATE);
  176.         // return success in JSON format
  177.         return Json(true);
  178.       }
  179.       catch (Exception ex)
  180.       {
  181.         // return error in JSON format
  182.         return Json(new { error = ex.Message });
  183.       }
  184.     }
  185.     else {
  186.       string messages = string.Join("; ", ModelState.Values
  187.                        .SelectMany(x => x.Errors)
  188.                        .Select(x => x.ErrorMessage));
  189.       // return error in JSON format
  190.       return Json(new { error = messages });
  191.     }
  192.   }
  194.   // Edit invoice
  195.   [HttpPost]
  196.   [ValidateAntiForgeryToken]
  197.   public ActionResult Edit(
  198.   [Bind(Include = "INVOICE_ID,CUSTOMER_ID,INVOICE_DATE")] INVOICE invoice)
  199.   {
  200.     // check the correctness of the model
  201.     if (ModelState.IsValid)
  202.     {
  203.       try
  204.       {
  205.         var INVOICE_ID = new FbParameter("INVOICE_ID", FbDbType.Integer);
  206.         var CUSTOMER_ID = new FbParameter("CUSTOMER_ID", FbDbType.Integer);
  207.         var INVOICE_DATE = new FbParameter("INVOICE_DATE",
  208.                                            FbDbType.TimeStamp);
  209.         // initialize parameters query
  210.         INVOICE_ID.Value = invoice.INVOICE_ID;
  211.         CUSTOMER_ID.Value = invoice.CUSTOMER_ID;
  212.         INVOICE_DATE.Value = invoice.INVOICE_DATE;
  213.         // execute stored procedure
  214.         db.Database.ExecuteSqlCommand(
  215.           "EXECUTE PROCEDURE SP_EDIT_INVOICE(@INVOICE_ID, @CUSTOMER_ID, @INVOICE_DATE)",
  216.           INVOICE_ID,
  217.           CUSTOMER_ID,
  218.           INVOICE_DATE);
  219.         // return success in JSON format
  220.         return Json(true);
  221.       }
  222.       catch (Exception ex)
  223.       {
  224.         // return error in JSON format
  225.         return Json(new { error = ex.Message });
  226.       }
  227.     }
  228.     else {
  229.       string messages = string.Join("; ", ModelState.Values
  230.                        .SelectMany(x => x.Errors)
  231.                        .Select(x => x.ErrorMessage));
  232.       // return error in JSON format
  233.       return Json(new { error = messages });
  234.     }
  235.   }
  237.   // Delete invoice
  238.   [HttpPost]
  239.   [ValidateAntiForgeryToken]
  240.   public ActionResult Delete(int id)
  241.   {
  242.     try
  243.     {
  244.       var INVOICE_ID = new FbParameter("INVOICE_ID", FbDbType.Integer);
  245.       // initialize parameters query
  246.       INVOICE_ID.Value = id;
  247.       // execute stored procedure
  248.       db.Database.ExecuteSqlCommand(
  249.         "EXECUTE PROCEDURE SP_DELETE_INVOICE(@INVOICE_ID)",
  250.         INVOICE_ID);
  251.       // return success in JSON format
  252.       return Json(true);
  253.     }
  254.     catch (Exception ex)
  255.     {
  256.       // return error in JSON format
  257.       return Json(new { error = ex.Message });
  258.     }
  259.   }
  261.   // Payment of invoice
  262.   [HttpPost]
  263.   [ValidateAntiForgeryToken]
  264.   public ActionResult Pay(int id)
  265.   {
  266.     try
  267.     {
  268.       var INVOICE_ID = new FbParameter("INVOICE_ID", FbDbType.Integer);
  269.       // initialize parameters query
  270.       INVOICE_ID.Value = id;
  271.       // execute stored procedure
  272.       db.Database.ExecuteSqlCommand(
  273.         "EXECUTE PROCEDURE SP_PAY_FOR_INOVICE(@INVOICE_ID)",
  274.         INVOICE_ID);
  275.       // return success in JSON format
  276.       return Json(true);
  277.     }
  278.     catch (Exception ex)
  279.     {
  280.       // return error in JSON format
  281.       return Json(new { error = ex.Message });
  282.     }
  283.   }
  285.   // Add invoice position
  286.   [HttpPost]
  287.   [ValidateAntiForgeryToken]
  288.   public ActionResult CreateDetail(
  289.   [Bind(Include = "INVOICE_ID,PRODUCT_ID,QUANTITY")] INVOICE_LINE invoiceLine)
  290.   {
  291.     // check the correctness of the model
  292.     if (ModelState.IsValid)
  293.     {
  294.       try
  295.       {
  296.         var INVOICE_ID = new FbParameter("INVOICE_ID", FbDbType.Integer);
  297.         var PRODUCT_ID = new FbParameter("PRODUCT_ID", FbDbType.Integer);
  298.         var QUANTITY = new FbParameter("QUANTITY", FbDbType.Integer);
  299.         // initialize parameters query
  300.         INVOICE_ID.Value = invoiceLine.INVOICE_ID;
  301.         PRODUCT_ID.Value = invoiceLine.PRODUCT_ID;
  302.         QUANTITY.Value = invoiceLine.QUANTITY;
  303.         // execute stored procedure
  304.         db.Database.ExecuteSqlCommand(
  305.           ""EXECUTE PROCEDURE SP_ADD_INVOICE_LINE(@INVOICE_ID, @PRODUCT_ID, @QUANTITY)",
  306.           INVOICE_ID,
  307.           PRODUCT_ID,
  308.           QUANTITY);
  309.         // return success in JSON format
  310.         return Json(true);
  311.       }
  312.       catch (Exception ex)
  313.       {
  314.         // return error in JSON format
  315.         return Json(new { error = ex.Message });
  316.       }
  317.     }
  318.     else {
  319.       string messages = string.Join("; ", ModelState.Values
  320.                        .SelectMany(x => x.Errors)
  321.                        .Select(x => x.ErrorMessage));
  322.       // return error in JSON format
  323.       return Json(new { error = messages });
  324.     }
  325.   }
  327.   // Edit invoice position
  328.   [HttpPost]
  329.   [ValidateAntiForgeryToken]
  330.   public ActionResult EditDetail(
  331.   [Bind(Include = "INVOICE_LINE_ID,INVOICE_ID,PRODUCT_ID,QUANTITY")]
  332.     INVOICE_LINE invoiceLine)
  333.   {
  334.     // check the correctness of the model
  335.     if (ModelState.IsValid)
  336.     {
  337.       try
  338.       {
  339.         // Create parameters
  340.         var INVOICE_LINE_ID = new FbParameter("INVOICE_LINE_ID",
  341.                                               FbDbType.Integer);
  342.         var QUANTITY = new FbParameter("QUANTITY", FbDbType.Integer);
  343.         // initialize parameters query
  344.         INVOICE_LINE_ID.Value = invoiceLine.INVOICE_LINE_ID;
  345.         QUANTITY.Value = invoiceLine.QUANTITY;
  346.         // execute stored procedure
  347.         db.Database.ExecuteSqlCommand(
  348.           "EXECUTE PROCEDURE SP_EDIT_INVOICE_LINE(@INVOICE_LINE_ID, @QUANTITY)",
  349.           INVOICE_LINE_ID,
  350.           QUANTITY);
  351.         // return success in JSON format
  352.         return Json(true);
  353.       }
  354.       catch (Exception ex)
  355.       {
  356.         // return error in JSON format
  357.         return Json(new { error = ex.Message });
  358.       }
  359.     }
  360.     else {
  361.       string messages = string.Join("; ", ModelState.Values
  362.                        .SelectMany(x => x.Errors)
  363.                        .Select(x => x.ErrorMessage));
  364.       // return error in JSON format
  365.       return Json(new { error = messages });
  366.     }
  367.   }
  369.   // Delete invoice position
  370.   [HttpPost]
  371.   [ValidateAntiForgeryToken]
  372.   public ActionResult DeleteDetail(int id)
  373.   {
  374.     try
  375.     {
  376.       // create parameters
  377.       var INVOICE_LINE_ID = new FbParameter("INVOICE_LINE_ID",
  378.                                             FbDbType.Integer);
  379.       // initialize parameters query
  380.       INVOICE_LINE_ID.Value = id;
  381.       // execute stored procedure
  382.       db.Database.ExecuteSqlCommand(
  383.         "EXECUTE PROCEDURE SP_DELETE_INVOICE_LINE(@INVOICE_LINE_ID)",
  384.         INVOICE_LINE_ID);
  385.       // return success in JSON format
  386.       return Json(true);
  387.     }
  388.     catch (Exception ex)
  389.     {
  390.       // return error in JSON format
  391.       return Json(new { error = ex.Message });
  392.     }
  393.   }
  395.   protected override void Dispose(bool disposing)
  396.   {
  397.     if (disposing)
  398.     {
  399.       db.Dispose();
  400.     }
  401.     base.Dispose(disposing);
  402.   }
  403. }

The GetDetailData method for retrieving the list of lines in an invoice lacks the code for page-by-page navigation. Realistically, a typical invoice does not have enough lines to justify using page-by-page navigation for them. Omitting it simplifies and speeds up the code.

In our project, all data modification operations are performed in stored procedures, but you could do the same work using Entity Framework. DDL code for the stored procedures can be found in the database creation script in an earlier chapter and also in the .zip archives of all the DDL scripts:

https://github.com/sim1984/example-db_2_5/archive/1.0.zip
or https://github.com/sim1984/example-db_3_0/archive/1.0.zip