3.9.5. 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;