Firebird Documentation IndexFirebird 3.0 Developer's GuideDeveloping Firebird Applications in Delphi → Creating a Secondary Module
Firebird Home Firebird Home Prev: Creating the Primary ModulesFirebird Documentation IndexUp: Developing Firebird Applications in DelphiNext: The Result

Creating a Secondary Module

Table of Contents

The Transactions for Invoice Data
A Filter for the Data
Configuring the Module
Doing the Work
The Invoice Details

Secondary datasets typically contain larger numbers of records than primary datasets and new records are added frequently. Our application will have only one secondary module, named “Invoices”.

An invoice consists of a header where some general attributes are described (number, date, customer …) and invoice lines with the list of products, their quantities, prices, etc. It is convenient to have two grids for such documents: the main one (master) showing the data invoice header data and the detail one showing the invoice lines.

We want to place two TDBGrid components on the invoice form and link a separate TDataSource to each of them that will be linked to its respective TFDQuery. In our project, the dataset with the invoice headers (the master set) will be called qryInvoice, and the one with the invoice lines (the detail set) will be called qryInvoiceLine.

The Transactions for Invoice Data

The Transaction property of each dataset will specify the read-only transaction trRead that is located in the dmInvoicedata module. Use the UpdateTransaction property to specify the trWrite transaction and the Connection property to specify the connection located in the main data module.

A Filter for the Data

Secondary datasets usually contain a field with the record creation date. In order to reduce the amount of retrieved data, a notion such as “a work period” is commonly incorporated in the application to filter the set of data sent to the client. A work period is a range of dates for which the records are required.

Since the application could have more than one secondary dataset, it makes sense to add variables containing the start and end dates of a work period to the global dmMain data module that is used by all modules working with the database in one way or another. Once the application is started, the work period could be defined by the start and end dates of the current quarter, or some other appropriate start/end date pair. The application could allow the user to change the work period while working with the application.

Configuring the Module

Figure 3.6. The Invoice form tab

The Invoice form tab


Figure 3.7. The Invoice data module tab

The Invoice data module tab


Since the latest invoices are the most requested ones, it makes sense to sort them by date in reverse order. The query will look like this in the SQL property of the qryInvoice dataset:

SELECT
  invoice.invoice_id AS invoice_id,
  invoice.customer_id AS customer_id,
  customer.NAME AS customer_name,
  invoice.invoice_date AS invoice_date,
  invoice.total_sale AS total_sale,
  IIF(invoice.payed=1, 'Yes', 'No') AS payed
FROM
  invoice
  JOIN customer ON customer.customer_id = invoice.customer_id
WHERE invoice.invoice_date BETWEEN :date_begin AND :date_end
ORDER BY invoice.invoice_date DESC
        

To open this dataset, it will be necessary to initialise the query parameters:

qryInvoice.ParamByName('date_begin').AsSqlTimeStamp := dmMain.BeginDateSt;
qryInvoice.ParamByName('date_end').AsSqlTimeStamp := dmMain.EndDateSt;
qryInvoice.Open;
        

For the purpose of illustration, we will use stored procedures to perform all operations on an invoice. Regular INSERT/UPDATE/DELETE queries can be used when operations are simple and involve writing to only one table in the database. We will execute each stored procedure as a separate query in TFDCommand objects. This component is not descended from TFDRdbmsDataSet, does not buffer data and returns not more than one result row. We are using it because it consumes fewer resources for queries that do not return data.

Since our stored procedures modify data, it is necessary to point the Transaction property of each TFDCommand object to the trWrite transaction.

Tip

Another alternative is to place the stored procedure calls for inserting, editing and adding a record in the corresponding properties of a TFDUpdateSQL object.

Doing the Work

Four operations are provided for working with the invoice header: adding, editing, deleting and setting the “paid” attribute. Once an invoice is paid, we prevent any modifications to either the header or the lines. The rule is implemented at stored procedure level. Let's examine the query strings in the CommandText property for calling the stored procedures.

qryAddInvoice.CommandText

EXECUTE PROCEDURE sp_add_invoice(
  NEXT VALUE FOR gen_invoice_id,
  :CUSTOMER_ID,
  :INVOICE_DATE
)
        

qryEditInvoice.CommandText

EXECUTE PROCEDURE sp_edit_invoice(
  :INVOICE_ID,
  :CUSTOMER_ID,
  :INVOICE_DATE
)
        

qryDeleteInvoice.CommandText

EXECUTE PROCEDURE sp_delete_invoice(:INVOICE_ID)
        

qryPayForInvoice.CommandText

EXECUTE PROCEDURE sp_pay_for_invoice(:invoice_id)
        

Since our stored procedures are not called from a TFDUpdateSQL object, we need to call qryInvoice.Refresh after they are executed, in order to update the data in the grid.

