Firebird Documentation IndexFirebird 3.0 Developer's GuideCreating an Application with jOOQ and Spring MVC → Creating Secondary Modules
Firebird Home Firebird Home Prev: Creating the Primary ModulesFirebird Documentation IndexUp: Creating an Application with jOOQ and Spring MVCNext: The Result

Creating Secondary Modules

Table of Contents

Invoice Items
InvoiceManager Class
Invoice Controller Class
Displaying the Invoices

A secondary module typically contains many more records than a primary one and new records are added frequently. Most secondary tables contain a field with the record creation date. In order to reduce the amount of retrieved data, the notion of a work period is often incorporated to limit the range of data sent to the client. A work period is a range of dates for which the records are required. The work period is described by the WorkingPeriod class, defined via the workingPeriod bean in the ru.ibase.fbjavaex.config.JooqConfig configuration class.

package ru.ibase.fbjavaex.config;

import java.sql.Timestamp;
import java.time.LocalDateTime;

/**
 * Working period
 *
 * @author Simonov Denis
 */
public class WorkingPeriod {

    private Timestamp beginDate;
    private Timestamp endDate;

    /**
     * Constructor
     */
    WorkingPeriod() {
        // in real applications is calculated from the current date
        this.beginDate = Timestamp.valueOf("2015-06-01 00:00:00");
        this.endDate = Timestamp.valueOf(LocalDateTime.now().plusDays(1));
    }

    /**
     * Returns the start date of the work period
     *
     * @return
     */
    public Timestamp getBeginDate() {
        return this.beginDate;
    }

    /**
     * Returns the end date of the work period
     *
     * @return
     */
    public Timestamp getEndDate() {
        return this.endDate;
    }

    /**
     * Setting the start date of the work period
     *
     * @param value
     */
    public void setBeginDate(Timestamp value) {
        this.beginDate = value;
    }

    /**
     * Setting the end date of the work period
     *
     * @param value
     */
    public void setEndDate(Timestamp value) {
        this.endDate = value;
    }

    /**
     * Setting the working period
     *
     * @param beginDate
     * @param endDate
     */
    public void setRangeDate(Timestamp beginDate, Timestamp endDate) {
        this.beginDate = beginDate;
        this.endDate = endDate;
    }
}
      

In our project we have only one secondary module called "Invoices". An invoice consists of a header where some general attributes are described (number, date, customer …) and one or more invoice items (product name, quantity, price, etc.). The invoice header is displayed in the main grid while items can be viewed in a detail grid that is opened with a click on the "+" icon of the selected document.

We implement a class, inherited from the ru.ibase.fbjavaex.jqgrid.JqGrid abstract class described earlier, for viewing the invoice headers via jqGrid. Searching can be by customer name or invoice date and reversible date order is supported, too.

package ru.ibase.fbjavaex.jqgrid;

import java.sql.*;
import org.jooq.*;

import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import ru.ibase.fbjavaex.config.WorkingPeriod;

import static ru.ibase.fbjavaex.exampledb.Tables.INVOICE;
import static ru.ibase.fbjavaex.exampledb.Tables.CUSTOMER;

/**
 * Grid handler for the invoice journal
 *
 * @author Simonov Denis
 */
public class JqGridInvoice extends JqGrid {

    @Autowired(required = true)
    private WorkingPeriod workingPeriod;

    /**
     * Adding a search condition
     *
     * @param query
     */
    private void makeSearchCondition(SelectQuery<?> query) {
        // adding a search condition to the query,
        // if it is produced for different fields,
        // different comparison operators are available when searching.
        if (this.searchString.isEmpty()) {
            return;
        }

        if (this.searchField.equals("CUSTOMER_NAME")) {
            switch (this.searchOper) {
               case "eq": // equal
                 query.addConditions(CUSTOMER.NAME.eq(this.searchString));
                 break;
               case "bw": // starting with
                 query.addConditions(CUSTOMER.NAME.startsWith(this.searchString));
                 break;
               case "cn": // containing
                 query.addConditions(CUSTOMER.NAME.contains(this.searchString));
                 break;
            }
        }
        if (this.searchField.equals("INVOICE_DATE")) {
            Timestamp dateValue = Timestamp.valueOf(this.searchString);

            switch (this.searchOper) {
               case "eq": // =
                 query.addConditions(INVOICE.INVOICE_DATE.eq(dateValue));
                 break;
               case "lt": // <
                 query.addConditions(INVOICE.INVOICE_DATE.lt(dateValue));
                 break;
               case "le": // <=
                 query.addConditions(INVOICE.INVOICE_DATE.le(dateValue));
                 break;
               case "gt": // >
                 query.addConditions(INVOICE.INVOICE_DATE.gt(dateValue));
                 break;
               case "ge": // >=
                 query.addConditions(INVOICE.INVOICE_DATE.ge(dateValue));
                 break;
            }
        }
    }

    /**
     * Returns the total number of records
     *
     * @return
     */
    @Override
    public int getCountRecord() {
        SelectFinalStep<?> select
                = dsl.selectCount()
                     .from(INVOICE)
                     .where(INVOICE.INVOICE_DATE.between(
                                this.workingPeriod.getBeginDate(),
                                this.workingPeriod.getEndDate()));

        SelectQuery<?> query = select.getQuery();

        if (this.searchFlag) {
            makeSearchCondition(query);
        }

        return (int) query.fetch().getValue(0, 0);
    }



    /**
     * Returns the list of invoices
     *
     * @return
     */
    @Override
    public List<Map<String, Object>> getRecords() {
        SelectFinalStep<?> select = dsl.select(
                INVOICE.INVOICE_ID,
                INVOICE.CUSTOMER_ID,
                CUSTOMER.NAME.as("CUSTOMER_NAME"),
                INVOICE.INVOICE_DATE,
                INVOICE.PAID,
                INVOICE.TOTAL_SALE)
            .from(INVOICE)
            .innerJoin(CUSTOMER).on(CUSTOMER.CUSTOMER_ID.eq(INVOICE.CUSTOMER_ID))
            .where(INVOICE.INVOICE_DATE.between(
                       this.workingPeriod.getBeginDate(),
                       this.workingPeriod.getEndDate()));

        SelectQuery<?> query = select.getQuery();
        // add a search condition
        if (this.searchFlag) {
            makeSearchCondition(query);
        }
        // add sorting
        if (this.sIdx.equals("INVOICE_DATE")) {
            switch (this.sOrd) {
               case "asc":
                 query.addOrderBy(INVOICE.INVOICE_DATE.asc());
                 break;
               case "desc":
                 query.addOrderBy(INVOICE.INVOICE_DATE.desc());
                 break;
            }
        }
        // limit the number of records and add an offset
        if (this.limit != 0) {
            query.addLimit(this.limit);
        }
        if (this.offset != 0) {
            query.addOffset(this.offset);
        }

        return query.fetchMaps();
    }
}
      

Invoice Items

We make the class for viewing the invoice items via jqGrid a little simpler. Its records are filtered by invoice header code and user-driven search and sort options are not implemented.

package ru.ibase.fbjavaex.jqgrid;

