7.10. Creating Secondary Modules

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();
    }
}