Stored procedures that do not require input data from the user are called as follows:

procedure TdmInvoice.DeleteInvoice;
begin
  // We do everything in a short transaction
  trWrite.StartTransaction;
  try
    qryDeleteInvoice.ParamByName('INVOICE_ID').AsInteger :=
      Invoice.INVOICE_ID.Value;
    qryDeleteInvoice.Execute;
    trWrite.Commit;
    qryInvoice.Refresh;
  except
    on E: Exception do
    begin
      if trWrite.Active then
        trWrite.Rollback;
      raise;
    end;
  end;
end;
        

Getting User Confirmation

Before performing some operations, such as deleting an invoice, we want to get confirmation from the user:

procedure TInvoiceForm.actDeleteInvoiceExecute(Sender: TObject);
begin
  if MessageDlg('Are you sure you want to delete an invoice?',
                mtConfirmation,
                [mbYes, mbNo], 0) = mrYes then
  begin
    Invoices.DeleteInvoice;
  end;
end;
          

Adding or Editing Records

As with the primary modules, we will use modal forms to add a new record or edit an existing one. We will not use data-aware visual components in this implementation. As another variation, we will use a TButtonedEdit component to select a customer. It will display the name of the current customer and open a modal form with a grid for selecting a customer on the click of the embedded button. We could use something like TDBLookupCombobox, of course, but it has drawbacks: first, the customer list may be too large for scrolling comfortably through the drop-down list; secondly, the name alone may not be enough to find the customer you want.

Figure 3.8. The Customer input form

The Customer input form


As the window for selecting a customer, we will use the same modal form that was created for adding customers. The code for the button click handler for the TButtonedEdit component is as follows:

procedure TEditInvoiceForm.edtCustomerRightButtonClick(Sender: TObject);
var
  xSelectForm: TCustomerForm;
begin
  xSelectForm := TCustomerForm.Create(Self);
  try
    xSelectForm.Visible := False;
    if xSelectForm.ShowModal = mrOK then
    begin
      FCustomerId := xSelectForm.Customers.Customer.CUSTOMER_ID.Value;
      edtCustomer.Text := xSelectForm.Customers.Customer.NAME.Value;
    end;
  finally
    xSelectForm.Free;
  end;
end;
          

Since we are not using data-aware visual components, we need to initialize the customer code and name for displaying during the call to the edit form:

procedure TInvoiceForm.actEditInvoiceExecute(Sender: TObject);
var
  xEditorForm: TEditInvoiceForm;
begin
  xEditorForm := TEditInvoiceForm.Create(Self);
  try
    xEditorForm.OnClose := EditInvoiceEditorClose;
    xEditorForm.Caption := 'Edit invoice';
    xEditorForm.InvoiceId := Invoices.Invoice.INVOICE_ID.Value;
    xEditorForm.SetCustomer(
      Invoices.Invoice.CUSTOMER_ID.Value,
      Invoices.Invoice.CUSTOMER_NAME.Value);
    xEditorForm.InvoiceDate := Invoices.Invoice.INVOICE_DATE.AsDateTime;
    xEditorForm.ShowModal;
  finally
    xEditorForm.Free;
  end;
end;

procedure TEditInvoiceForm.SetCustomer(ACustomerId: Integer;
  const ACustomerName: string);
begin
  FCustomerId := ACustomerId;
  edtCustomer.Text := ACustomerName;
end;
          

Adding a new invoice and editing an existing one will be handled in the Close event of the modal form as it is for the primary modules. However, we will not switch the dataset to CachedUpdates mode for these because the updates carried out by stored procedures and we are not using data-aware visual components to capture input.

procedure TInvoiceForm.actAddInvoiceExecute(Sender: TObject);
var
  xEditorForm: TEditInvoiceForm;
begin
  xEditorForm := TEditInvoiceForm.Create(Self);
  try
    xEditorForm.Caption := 'Add invoice';
    xEditorForm.OnClose := AddInvoiceEditorClose;
    xEditorForm.InvoiceDate := Now;
    xEditorForm.ShowModal;
  finally
    xEditorForm.Free;
  end;
end;

procedure TInvoiceForm.AddInvoiceEditorClose(Sender: TObject;
  var Action: TCloseAction);
var
  xEditorForm: TEditInvoiceForm;
begin
  xEditorForm := TEditInvoiceForm(Sender);
  if xEditorForm.ModalResult <> mrOK then
  begin
    Action := caFree;
    Exit;
  end;
  try
    Invoices.AddInvoice(xEditorForm.CustomerId, xEditorForm.InvoiceDate);
    Action := caFree;
  except
    on E: Exception do
    begin
      Application.ShowException(E);