import org.jooq.*;

import java.util.List;
import java.util.Map;

import static ru.ibase.fbjavaex.exampledb.Tables.INVOICE_LINE;
import static ru.ibase.fbjavaex.exampledb.Tables.PRODUCT;

/**
 * The grid handler for the invoice items
 *
 * @author Simonov Denis
 */
public class JqGridInvoiceLine extends JqGrid {

    private int invoiceId;


    public int getInvoiceId() {
        return this.invoiceId;
    }

    public void setInvoiceId(int invoiceId) {
        this.invoiceId = invoiceId;
    }

    /**
     * Returns the total number of records
     *
     * @return
     */
    @Override
    public int getCountRecord() {
        SelectFinalStep<?> select
            = dsl.selectCount()
                 .from(INVOICE_LINE)
                 .where(INVOICE_LINE.INVOICE_ID.eq(this.invoiceId));

        SelectQuery<?> query = select.getQuery();

        return (int) query.fetch().getValue(0, 0);
    }


    /**
     * Returns invoice items
     *
     * @return
     */
    @Override
    public List<Map<String, Object>> getRecords() {
        SelectFinalStep<?> select = dsl.select(
                INVOICE_LINE.INVOICE_LINE_ID,
                INVOICE_LINE.INVOICE_ID,
                INVOICE_LINE.PRODUCT_ID,
                PRODUCT.NAME.as("PRODUCT_NAME"),
                INVOICE_LINE.QUANTITY,
                INVOICE_LINE.SALE_PRICE,
                INVOICE_LINE.SALE_PRICE.mul(INVOICE_LINE.QUANTITY).as("TOTAL"))
            .from(INVOICE_LINE)
            .innerJoin(PRODUCT).on(PRODUCT.PRODUCT_ID.eq(INVOICE_LINE.PRODUCT_ID))
            .where(INVOICE_LINE.INVOICE_ID.eq(this.invoiceId));

        SelectQuery<?> query = select.getQuery();
        return query.fetchMaps();
    }
}
        

InvoiceManager Class

The ru.ibase.fbjavaex.managers.InvoiceManager class is a kind of business layer that will be used to direct adding, editing and deleting invoices and their items, along with invoice payment. All operations in this layer will be performed in a SNAPSHOT transaction. We have chosen to have our application perform all of the invoice management options in this class by calling stored procedures. It is not mandatory to do it this way, of course. It is just one option.

package ru.ibase.fbjavaex.managers;


import java.sql.Timestamp;
import org.jooq.DSLContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Isolation;

import static ru.ibase.fbjavaex.exampledb.Sequences.GEN_INVOICE_ID;
import static ru.ibase.fbjavaex.exampledb.Routines.spAddInvoice;
import static ru.ibase.fbjavaex.exampledb.Routines.spEditInvoice;
import static ru.ibase.fbjavaex.exampledb.Routines.spPayForInovice;
import static ru.ibase.fbjavaex.exampledb.Routines.spDeleteInvoice;
import static ru.ibase.fbjavaex.exampledb.Routines.spAddInvoiceLine;
import static ru.ibase.fbjavaex.exampledb.Routines.spEditInvoiceLine;
import static ru.ibase.fbjavaex.exampledb.Routines.spDeleteInvoiceLine;

/**
 * Invoice manager
 *
 * @author Simonov Denis
 */
public class InvoiceManager {

    @Autowired(required = true)
    private DSLContext dsl;

    /**
     * Add invoice
     *
     * @param customerId
     * @param invoiceDate
     */
    @Transactional(propagation = Propagation.REQUIRED,
                   isolation = Isolation.REPEATABLE_READ)
    public void create(Integer customerId,
                       Timestamp invoiceDate) {
        int invoiceId = this.dsl.nextval(GEN_INVOICE_ID).intValue();

        spAddInvoice(this.dsl.configuration(),
            invoiceId,
            customerId,
            invoiceDate);
    }

    /**
     * Edit invoice
     *
     * @param invoiceId
     * @param customerId
     * @param invoiceDate
     */
    @Transactional(propagation = Propagation.REQUIRED,
                   isolation = Isolation.REPEATABLE_READ)
    public void edit(Integer invoiceId,
                     Integer customerId,
                     Timestamp invoiceDate) {
        spEditInvoice(this.dsl.configuration(),
            invoiceId,
            customerId,
            invoiceDate);
    }

    /**
     * Payment of invoices
     *
     * @param invoiceId
     */
    @Transactional(propagation = Propagation.REQUIRED,
                   isolation = Isolation.REPEATABLE_READ)
    public void pay(Integer invoiceId) {
        spPayForInovice(this.dsl.configuration(),
            invoiceId);
    }

    /**
     * Delete invoice
     *
     * @param invoiceId
     */
    @Transactional(propagation = Propagation.REQUIRED,
                   isolation = Isolation.REPEATABLE_READ)
    public void delete(Integer invoiceId) {
        spDeleteInvoice(this.dsl.configuration(),
            invoiceId);
    }

    /**
     * Add invoice item
     *
     * @param invoiceId
     * @param productId
     * @param quantity
     */
    @Transactional(propagation = Propagation.REQUIRED,
                   isolation = Isolation.REPEATABLE_READ)
    public void addInvoiceLine(Integer invoiceId,
                               Integer productId,
                               Integer quantity) {
        spAddInvoiceLine(this.dsl.configuration(),
            invoiceId,
            productId,
            quantity);
    }

    /**
     * Edit invoice item
     *
     * @param invoiceLineId
     * @param quantity
     */
    @Transactional(propagation = Propagation.REQUIRED,
                   isolation = Isolation.REPEATABLE_READ)
    public void editInvoiceLine(Integer invoiceLineId,
                                Integer quantity) {
        spEditInvoiceLine(this.dsl.configuration(),
            invoiceLineId,
            quantity);
    }

    /**
     * Delete invoice item
     *
     * @param invoiceLineId
     */
    @Transactional(propagation = Propagation.REQUIRED,
                   isolation = Isolation.REPEATABLE_READ)
    public void deleteInvoiceLine(Integer invoiceLineId) {
        spDeleteInvoiceLine(this.dsl.configuration(),
            invoiceLineId);
    }
}
        

Invoice Controller Class

Table of Contents

Working with Dates in Java

Now we move on to writing the controller. The input point of our controller will be the index method, that is responsible for displaying the JSP page (view). This page contains the layout for displaying the grid and the tool and navigation bars.

Data for displaying invoice headers are loaded asynchronously by the jqGrid component (the path is /invoice/getdata). The getData method is connected with this path, similarly to the primary modules. Invoice items are returned by the getDetailData method (the path is /invoice/getdetaildata). The primary key of the invoice whose detail grid is currently open is passed to this method.

The methods implemented are addInvoice, editInvoice, deleteInvoice, payInvoice for invoice headers and addInvoiceLine, editInvoiceLine, deleteInvoiceLine for invoice line items.

package ru.ibase.fbjavaex.controllers;

import java.sql.Timestamp;
import java.util.HashMap;
import java.util.Map;
import java.util.Date;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.beans.PropertyEditorSupport;

