3.9.4. 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 9. 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;