// It does not close the window give the user correct the error
      Action := caNone;
    end;
  end;
end;

procedure TdmInvoice.AddInvoice(ACustomerId: Integer; AInvoiceDate: TDateTime);
begin
  // We do everything in a short transaction
  trWrite.StartTransaction;
  try
    qryAddInvoice.ParamByName('CUSTOMER_ID').AsInteger := ACustomerId;
    qryAddInvoice.ParamByName('INVOICE_DATE').AsSqlTimeStamp :=
    DateTimeToSQLTimeStamp(AInvoiceDate);
    qryAddInvoice.Execute();
    trWrite.Commit;
    qryInvoice.Refresh;
  except
    on E: Exception do
    begin
      if trWrite.Active then
        trWrite.Rollback;
      raise;
    end;
  end;
end;
          

The Invoice Details

Next, we move on to the details of an invoice. For the qryInvoiceLine dataset, we set the MasterSource property to the datasource that is linked to qryInvoice and the MasterFields property to INVOICE_ID. We specify the following query in the SQL property:

SELECT
  invoice_line.invoice_line_id AS invoice_line_id,
  invoice_line.invoice_id AS invoice_id,
  invoice_line.product_id AS product_id,
  product.name AS productname,
  invoice_line.quantity AS quantity,
  invoice_line.sale_price AS sale_price,
  invoice_line.quantity * invoice_line.sale_price AS total
FROM
  invoice_line
  JOIN product ON product.product_id = invoice_line.product_id
WHERE invoice_line.invoice_id = :invoice_id
        

As with the invoice header, we will use stored procedures to perform all modifications. Let's examine the query strings in the CommandText property of the commands that call the stored procedures.

qryAddInvoiceLine.CommandText

EXECUTE PROCEDURE sp_add_invoice_line(
  :invoice_id,
  :product_id,
  :quantity
)
        

qryEditInvoiceLine.CommandText

EXECUTE PROCEDURE sp_edit_invoice_line(
  :invoice_line_id,
  :quantity
)
        

qryDeleteInvoiceLine.CommandText

EXECUTE PROCEDURE sp_delete_invoice_line(
  :invoice_line_id
)
        

As with the header, the form for adding a new record and editing an existing one does not use data-aware visual components. To select a product, we use the TButtonedEdit component again. The code for the on-click handler for the button on the TButtonedEdit object is as follows:

procedure TEditInvoiceLineForm.edtProductRightButtonClick(Sender: TObject);
var
  xSelectForm: TGoodsForm;
begin
  if FEditMode = emInvoiceLineEdit then
    Exit;
  xSelectForm := TGoodsForm.Create(Self);
  try
    xSelectForm.Visible := False;
    if xSelectForm.ShowModal = mrOK then
    begin
      FProductId := xSelectForm.Goods.Product.PRODUCT_ID.Value;
      edtProduct.Text := xSelectForm.Goods.Product.NAME.Value;
      edtPrice.Text := xSelectForm.Goods.Product.PRICE.AsString;
    end;
  finally
    xSelectForm.Free;
  end;
end;
        

Since we are not using data-aware visual components, again we will need to initialize the product code and name and its price for displaying on the edit form.

procedure TInvoiceForm.actEditInvoiceLineExecute(Sender: TObject);
var
  xEditorForm: TEditInvoiceLineForm;
begin
  xEditorForm := TEditInvoiceLineForm.Create(Self);
  try
    xEditorForm.EditMode := emInvoiceLineEdit;
    xEditorForm.OnClose := EditInvoiceLineEditorClose;
    xEditorForm.Caption := 'Edit invoice line';
    xEditorForm.InvoiceLineId := Invoices.InvoiceLine.INVOICE_LINE_ID.Value;
    xEditorForm.SetProduct(
      Invoices.InvoiceLine.PRODUCT_ID.Value,
      Invoices.InvoiceLine.PRODUCTNAME.Value,
      Invoices.InvoiceLine.SALE_PRICE.AsCurrency);
    xEditorForm.Quantity := Invoices.InvoiceLine.QUANTITY.Value;
    xEditorForm.ShowModal;
  finally
    xEditorForm.Free;
  end;
end;

procedure TEditInvoiceLineForm.SetProduct(AProductId: Integer;
  AProductName: string; APrice: Currency);
begin
  FProductId := AProductId;
  edtProduct.Text := AProductName;
  edtPrice.Text := CurrToStr(APrice);
end;
        

We handle adding a new item and editing an existing one in the Close event of the modal form.

procedure TInvoiceForm.actAddInvoiceLineExecute(Sender: TObject);
var
  xEditorForm: TEditInvoiceLineForm;
begin
  xEditorForm := TEditInvoiceLineForm.Create(Self);
  try