import javax.ws.rs.core.MediaType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.InitBinder;
import org.springframework.web.bind.WebDataBinder;
import ru.ibase.fbjavaex.jqgrid.JqGridInvoice;
import ru.ibase.fbjavaex.jqgrid.JqGridInvoiceLine;

import ru.ibase.fbjavaex.managers.InvoiceManager;

import ru.ibase.fbjavaex.jqgrid.JqGridData;


/**
 * Invoice controller
 *
 * @author Simonov Denis
 */
@Controller
public class InvoiceController {

    @Autowired(required = true)
    private JqGridInvoice invoiceGrid;

    @Autowired(required = true)
    private JqGridInvoiceLine invoiceLineGrid;

    @Autowired(required = true)
    private InvoiceManager invoiceManager;

    /**
     * Describe how a string is converted to a date
     * from the input parameters of the HTTP request
     *
     * @param binder
     */
    @InitBinder
    public void initBinder(WebDataBinder binder)   {
        binder.registerCustomEditor(Timestamp.class,
                new PropertyEditorSupport() {
            @Override
            public void setAsText(String value) {
              try {
                if ((value == null) || (value.isEmpty())) {
                  setValue(null);
                } else {
                  Date parsedDate = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss")
                       .parse(value);
                  setValue(new Timestamp(parsedDate.getTime()));
                }
              } catch (ParseException e) {
                throw new java.lang.IllegalArgumentException(value);
              }
            }
        });
    }

    /**
     * Default action
     * Returns the JSP name of the page (view) to display
     *
     * @param map
     * @return JSP page name
     */
    @RequestMapping(value = "/invoice/", method = RequestMethod.GET)
    public String index(ModelMap map) {

        return "invoice";
    }

    /**
     * Returns a list of invoices in JSON format for jqGrid
     *
     * @param rows number of entries per page
     * @param page current page number
     * @param sIdx sort field
     * @param sOrd sorting order
     * @param search search flag
     * @param searchField search field
     * @param searchString search value
     * @param searchOper comparison operation
     * @param filters filter
     * @return
     */
    @RequestMapping(value = "/invoice/getdata",
            method = RequestMethod.GET,
            produces = MediaType.APPLICATION_JSON)
    @ResponseBody
    public JqGridData getData(
            @RequestParam(value = "rows", required = false,
                          defaultValue = "20") int rows,
            @RequestParam(value = "page", required = false,
                          defaultValue = "1") int page,
            @RequestParam(value = "sidx", required = false,
                          defaultValue = "") String sIdx,
            @RequestParam(value = "sord", required = false,
                          defaultValue = "asc") String sOrd,
            @RequestParam(value = "_search", required = false,
                          defaultValue = "false") Boolean search,
            @RequestParam(value = "searchField", required = false,
                          defaultValue = "") String searchField,
            @RequestParam(value = "searchString", required = false,
                          defaultValue = "") String searchString,
            @RequestParam(value = "searchOper", required = false,
                          defaultValue = "") String searchOper,
            @RequestParam(value = "filters", required = false,
                          defaultValue = "") String filters) {

        if (search) {
            invoiceGrid.setSearchCondition(searchField, searchString, searchOper);
        }
        invoiceGrid.setLimit(rows);
        invoiceGrid.setPageNo(page);

        invoiceGrid.setOrderBy(sIdx, sOrd);

        return invoiceGrid.getJqGridData();
    }

    /**
     * Add invoice
     *
     * @param customerId customer id
     * @param invoiceDate invoice date
     * @return
     */
    @RequestMapping(value = "/invoice/create",
            method = RequestMethod.POST,
            produces = MediaType.APPLICATION_JSON)
    @ResponseBody
    public Map<String, Object> addInvoice(
            @RequestParam(value = "CUSTOMER_ID", required = true,
                          defaultValue = "0") Integer customerId,
            @RequestParam(value = "INVOICE_DATE", required = false,
                          defaultValue = "") Timestamp invoiceDate) {
        Map<String, Object> map = new HashMap<>();
        try {
            invoiceManager.create(customerId, invoiceDate);
            map.put("success", true);
        } catch (Exception ex) {
            map.put("error", ex.getMessage());
        }
        return map;
    }

    /**
     * Edit invoice
     *
     * @param invoiceId invoice id
     * @param customerId customer id
     * @param invoiceDate invoice date
     * @return
     */
    @RequestMapping(value = "/invoice/edit",
            method = RequestMethod.POST,
            produces = MediaType.APPLICATION_JSON)
    @ResponseBody
    public Map<String, Object> editInvoice(
            @RequestParam(value = "INVOICE_ID", required = true,
                          defaultValue = "0") Integer invoiceId,
            @RequestParam(value = "CUSTOMER_ID", required = true,
                          defaultValue = "0") Integer customerId,
            @RequestParam(value = "INVOICE_DATE", required = false,
                          defaultValue = "") Timestamp invoiceDate) {
        Map<String, Object> map = new HashMap<>();
        try {
            invoiceManager.edit(invoiceId, customerId, invoiceDate);
            map.put("success", true);
        } catch (Exception ex) {
            map.put("error", ex.getMessage());
        }
        return map;
    }

    /**
     * Pays an invoice
     *
     * @param invoiceId invoice id
     * @return
     */
    @RequestMapping(value = "/invoice/pay",
            method = RequestMethod.POST,
            produces = MediaType.APPLICATION_JSON)
    @ResponseBody
    public Map<String, Object> payInvoice(
            @RequestParam(value = "INVOICE_ID", required = true,
                          defaultValue = "0") Integer invoiceId) {
        Map<String, Object> map = new HashMap<>();
        try {
            invoiceManager.pay(invoiceId);
            map.put("success", true);
        } catch (Exception ex) {
            map.put("error", ex.getMessage());
        }
        return map;
    }

    /**
     * Delete invoice
     *
     * @param invoiceId invoice id
     * @return
     */
    @RequestMapping(value = "/invoice/delete",
            method = RequestMethod.POST,
            produces = MediaType.APPLICATION_JSON)
    @ResponseBody
    public Map<String, Object> deleteInvoice(
            @RequestParam(value = "INVOICE_ID", required = true,
                          defaultValue = "0") Integer invoiceId) {
        Map<String, Object> map = new HashMap<>();
        try {
            invoiceManager.delete(invoiceId);
            map.put("success", true);
        } catch (Exception ex) {
            map.put("error", ex.getMessage());
        }
        return map;
    }

    /**
     * Returns invoice item
     *
     * @param invoice_id invoice id
     * @return
     */
    @RequestMapping(value = "/invoice/getdetaildata",
            method = RequestMethod.GET,
            produces = MediaType.APPLICATION_JSON)
    @ResponseBody
    public JqGridData getDetailData(
            @RequestParam(value = "INVOICE_ID", required = true) int invoice_id) {

        invoiceLineGrid.setInvoiceId(invoice_id);

        return invoiceLineGrid.getJqGridData();

    }