xEditorForm.EditMode := emInvoiceLineAdd;
xEditorForm.OnClose := AddInvoiceLineEditorClose;
    xEditorForm.Caption := 'Add invoice line';
    xEditorForm.Quantity := 1;
    xEditorForm.InvoiceId := Invoices.Invoice.INVOICE_ID.Value;
    xEditorForm.ShowModal;
  finally
    xEditorForm.Free;
  end;
end;

procedure TInvoiceForm.actEditInvoiceLineExecute(Sender: TObject);
var
  xEditorForm: TEditInvoiceLineForm;
begin
  xEditorForm := TEditInvoiceLineForm.Create(Self);
  try
    xEditorForm.EditMode := emInvoiceLineEdit;
    xEditorForm.OnClose := EditInvoiceLineEditorClose;
    xEditorForm.Caption := 'Edit invoice line';
    xEditorForm.InvoiceLineId := Invoices.InvoiceLine.INVOICE_LINE_ID.Value;
    xEditorForm.SetProduct(
      Invoices.InvoiceLine.PRODUCT_ID.Value,
      Invoices.InvoiceLine.PRODUCTNAME.Value,
      Invoices.InvoiceLine.SALE_PRICE.AsCurrency);
    xEditorForm.Quantity := Invoices.InvoiceLine.QUANTITY.Value;
    xEditorForm.ShowModal;
  finally
    xEditorForm.Free;
  end;
end;

procedure TInvoiceForm.AddInvoiceLineEditorClose(Sender: TObject;
  var Action: TCloseAction);
var
  xEditorForm: TEditInvoiceLineForm;
  xCustomerId: Integer;
begin
  xEditorForm := TEditInvoiceLineForm(Sender);
  if xEditorForm.ModalResult <> mrOK then
  begin
    Action := caFree;
    Exit;
  end;
  try
    Invoices.AddInvoiceLine(xEditorForm.ProductId, xEditorForm.Quantity);
    Action := caFree;
  except
    on E: Exception do
    begin
      Application.ShowException(E);
      // It does not close the window give the user correct the error
      Action := caNone;
    end;
  end;
end;

procedure TInvoiceForm.EditInvoiceLineEditorClose(Sender: TObject;
  var Action: TCloseAction);
var
  xCustomerId: Integer;
  xEditorForm: TEditInvoiceLineForm;
begin
  xEditorForm := TEditInvoiceLineForm(Sender);
  if xEditorForm.ModalResult <> mrOK then
  begin
    Action := caFree;
    Exit;
  end;
  try
    Invoices.EditInvoiceLine(xEditorForm.Quantity);
    Action := caFree;
  except
    on E: Exception do
    begin
      Application.ShowException(E);
      // It does not close the window give the user correct the error
      Action := caNone;
    end;
  end;
end;
        

Now let's take a look at the code for the AddInvoiceLine and EditInvoiceLine procedures of the dmInvoice data module:

procedure TdmInvoice.AddInvoiceLine(AProductId: Integer; AQuantity: Integer);
begin
  // We do everything in a short transaction
  trWrite.StartTransaction;
  try
    qryAddInvoiceLine.ParamByName('INVOICE_ID').AsInteger :=
      Invoice.INVOICE_ID.Value;
    if AProductId = 0 then
      raise Exception.Create('Not selected product');
    qryAddInvoiceLine.ParamByName('PRODUCT_ID').AsInteger := AProductId;
    qryAddInvoiceLine.ParamByName('QUANTITY').AsInteger := AQuantity;
    qryAddInvoiceLine.Execute();
    trWrite.Commit;
    qryInvoice.Refresh;
    qryInvoiceLine.Refresh;
  except
    on E: Exception do
    begin
      if trWrite.Active then
        trWrite.Rollback;
      raise;
    end;
  end;
end;

procedure TdmInvoice.EditInvoiceLine(AQuantity: Integer);
begin
  // We do everything in a short transaction
  trWrite.StartTransaction;
  try
    qryEditInvoiceLine.ParamByName('INVOICE_LINE_ID').AsInteger :=
      InvoiceLine.INVOICE_LINE_ID.Value;
    qryEditInvoiceLine.ParamByName('QUANTITY').AsInteger := AQuantity;
    qryEditInvoiceLine.Execute();
    trWrite.Commit;
    qryInvoice.Refresh;
    qryInvoiceLine.Refresh;
  except
    on E: Exception do
    begin
      if trWrite.Active then
        trWrite.Rollback;
      raise;
    end;
  end;
end;
        

Prev: Creating the Primary ModulesFirebird Documentation IndexUp: Developing Firebird Applications in DelphiNext: The Result
Firebird Documentation IndexFirebird 3.0 Developer's GuideDeveloping Firebird Applications in Delphi → Creating a Secondary Module