    /**
     * Add invoice item
     *
     * @param invoiceId invoice id
     * @param productId product id
     * @param quantity quantity of products
     * @return
     */
    @RequestMapping(value = "/invoice/createdetail",
            method = RequestMethod.POST,
            produces = MediaType.APPLICATION_JSON)
    @ResponseBody
    public Map<String, Object> addInvoiceLine(
            @RequestParam(value = "INVOICE_ID", required = true,
                          defaultValue = "0") Integer invoiceId,
            @RequestParam(value = "PRODUCT_ID", required = true,
                          defaultValue = "0") Integer productId,
            @RequestParam(value = "QUANTITY", required = true,
                          defaultValue = "0") Integer quantity) {
        Map<String, Object> map = new HashMap<>();
        try {
            invoiceManager.addInvoiceLine(invoiceId, productId, quantity);
            map.put("success", true);
        } catch (Exception ex) {
            map.put("error", ex.getMessage());
        }
        return map;
    }

    /**
     * Edit invoice item
     *
     * @param invoiceLineId invoice item id
     * @param quantity quantity of products
     * @return
     */
    @RequestMapping(value = "/invoice/editdetail",
            method = RequestMethod.POST,
            produces = MediaType.APPLICATION_JSON)
    @ResponseBody
    public Map<String, Object> editInvoiceLine(
            @RequestParam(value = "INVOICE_LINE_ID", required = true,
                          defaultValue = "0") Integer invoiceLineId,
            @RequestParam(value = "QUANTITY", required = true,
                          defaultValue = "0") Integer quantity) {
        Map<String, Object> map = new HashMap<>();
        try {
            invoiceManager.editInvoiceLine(invoiceLineId, quantity);
            map.put("success", true);
        } catch (Exception ex) {
            map.put("error", ex.getMessage());
        }
        return map;
    }

    /**
     * Delete invoice item
     *
     * @param invoiceLineId invoice item id
     * @return
     */
    @RequestMapping(value = "/invoice/deletedetail",
            method = RequestMethod.POST,
            produces = MediaType.APPLICATION_JSON)
    @ResponseBody
    public Map<String, Object> deleteInvoiceLine(
            @RequestParam(value = "INVOICE_LINE_ID", required = true,
                          defaultValue = "0") Integer invoiceLineId) {
        Map<String, Object> map = new HashMap<>();
        try {
            invoiceManager.deleteInvoiceLine(invoiceLineId);
            map.put("success", true);
        } catch (Exception ex) {
            map.put("error", ex.getMessage());
        }
        return map;
    }
}
        

The invoice controller is very similar to the primary module controllers except for two things:

  1. The controller displays and works with the data of both the main grid and the detail grid
  2. Invoices are filtered by the date field so that only those invoices that are included in the work period are displayed

Working with Dates in Java

Working with dates in Java throws up a few quirks.

The java.sql.Timestamp type in Java supports precision up to nanoseconds whereas the maximum precision of the TIMESTAMP type in Firebird is one ten-thousandth of a second. That is not really a significant problem.

Date and time types in Java support working with time zones. Firebird does not currently support the TIMESTAMP WITH TIMEZONE type. Java works on the assumption that dates in the database are stored in the time zone of the server. However, time will be converted to UTC during serialization into JSON. It must be taken into account when processing time data in JavaScript.

Attention!

Java takes the time offset from its own time zone database, not from the operating system. This practice considerably increases the need to keep up with the latest version of JDK. If you have some old version of JDK installed, working with date and time may be incorrect.

By default, a date is serialized into JSON in as the number of nanoseconds since January 1, 1970, which is not always what is wanted. A date can be serialized into a text representation, by setting to False the date conversion configuration property SerializationFeature.WRITE_DATES_AS_TIMESTAMPS date conversion in the configureMessageConverters method of the WebAppConfig class.

We will return to date processing a little later.

@Configuration
@ComponentScan("ru.ibase.fbjavaex")
@EnableWebMvc
public class WebAppConfig extends WebMvcConfigurerAdapter {

    @Override
    public void configureMessageConverters(
      List<HttpMessageConverter<?>> httpMessageConverters) {
        MappingJackson2HttpMessageConverter jsonConverter =
            new MappingJackson2HttpMessageConverter();
        ObjectMapper objectMapper = new ObjectMapper();
        objectMapper.configure(SerializationFeature.WRITE_DATES_AS_TIMESTAMPS,
                               false);
        jsonConverter.setObjectMapper(objectMapper);
        httpMessageConverters.add(jsonConverter);
    }
…
}
          

The initBinder method of the InvoiceController controller describes how the text representation of a date sent by the browser is converted into a value of type Timestamp.

Displaying the Invoices

The JSP page contains the layout for displaying the grid with invoice headers and the navigation bar. Invoice items are displayed as a drop-down grid when the header of the selected invoice is clicked.

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<c:set var="cp" value="${pageContext.request.servletContext.contextPath}"
       scope="request" />

<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>An example of a Spring MVC application using Firebird and jOOQ</title>

        <!-- Scripts and styles -->
        <%@ include file="../jspf/head.jspf" %>
        <script src="${cp}/resources/js/jqGridProduct.js"></script>
        <script src="${cp}/resources/js/jqGridCustomer.js"></script>
        <script src="${cp}/resources/js/jqGridInvoice.js"></script>
    </head>
    <body>
        <!-- Navigation menu -->
        <%@ include file="../jspf/menu.jspf" %>

        <div class="container body-content">

            <h2>Invoices</h2>

            <table id="jqGridInvoice"></table>
            <div id="jqPagerInvoice"></div>

            <hr />
            <footer>
                <p>© 2016 - An example of a Spring MVC application using
                   Firebird and jOOQ</p>
            </footer>
        </div>

        <script type="text/javascript">
            var invoiceGrid = null;
            $(document).ready(function () {
                invoiceGrid = JqGridInvoice({
                    baseAddress: '${cp}'
                });
            });
        </script>

    </body>
</html>
          

The basic logic on the client side is concentrated in the /resources/js/jqGridInvoice.js JavaScript module.

var JqGridInvoice = (function ($, jqGridProductFactory, jqGridCustomerFactory) {

    return function (options) {
        var jqGridInvoice = {
            dbGrid: null,
            detailGrid: null,
            options: $.extend({
                baseAddress: null
            }, options),
            // return invoice model description
            getInvoiceColModel: function () {
                return [
                    {
                        label: 'Id',
                        name: 'INVOICE_ID', // field name
                        key: true,
                        hidden: true
                    },
                    {
                        label: 'Customer Id'
                        name: 'CUSTOMER_ID',
                        hidden: true,
                        editrules: {edithidden: true, required: true},
                        editable: true,
                        edittype: 'custom', // custom type
                        editoptions: {
                            custom_element: function (value, options) {
                                // add hidden input
                                return $("<input>")
                                        .attr('type', 'hidden')
                                        .attr('rowid', options.rowId)
                                        .addClass("FormElement")
                                        .addClass("form-control")
                                        .val(value)
                                        .get(0);
                            }
                        }
                    },
                    {
                        label: 'Date',
                        name: 'INVOICE_DATE',
                        width: 60,
                        sortable: true,
                        editable: true,
                        search: true,
                        edittype: "text", // input type
                        align: "right",
                        // format as date
                        formatter: jqGridInvoice.dateTimeFormatter,
                        sorttype: 'date', // sort as date
                        formatoptions: {
                            srcformat: 'Y-m-d\TH:i:s', // input format
                            newformat: 'Y-m-d H:i:s'   // output format
                        },
                        editoptions: {
                            // initializing the form element for editing
                            dataInit: function (element) {
                                // creating datepicker
                                $(element).datepicker({
                                    id: 'invoiceDate_datePicker',
                                    dateFormat: 'dd.mm.yy',
                                    minDate: new Date(2000, 0, 1),
                                    maxDate: new Date(2030, 0, 1)
                                });
                            }
                        },
                        searchoptions: {
                            // initializing the form element for searching
                            dataInit: function (element) {
                                // create datepicker
                                $(element).datepicker({
                                    id: 'invoiceDate_datePicker',
                                    dateFormat: 'dd.mm.yy',
                                    minDate: new Date(2000, 0, 1),
                                    maxDate: new Date(2030, 0, 1)
                                });
                            },
                            searchoptions: { // search types
                                sopt: ['eq', 'lt', 'le', 'gt', 'ge']
                            }
                        }
                    },
                    {
                        label: 'Customer',
                        name: 'CUSTOMER_NAME',
                        width: 250,
                        editable: true,
                        edittype: "text",
                        editoptions: {
                            size: 50,
                            maxlength: 60,
                            readonly: true
                        },
                        editrules: {required: true},
                        search: true,
                        searchoptions: {
                            sopt: ['eq', 'bw', 'cn']
                        }
                    },
                    {
                        label: 'Amount',
                        name: 'TOTAL_SALE',
                        width: 60,
                        sortable: false,
                        editable: false,
                        search: false,
                        align: "right",
                        // foramt as currency
                        formatter: 'currency',
                        sorttype: 'number',
                        searchrules: {
                            "required": true,
                            "number": true,
                            "minValue": 0
                        }
                    },
                    {
                        label: 'Paid',
                        name: 'PAID',
                        width: 30,
                        sortable: false,
                        editable: true,
                        search: true,
                        searchoptions: {
                            sopt: ['eq']
                        },
                        edittype: "checkbox",
                        formatter: "checkbox",
                        stype: "checkbox",
                        align: "center",
                        editoptions: {
                            value: "1",
                            offval: "0"
                        }
                    }
                ];
            },
            initGrid: function () {
                // url to retrieve data
                var url = jqGridInvoice.options.baseAddress + '/invoice/getdata';
                jqGridInvoice.dbGrid = $("#jqGridInvoice").jqGrid({
                    url: url,
                    datatype: "json", // data format
                    mtype: "GET", // http request type
                    // model description
                    colModel: jqGridInvoice.getInvoiceColModel(),
                    rowNum: 500, // number of rows displayed
                    loadonce: false, // load only once
                    // default sort by INVOICE_DATE column
                    sortname: 'INVOICE_DATE',
                    sortorder: "desc", // sorting order
                    width: window.innerWidth - 80,
                    height: 500,
                    viewrecords: true, // display the number of entries
                    guiStyle: "bootstrap",
                    iconSet: "fontAwesome",
                    caption: "Invoices",
                    // pagination element
                    pager: '#jqPagerInvoice',
                    subGrid: true, // show subGrid
                    // javascript function to display the child grid
                    subGridRowExpanded: jqGridInvoice.showChildGrid,
                    subGridOptions: {
                        // load only once
                        reloadOnExpand: false,
                        // load the subgrid string only when you click on the "+"
                        selectOnExpand: true
                    }
                });
            },
            // date format function
            dateTimeFormatter: function(cellvalue, options, rowObject) {
                var date = new Date(cellvalue);
                return date.toLocaleString().replace(",", "");
            },
            // returns a template for the editing dialog
            getTemplate: function () {
              var template = "<div style='margin-left:15px;' id='dlgEditInvoice'>";
              template += "<div>{CUSTOMER_ID} </div>";
              template += "<div> Date: </div><div>{INVOICE_DATE}</div>";
              // customer input field with a button
              template += "<div> Customer <sup>*</sup>:</div>";
              template += "<div>";
              template += "<div style='float: left;'>{CUSTOMER_NAME}</div> ";
              template += "<a style='margin-left: 0.2em;' class='btn' ";
              template += "onclick='invoiceGrid.showCustomerWindow(); ";
              template += "return false;'>";
              template += "<span class='glyphicon glyphicon-folder-open'>";
              template += "</span>Select</a> ";
              template += "<div style='clear: both;'></div>";
              template += "</div>";
              template += "<div> {PAID} Paid </div>";
              template += "<hr style='width: 100%;'/>";
              template += "<div> {sData} {cData}  </div>";
              template += "</div>";
              return template;
            },
            // date conversion in UTC
            convertToUTC: function(datetime) {
              if (datetime) {
                  var dateParts = datetime.split('.');
                  var date = dateParts[2].substring(0, 4) + '-' +
                             dateParts[1] + '-' + dateParts[0];
                  var time = dateParts[2].substring(5);
                  if (!time) {
                      time = '00:00:00';
                  }
                  var dt = Date.parse(date + 'T' + time);
                  var s = dt.getUTCFullYear() + '-' +
                          dt.getUTCMonth() + '-' +
                          dt.getUTCDay() + 'T' +
                          dt.getUTCHour() + ':' +
                          dt.getUTCMinute() + ':' +
                          dt.getUTCSecond() + '  GMT';
                  return s;
              } else
                  return null;
            },
            // returns the options for editing invoices
            getEditInvoiceOptions: function () {
              return {
                url: jqGridInvoice.options.baseAddress + '/invoice/edit',
                reloadAfterSubmit: true,
                closeOnEscape: true,
                closeAfterEdit: true,
                drag: true,
                modal: true,
                top: $(".container.body-content").position().top + 150,
                left: $(".container.body-content").position().left + 150,
                template: jqGridInvoice.getTemplate(),
                afterSubmit: jqGridInvoice.afterSubmit,
                editData: {
                  INVOICE_ID: function () {
                    var selectedRow = jqGridInvoice.dbGrid.getGridParam("selrow");
                    var value = jqGridInvoice.dbGrid
                               .getCell(selectedRow, 'INVOICE_ID');
                    return value;
                  },
                  CUSTOMER_ID: function () {
                    return $('#dlgEditInvoice input[name=CUSTOMER_ID]').val();
                  },
                  INVOICE_DATE: function () {
                    var datetime = $('#dlgEditInvoice input[name=INVOICE_DATE]')
                                  .val();
                    return jqGridInvoice.convertToUTC(datetime);
                  }
                }
              };
            },
            // returns options for adding invoices
            getAddInvoiceOptions: function () {
              return {
                url: jqGridInvoice.options.baseAddress + '/invoice/create',
                reloadAfterSubmit: true,
                closeOnEscape: true,
                closeAfterAdd: true,
                drag: true,
                modal: true,
                top: $(".container.body-content").position().top + 150,
                left: $(".container.body-content").position().left + 150,
                template: jqGridInvoice.getTemplate(),
                afterSubmit: jqGridInvoice.afterSubmit,
                editData: {
                  CUSTOMER_ID: function () {
                    return $('#dlgEditInvoice input[name=CUSTOMER_ID]').val();
                  },
                  INVOICE_DATE: function () {
                    var datetime = $('#dlgEditInvoice input[name=INVOICE_DATE]')
                                  .val();
                    return jqGridInvoice.convertToUTC(datetime);
                  }
                }
              };
            },
            // returns the options for deleting invoices
            getDeleteInvoiceOptions: function () {
              return {
                url: jqGridInvoice.options.baseAddress + '/invoice/delete',
                reloadAfterSubmit: true,
                closeOnEscape: true,
                closeAfterDelete: true,
                drag: true,
                msg: "Delete the selected invoice?",
                afterSubmit: jqGridInvoice.afterSubmit,
                delData: {
                  INVOICE_ID: function () {
                    var selectedRow = jqGridInvoice.dbGrid.getGridParam("selrow");
                    var value = jqGridInvoice.dbGrid
                               .getCell(selectedRow, 'INVOICE_ID');
                    return value;
                  }
                }
              };
            },
            initPager: function () {
                // display the navigation bar
                jqGridInvoice.dbGrid.jqGrid('navGrid', '#jqPagerInvoice',
                    {
                         search: true,
                         add: true,
                         edit: true,
                         del: true,
                         view: false,
                         refresh: true,

                         searchtext: "Search",
                         addtext: "Add",
                         edittext: "Edit",
                         deltext: "Delete",
                         viewtext: "View",
                         viewtitle: "Selected record",
                         refreshtext: "Refresh"
                    },
                    jqGridInvoice.getEditInvoiceOptions(),
                    jqGridInvoice.getAddInvoiceOptions(),
                    jqGridInvoice.getDeleteInvoiceOptions()
                );
                // Add a button to pay the invoice
                var urlPay = jqGridInvoice.options.baseAddress + '/invoice/pay';
                jqGridInvoice.dbGrid.navButtonAdd('#jqPagerInvoice',
                    {
                       buttonicon: "glyphicon-usd",
                       title: "Pay",
                       caption: "Pay",
                       position: "last",
                       onClickButton: function () {
                         // get the id of the current record
                         var id = jqGridInvoice.dbGrid.getGridParam("selrow");
                         if (id) {
                           $.ajax({
                               url: urlPay,
                               type: 'POST',
                               data: {INVOICE_ID: id},
                               success: function (data) {
                                   // Check if an error has occurred
                                   if (data.hasOwnProperty("error")) {
                                       jqGridInvoice.alertDialog('??????',
                                                                 data.error);
                                   } else {
                                          // refresh grid
                                       $("#jqGridInvoice").jqGrid(
                                           'setGridParam',
                                           {
                                               datatype: 'json'
                                           }
                                       ).trigger('reloadGrid');
                                   }
                               }
                           });
                         }
                       }
                    }
                );
            },
            init: function () {
                jqGridInvoice.initGrid();
                jqGridInvoice.initPager();
            },
            afterSubmit: function (response, postdata) {
                var responseData = response.responseJSON;
                // Check if an error has occurred
                if (responseData.hasOwnProperty("error")) {
                    if (responseData.error.length) {
                        return [false, responseData.error];
                    }
                } else {
                    // refresh grid
                    $(this).jqGrid(
                            'setGridParam',
                            {
                                datatype: 'json'
                            }
                    ).trigger('reloadGrid');
                }
                return [true, "", 0];
            },
            getInvoiceLineColModel: function (parentRowKey) {
              return [
                  {
                    label: 'Invoice Line ID',
                    name: 'INVOICE_LINE_ID',
                    key: true,
                    hidden: true
                  },
                  {
                    label: 'Invoice ID',
                    name: 'INVOICE_ID',
                    hidden: true,
                    editrules: {edithidden: true, required: true},
                    editable: true,
                    edittype: 'custom',
                    editoptions: {
                      custom_element: function (value, options) {
                          // create hidden input
                          return $("<input>")
                                 .attr('type', 'hidden')
                                 .attr('rowid', options.rowId)
                                 .addClass("FormElement")
                                 .addClass("form-control")
                                 .val(parentRowKey)
                                 .get(0);
                      }
                    }
                  },
                  {
                    label: 'Product ID',
                    name: 'PRODUCT_ID',
                    hidden: true,
                    editrules: {edithidden: true, required: true},
                    editable: true,
                    edittype: 'custom',
                    editoptions: {
                      custom_element: function (value, options) {
                          // create hidden input
                          return $("<input>")
                                 .attr('type', 'hidden')
                                 .attr('rowid', options.rowId)
                                 .addClass("FormElement")
                                 .addClass("form-control")
                                 .val(value)
                                 .get(0);
                      }
                    }
                  },
                  {
                    label: 'Product',
                    name: 'PRODUCT_NAME',
                    width: 300,
                    editable: true,
                    edittype: "text",
                    editoptions: {
                        size: 50,
                        maxlength: 60,
                        readonly: true
                    },
                    editrules: {required: true}
                  },
                  {
                    label: 'Price',
                    name: 'SALE_PRICE',
                    formatter: 'currency',
                    editable: true,
                    editoptions: {
                        readonly: true
                    },
                    align: "right",
                    width: 100
                  },
                  {
                    label: 'Quantity',
                    name: 'QUANTITY',
                    align: "right",
                    width: 100,
                    editable: true,
                    editrules: {required: true, number: true, minValue: 1},
                    editoptions: {
                      dataEvents: [{
                        type: 'change',
                        fn: function (e) {
                          var quantity = $(this).val() - 0;
                          var price =
                            $('#dlgEditInvoiceLine input[name=SALE_PRICE]').val()-0;
                          var total = quantity * price;
                          $('#dlgEditInvoiceLine input[name=TOTAL]').val(total);
                        }
                      }],
                      defaultValue: 1
                    }
                  },
                  {
                    label: 'Total',
                    name: 'TOTAL',
                    formatter: 'currency',
                    align: "right",
                    width: 100,
                    editable: true,
                    editoptions: {
                        readonly: true
                    }
                  }
              ];
            },
            // returns the options for editing the invoice item
            getEditInvoiceLineOptions: function () {
              return {
                url: jqGridInvoice.options.baseAddress + '/invoice/editdetail',
                reloadAfterSubmit: true,
                closeOnEscape: true,
                closeAfterEdit: true,
                drag: true,
                modal: true,
                top: $(".container.body-content").position().top + 150,
                left: $(".container.body-content").position().left + 150,
                template: jqGridInvoice.getTemplateDetail(),
                afterSubmit: jqGridInvoice.afterSubmit,
                editData: {
                  INVOICE_LINE_ID: function () {
                    var selectedRow = jqGridInvoice.detailGrid
                                     .getGridParam("selrow");
                    var value = jqGridInvoice.detailGrid
                               .getCell(selectedRow, 'INVOICE_LINE_ID');
                    return value;
                  },
                  QUANTITY: function () {
                    return $('#dlgEditInvoiceLine input[name=QUANTITY]').val();
                  }
                }
              };
            },
            // returns options for adding an invoice item
            getAddInvoiceLineOptions: function () {
              return {
                url: jqGridInvoice.options.baseAddress + '/invoice/createdetail',
                reloadAfterSubmit: true,
                closeOnEscape: true,
                closeAfterAdd: true,
                drag: true,
                modal: true,
                top: $(".container.body-content").position().top + 150,
                left: $(".container.body-content").position().left + 150,
                template: jqGridInvoice.getTemplateDetail(),
                afterSubmit: jqGridInvoice.afterSubmit,
                editData: {
                  INVOICE_ID: function () {
                    var selectedRow = jqGridInvoice.dbGrid.getGridParam("selrow");
                    var value = jqGridInvoice.dbGrid
                               .getCell(selectedRow, 'INVOICE_ID');
                    return value;
                  },
                  PRODUCT_ID: function () {
                    return $('#dlgEditInvoiceLine input[name=PRODUCT_ID]').val();
                  },
                  QUANTITY: function () {
                    return $('#dlgEditInvoiceLine input[name=QUANTITY]').val();
                  }
                }
              };
            },
            // returns the option to delete the invoice item
            getDeleteInvoiceLineOptions: function () {
              return {
                url: jqGridInvoice.options.baseAddress + '/invoice/deletedetail',
                reloadAfterSubmit: true,
                closeOnEscape: true,
                closeAfterDelete: true,
                drag: true,
                msg: "Delete the selected item?",
                afterSubmit: jqGridInvoice.afterSubmit,
                delData: {
                  INVOICE_LINE_ID: function () {
                    var selectedRow = jqGridInvoice.detailGrid
                                     .getGridParam("selrow");
                    var value = jqGridInvoice.detailGrid
                               .getCell(selectedRow, 'INVOICE_LINE_ID');
                    return value;
                  }
                }
              };
            },
            // Event handler for the parent grid expansion event
            // takes two parameters: the parent record identifier
            // and the primary record key
            showChildGrid: function (parentRowID, parentRowKey) {
                var childGridID = parentRowID + "_table";
                var childGridPagerID = parentRowID + "_pager";
                // send the primary key of the parent record
                // to filter the entries of the invoice items
                var childGridURL = jqGridInvoice.options.baseAddress
                                 + '/invoice/getdetaildata';
                childGridURL = childGridURL + "?INVOICE_ID="
                             + encodeURIComponent(parentRowKey);
                // add HTML elements to display the table and page navigation
                // as children for the selected row in the master grid
                $('<table>')
                        .attr('id', childGridID)
                        .appendTo($('#' + parentRowID));
                $('<div>')
                        .attr('id', childGridPagerID)
                        .addClass('scroll')
                        .appendTo($('#' + parentRowID));
                // create and initialize the child grid
                jqGridInvoice.detailGrid = $("#" + childGridID).jqGrid({
                    url: childGridURL,
                    mtype: "GET",
                    datatype: "json",
                    page: 1,
                    colModel: jqGridInvoice.getInvoiceLineColModel(parentRowKey),
                    loadonce: false,
                    width: '100%',
                    height: '100%',
                    guiStyle: "bootstrap",
                    iconSet: "fontAwesome",
                    pager: "#" + childGridPagerID
                });
                // displaying the toolbar
                $("#" + childGridID).jqGrid(
                    'navGrid', '#' + childGridPagerID,
                    {
                        search: false,
                        add: true,
                        edit: true,
                        del: true,
                        refresh: true
                    },
                    jqGridInvoice.getEditInvoiceLineOptions(),
                    jqGridInvoice.getAddInvoiceLineOptions(),
                    jqGridInvoice.getDeleteInvoiceLineOptions()
                );
            },
            // returns a template for the invoice item editor
            getTemplateDetail: function () {
              var template = "<div style='margin-left:15px;' ";
              template += "id='dlgEditInvoiceLine'>";
              template += "<div>{INVOICE_ID} </div>";
              template += "<div>{PRODUCT_ID} </div>";
              // input field with a button
              template += "<div> Product <sup>*</sup>:</div>";
              template += "<div>";
              template += "<div style='float: left;'>{PRODUCT_NAME}</div> ";
              template += "<a style='margin-left: 0.2em;' class='btn' ";
              template += "onclick='invoiceGrid.showProductWindow(); ";
              template += "return false;'>";
              template += "<span class='glyphicon glyphicon-folder-open'>";
              template += "</span> Select</a> ";
              template += "<div style='clear: both;'></div>";
              template += "</div>";
              template += "<div> Quantity: </div><div>{QUANTITY} </div>";
              template += "<div> Price: </div><div>{SALE_PRICE} </div>";
              template += "<div> Total: </div><div>{TOTAL} </div>";
              template += "<hr style='width: 100%;'/>";
              template += "<div> {sData} {cData}  </div>";
              template += "</div>";
              return template;
            },
            // Display selection window from the goods directory.
            showProductWindow: function () {
              var dlg = $('<div>')
                        .attr('id', 'dlgChooseProduct')
                        .attr('aria-hidden', 'true')
                        .attr('role', 'dialog')
                        .attr('data-backdrop', 'static')
                        .css("z-index", '2000')
                        .addClass('modal')
                        .appendTo($('body'));

              var dlgContent = $("<div>")
                        .addClass("modal-content")
                        .css('width', '760px')
                        .appendTo($('<div>')
                                .addClass('modal-dialog')
                                .appendTo(dlg));

              var dlgHeader = $('<div>').addClass("modal-header")
                                          .appendTo(dlgContent);
              $("<button>")
                        .addClass("close")
                        .attr('type', 'button')
                        .attr('aria-hidden', 'true')
                        .attr('data-dismiss', 'modal')
                        .html("×")
                        .appendTo(dlgHeader);
              $("<h5>").addClass("modal-title")
                         .html("Select product")
                         .appendTo(dlgHeader);
              var dlgBody = $('<div>')
                        .addClass("modal-body")
                        .appendTo(dlgContent);
              var dlgFooter = $('<div>').addClass("modal-footer")
                                          .appendTo(dlgContent);
              $("<button>")
                .attr('type', 'button')
                .addClass('btn')
                .html('OK')
                .on('click', function () {
                    var rowId = $("#jqGridProduct")
                               .jqGrid("getGridParam", "selrow");
                    var row = $("#jqGridProduct")
                             .jqGrid("getRowData", rowId);
                    $('#dlgEditInvoiceLine input[name=PRODUCT_ID]')
                        .val(row["PRODUCT_ID"]);
                    $('#dlgEditInvoiceLine input[name=PRODUCT_NAME]')
                        .val(row["NAME"]);
                    $('#dlgEditInvoiceLine input[name=SALE_PRICE]')
                        .val(row["PRICE"]);
                    var price = $('#dlgEditInvoiceLine input[name=SALE_PRICE]')
                        .val()-0;
                    var quantity = $('#dlgEditInvoiceLine input[name=QUANTITY]')
                        .val()-0;
                    var total = Math.round(price * quantity * 100) / 100;
                    $('#dlgEditInvoiceLine input[name=TOTAL]').val(total);
                    dlg.modal('hide');
                })
                .appendTo(dlgFooter);

              $("<button>")
                    .attr('type', 'button')
                    .addClass('btn')
                    .html('Cancel')
                    .on('click', function () {
                        dlg.modal('hide');
                    })
                    .appendTo(dlgFooter);

              $('<table>')
                    .attr('id', 'jqGridProduct')
                    .appendTo(dlgBody);
              $('<div>')
                    .attr('id', 'jqPagerProduct')
                    .appendTo(dlgBody);

              dlg.on('hidden.bs.modal', function () {
                    dlg.remove();
              });
              dlg.modal();

              jqGridProductFactory({
                  baseAddress: jqGridInvoice.options.baseAddress
              });
            },
            // Display the selection window from the customer's directory.
            showCustomerWindow: function () {
                // the main block of the dialog
                var dlg = $('<div>')
                        .attr('id', 'dlgChooseCustomer')
                        .attr('aria-hidden', 'true')
                        .attr('role', 'dialog')
                        .attr('data-backdrop', 'static')
                        .css("z-index", '2000')
                        .addClass('modal')
                        .appendTo($('body'));
                // block with the contents of the dialog
                var dlgContent = $("<div>")
                        .addClass("modal-content")
                        .css('width', '730px')
                        .appendTo($('<div>')
                                .addClass('modal-dialog')
                                .appendTo(dlg));
                // block with dialog header
                var dlgHeader = $('<div>').addClass("modal-header")
                                          .appendTo(dlgContent);
                // button "X" for closing
                $("<button>")
                        .addClass("close")
                        .attr('type', 'button')
                        .attr('aria-hidden', 'true')
                        .attr('data-dismiss', 'modal')
                        .html("×")
                        .appendTo(dlgHeader);
                // title of dialog
                $("<h5>").addClass("modal-title")
                         .html("Select customer")
                         .appendTo(dlgHeader);
                // body of dialog
                var dlgBody = $('<div>')
                        .addClass("modal-body")
                        .appendTo(dlgContent);
                // footer of dialog
                var dlgFooter = $('<div>').addClass("modal-footer")
                                          .appendTo(dlgContent);
                // "OK" button
                $("<button>")
                    .attr('type', 'button')
                    .addClass('btn')
                    .html('OK')
                    .on('click', function () {
                        var rowId = $("#jqGridCustomer")
                                   .jqGrid("getGridParam", "selrow");
                        var row = $("#jqGridCustomer")
                                 .jqGrid("getRowData", rowId);
                        // Keep the identifier and the name of the customer
                        // in the input elements of the parent form.
                        $('#dlgEditInvoice input[name=CUSTOMER_ID]')
                            .val(rowId);
                        $('#dlgEditInvoice input[name=CUSTOMER_NAME]')
                            .val(row["NAME"]);
                        dlg.modal('hide');
                    })
                    .appendTo(dlgFooter);
                // "Cancel" button
                $("<button>")
                        .attr('type', 'button')
                        .addClass('btn')
                        .html('Cancel')
                        .on('click', function () {
                            dlg.modal('hide');
                        })
                        .appendTo(dlgFooter);
                // add a table to display the customers in the body of the dialog
                $('<table>')
                        .attr('id', 'jqGridCustomer')
                        .appendTo(dlgBody);
                // add the navigation bar
                $('<div>')
                        .attr('id', 'jqPagerCustomer')
                        .appendTo(dlgBody);
                dlg.on('hidden.bs.modal', function () {
                    dlg.remove();
                });
                // display dialog
                dlg.modal();
                jqGridCustomerFactory({
                    baseAddress: jqGridInvoice.options.baseAddress
                });
            },
            // A window for displaying the error.
            alertDialog: function (title, error) {
                var alertDlg = $('<div>')
                        .attr('aria-hidden', 'true')
                        .attr('role', 'dialog')
                        .attr('data-backdrop', 'static')
                        .addClass('modal')
                        .appendTo($('body'));
                var dlgContent = $("<div>")
                        .addClass("modal-content")
                        .appendTo($('<div>')
                                .addClass('modal-dialog')
                                .appendTo(alertDlg));
                var dlgHeader = $('<div>').addClass("modal-header")
                                          .appendTo(dlgContent);
                $("<button>")
                        .addClass("close")
                        .attr('type', 'button')
                        .attr('aria-hidden', 'true')
                        .attr('data-dismiss', 'modal')
                        .html("×")
                        .appendTo(dlgHeader);
                $("<h5>").addClass("modal-title")
                         .html(title)
                         .appendTo(dlgHeader);
                $('<div>')
                        .addClass("modal-body")
                        .appendTo(dlgContent)
                        .append(error);
                alertDlg.on('hidden.bs.modal', function () {
                    alertDlg.remove();
                });
                alertDlg.modal();
            }
        };
        jqGridInvoice.init();
        return jqGridInvoice;
    };
})(jQuery, JqGridProduct, JqGridCustomer);
        

Displaying and Editing Invoice Lines

In the invoice module, the main grid is used to display headers and the detail grid, opened with a click, is used to display invoice items. For the child grid to be displayed, the True value is assigned to the subGrid property. The child grid is displayed using the subGridRowExpanded event connected with the showChildGrid method.

The items are filtered by the primary key of the invoice. Along with the main buttons on the navigation bar, a custom button for paying for the invoice is added to the invoice header using the jqGridInvoice.dbGrid.navButtonAdd function (see the initPager method).

Dialog Boxes

Dialog boxes for editing secondary modules are much more complicated than their primary counterparts. They often use options selected from other modules. For that reason, these edit dialog boxes cannot be built automatically using jqGrid. However, this library has an option to build dialog boxes using templates, which we use.

The dialog box template is returned by the getTemplate function. The invoiceGrid.showCustomerWindow() function opens the customer module for selecting a customer. It uses the functions of the JqGridCustomer module described earlier. After the customer is selected in the modal window, its key is inserted into the CUSTOMER_ID field. Fields that are to be sent to the server using pre-processing or from hidden fields are described in the editData property of the Edit and Add options.

Processing Dates

To get back to processing dates: as we already know, the InvoiceController controller returns the date in UTC. Because we want to display it in the current time zone, we specify the jqGridInvoice.dateTimeFormatter date formatting function via the formatter property of the corresponding INVOICE_DATE field.

When sending data to the server, we need the reverse operation—convert time from the current time zone to UTC. The convertToUTC function is responsible for that.

The custom template returned by the getTemplateDetail function is also used for editing invoice items. The invoiceGrid.showProductWindow() function opens a window for selecting a product from the product list. This function uses the functions of the JqGridProduct module.

The code for the JqGridInvoice module contains detailed comments and more explanation so that you can understand the logic of its workings.

Prev: Creating the Primary ModulesFirebird Documentation IndexUp: Creating an Application with jOOQ and Spring MVCNext: The Result
Firebird Documentation IndexFirebird 3.0 Developer's GuideCreating an Application with jOOQ and Spring MVC → Creating Secondary Modules