Firebird 3.0 Developer’s Guide

examples = D:\fbdata\2.5\examples.fdb

examples = D:\fbdata\3.0\examples.fdb
{
    DefaultDbCachePages = 16K
    TempCacheLimit = 512M
}

CREATE DOMAIN D_BOOLEAN AS
SMALLINT
CHECK (VALUE IN (0, 1));

COMMENT ON DOMAIN D_BOOLEAN IS
'Boolean type. 0 - FALSE, 1- TRUE';

CREATE DOMAIN D_MONEY AS
NUMERIC(15,2);

CREATE DOMAIN D_ZIPCODE AS
CHAR(10) CHARACTER SET UTF8
CHECK (TRIM(TRAILING FROM VALUE) SIMILAR TO '[0-9]+');

COMMENT ON DOMAIN D_ZIPCODE IS
'Zip code';

CREATE GENERATOR GEN_CUSTOMER_ID;

CREATE TABLE CUSTOMER (
  CUSTOMER_ID INTEGER NOT NULL,
  NAME VARCHAR(60) NOT NULL,
  ADDRESS VARCHAR(250),
  ZIPCODE D_ZIPCODE,
  PHONE VARCHAR(14),
  CONSTRAINT PK_CUSTOMER PRIMARY KEY (CUSTOMER_ID)
);

SET TERM ^ ;

CREATE OR ALTER TRIGGER CUSTOMER_BI FOR CUSTOMER
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.CUSTOMER_ID IS NULL) THEN
    NEW.CUSTOMER_ID = NEXT VALUE FOR GEN_CUSTOMER_ID;
END
^

SET TERM ; ^

COMMENT ON TABLE CUSTOMER IS
'Customers';

COMMENT ON COLUMN CUSTOMER.CUSTOMER_ID IS
'Customer Id';

COMMENT ON COLUMN CUSTOMER.NAME IS
'Name';

COMMENT ON COLUMN CUSTOMER.ADDRESS IS
'Address';

COMMENT ON COLUMN CUSTOMER.ZIPCODE IS
'Zip Code';

COMMENT ON COLUMN CUSTOMER.PHONE IS
'Phone';

CREATE GENERATOR GEN_PRODUCT_ID;

CREATE TABLE PRODUCT (
  PRODUCT_ID INTEGER NOT NULL,
  NAME VARCHAR(100) NOT NULL,
  PRICE D_MONEY NOT NULL,
  DESCRIPTION BLOB SUB_TYPE 1 SEGMENT SIZE 80,
  CONSTRAINT PK_PRODUCT PRIMARY KEY (PRODUCT_ID)
);

SET TERM ^;

CREATE OR ALTER TRIGGER PRODUCT_BI FOR PRODUCT
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.PRODUCT_ID IS NULL) THEN
    NEW.PRODUCT_ID = NEXT VALUE FOR GEN_PRODUCT_ID;
END
^

SET TERM ;^

COMMENT ON TABLE PRODUCT IS
'Goods';

COMMENT ON COLUMN PRODUCT.PRODUCT_ID IS
'Product Id';

COMMENT ON COLUMN PRODUCT.NAME IS
'Name';

COMMENT ON COLUMN PRODUCT.PRICE IS
'Price';

COMMENT ON COLUMN PRODUCT.DESCRIPTION IS
'Description';

CREATE GENERATOR GEN_INVOICE_ID;

CREATE TABLE INVOICE (
  INVOICE_ID INTEGER NOT NULL,
  CUSTOMER_ID INTEGER NOT NULL,
  INVOICE_DATE TIMESTAMP,
  TOTAL_SALE D_MONEY,
  PAID D_BOOLEAN DEFAULT 0 NOT NULL,
  CONSTRAINT PK_INVOICE PRIMARY KEY (INVOICE_ID)
);

ALTER TABLE INVOICE ADD CONSTRAINT FK_INVOCE_CUSTOMER
FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID);

CREATE INDEX INVOICE_IDX_DATE ON INVOICE (INVOICE_DATE);

SET TERM ^;

CREATE OR ALTER TRIGGER INVOICE_BI FOR INVOICE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.INVOICE_ID IS NULL) THEN
    NEW.INVOICE_ID = GEN_ID(GEN_INVOICE_ID,1);
END
^

SET TERM ;^

COMMENT ON TABLE INVOICE IS
'Invoices';

COMMENT ON COLUMN INVOICE.INVOICE_ID IS
'Invoice number';

COMMENT ON COLUMN INVOICE.CUSTOMER_ID IS
'Customer Id';

COMMENT ON COLUMN INVOICE.INVOICE_DATE IS
'The date of issuance invoices';

COMMENT ON COLUMN INVOICE.TOTAL_SALE IS
'Total sum';

COMMENT ON COLUMN INVOICE.PAID IS
'Paid';

CREATE GENERATOR GEN_INVOICE_LINE_ID;

CREATE TABLE INVOICE_LINE (
  INVOICE_LINE_ID INTEGER NOT NULL,
  INVOICE_ID INTEGER NOT NULL,
  PRODUCT_ID INTEGER NOT NULL,
  QUANTITY NUMERIC(15,0) NOT NULL,
  SALE_PRICE D_MONEY NOT NULL,
  CONSTRAINT PK_INVOICE_LINE PRIMARY KEY (INVOICE_LINE_ID)
);

ALTER TABLE INVOICE_LINE ADD CONSTRAINT FK_INVOICE_LINE_INVOICE
FOREIGN KEY (INVOICE_ID) REFERENCES INVOICE (INVOICE_ID);

ALTER TABLE INVOICE_LINE ADD CONSTRAINT FK_INVOICE_LINE_PRODUCT
FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT (PRODUCT_ID);

SET TERM ^;

CREATE OR ALTER TRIGGER INVOICE_LINE_BI FOR INVOICE_LINE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.INVOICE_LINE_ID IS NULL) THEN
    NEW.INVOICE_LINE_ID = NEXT VALUE FOR GEN_INVOICE_LINE_ID;
END
^

SET TERM ;^

COMMENT ON TABLE INVOICE_LINE IS
'Invoice lines';

COMMENT ON COLUMN INVOICE_LINE.INVOICE_LINE_ID IS
'Invoice line Id';

COMMENT ON COLUMN INVOICE_LINE.INVOICE_ID IS
'Invoice number';

COMMENT ON COLUMN INVOICE_LINE.PRODUCT_ID IS
'Product Id';

COMMENT ON COLUMN INVOICE_LINE.QUANTITY IS
'Quantity';

COMMENT ON COLUMN INVOICE_LINE.SALE_PRICE IS
'Price';

CREATE ROLE MANAGER;
CREATE ROLE SUPERUSER;
-- The MANAGER role can read any table and use the corresponding procedures
-- to manage invoices:
GRANT SELECT ON CUSTOMER TO MANAGER;
GRANT SELECT ON INVOICE TO MANAGER;
GRANT SELECT ON INVOICE_LINE TO MANAGER;
GRANT SELECT ON PRODUCT TO MANAGER;
GRANT EXECUTE ON PROCEDURE SP_ADD_INVOICE TO MANAGER;
GRANT EXECUTE ON PROCEDURE SP_ADD_INVOICE_LINE TO MANAGER;
GRANT EXECUTE ON PROCEDURE SP_DELETE_INVOICE TO MANAGER;
GRANT EXECUTE ON PROCEDURE SP_DELETE_INVOICE_LINE TO MANAGER;
GRANT EXECUTE ON PROCEDURE SP_EDIT_INVOICE TO MANAGER;
GRANT EXECUTE ON PROCEDURE SP_EDIT_INVOICE_LINE TO MANAGER;
GRANT EXECUTE ON PROCEDURE SP_PAY_FOR_INVOICE TO MANAGER;
GRANT USAGE ON SEQUENCE GEN_INVOICE_ID TO MANAGER;
-- The SUPERUSER role can read any table, edit the primary tables directly
-- and use the procedures to manage invoices:
GRANT SELECT, INSERT, UPDATE, DELETE ON CUSTOMER TO SUPERUSER;
GRANT SELECT ON INVOICE TO SUPERUSER;
GRANT SELECT ON INVOICE_LINE TO SUPERUSER;
GRANT SELECT, INSERT, UPDATE, DELETE ON PRODUCT TO SUPERUSER;
GRANT EXECUTE ON PROCEDURE SP_ADD_INVOICE TO SUPERUSER;
GRANT EXECUTE ON PROCEDURE SP_ADD_INVOICE_LINE TO SUPERUSER;
GRANT EXECUTE ON PROCEDURE SP_DELETE_INVOICE TO SUPERUSER;
GRANT EXECUTE ON PROCEDURE SP_DELETE_INVOICE_LINE TO SUPERUSER;
GRANT EXECUTE ON PROCEDURE SP_EDIT_INVOICE TO SUPERUSER;
GRANT EXECUTE ON PROCEDURE SP_EDIT_INVOICE_LINE TO SUPERUSER;
GRANT EXECUTE ON PROCEDURE SP_PAY_FOR_INVICE TO SUPERUSER;
GRANT USAGE ON SEQUENCE GEN_CUSTOMER_ID TO SUPERUSER;
GRANT USAGE ON SEQUENCE GEN_INVOICE_ID TO SUPERUSER;
GRANT USAGE ON SEQUENCE GEN_PRODUCT_ID TO SUPERUSER;

CREATE USER IVAN PASSWORD 'z12a';
CREATE USER ANNA PASSWORD 'lh67';

GRANT MANAGER TO ANNA;
GRANT MANAGER TO IVAN WITH ADMIN OPTION;
GRANT SUPERUSER TO IVAN;

xIniFile := TIniFile.Create(xAppPath + 'config.ini');
try
  xIniFile.ReadSectionValues('connection', FDConnection.Params);
finally
  xIniFile.Free;
end;

Options.AutoStart = False
Options.AutoCommit = False
Options.AutoStop = False
Options.DisconnectAction = xdCommit
Options.Isolations = xiReadCommitted
Options.ReadOnly = True

Options.AutoStart = False
Options.AutoCommit = False
Options.AutoStop = False
Options.DisconnectAction = xdRollback
Options.Isolations = xiSnapshot
Options.ReadOnly = False

procedure TCustomerForm.actEditRecordExecute(Sender: TObject);
var
  xEditorForm: TEditCustomerForm;
begin
  xEditorForm := TEditCustomerForm.Create(Self);
  try
    xEditorForm.OnClose := CustomerEditorClose;
    xEditorForm.DataSource := Customers.DataSource;
    xEditorForm.Caption := 'Edit customer';
    Customers.Edit;
    xEditorForm.ShowModal;
  finally
    xEditorForm.Free;
  end;
end;
The Customers property is initiated in the OnCreate event:
procedure TCustomerForm.FormCreate(Sender: TObject);
begin
  FCustomers := TDMCustomers.Create(Self);
  DBGrid.DataSource := Customers.DataSource;
end;

procedure TdmCustomers.Edit;
begin
  qryCustomer.CachedUpdates := True;
  qryCustomer.Edit;
end;

procedure TCustomerForm.CustomerEditorClose(Sender: TObject;
  var Action: TCloseAction);
begin
  if TEditCustomerForm(Sender).ModalResult <> mrOK then
  begin
    Customers.Cancel;
    Action := caFree;
    Exit;
  end;
  try
    Customers.Post;
    Customers.Save;
    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 TdmCustomers.Cancel;
begin
  qryCustomer.Cancel;
  qryCustomer.CancelUpdates;
  qryCustomer.CachedUpdates := False;
end;

procedure TdmCustomers.Post;
begin
  qryCustomer.Post;
end;

procedure TdmCustomers.Save;
begin
  // We do everything in a short transaction
  // In CachedUpdates mode an error does not interrupt the running code.
  // The ApplyUpdates method returns the number of errors.
  // The error can be obtained from the property RowError
  try
    trWrite.StartTransaction;
    if (qryCustomer.ApplyUpdates = 0) then
    begin
      qryCustomer.CommitUpdates;
      trWrite.Commit;
    end
    else
      raise Exception.Create(qryCustomer.RowError.Message);
    qryCustomer.CachedUpdates := False;
  except
    on E: Exception do
    begin
      if trWrite.Active then
        trWrite.Rollback;
      raise;
    end;
  end;
end;

SELECT
  product_id,
  name,
  price,
  description
FROM product
ORDER BY name

INSERT INTO PRODUCT (NAME, PRICE, DESCRIPTION)
VALUES (:NEW_NAME, :NEW_PRICE, :NEW_DESCRIPTION)
RETURNING PRODUCT_ID

Required = False

ReadOnly = True

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

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

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

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

EXECUTE PROCEDURE sp_delete_invoice(:INVOICE_ID)

EXECUTE PROCEDURE sp_pay_for_invoice(:invoice_id)

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;

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

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

EXECUTE PROCEDURE sp_edit_invoice_line(
  :invoice_line_id,
  :quantity
)

EXECUTE PROCEDURE sp_delete_invoice_line(
  :invoice_line_id
)

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;

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;

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;

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;

DbModel dbContext = new DbModel();

static class AppVariables
{
    private static DbModel dbContext = null;

    /// <summary>
    /// Start date of the working period
    /// </summary>
    public static DateTime StartDate { get; set; }

    /// <summary>
    /// End date of the working period
    /// </summary>
    public static DateTime FinishDate { get; set; }

    /// <summary>
    /// Returns an instance of the model (context)
    /// </summary>
    /// <returns>Model</returns>
    public static DbModel CreateDbContext() {
        dbContext = dbContext ?? new DbModel();
        return dbContext;
    }
}

private void MainForm_Load(object sender, EventArgs e) {
    var dialog = new LoginForm();
    if (dialog.ShowDialog() == DialogResult.OK)
    {
        var dbContext = AppVariables.getDbContext();
        try
        {
            string s = dbContext.Database.Connection.ConnectionString;
            var builder = new FbConnectionStringBuilder(s);
            builder.UserID = dialog.UserName;
            builder.Password = dialog.Password;
            dbContext.Database.Connection.ConnectionString = builder.ConnectionString;
            // try connect
            dbContext.Database.Connection.Open();
        }
        catch (Exception ex)
        {
           // display error
           MessageBox.Show(ex.Message, "Error");
           Application.Exit();
        }
     }
     else
        Application.Exit();
}

var dbContext = AppVariables.getDbContext();

private void LoadCustomersData()
{
    var dbContext = AppVariables.getDbContext();
    dbContext.CUSTOMERS.Load();
    var customers =
        from customer in dbContext.CUSTOMERS.Local
        orderby customer.NAME
        select new customer;
    bindingSource.DataSource = customers.ToBindingList();
}

public void LoadInvoicesData() {
    var dbContext = AppVariables.getDbContext();

    var invoices =
        from invoice in dbContext.INVOICES
        where (invoice.INVOICE_DATE >= AppVariables.StartDate) &&
              (invoice.INVOICE_DATE <= AppVariables.FinishDate)
        orderby invoice.INVOICE_DATE descending
        select new InvoiceView
        {
            Id = invoice.INVOICE_ID,
            Cusomer_Id = invoice.CUSTOMER_ID,
            Customer = invoice.CUSTOMER.NAME,
            Date = invoice.INVOICE_DATE,
            Amount = invoice.TOTAL_SALE,
            Payed = (invoice.PAYED == 1) ? "Yes" : "No"
        };
    masterBinding.DataSource = invoices.ToBindingList();
}

public class InvoiceView {
    public int Id { get; set; }

    public int Cusomer_Id { get; set; }

    public string Customer { get; set; }

    public DateTime? Date { get; set; }

    public decimal? Amount { get; set; }

    public string Payed { get; set; }

    public void Load(int Id) {
        var dbContext = AppVariables.getDbContext();
        var invoices =
            from invoice in dbContext.INVOICES
            where invoice.INVOICE_ID == Id
            select new InvoiceView
            {
                Id = invoice.INVOICE_ID,
                Cusomer_Id = invoice.CUSTOMER_ID,
                Customer = invoice.CUSTOMER.NAME,
                Date = invoice.INVOICE_DATE,
                Amount = invoice.TOTAL_SALE,
                Payed = (invoice.PAYED == 1) ? "Yes" : "No"
            };

        InvoiceView invoiceView = invoices.ToList().First();
        this.Id = invoiceView.Id;
        this.Cusomer_Id = invoiceView.Cusomer_Id;
        this.Customer = invoiceView.Customer;
        this.Date = invoiceView.Date;
        this.Amount = invoiceView.Amount;
        this.Payed = invoiceView.Payed;
    }
}

private void btnAddInvoice_Click(object sender, EventArgs e) {
  var dbContext = AppVariables.getDbContext();
  var invoice = dbContext.INVOICES.Create();
  using (InvoiceEditorForm editor = new InvoiceEditorForm()) {
    editor.Text = "Add invoice";
    editor.Invoice = invoice;
    // Form Close Handler
    editor.FormClosing += delegate (object fSender, FormClosingEventArgs fe) {
      if (editor.DialogResult == DialogResult.OK) {
        try {
          // get next sequence value
          invoice.INVOICE_ID = dbContext.NextValueFor("GEN_INVOICE_ID");
          // add a record
          dbContext.INVOICES.Add(invoice);
          // trying to save the changes
          dbContext.SaveChanges();
          // add the projection to the grid list
          ((InvoiceView)masterBinding.AddNew()).Load(invoice.INVOICE_ID);
        }
        catch (Exception ex) {
          // display error
          MessageBox.Show(ex.Message, "Error");
          // Do not close the form to correct the error
          fe.Cancel = true;
        }
      }
    };
    // show the modal form
    editor.ShowDialog(this);
  }
}

private void btnEditInvoice_Click(object sender, EventArgs e) {
  var dbContext = AppVariables.getDbContext();
  // find entity by id
  var invoice = dbContext.INVOICES.Find(this.CurrentInvoice.Id);
  if (invoice.PAYED == 1) {
    MessageBox.Show("The change is not possible, the invoice has already been paid.",
                    "Error");
    return;
  }
  using (InvoiceEditorForm editor = new InvoiceEditorForm()) {
    editor.Text = "Edit invoice";
    editor.Invoice = invoice;
    // Form Close Handler
    editor.FormClosing += delegate (object fSender, FormClosingEventArgs fe) {
      if (editor.DialogResult == DialogResult.OK) {
        try {
          // trying to save the changes
          dbContext.SaveChanges();
          // refresh
          CurrentInvoice.Load(invoice.INVOICE_ID);
          masterBinding.ResetCurrentItem();
        }
        catch (Exception ex) {
          // display error
          MessageBox.Show(ex.Message, "Error");
          // Do not close the form to correct the error
          fe.Cancel = true;
        }
      }
    };
    editor.ShowDialog(this);
  }
}

public InvoiceView CurrentInvoice {
    get {
        return (InvoiceView)masterBinding.Current;
    }
}

private void masterBinding_CurrentChanged(object sender, EventArgs e) {
    LoadInvoiceLineData(this.CurrentInvoice.Id);
    detailGridView.DataSource = detailBinding;
}

private void LoadInvoiceLineData(int? id) {
  var dbContext = AppVariables.getDbContext();
  var lines =
      from line in dbContext.INVOICE_LINES
      where line.INVOICE_ID == id
      select new InvoiceLineView
      {
          Id = line.INVOICE_LINE_ID,
          Invoice_Id = line.INVOICE_ID,
          Product_Id = line.PRODUCT_ID,
          Product = line.PRODUCT.NAME,
          Quantity = line.QUANTITY,
          Price = line.SALE_PRICE,
          Total = Math.Round(line.QUANTITY * line.SALE_PRICE, 2)
      };
  detailBinding.DataSource = lines.ToBindingList();
}

public class InvoiceLineView {
    public int Id { get; set; }
    public int Invoice_Id { get; set; }
    public int Product_Id { get; set; }
    public string Product { get; set; }
    public decimal Quantity { get; set; }
    public decimal Price { get; set; }
    public decimal Total { get; set; }
}

public InvoiceLineView CurrentInvoiceLine {
    get {
        return (InvoiceLineView)detailBinding.Current;
    }
}

private void btnAddInvoiceLine_Click(object sender, EventArgs e) {
  var dbContext = AppVariables.getDbContext();
  // get current invoice
  var invoice = dbContext.INVOICES.Find(this.CurrentInvoice.Id);
  if (invoice.PAYED == 1) {
    MessageBox.Show("The change is not possible, the invoice has already been paid.", "Error");
    return;
  }
  // create invoice position
  var invoiceLine = dbContext.INVOICE_LINES.Create();
  invoiceLine.INVOICE_ID = invoice.INVOICE_ID;
  // create the position editor of the invoice
  using (InvoiceLineEditorForm editor = new InvoiceLineEditorForm()) {
    editor.Text = "Add invoice line";
    editor.InvoiceLine = invoiceLine;
    // Form Close Handler
    editor.FormClosing += delegate (object fSender, FormClosingEventArgs fe) {
      if (editor.DialogResult == DialogResult.OK) {
        try {
          // create SP parameters
          var invoiceIdParam = new FbParameter("INVOICE_ID",
                                               FbDbType.Integer);
          var productIdParam = new FbParameter("PRODUCT_ID",
                                               FbDbType.Integer);
          var quantityParam = new FbParameter("QUANTITY", FbDbType.Integer);
          // initial parameters values
          invoiceIdParam.Value = invoiceLine.INVOICE_ID;
          productIdParam.Value = invoiceLine.PRODUCT_ID;
          quantityParam.Value = invoiceLine.QUANTITY;
          // execute stored procedure
          dbContext.Database.ExecuteSqlCommand(
            "EXECUTE PROCEDURE SP_ADD_INVOICE_LINE("
          + "@INVOICE_ID, @PRODUCT_ID, @QUANTITY)",
             invoiceIdParam,
             productIdParam,
             quantityParam);
          // refresh grids
          // reload current invoice record
          CurrentInvoice.Load(invoice.INVOICE_ID);
          // reload all record in detail grid
          LoadInvoiceLineData(invoice.INVOICE_ID);
          // refresh all related data
          masterBinding.ResetCurrentItem();
        }
        catch (Exception ex) {
          // display error
          MessageBox.Show(ex.Message, "Error");
          // Do not close the form to correct the error
          fe.Cancel = true;
        }
      }
    };
    editor.ShowDialog(this);
  }
}

private void btnEditInvoiceLine_Click(object sender, EventArgs e) {
  var dbContext = AppVariables.getDbContext();
  // get current invoice
  var invoice = dbContext.INVOICES.Find(this.CurrentInvoice.Id);
  if (invoice.PAYED == 1) {
    MessageBox.Show("The change is not possible, the invoice has already been paid.",
                    "Error");
    return;
  }
  // get current invoice position
  var invoiceLine = invoice.INVOICE_LINES
         .Where(p => p.INVOICE_LINE_ID == this.CurrentInvoiceLine.Id)
         .First();
  // create invoice position editor
  using (InvoiceLineEditorForm editor = new InvoiceLineEditorForm()) {
    editor.Text = "Edit invoice line";
    editor.InvoiceLine = invoiceLine;
    // form close handler
    editor.FormClosing += delegate (object fSender, FormClosingEventArgs fe) {
      if (editor.DialogResult == DialogResult.OK) {
        try {
          // create parameters
          var idParam = new FbParameter("INVOICE_LINE_ID", FbDbType.Integer);
          var quantityParam = new FbParameter("QUANTITY", FbDbType.Integer);
          // initial parameters values
          idParam.Value = invoiceLine.INVOICE_LINE_ID;
          quantityParam.Value = invoiceLine.QUANTITY;
          // execute stored procedure
          dbContext.Database.ExecuteSqlCommand(
              "EXECUTE PROCEDURE SP_EDIT_INVOICE_LINE("
            + "@INVOICE_LINE_ID, @QUANTITY)",
              idParam,
              quantityParam);
          // refresh grids
          // reload current invoice record
          CurrentInvoice.Load(invoice.INVOICE_ID);
          // reload all records in detail grid
          LoadInvoiceLineData(invoice.INVOICE_ID);
          // refresh all related controls
          masterBinding.ResetCurrentItem();
        }
        catch (Exception ex) {
          // display error
          MessageBox.Show(ex.Message, "Error");
          // Do not close the form to correct the error
          fe.Cancel = true;
        }
      }
    };
    editor.ShowDialog(this);
  }
}

public partial class InvoiceLineEditorForm : Form {
    public InvoiceLineEditorForm() {
        InitializeComponent();
    }

    public INVOICE_LINE InvoiceLine { get; set; }

    private void InvoiceLineEditorForm_Load(object sender, EventArgs e) {
        if (this.InvoiceLine.PRODUCT != null) {
            edtProduct.Text = this.InvoiceLine.PRODUCT.NAME;
            edtPrice.Text = this.InvoiceLine.PRODUCT.PRICE.ToString("F2");
            btnChooseProduct.Click -= this.btnChooseProduct_Click;
        }
        if (this.InvoiceLine.QUANTITY == 0)
            this.InvoiceLine.QUANTITY = 1;
        edtQuantity.DataBindings.Add("Value", this.InvoiceLine, "QUANTITY");
    }

    private void btnChooseProduct_Click(object sender, EventArgs e) {
        GoodsForm goodsForm = new GoodsForm();
        if (goodsForm.ShowDialog() == DialogResult.OK) {
            InvoiceLine.PRODUCT_ID = goodsForm.CurrentProduct.Id;
            edtProduct.Text = goodsForm.CurrentProduct.Name;
            edtPrice.Text = goodsForm.CurrentProduct.Price.ToString("F2");
        }
    }
}

<!DOCTYPE html>
<html>
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
  <meta charset="utf-8" />
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>@ViewBag.Title - ASP.NET application</title>
  @Styles.Render("~/Content/css")
  @Scripts.Render("~/bundles/modernizr")
  @Scripts.Render("~/bundles/jquery")
  @Scripts.Render("~/bundles/jquery-ui")
  <link href="~/Content/jquery.jqGrid/ui.jqgrid.css"
        rel="stylesheet" type="text/css" />
   <link href="~/Content/jquery.jqGrid/ui.jqgrid-bootstrap.css"
         rel="stylesheet" type="text/css" />
   <link href="~/Content/jquery.jqGrid/ui.jqgrid-bootstrap-ui.css"
         rel="stylesheet" type="text/css" />
   <script src="~/Scripts/jquery.jqGrid.min.js"
           type="text/javascript"></script>
    <script src="~/Scripts/i18n/grid.locale-en.js"
            type="text/javascript"></script>
</head>
<body>
  @Html.AntiForgeryToken()
  <script>

    function GetAntiForgeryToken() {
      var tokenField =
        $("input[type='hidden'][name$='RequestVerificationToken']");
      if (tokenField.length == 0) {
        return null;
      } else {
        return {
          name: tokenField[0].name,
          value: tokenField[0].value
        };
      }
    }

    // add prefilter to all ajax requests
    // it will add to any POST ajax request
    // AntiForgery token
    $.ajaxPrefilter(
      function (options, localOptions, jqXHR) {
        if (options.type !== "GET") {
          var token = GetAntiForgeryToken();
          if (token !== null) {
            if (options.data.indexOf(""X-Requested-With") === -1) {
              options.data = "X-Requested-With=XMLHttpRequest"
              + ((options.data === "") ? "" : "&" + options.data);
            }
            options.data = options.data + "&" + token.name + '='
                         + token.value;
          }
        }
      }
    );
    // initialize the general properties of the jqGrid module
    $.jgrid.defaults.width = 780;
    $.jgrid.defaults.responsive = true;
    $.jgrid.defaults.styleUI = 'Bootstrap';
  </script>

  <!-- Navigation menu -->
  <div class="navbar navbar-inverse navbar-fixed-top">
    <div class="container">
      <div class="navbar-header">
        <button type="button" class="navbar-toggle" data-toggle="collapse"
                data-target=".navbar-collapse">
          <span class="icon-bar"></span>
          <span class="icon-bar"></span>
          <span class="icon-bar"7gt;</span>
        </button>
      </div>>
      <div class="navbar-collapse collapse">
        <ul class="nav navbar-nav">
          <li>@Html.ActionLink("Customers", "Index", "Customer")</li>
          <li>@Html.ActionLink("Goods", "Index", "Product")</li>
          <li>@Html.ActionLink("Invoices", "Index", "Invoice")</li>
        </ul>
      </div>
    </div>
  </div>

  <div class="container body-content">
    @RenderBody()
    <hr />
    <footer>
      <p>&copy; @DateTime.Now.Year - ASP.NET application</p>
    </footer>
  </div>

  @Scripts.Render("~/bundles/bootstrap")
  @RenderSection("scripts", required: false)
</body>
</html>

[Authorize(Roles = "manager")]
public class InvoiceController : Controller
{
  private DbModel db = new DbModel();

  // display view
  public ActionResult Index()
  {
    return View();
  }

  // Receiving data in the JSON format for the main grid
  public ActionResult GetData(int? rows, int? page, string sidx, string sord,
string searchField, string searchString, string searchOper)
  {
    // get the page number, the number of data displayed
    int pageNo = page ?? 1;
    int limit = rows ?? 20;
    // calculate offset
    int offset = (pageNo - 1) * limit;
    // building a request for receipt of invoices
    var invoicesQuery =
        from invoice in db.INVOICES
        where (invoice.INVOICE_DATE >= AppVariables.StartDate) &&
              (invoice.INVOICE_DATE <= AppVariables.FinishDate)
        select new
        {
          INVOICE_ID = invoice.INVOICE_ID,
          CUSTOMER_ID = invoice.CUSTOMER_ID,
          CUSTOMER_NAME = invoice.CUSTOMER.NAME,
          INVOICE_DATE = invoice.INVOICE_DATE,
          TOTAL_SALE = invoice.TOTAL_SALE,
          PAID = invoice.PAID
        };
    // adding a search condition to the query, if it is produced
    // for different fields, different comparison operators
    // are available when searching
    if (searchField == "CUSTOMER_NAME")
    {
      switch (searchOper)
      {
        case "eq": // equal
          invoicesQuery = invoicesQuery.Where(
          c => c.CUSTOMER_NAME == searchString);
          break;
        case "bw": // starting with
          invoicesQuery = invoicesQuery.Where(
          c => c.CUSTOMER_NAME.StartsWith(searchString));
          break;
        case "cn": // containing
          invoicesQuery = invoicesQuery.Where(
          c => c.CUSTOMER_NAME.Contains(searchString));
          break;
      }
    }
    if (searchField == "INVOICE_DATE")
    {
      var dateValue = DateTime.Parse(searchString);
      switch (searchOper)
      {
        case "eq": // =
          invoicesQuery = invoicesQuery.Where(
          c => c.INVOICE_DATE == dateValue);
          break;
        case "lt": // <
          invoicesQuery = invoicesQuery.Where(
          c => c.INVOICE_DATE < dateValue);
          break;
        case "le": // <=
          invoicesQuery = invoicesQuery.Where(
          c => c.INVOICE_DATE <= dateValue);
          break;
        case "gt": // >
          invoicesQuery = invoicesQuery.Where(
          c => c.INVOICE_DATE > dateValue);
          break;
        case "ge": // >=
          invoicesQuery = invoicesQuery.Where(
          c => c.INVOICE_DATE >= dateValue);
          break;
      }
    }
    if (searchField == "PAID")
    {
      int iVal = (searchString == "on") ? 1 : 0;
      invoicesQuery = invoicesQuery.Where(c => c.PAID == iVal);
    }
    // get the total number of invoices
    int totalRows = invoicesQuery.Count();
    // add sorting
    switch (sord)
    {
      case "asc":
        invoicesQuery = invoicesQuery.OrderBy(
        invoice => invoice.INVOICE_DATE);
        break;
      case "desc":
        invoicesQuery = invoicesQuery.OrderByDescending(
        invoice => invoice.INVOICE_DATE);
        break;
    }
    // get invoice list
    var invoices = invoicesQuery
       .Skip(offset)
       .Take(limit)
       .ToList();
    // calculate the total number of pages
    int totalPages = totalRows / limit + 1;
    // create the result for jqGrid
    var result = new
      {
        page = pageNo,
        total = totalPages,
        records = totalRows,
        rows = invoices
      };
    // convert the result to JSON
    return Json(result, JsonRequestBehavior.AllowGet);
  }

  // Receiving data in the form of JSON for the detail grid
  public ActionResult GetDetailData(int? invoice_id)
  {
    // build a LINQ query for receiving invoice items
    // filtered by invoice id
    var lines =
        from line in db.INVOICE_LINES
        where line.INVOICE_ID == invoice_id
        select new
      {
        INVOICE_LINE_ID = line.INVOICE_LINE_ID,
        INVOICE_ID = line.INVOICE_ID,
        PRODUCT_ID = line.PRODUCT_ID,
        Product = line.PRODUCT.NAME,
        Quantity = line.QUANTITY,
        Price = line.SALE_PRICE,
        Total = line.QUANTITY * line.SALE_PRICE
      };
    // get invoice position list
    var invoices = lines
        .ToList();
    // create the result for jqGrid
    var result = new
    {
      rows = invoices
    };
    // convert the result to JSON
    return Json(result, JsonRequestBehavior.AllowGet);
  }

  // Add new invoice
  [HttpPost]
  [ValidateAntiForgeryToken]
  public ActionResult Create(
  [Bind(Include = "CUSTOMER_ID,INVOICE_DATE")] INVOICE invoice)
  {
    // check the correctness of the model
    if (ModelState.IsValid)
    {
      try
      {
        var INVOICE_ID = new FbParameter("INVOICE_ID", FbDbType.Integer);
        var CUSTOMER_ID = new FbParameter("CUSTOMER_ID", FbDbType.Integer);
        var INVOICE_DATE = new FbParameter("INVOICE_DATE",
                               FbDbType.TimeStamp);
        // initialize parameters query
        INVOICE_ID.Value = db.NextValueFor("GEN_INVOICE_ID");
        CUSTOMER_ID.Value = invoice.CUSTOMER_ID;
        INVOICE_DATE.Value = invoice.INVOICE_DATE;
        // execute stored procedure
        db.Database.ExecuteSqlCommand(
          "EXECUTE PROCEDURE SP_ADD_INVOICE(@INVOICE_ID, @CUSTOMER_ID, @INVOICE_DATE)",
          INVOICE_ID,
          CUSTOMER_ID,
          INVOICE_DATE);
        // return success in JSON format
        return Json(true);
      }
      catch (Exception ex)
      {
        // return error in JSON format
        return Json(new { error = ex.Message });
      }
    }
    else {
      string messages = string.Join("; ", ModelState.Values
                       .SelectMany(x => x.Errors)
                       .Select(x => x.ErrorMessage));
      // return error in JSON format
      return Json(new { error = messages });
    }
  }

  // Edit invoice
  [HttpPost]
  [ValidateAntiForgeryToken]
  public ActionResult Edit(
  [Bind(Include = "INVOICE_ID,CUSTOMER_ID,INVOICE_DATE")] INVOICE invoice)
  {
    // check the correctness of the model
    if (ModelState.IsValid)
    {
      try
      {
        var INVOICE_ID = new FbParameter("INVOICE_ID", FbDbType.Integer);
        var CUSTOMER_ID = new FbParameter("CUSTOMER_ID", FbDbType.Integer);
        var INVOICE_DATE = new FbParameter("INVOICE_DATE",
                                           FbDbType.TimeStamp);
        // initialize parameters query
        INVOICE_ID.Value = invoice.INVOICE_ID;
        CUSTOMER_ID.Value = invoice.CUSTOMER_ID;
        INVOICE_DATE.Value = invoice.INVOICE_DATE;
        // execute stored procedure
        db.Database.ExecuteSqlCommand(
          "EXECUTE PROCEDURE SP_EDIT_INVOICE(@INVOICE_ID, @CUSTOMER_ID, @INVOICE_DATE)",
          INVOICE_ID,
          CUSTOMER_ID,
          INVOICE_DATE);
        // return success in JSON format
        return Json(true);
      }
      catch (Exception ex)
      {
        // return error in JSON format
        return Json(new { error = ex.Message });
      }
    }
    else {
      string messages = string.Join("; ", ModelState.Values
                       .SelectMany(x => x.Errors)
                       .Select(x => x.ErrorMessage));
      // return error in JSON format
      return Json(new { error = messages });
    }
  }

  // Delete invoice
  [HttpPost]
  [ValidateAntiForgeryToken]
  public ActionResult Delete(int id)
  {
    try
    {
      var INVOICE_ID = new FbParameter("INVOICE_ID", FbDbType.Integer);
      // initialize parameters query
      INVOICE_ID.Value = id;
      // execute stored procedure
      db.Database.ExecuteSqlCommand(
        "EXECUTE PROCEDURE SP_DELETE_INVOICE(@INVOICE_ID)",
        INVOICE_ID);
      // return success in JSON format
      return Json(true);
    }
    catch (Exception ex)
    {
      // return error in JSON format
      return Json(new { error = ex.Message });
    }
  }

  // Payment of invoice
  [HttpPost]
  [ValidateAntiForgeryToken]
  public ActionResult Pay(int id)
  {
    try
    {
      var INVOICE_ID = new FbParameter("INVOICE_ID", FbDbType.Integer);
      // initialize parameters query
      INVOICE_ID.Value = id;
      // execute stored procedure
      db.Database.ExecuteSqlCommand(
        "EXECUTE PROCEDURE SP_PAY_FOR_INOVICE(@INVOICE_ID)",
        INVOICE_ID);
      // return success in JSON format
      return Json(true);
    }
    catch (Exception ex)
    {
      // return error in JSON format
      return Json(new { error = ex.Message });
    }
  }

  // Add invoice position
  [HttpPost]
  [ValidateAntiForgeryToken]
  public ActionResult CreateDetail(
  [Bind(Include = "INVOICE_ID,PRODUCT_ID,QUANTITY")] INVOICE_LINE invoiceLine)
  {
    // check the correctness of the model
    if (ModelState.IsValid)
    {
      try
      {
        var INVOICE_ID = new FbParameter("INVOICE_ID", FbDbType.Integer);
        var PRODUCT_ID = new FbParameter("PRODUCT_ID", FbDbType.Integer);
        var QUANTITY = new FbParameter("QUANTITY", FbDbType.Integer);
        // initialize parameters query
        INVOICE_ID.Value = invoiceLine.INVOICE_ID;
        PRODUCT_ID.Value = invoiceLine.PRODUCT_ID;
        QUANTITY.Value = invoiceLine.QUANTITY;
        // execute stored procedure
        db.Database.ExecuteSqlCommand(
          ""EXECUTE PROCEDURE SP_ADD_INVOICE_LINE(@INVOICE_ID, @PRODUCT_ID, @QUANTITY)",
          INVOICE_ID,
          PRODUCT_ID,
          QUANTITY);
        // return success in JSON format
        return Json(true);
      }
      catch (Exception ex)
      {
        // return error in JSON format
        return Json(new { error = ex.Message });
      }
    }
    else {
      string messages = string.Join("; ", ModelState.Values
                       .SelectMany(x => x.Errors)
                       .Select(x => x.ErrorMessage));
      // return error in JSON format
      return Json(new { error = messages });
    }
  }

  // Edit invoice position
  [HttpPost]
  [ValidateAntiForgeryToken]
  public ActionResult EditDetail(
  [Bind(Include = "INVOICE_LINE_ID,INVOICE_ID,PRODUCT_ID,QUANTITY")]
    INVOICE_LINE invoiceLine)
  {
    // check the correctness of the model
    if (ModelState.IsValid)
    {
      try
      {
        // Create parameters
        var INVOICE_LINE_ID = new FbParameter("INVOICE_LINE_ID",
                                              FbDbType.Integer);
        var QUANTITY = new FbParameter("QUANTITY", FbDbType.Integer);
        // initialize parameters query
        INVOICE_LINE_ID.Value = invoiceLine.INVOICE_LINE_ID;
        QUANTITY.Value = invoiceLine.QUANTITY;
        // execute stored procedure
        db.Database.ExecuteSqlCommand(
          "EXECUTE PROCEDURE SP_EDIT_INVOICE_LINE(@INVOICE_LINE_ID, @QUANTITY)",
          INVOICE_LINE_ID,
          QUANTITY);
        // return success in JSON format
        return Json(true);
      }
      catch (Exception ex)
      {
        // return error in JSON format
        return Json(new { error = ex.Message });
      }
    }
    else {
      string messages = string.Join("; ", ModelState.Values
                       .SelectMany(x => x.Errors)
                       .Select(x => x.ErrorMessage));
      // return error in JSON format
      return Json(new { error = messages });
    }
  }

  // Delete invoice position
  [HttpPost]
  [ValidateAntiForgeryToken]
  public ActionResult DeleteDetail(int id)
  {
    try
    {
      // create parameters
      var INVOICE_LINE_ID = new FbParameter("INVOICE_LINE_ID",
                                            FbDbType.Integer);
      // initialize parameters query
      INVOICE_LINE_ID.Value = id;
      // execute stored procedure
      db.Database.ExecuteSqlCommand(
        "EXECUTE PROCEDURE SP_DELETE_INVOICE_LINE(@INVOICE_LINE_ID)",
        INVOICE_LINE_ID);
      // return success in JSON format
      return Json(true);
    }
    catch (Exception ex)
    {
      // return error in JSON format
      return Json(new { error = ex.Message });
    }
  }

  protected override void Dispose(bool disposing)
  {
    if (disposing)
    {
      db.Dispose();
    }
    base.Dispose(disposing);
  }
}

@{
    ViewBag.Title = "Index";
}

<h2>Invoices</h2>
<table id="jqg"></table>
<div id="jpager"></div>

<script type="text/javascript">
  /**
    * The code to work with jqGrid
    */
</script>

// invoice grid
var dbGrid = $("#jqg").jqGrid({
  url: '@Url.Action("GetData")', URL to retrieve data
  datatype: "json", // format data
  mtype: "GET", // type of http request
  // model description
  colModel: [
  {
    label: 'Id',
    name: 'INVOICE_ID',
    key: true,
    hidden: true
  },
  {
    label: 'CUSTOMER_ID',
    name: 'CUSTOMER_ID',
    hidden: true,
    editrules: { edithidden: true, required: true },
    editable: true,
    edittype:'custom', // own 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", // type of input
    align: "right",
    formatter: 'date', // formatted as date
    sorttype: 'date', // sorted as date
    formatoptions: { // date format
      srcformat: 'd.m.Y H:i:s',
      newformat: 'd.m.Y H:i:s'
    },
    editoptions: {
      // initializing the form element for editing
      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: {
      // 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: { // searching 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",
    formatter: 'currency', // format as 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"
    }
  }
  ],
  rowNum: 500, // number of rows displayed
  loadonce: false,
  sortname: 'INVOICE_DATE', // sort by default by NAME column
  sortorder: "desc",
  width: window.innerWidth - 80, // grid width
  height: 500, // grid height
  viewrecords: true, // display the number of records
  caption: "Invoices", // grid caption
  pager: '#jpager', // pagination element
  subGrid: true, // show subgrid
  // javascript function for displaying the parent grid
  subGridRowExpanded: showChildGrid,
  subGridOptions: {
    // upload data only once
    reloadOnExpand: false,
    // load the subgrid rows only when you click on the icon "+"
    selectOnExpand: true
  },
});

// display the navigation bar
dbGrid.jqGrid('navGrid', '#jpager',
  {
    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"
  },
  update("edit"),
  update("add"),
  update("del")
);

// Add a button to pay the invoice
dbGrid.navButtonAdd('#jpager',
{
  buttonicon: "glyphicon-usd",
  title: "Pay",
  caption: "Pay",
  position: "last",
  onClickButton: function () {
    // get the current record ID
    var id = dbGrid.getGridParam("selrow");
    if (id) {
      var url = '@Url.Action("Pay")';
      $.ajax({
        url: url,
        type: 'POST',
        data: { id: id },
        success: function (data) {
          // check if an error has occurred
          if (data.hasOwnProperty("error")) {
            alertDialog('Error', data.error);
          }
          else {
            // refresh grid
            $("#jqg").jqGrid(
              'setGridParam',
              {
                datatype: 'json'
              }
            ).trigger('reloadGrid');
          }
        }
      });
    }
  }
});

// returns properties to create edit dialogs
function update(act) {
  // editing dialog template
  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='showCustomerWindow(); return false;'>";
  template += "<span class='glyphicon glyphicon-folder-open'></span>";
  template += " 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 {
    top: $(".container.body-content").position().top + 150,
    left: $(".container.body-content").position().left + 150,
    modal: true,
    drag: true,
    closeOnEscape: true,
    closeAfterAdd: true,
    closeAfterEdit: true,
    reloadAfterSubmit: true,
    template: (act != "del") ? template : null,
    onclickSubmit: function (params, postdata) {
      // get row id
      var selectedRow = dbGrid.getGridParam("selrow");
      switch (act) {
        case "add":
          params.url = '@Url.Action("Create")';
          // get customer id for current row
          postdata.CUSTOMER_ID =
            $('#dlgEditInvoice input[name=CUSTOMER_ID]').val();
          break;
        case "edit":
          params.url = '@Url.Action("Edit")';
          postdata.INVOICE_ID = selectedRow;
          // get customer id for current row
          postdata.CUSTOMER_ID =
            $('#dlgEditInvoice input[name=CUSTOMER_ID]').val();
          break;
        case "del":
          params.url = '@Url.Action("Delete")';
          postdata.INVOICE_ID = selectedRow;
          break;
      }
    },
    afterSubmit: function (response, postdata) {
      var responseData = response.responseJSON;
      // check the result for error messages
      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];
      }
    };
  };
}

/**
 * Display a window for selecting a customer
 */
function showCustomerWindow() {
  // 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 dialogue 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("&asmp;times;")
    .appendTo(dlgHeader);

  // title
  $("<h5>").addClass("modal-title")
           .html("Select customer")
           .appendTo(dlgHeader);

  // body of dialogue
  var dlgBody = $('<div>')
     .addClass("modal-body")
     .appendTo(dlgContent);

  // footer of the dialogue
  var dlgFooter = $('<div>').addClass("modal-footer").appendTo(dlgContent);

  // button "OK"
  $("<button>")
    .attr('type', 'button')
    .addClass('btn')
    .html('OK')
    .on('click', function () {
       var rowId = $("#jqgCustomer").jqGrid("getGridParam", "selrow");
       var row = $("#jqgCustomer").jqGrid("getRowData", rowId);
       // To save the identifier and customer name
       // to 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);

  // button "Cancel"
  $("<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', 'jqgCustomer')
    .appendTo(dlgBody);

  // add the navigation bar
  $('<div>')
    .attr('id', 'jqgCustomerPager')
    .appendTo(dlgBody);

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

  // show dialog
  dlg.modal();

  // create and initialize jqGrid
  var dbGrid = $("#jqgCustomer").jqGrid({
    url: '@Url.Action("GetData", "Customer")', // URL to retrieve data
    mtype: "GET", // http type of request
    datatype: "json", // data format
    page: 1,
    width: '100%',
    // view description
    colModel: [
    {
      label: 'Id',
      name: 'CUSTOMER_ID',
      key: true,
      hidden: true
    },
    {
      label: 'Name',
      name: 'NAME',
      width: 250,
      sortable: true,
      editable: true,
      edittype: "text", // input type
      search: true,
      searchoptions: {
        sopt: ['eq', 'bw', 'cn'] // allowed search operators
      },
      // size and maximum length for the input field
      editoptions: { size: 30, maxlength: 60 },
      // required input
      editrules: { required: true }
    },
    {
      label: 'Address',
      name: 'ADDRESS',
      width: 300,
      sortable: false,
      editable: true,
      search: false,
      edittype: "textarea",
      editoptions: { maxlength: 250, cols: 30, rows: 4 }
    },
    {
      label: 'Zip Code',
      name: 'ZIPCODE',
      width: 60,
      sortable: false,
      editable: true,
      search: false,
      edittype: "text",
      editoptions: { size: 30, maxlength: 10 },
    },
    {
      label: 'Phone',
      name: 'PHONE',
      width: 85,
      sortable: false,
      editable: true,
      search: false,
      edittype: "text",
      editoptions: { size: 30, maxlength: 14 },
    }
    ],
    loadonce: false,
    pager: '#jqgCustomerPager',
    rowNum: 500, // number of rows displayed
    sortname: 'NAME', // sort by default by NAME column
    sortorder: "asc",
    height: 500
  });

  dbGrid.jqGrid('navGrid', '#jqgCustomerPager',
    {
      search: true,
      add: false,
      edit: false,
      del: false,
      view: false,
      refresh: true,
      searchtext: "Search",
      viewtext: "View",
      viewtitle: "Selected record",
      refreshtext: "Refresh"
    }
  );
}

// handler of the event of opening the parent grid
// takes two parameters: the identifier of the parent record
// and the value of the primary key
function showChildGrid(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 = '@Url.Action("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
  var detailGrid = $("#" + childGridID).jqGrid({
    url: childGridURL,
    mtype: "GET",
    datatype: "json",
    page: 1,
    colModel: [
    {
      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',
      width: 300,
      editable: true,
      edittype: "text",
      editoptions: {
        size: 50,
        maxlength: 60,
        readonly: true
      },
      editrules: { required: true }
    },
    {
      label: 'Price',
      name: '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=Price]').val() - 0;
            $('#dlgEditInvoiceLine input[name=Total]').val(quantity * price);
          }
        }
        ],
        defaultValue: 1
      }
    },
    {
      label: 'Total',
      name: 'Total',
      formatter: 'currency',
      align: "right",
      width: 100,
      editable: true,
      editoptions: {
        readonly: true
      }
    }
    ],
    loadonce: false,
    width: '100%',
    height: '100%',
    pager: "#" + childGridPagerID
  });

  // displaying the toolbar
  $("#" + childGridID).jqGrid('navGrid', '#' + childGridPagerID,
    {
      search: false,
      add: true,
      edit: true,
      del: true,
      refresh: true
    },
    updateDetail("edit"),
    updateDetail("add"),
    updateDetail("del")
  );

  // function that returns settings for the editing dialog
  function updateDetail(act) {
    // editing dialog template
    var template = "<div style='margin-left:15px;' id='dlgEditInvoiceLine'>";
    template += "<div>{INVOICE_ID} </div>";
    template += "<div>{PRODUCT_ID} </div>";
    // input field for goods with a button
    template += "<div> Product <sup>*</sup>:</div>";
    template += "<div>";
    template += "<div style='float: left;'>{Product}</div> ";
    template += "<a style='margin-left: 0.2em;' class='btn' ";
    template += "onclick='showProductWindow(); return false;'>";
    template += "<span class='glyphicon glyphicon-folder-open'></span>";
    template += " ???????</a> ";
    template += "<div style='clear: both;'></div>";
    template += "</div>";
    template += "<div> Quantity: </div><div>{Quantity} </div>";
    template += "<div> Price: </div><div>{Price} </div>";
    template += "<div> Total: </div><div>{Total} </div>";
    template += "<hr style='width: 100%;'/>";
    template += "<div> {sData} {cData} </div>";
    template += "</div>";
    return {
      top: $(".container.body-content").position().top + 150,
      left: $(".container.body-content").position().left + 150,
      modal: true,
      drag: true,
      closeOnEscape: true,
      closeAfterAdd: true,
      closeAfterEdit: true,
      reloadAfterSubmit: true,
      template: (act != "del") ? template : null,
      onclickSubmit: function (params, postdata) {
        var selectedRow = detailGrid.getGridParam("selrow");
        switch (act) {
          case "add":
            params.url = '@Url.Action("CreateDetail")';
            // get invoice id
            postdata.INVOICE_ID =
              $('#dlgEditInvoiceLine input[name=INVOICE_ID]').val();
            // get the product ID for the current record
            postdata.PRODUCT_ID =
              $('#dlgEditInvoiceLine input[name=PRODUCT_ID]').val();
            break;
          case "edit":
            params.url = '@Url.Action("EditDetail")';
            // get current record id
            postdata.INVOICE_LINE_ID = selectedRow;
            break;
          case "del":
            params.url = '@Url.Action("DeleteDetail")';
            // get current record id
            postdata.INVOICE_LINE_ID = selectedRow;
            break;
        }
      },
      afterSubmit: function (response, postdata) {
        var responseData = response.responseJSON;
        // check the result for error messages
        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];
      }
    };
  };
}

[Table("Firebird.WEBUSER")]
public partial class WEBUSER
{
  [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage",
   "CA2214:DoNotCallOverridableMethodsInConstructors")]
  public WEBUSER()
  {
    WEBUSERINROLES = new HashSet<WEBUSERINROLE>();
  }

  [Key]
  [DatabaseGenerated(DatabaseGeneratedOption.None)]
  public int WEBUSER_ID { get; set; }

  [Required]
  [StringLength(63)]
  public string EMAIL { get; set; }

  [Required]
  [StringLength(63)]
  public string PASSWD { get; set; }

  [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage",
   "CA2227:CollectionPropertiesShouldBeReadOnly")]
  public virtual ICollection<WEBUSERINROLE> WEBUSERINROLES { get; set; }
}

[Table("Firebird.WEBROLE")]
public partial class WEBROLE
{
  [Key]
  [DatabaseGenerated(DatabaseGeneratedOption.None)]
  public int WEBROLE_ID { get; set; }

  [Required]
  [StringLength(63)]
  public string NAME { get; set; }
}

[Table("Firebird.WEBUSERINROLE")]
public partial class WEBUSERINROLE
{
  [Key]
  [DatabaseGenerated(DatabaseGeneratedOption.None)]
  public int ID { get; set; }

  [Required]
  public int WEBUSER_ID { get; set; }

  [Required]
  public int WEBROLE_ID { get; set; }

  public virtual WEBUSER WEBUSER { get; set; }

  public virtual WEBROLE WEBROLE { get; set; }
}

…
  public virtual DbSet<WEBUSER> WEBUSERS { get; set; }
  public virtual DbSet<WEBROLE> WEBROLES { get; set; }
  public virtual DbSet<WEBUSERINROLE> WEBUSERINROLES { get; set; }
…
  protected override void OnModelCreating(DbModelBuilder modelBuilder)
  {
    modelBuilder.Entity<WEBUSER>()
      .HasMany(e => e.WEBUSERINROLES)
      .WithRequired(e => e.WEBUSER)
      .WillCascadeOnDelete(false);
    …
  }
…

RECREATE TABLE WEBUSER (
  WEBUSER_ID INT NOT NULL,
  EMAIL VARCHAR(63) NOT NULL,
  PASSWD VARCHAR(63) NOT NULL,
  CONSTRAINT PK_WEBUSER PRIMARY KEY(WEBUSER_ID),
  CONSTRAINT UNQ_WEBUSER UNIQUE(EMAIL)
);

RECREATE TABLE WEBROLE (
  WEBROLE_ID INT NOT NULL,
  NAME VARCHAR(63) NOT NULL,
  CONSTRAINT PK_WEBROLE PRIMARY KEY(WEBROLE_ID),
  CONSTRAINT UNQ_WEBROLE UNIQUE(NAME)
);

RECREATE TABLE WEBUSERINROLE (
  ID INT NOT NULL,
  WEBUSER_ID INT NOT NULL,
  WEBROLE_ID INT NOT NULL,
  CONSTRAINT PK_WEBUSERINROLE PRIMARY KEY(ID)
);

ALTER TABLE WEBUSERINROLE
ADD CONSTRAINT FK_WEBUSERINROLE_USER
FOREIGN KEY (WEBUSER_ID) REFERENCES WEBUSER (WEBUSER_ID);

ALTER TABLE WEBUSERINROLE
ADD CONSTRAINT FK_WEBUSERINROLE_ROLE
FOREIGN KEY (WEBROLE_ID) REFERENCES WEBROLE (WEBROLE_ID);

RECREATE SEQUENCE SEQ_WEBUSER;
RECREATE SEQUENCE SEQ_WEBROLE;
RECREATE SEQUENCE SEQ_WEBUSERINROLE;

SET TERM ^;

RECREATE TRIGGER TBI_WEBUSER
FOR WEBUSER
ACTIVE BEFORE INSERT
AS
BEGIN
  IF (NEW.WEBUSER_ID IS NULL) THEN
    NEW.WEBUSER_ID = NEXT VALUE FOR SEQ_WEBUSER;
END^

RECREATE TRIGGER TBI_WEBROLE
FOR WEBROLE
ACTIVE BEFORE INSERT
AS
BEGIN
  IF (NEW.WEBROLE_ID IS NULL) THEN
    NEW.WEBROLE_ID = NEXT VALUE FOR SEQ_WEBROLE;
END^

RECREATE TRIGGER TBI_WEBUSERINROLE
FOR WEBUSERINROLE
ACTIVE BEFORE INSERT
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
    NEW.ID = NEXT VALUE FOR SEQ_WEBUSERINROLE;
END^

SET TERM ;^

INSERT INTO WEBUSER (EMAIL, PASSWD) VALUES ('john', '12345');
INSERT INTO WEBUSER (EMAIL, PASSWD) VALUES ('alex', '123');
COMMIT;

INSERT INTO WEBROLE (NAME) VALUES ('admin');
INSERT INTO WEBROLE (NAME) VALUES ('manager');
COMMIT;

-- Link users and roles
INSERT INTO WEBUSERINROLE(WEBUSER_ID, WEBROLE_ID) VALUES(1, 1);
INSERT INTO WEBUSERINROLE(WEBUSER_ID, WEBROLE_ID) VALUES(1, 2);
INSERT INTO WEBUSERINROLE(WEBUSER_ID, WEBROLE_ID) VALUES(2, 2);
COMMIT;

namespace FBMVCExample.Models
{
  using System;
  using System.Collections.Generic;
  using System.ComponentModel.DataAnnotations;
  using System.ComponentModel.DataAnnotations.Schema;
  using System.Data.Entity.Spatial;

  // Login model
  public class LoginModel
  {
    [Required]
    public string Name { get; set; }

    [Required]
    [DataType(DataType.Password)]
    public string Password { get; set; }
  }

  // Model for registering a new user
  public class RegisterModel
  {
    [Required]
    public string Name { get; set; }

    [Required]
    [DataType(DataType.Password)]
    public string Password { get; set; }

    [Required]
    [DataType(DataType.Password)]
    [Compare("Password", ErrorMessage = " Passwords do not match ")]
    public string ConfirmPassword { get; set; }
  }
}

@model FBMVCExample.Models.LoginModel

@{
  ViewBag.Title = "Login";
}

<h2>Login</h2>

@using (Html.BeginForm())
{
  @Html.AntiForgeryToken()
  <div class="form-horizontal">

    @Html.ValidationSummary(true)
    <div class="form-group">

      @Html.LabelFor(model => model.Name,
        new { @class = "control-label col-md-2" })
      <div class="col-md-10">
        @Html.EditorFor(model => model.Name)
        @Html.ValidationMessageFor(model => model.Name)
      </div>
    </div>

    <div class="form-group">
      @Html.LabelFor(model => model.Password,
        new { @class = "control-label col-md-2" })
      <div class="col-md-10">
        @Html.EditorFor(model => model.Password)
        @Html.ValidationMessageFor(model => model.Password)
      </div>
    </div>

    <div class="form-group">
      <div class="col-md-offset-2 col-md-10">
        <input type="submit" value="Logon" class="btn btn-default" />
      </div>
    </div>
  </div>
}

@section Scripts {
  @Scripts.Render("~/bundles/jqueryval")
}

The registration view, in turn, is coded as follows:
@model FBMVCExample.Models.RegisterModel

@{
  ViewBag.Title = "Registration";
}

<h2>???????????</h2>

@using (Html.BeginForm())
{
  @Html.AntiForgeryToken()
  <div class="form-horizontal">

    @Html.ValidationSummary(true)
    <div class="form-group">
      @Html.LabelFor(model => model.Name,
        new { @class = "control-label col-md-2" })

      <div class="col-md-10">
        @Html.EditorFor(model => model.Name)
        @Html.ValidationMessageFor(model => model.Name)
      </div>
    </div>

    <div class="form-group">
      @Html.LabelFor(model => model.Password,
        new { @class = "control-label col-md-2" })

      <div class="col-md-10">
        @Html.EditorFor(model => model.Password)
        @Html.ValidationMessageFor(model => model.Password)
      </div>
    </div>

    <div class="form-group">
      @Html.LabelFor(model => model.ConfirmPassword,
        new { @class = "control-label col-md-2" })

      <div class="col-md-10">
        @Html.EditorFor(model => model.ConfirmPassword)
        @Html.ValidationMessageFor(model => model.ConfirmPassword)
      </div>
    </div>

    <div class="form-group">
      <div class="col-md-offset-2 col-md-10">
        <input type="submit" value="Register"
               class="btn btn-default" />
      </div>
    </div>
  </div>
}

@section Scripts {
  @Scripts.Render("~/bundles/jqueryval")
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Security;
using FBMVCExample.Models;

namespace FBMVCExample.Controllers
{
  public class AccountController : Controller
  {
    public ActionResult Login()
    {
      return View();
    }

    [HttpPost]
    [ValidateAntiForgeryToken]
    public ActionResult Login(LoginModel model)
    {
      if (ModelState.IsValid)
      {
        // search user in db
        WEBUSER user = null;
        using (DbModel db = new DbModel())
        {
          user = db.WEBUSERS.FirstOrDefault(
               u => u.EMAIL == model.Name &&
                    u.PASSWD == model.Password);
        }
        // if you find a user with a login and password,
        // then remember it and do a redirect to the start page
        if (user != null)
        {
          FormsAuthentication.SetAuthCookie(model.Name, true);
          return RedirectToAction("Index", "Invoice");
        }
        else
        {
          ModelState.AddModelError("",
            " A user with such a username and password does not exist ");
        }
      }
      return View(model);
    }

    [Authorize(Roles = "admin")]
    public ActionResult Register()
    {
      return View();
    }

    [HttpPost]
    [ValidateAntiForgeryToken]
    public ActionResult Register(RegisterModel model)
    {
      if (ModelState.IsValid)
      {
        WEBUSER user = null;
        using (DbModel db = new DbModel())
        {
          user = db.WEBUSERS.FirstOrDefault(u => u.EMAIL == model.Name);
        }
        if (user == null)
        {
          // create a new user
          using (DbModel db = new DbModel())
          {
            // get a new identifier using a sequence
            int userId = db.NextValueFor("SEQ_WEBUSER");
            db.WEBUSERS.Add(new WEBUSER {
              WEBUSER_ID = userId,
              EMAIL = model.Name,
              PASSWD = model.Password
            });
            db.SaveChanges();
            user = db.WEBUSERS.Where(u => u.WEBUSER_ID == userId)
                     .FirstOrDefault();
            // find the role of manager
            // This role will be the default role, i.e.
            // will be issued automatically upon registration
            var defaultRole =
                db.WEBROLES
                  .Where(r => r.NAME == "manager")
                  .FirstOrDefault();
            // Assign the default role to the newly added user
            if (user != null && defaultRole != null)
            {
              db.WEBUSERINROLES.Add(new WEBUSERINROLE
                {
                  WEBUSER_ID = user.WEBUSER_ID,
                  WEBROLE_ID = defaultRole.WEBROLE_ID
                });
              db.SaveChanges();
            }
          }
          // if the user is successfully added to the database
          if (user != null)
          {
            FormsAuthentication.SetAuthCookie(model.Name, true);
            return RedirectToAction("Login", "Account");
          }
        }
        else
        {
          ModelState.AddModelError("",
            "User with such login already exists");
        }
      }
      return View(model);
    }

    public ActionResult Logoff()
    {
      FormsAuthentication.SignOut();
      return RedirectToAction("Login", "Account");
    }
  }
}

composer global require "laravel/installer"

"repositories": [
  {
    "type": "package",
    "package": {
      "version": "dev-master",
      "name": "sim1984/laravel-firebird",
      "source": {
        "url": "https://github.com/sim1984/laravel-firebird",
        "type": "git",
        "reference": "master"
      },
      "autoload": {
        "classmap": [""]
      }
    }
  }
],

"zofe/rapyd": "2.2.*",
"sim1984/laravel-firebird": "dev-master"

composer update

php artisan vendor:publish

Zofe\Rapyd\RapydServiceProvider::class,
Firebird\FirebirdServiceProvider::class,

'firebird' => [
  'driver' => 'firebird',
  'host' => env('DB_HOST', 'localhost'),
  'port' => env('DB_PORT', '3050'),
  'database' => env('DB_DATABASE', 'examples'),
  'username' => env('DB_USERNAME', 'SYSDBA'),
  'password' => env('DB_PASSWORD', 'masterkey'),
  'charset' => env('DB_CHARSET', 'UTF8'),
  'engine_version' => '3.0.0',
],

 'default' => env('DB_CONNECTION', 'firebird'),

DB_CONNECTION=firebird
DB_HOST=localhost
DB_PORT=3050
DB_DATABASE=examples
DB_USERNAME=SYSDBA
DB_PASSWORD=masterkey

'fields' => [
  'attributes' => ['class' => 'form-control'],
  'date' => [
    'format' => 'Y-m-d',
  ],
  'datetime' => [
    'format'   => 'Y-m-d H:i:s',
    'store_as' => 'Y-m-d H:i:s',
  ],
],

php artisan make:model Customer

namespace App;

use Firebird\Eloquent\Model;

class Customer extends Model
{
  /**
   * Table associated with the model
   *
   * @var string
   */
  protected $table = 'CUSTOMER';

  /**
   * Primary key of the model
   *
   * @var string
   */
  protected $primaryKey = 'CUSTOMER_ID';
  /**
   * Our model does not have a timestamp
   *
   * @var bool
   */
  public $timestamps = false;

  /**
   * The name of the sequence for generating the primary key
   *
   * @var string
   */
  protected $sequence = 'GEN_CUSTOMER_ID';
}

namespace App;

use Firebird\Eloquent\Model;

class Product extends Model
{
  /**
   * Table associated with the model
   *
   * @var string
   */
  protected $table = 'PRODUCT';

  /**
   * Primary key of the model
   *
   * @var string
   */
  protected $primaryKey = 'PRODUCT_ID';

  /**
   * Our model does not have a timestamp
   *
   * @var bool
   */
  public $timestamps = false;

  /**
   * The name of the sequence for generating the primary key
   *
   * @var string
   */
  protected $sequence = 'GEN_PRODUCT_ID';
}

namespace App;

use Firebird\Eloquent\Model;

class Invoice extends Model {

  /**
   * Table associated with the model
   *
   * @var string
   */
  protected $table = 'INVOICE';

  /**
   * Primary key of the model
   *
   * @var string
   */
  protected $primaryKey = 'INVOICE_ID';

  /**
   * Our model does not have a timestamp
   *
   * @var bool
   */
  public $timestamps = false;

  /**
   * The name of the sequence for generating the primary key
   *
   * @var string
   */
  protected $sequence = 'GEN_INVOICE_ID';

  /**
   * Customer
   *
   * @return \App\Customer
   */
  public function customer() {
    return $this->belongsTo('App\Customer', 'CUSTOMER_ID');
  }
  /**
   * Invoice lines
   * @return \App\InvoiceLine[]
   */
  public function lines() {
    return $this->hasMany('App\InvoiceLine', 'INVOICE_ID');
  }

  /**
   * Payed
   */
  public function pay() {
    $connection = $this->getConnection();
    $attributes = $this->attributes;
    $connection->executeProcedure('SP_PAY_FOR_INOVICE',
                                  [$attributes['INVOICE_ID']]);
  }
}

namespace App;

use Firebird\Eloquent\Model;
use Illuminate\Database\Eloquent\Builder;

class InvoiceLine extends Model {

  /**
   * Table associated with the model
   *
   * @var string
   */
  protected $table = 'INVOICE_LINE';

  /**
   * Primary key of the model
   *
   * @var string
   */
  protected $primaryKey = 'INVOICE_LINE_ID';

  /**
   * Our model does not have a timestamp
   *
   * @var bool
   */
  public $timestamps = false;

  /**
   * The name of the sequence for generating the primary key
   *
   * @var string
   */
  protected $sequence = 'GEN_INVOICE_LINE_ID';

  /**
   * Array of names of computed fields
   *
   * @var array
   */
  protected $appends = ['SUM_PRICE'];

  /**
   * Product
   *
   * @return \App\Product
   */
  public function product() {
    return $this->belongsTo('App\Product', 'PRODUCT_ID');
  }

  /**
   * Amount by item
   *
   * @return double
   */
  public function getSumPriceAttribute() {
    return $this->SALE_PRICE * $this->QUANTITY;
  }

  /**
   * Adding a model object to the database
   * Override this method, because in this case, we work with a stored procedure
   *
   * @param \Illuminate\Database\Eloquent\Builder $query
   * @param array $options
   * @return bool
   */
  protected function performInsert(Builder $query, array $options = []) {
    if ($this->fireModelEvent('creating') === false) {
      return false;
    }
    $connection = $this->getConnection();
    $attributes = $this->attributes;
    $connection->executeProcedure('SP_ADD_INVOICE_LINE', [
      $attributes['INVOICE_ID'],
      $attributes['PRODUCT_ID'],
      $attributes['QUANTITY']
    ]);
    // We will go ahead and set the exists property to true,
    // so that it is set when the created event is fired, just in case
    // the developer tries to update it during the event. This will allow
    // them to do so and run an update here.
    $this->exists = true;
    $this->wasRecentlyCreated = true;
    $this->fireModelEvent('created', false);
    return true;
  }

  /**
   * Saving changes to the current model instance in the database
   * Override this method, because in this case, we work with a stored procedure
   *
   * @param \Illuminate\Database\Eloquent\Builder $query
   * @param array $options
   * @return bool
   */
  protected function performUpdate(Builder $query, array $options = []) {
    $dirty = $this->getDirty();
    if (count($dirty) > 0) {
      // If the updating event returns false, we will cancel
      // the update operation so developers can hook Validation systems
      // into their models and cancel this operation if the model does
      // not pass validation. Otherwise, we update.
      if ($this->fireModelEvent('updating') === false) {
        return false;
      }
      $connection = $this->getConnection();
      $attributes = $this->attributes;
      $connection->executeProcedure('SP_EDIT_INVOICE_LINE', [
        $attributes['INVOICE_LINE_ID'],
        $attributes['QUANTITY']
      ]);
      $this->fireModelEvent('updated', false);
    }
  }

  /**
   * Deleting the current model instance from the database
   * Override this method, because in this case, we work with a stored procedure
   *
   * @return void
   */
  protected function performDeleteOnModel() {
    $connection = $this->getConnection();
    $attributes = $this->attributes;
    $connection->executeProcedure('SP_DELETE_INVOICE_LINE',
      [$attributes['INVOICE_LINE_ID']]);
  }
}

$customers = DB::table('CUSTOMER')->get();

DB::table('users')
  ->join('contacts', function ($join) {
    $join->on('users.id', '=', 'contacts.user_id')->orOn(...);
  })
  ->get()

$customers = Customer::all();

$customers = App\Customer::select()
           ->orderBy('name')
           ->take(20)
           ->get();

<?php

/*
* Customer controller
*/
namespace App\Http\Controllers;
use App\Http\Controllers\Controller;
use App\Customer;

class CustomerController extends Controller
{
  /**
   * Show customer list
   *
   * @return Response
   */
  public function showCustomers()
  {
    // get the first 20 customers
    // sorted alphabetically
    $customers = Customer::select()
               ->orderBy('NAME')
               ->take(20)
               ->get();
    var_dump($customers);
  }
}

Route::get('/customers', 'CustomerController@showCustomers');

<?php
/*
 * Customer Controller
 */
namespace App\Http\Controllers;

use App\Http\Controllers\Controller;
use App\Customer;

class CustomerController extends Controller {

  /**
   * Displays the list of customers
   *
   * @return Response
   */
  public function showCustomers() {
    // Connect widget for search
    $filter = \DataFilter::source(new Customer);
    // Search will be by the name of the supplier
    $filter->add('NAME', 'Name', 'text');
    // Set capture for search button
    $filter->submit('Search');
    // Add the filter reset button and assign it caption
    $filter->reset('Reset');
    // Create a grid to display the filtered data
    $grid = \DataGrid::source($filter);
    // output columns
    // Field, label, sorted
    $grid->add('NAME', 'Name', true);
    $grid->add('ADDRESS', 'Address');
    $grid->add('ZIPCODE', 'Zip Code');
    $grid->add('PHONE', 'Phone');
    // Add buttons to view, edit and delete records
    $grid->edit('/customer/edit', 'Edit', 'show|modify|delete');
    // Add the Add Customer button
    $grid->link('/customer/edit', "Add customer", "TR");
    $grid->orderBy('NAME', 'asc');
    // set the number of records per page
    $grid->paginate(10);
    // display the customer template and pass the filter and grid to it
    return view('customer', compact('filter', 'grid'));
  }

  /**
   * Add, edit and delete a customer
   *
   * @return Response
   */
  public function editCustomer() {
    if (\Input::get('do_delete') == 1)
      return "not the first";
    // create an editor
    $edit = \DataEdit::source(new Customer());
    // Set title of the dialog, depending on the type of operation
    switch ($edit->status) {
      case 'create':
        $edit->label('Add customer');
        break;
      case 'modify':
        $edit->label('Edit customer');
        break;
      case 'do_delete':
        $edit->label('Delete customer');
        break;
      case 'show':
        $edit->label("Customer's card");
        // add a link to go back to the list of customers
        $edit->link('customers', 'Back', 'TR');
        break;
    }
    // set that after the operations of adding, editing and deleting,
    // you need to return to the list of customers
    $edit->back('insert|update|do_delete', 'customers');
    // We add editors of a certain type, assign them a label and
    // associate them with the attributes of the model
    $edit->add('NAME', 'Name', 'text')->rule('required|max:60');
    $edit->add('ADDRESS', 'Address', 'textarea')
         ->attributes(['rows' => 3])
         ->rule('max:250');
    $edit->add('ZIPCODE', 'Zip code', 'text')->rule('max:10');
    $edit->add('PHONE', 'Phone', 'text')->rule('max:14');
    // display the template customer_edit and pass it to the editor
    return $edit->view('customer_edit', compact('edit'));
  }
}

<?php

/*
 * Product Controller
 */
namespace App\Http\Controllers;

use App\Http\Controllers\Controller;
use App\Product;

class ProductController extends Controller {

  /**
   * Displays a list of products
   *
   * @return Response
   */
  public function showProducts() {
    // Connect widget for search
    $filter = \DataFilter::source(new Product);
    // The search will be by product name
    $filter->add('NAME', 'Name', 'text');
    $filter->submit('Search');
    $filter->reset('Reset');
    // Create a grid to display the filtered data
    $grid = \DataGrid::source($filter);
    // output grid columns
    // Field, label, sorting
    $grid->add('NAME', 'Name', true);
    // Set the format with 2 decimal places
    $grid->add('PRICE|number_format[2,., ]', 'Price');
    $grid->row(function($row) {
      // Press the money values to the right
      $row->cell('PRICE')->style("text-align: right");
    });
    // Add buttons to view, edit and delete records
    $grid->edit('/product/edit', 'Edit', 'show|modify|delete');
    // Add the Add product button
    $grid->link('/product/edit', "?????????? ??????", "TR");
    // set sorting
    $grid->orderBy('NAME', 'asc');
    // set the number of records per page
    $grid->paginate(10);
    // display the customer template and pass the filter and grid to it
    return view('product', compact('filter', 'grid'));
  }

  /**
   * Add, edit and delete products
   *
   * @return Response
   */
  public function editProduct() {
    if (\Input::get('do_delete') == 1)
      return "not the first";
    // create editor
    $edit = \DataEdit::source(new Product());
    // Set the title of the dialog, depending on the type of operation
    switch ($edit->status) {
      case 'create':
        $edit->label('Add product');
        break;
      case 'modify':
        $edit->label('Edit product');
        break;
      case 'do_delete':
        $edit->label('Delete product');
        break;
      case 'show':
        $edit->label("Product's card");
        $edit->link('products', 'Back', 'TR');
        break;
    }
    // set that after the operations of adding, editing and deleting,
    // you need to return to the list of products
    $edit->back('insert|update|do_delete', 'products');
    // We add editors of a certain type, assign them a label and
    // associate them with the attributes of the model
    $edit->add('NAME', 'Name', 'text')->rule('required|max:100');
    $edit->add('PRICE', 'Price', 'text')->rule('max:19');
    $edit->add('DESCRIPTION', 'Description', 'textarea')
         ->attributes(['rows' => 8])
         ->rule('max:8192');
    // display the template product_edit and pass it to the editor
    return $edit->view('product_edit', compact('edit'));
  }
}

<?php
/*
 * Invoice controller
 */
namespace App\Http\Controllers;

use App\Http\Controllers\Controller;
use App\Invoice;
use App\Customer;
use App\Product;
use App\InvoiceLine;

class InvoiceController extends Controller {

  /**
   * Show invoice list
   *
   * @return Response
   */
  public function showInvoices() {
    // The invoice model will also select the related suppliers
    $invoices = Invoice::with('customer');
    // Add a widget for search.
    $filter = \DataFilter::source($invoices);
    // Let's filter by date range
    $filter->add('INVOICE_DATE', 'Date', 'daterange');
    // and filter by customer name
    $filter->add('customer.NAME', 'Customer', 'text');
    $filter->submit('Search');
    $filter->reset('Reset');
    // Create a grid to display the filtered data
    $grid = \DataGrid::source($filter);
    // output grid columns
    // Field, caption, sorted
    // For the date we set an additional function that converts
    // the date into a string
    $grid->add('INVOICE_DATE|strtotime|date[Y-m-d H:i:s]', 'Date', true);
    // for money we will set a format with two decimal places
    $grid->add('TOTAL_SALE|number_format[2,., ]', 'Amount');
    $grid->add('customer.NAME', 'Customer');
    // Boolean printed as Yes/No
    $grid->add('PAID', 'Paid')
         ->cell(function( $value, $row) {
                  return $value ? 'Yes' : 'No';
                });
    // set the function of processing each row
    $grid->row(function($row) {
      // The monetary values are pressed to the right
      $row->cell('TOTAL_SALE')->style("text-align: right");
      // paint the paid waybills in a different color
      if ($row->cell('PAID')->value == 'Yes') {
        $row->style("background-color: #ddffee;");
      }
    });
    // Add buttons to view, edit and delete records
    $grid->edit('/invoice/edit', '??????????????', 'show|modify|delete');
    // Add the button for adding invoices
    $grid->link('/invoice/edit', "?????????? ?????", "TR");

    $grid->orderBy('INVOICE_DATE', 'desc');
    // set the number of records per page
    $grid->paginate(10);
    // display the customer template and pass the filter and grid to it
    return view('invoice', compact('filter', 'grid'));
  }

  /**
   * Add, edit and delete invoice
   *
   * @return Response
   */
  public function editInvoice() {
    // get the text of the saved error, if it was
    $error_msg = \Request::old('error_msg');
    // create an invoice invoice editor
    $edit = \DataEdit::source(new Invoice());
    // if the invoice is paid, then we generate an error when trying to edit it
    if (($edit->model->PAID) && ($edit->status === 'modify')) {
      $edit->status = 'show';
      $error_msg = 'Editing is not possible. The account has already been paid.';
    }
    // if the invoice is paid, then we generate an error when trying to delete it
    if (($edit->model->PAID) && ($edit->status === 'delete')) {
      $edit->status = 'show';
      $error_msg = 'Deleting is not possible. The account has already been paid.';
    }
    // Set the label of the dialog, depending on the type of operation
    switch ($edit->status) {
      case 'create':
        $edit->label('Add invoice');
        break;

      case 'modify':
        $edit->label('Edit invoice');
        break;

      case 'do_delete':
        $edit->label('Delete invoice');
        break;

      case 'show':
        $edit->label('Invoice');
        $edit->link('invoices', 'Back', 'TR');
        // If the invoice is not paid, we show the pay button
        if (!$edit->model->PAID)
          $edit->link('invoice/pay/' . $edit->model->INVOICE_ID,
                      'Pay', 'BL');
        break;
    }
    // set that after the operations of adding, editing and deleting,
    // we return to the list of invoices
    $edit->back('insert|update|do_delete', 'invoices');
    // set the "date" field, that it is mandatory
    // The default is the current date
    $edit->add('INVOICE_DATE', '????', 'datetime')
         ->rule('required')
         ->insertValue(date('Y-m-d H:i:s'));
    // add a field for entering the customer. When typing a customer name,
    // a list of prompts will be displayed
    $edit->add('customer.NAME', 'Customer', 'autocomplete')
         ->rule('required')
         ->options(Customer::lists('NAME', 'CUSTOMER_ID')
                           ->all());
    // add a field that will display the invoice amount, read-only
    $edit->add('TOTAL_SALE', 'Amount', 'text')
         ->mode('readonly')
         ->insertValue('0.00');
    // add paid checkbox
    $paidCheckbox = $edit->add('PAID', 'Paid', 'checkbox')
                         ->insertValue('0')
                         ->mode('readonly');
    $paidCheckbox->checked_output = 'Yes';
    $paidCheckbox->unchecked_output = 'No';
    // create a grid to display the invoice line rows
    $grid = $this->getInvoiceLineGrid($edit->model, $edit->status);
    // we display the invoice_edit template and pass the editor and grid to
    // it to display the invoice invoice items
    return $edit->view('invoice_edit', compact('edit', 'grid', 'error_msg'));
  }

  /**
   * Payment of invoice
   *
   * @return Response
   */
  public function payInvoice($id) {
    try {
      // find the invoice by ID
      $invoice = Invoice::findOrFail($id);
      // call the payment procedure
      $invoice->pay();
    } catch (\Illuminate\Database\QueryException $e) {
      // if an error occurs, select the exclusion text
      $pos = strpos($e->getMessage(), 'E_INVOICE_ALREADY_PAYED');
      if ($pos !== false) {
        // redirect to the editor page and display the error there
        return redirect('invoice/edit?show=' . $id)
             ->withInput(['error_msg' => 'Invoice already paid']);
      } else
        throw $e;
    }
    // redirect to the editor page
    return redirect('invoice/edit?show=' . $id);
  }

  /**
   * Returns the grid for the invoice item
   * @param \App\Invoice $invoice
   * @param string $mode
   * @return \DataGrid
   */
  private function getInvoiceLineGrid(Invoice $invoice, $mode) {
    // Get invoice items
    // For each ivoice item, the associated product will be initialized
    $lines = InvoiceLine::with('product')
                ->where('INVOICE_ID', $invoice->INVOICE_ID);
    // Create a grid for displaying invoice items
    $grid = \DataGrid::source($lines);
    // output grid columns
    // Field, caption, sorted
    $grid->add('product.NAME', 'Name');
    $grid->add('QUANTITY', 'Quantity');
    $grid->add('SALE_PRICE|number_format[2,., ]', 'Price')
         ->style('min-width: 8em;');
    $grid->add('SUM_PRICE|number_format[2,., ]', 'Amount')
         ->style('min-width: 8em;');
    // set the function of processing each row
    $grid->row(function($row) {
      $row->cell('QUANTITY')->style("text-align: right");
      // The monetary values are pressed to the right
      $row->cell('SALE_PRICE')->style("text-align: right");
      $row->cell('SUM_PRICE')->style("text-align: right");
    });
    if ($mode == 'modify') {
      // Add buttons to view, edit and delete records
      $grid->edit('/invoice/editline', '??????????????', 'modify|delete');
      // Add a button to add an invoice item
      $grid->link('/invoice/editline?invoice_id=' . $invoice->INVOICE_ID,
                  "Add item", "TR");
    }
    return $grid;
  }

  /**
   * Add, edit and delete invoice items
   *
   * @return Response
   */
  public function editInvoiceLine() {
    if (\Input::get('do_delete') == 1)
      return "not the first";
    $invoice_id = null;
    // create the editor of the invoice item
    $edit = \DataEdit::source(new InvoiceLine());
    // Set the label of the dialog, depending on the type of operation
    switch ($edit->status) {
      case 'create':
        $edit->label('Add invoice item');
        $invoice_id = \Input::get('invoice_id');
        break;

      case 'modify':
        $edit->label('Edit invoice item');
        $invoice_id = $edit->model->INVOICE_ID;
        break;

      case 'delete':
        $invoice_id = $edit->model->INVOICE_ID;
        break;

      case 'do_delete':
        $edit->label('Delete invoice item');
        $invoice_id = $edit->model->INVOICE_ID;
        break;
    }
    // make url to go back
    $base = str_replace(\Request::path(), '', strtok(\Request::fullUrl(), '?'));
    $back_url = $base . 'invoice/edit?modify=' . $invoice_id;
    // set the page to go back
    $edit->back('insert|update|do_delete', $back_url);
    $edit->back_url = $back_url;
    // add a hidden field with an invoice code
    $edit->add('INVOICE_ID', '', 'hidden')
         ->rule('required')
         ->insertValue($invoice_id)
         ->updateValue($invoice_id);
    // Add a field for entering the goods. When you type the product name,
    // a list of prompts is displayed.
    $edit->add('product.NAME', 'Name', 'autocomplete')
         ->rule('required')
         ->options(Product::lists('NAME', 'PRODUCT_ID')->all());
    // Field for input quantity
    $edit->add('QUANTITY', 'Quantity', 'text')
         ->rule('required');
    // display the template invoice_line_edit and pass it to the editor
    return $edit->view('invoice_line_edit', compact('edit'));
  }
}

Route::get('/', 'InvoiceController@showInvoices');
Route::get('/customers', 'CustomerController@showCustomers');
Route::any('/customer/edit', 'CustomerController@editCustomer');
Route::get('/products', 'ProductController@showProducts');
Route::any('/product/edit', 'ProductController@editProduct');
Route::get('/invoices', 'InvoiceController@showInvoices');
Route::any('/invoice/edit', 'InvoiceController@editInvoice');
Route::any('/invoice/pay/{id}', 'InvoiceController@payInvoice');
Route::any('/invoice/editline', 'InvoiceController@editInvoiceLine');

SELECT *
  FROM author a
  JOIN book b ON a.id = b.author_id
 WHERE a.year_of_birth > 1920
   AND a.first_name = 'Paulo'
 ORDER BY b.title

Result<Record> result =
dsl.select()
   .from(AUTHOR.as("a"))
   .join(BOOK.as("b")).on(a.ID.equal(b.AUTHOR_ID))
   .where(a.YEAR_OF_BIRTH.greaterThan(1920)
   .and(a.FIRST_NAME.equal("Paulo")))
   .orderBy(b.TITLE)
   .fetch();

Result<Record> result =
dsl.select()
   .from(AUTHOR)
   .join(BOOK).on(AUTHOR.ID.equal(BOOK.AUTHOR_ID))
   .where(AUTHOR.YEAR_OF_BIRTH.greaterThan(1920)
   .and(AUTHOR.FIRST_NAME.equal("Paulo")))
   .orderBy(BOOK.TITLE)
   .fetch();

SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, COUNT(*)
FROM AUTHOR
  JOIN BOOK ON AUTHOR.ID = BOOK.AUTHOR_ID
WHERE BOOK.LANGUAGE = 'DE'
  AND BOOK.PUBLISHED > '2008-01-01'
GROUP BY AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
  HAVING COUNT(*) > 5
ORDER BY AUTHOR.LAST_NAME ASC NULLS FIRST
  OFFSET 1 ROWS
  FETCH FIRST 2 ROWS ONLY

dsl.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count())
   .from(AUTHOR)
   .join(BOOK).on(BOOK.AUTHOR_ID.equal(AUTHOR.ID))
   .where(BOOK.LANGUAGE.equal("DE"))
   .and(BOOK.PUBLISHED.greaterThan("2008-01-01"))
   .groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
   .having(count().greaterThan(5))
   .orderBy(AUTHOR.LAST_NAME.asc().nullsFirst())
   .limit(2)
   .offset(1)
   .fetch();

SelectFinalStep<?> select
    = dsl.select()
         .from(PRODUCT);

SelectQuery<?> query = select.getQuery();
switch (searchOper) {
    case "eq":
         query.addConditions(PRODUCT.NAME.eq(searchString));
         break;
    case "bw":
         query.addConditions(PRODUCT.NAME.startsWith(searchString));
         break;
    case "cn":
         query.addConditions(PRODUCT.NAME.contains(searchString));
         break;
}
switch (sOrd) {
    case "asc":
         query.addOrderBy(PRODUCT.NAME.asc());
         break;
    case "desc":
         query.addOrderBy(PRODUCT.NAME.desc());
         break;
}
return query.fetchMaps();

dsl.select()
   .from(BOOK)
   .where(BOOK.ID.equal(5))
   .and(BOOK.TITLE.equal("Animal Farm"))
   .fetch();

dsl.select()
   .from(BOOK)
   .where(BOOK.ID.equal(val(5)))
   .and(BOOK.TITLE.equal(val("Animal Farm")))
   .fetch();

SELECT *
FROM BOOK
WHERE BOOK.ID = ?
  AND BOOK.TITLE = ?

Select<?> select =
  dsl.select()
     .from(BOOK)
     .where(BOOK.ID.equal(5))
     .and(BOOK.TITLE.equal("Animal Farm"));
Param<?> param = select.getParam("2");
Param.setValue("Animals as Leaders");

Query query1 =
  dsl.select()
     .from(AUTHOR)
     .where(LAST_NAME.equal("Poe"));
query1.bind(1, "Orwell");

// Create a query with a named parameter. You can then use that name for
// accessing the parameter again
Query query1 =
  dsl.select()
     .from(AUTHOR)
     .where(LAST_NAME.equal(param("lastName", "Poe")));
Param<?> param1 = query.getParam("lastName");

// Or, keep a reference to the typed parameter in order
// not to lose the <T> type information:
Param<String> param2 = param("lastName", "Poe");
Query query2 =
  dsl.select()
     .from(AUTHOR)
     .where(LAST_NAME.equal(param2));

// You can now change the bind value directly on the Param reference:
param2.setValue("Orwell");

// Or, with named parameters
Query query2 =
  dsl.select()
     .from(AUTHOR)
     .where(LAST_NAME.equal(param("lastName", "Poe")));
query2.bind("lastName", "Orwell");

INSERT INTO AUTHOR
       (ID, FIRST_NAME, LAST_NAME)
VALUES (100, 'Hermann', 'Hesse');

dsl.insertInto(AUTHOR,
        AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
   .values(100, "Hermann", "Hesse")
   .execute();

UPDATE AUTHOR
   SET FIRST_NAME = 'Hermann',
       LAST_NAME = 'Hesse'
 WHERE ID = 3;

dsl.update(AUTHOR)
   .set(AUTHOR.FIRST_NAME, "Hermann")
   .set(AUTHOR.LAST_NAME, "Hesse")
   .where(AUTHOR.ID.equal(3))
   .execute();

DELETE FROM AUTHOR
 WHERE ID = 100;

dsl.delete(AUTHOR)
   .where(AUTHOR.ID.equal(100))
   .execute();

int invoiceId = dsl.nextval(GEN_INVOICE_ID).intValue();

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

SELECT NEXT VALUE FOR GEN_INVOICE_ID
  FROM RDB$DATABASE

EXECUTE PROCEDURE SP_ADD_INVOICE   (
   :INVOICE_ID, :CUSTOMER_ID, :INVOICE_DATE );

@Autowired
private DataSourceTransactionManager txMgr;

TransactionStatus tx = txMgr.getTransaction(new DefaultTransactionDefinition());
try {
    // actions in the context of a transaction
    for (int i = 0; i < 2; i++)
        dsl.insertInto(BOOK)
           .set(BOOK.ID, 5)
           .set(BOOK.AUTHOR_ID, 1)
           .set(BOOK.TITLE, "Book 5")
           .execute();
    // transaction commit
    txMgr.commit(tx);
}
catch (DataAccessException e) {
    // transaction rollback
    txMgr.rolback(tx);
}

/**
 * Delete customer
 *
 * @param customerId
 */
@Transactional(propagation = Propagation.REQUIRED,
               isolation = Isolation.REPEATABLE_READ)
public void delete(int customerId) {
    this.dsl.deleteFrom(CUSTOMER)
            .where(CUSTOMER.CUSTOMER_ID.eq(customerId))
            .execute();
}

package ru.ibase.fbjavaex.managers;

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.Tables.CUSTOMER;
import static ru.ibase.fbjavaex.exampledb.Sequences.GEN_CUSTOMER_ID;

/**
 * Customer manager
 *
 * @author Simonov Denis
 */
public class CustomerManager {

    @Autowired(required = true)
    private DSLContext dsl;

    /**
     * Adding a customer
     *
     * @param name
     * @param address
     * @param zipcode
     * @param phone
     */
    @Transactional(propagation = Propagation.REQUIRED,
                   isolation = Isolation.REPEATABLE_READ)
    public void create(String name, String address, String zipcode, String phone) {
        if (zipcode != null) {
            if (zipcode.trim().isEmpty()) {
                zipcode = null;
            }
        }

        int customerId = this.dsl.nextval(GEN_CUSTOMER_ID).intValue();

        this.dsl
                .insertInto(CUSTOMER,
                        CUSTOMER.CUSTOMER_ID,
                        CUSTOMER.NAME,
                        CUSTOMER.ADDRESS,
                        CUSTOMER.ZIPCODE,
                        CUSTOMER.PHONE)
                .values(
                        customerId,
                        name,
                        address,
                        zipcode,
                        phone
                )
                .execute();
    }

    /**
     * Editing a customer
     *
     * @param customerId
     * @param name
     * @param address
     * @param zipcode
     * @param phone
     */
    @Transactional(propagation = Propagation.REQUIRED,
                   isolation = Isolation.REPEATABLE_READ)
    public void edit(int customerId, String name, String address,
                     String zipcode, String phone) {

        if (zipcode != null) {
            if (zipcode.trim().isEmpty()) {
                zipcode = null;
            }
        }

        this.dsl.update(CUSTOMER)
                .set(CUSTOMER.NAME, name)
                .set(CUSTOMER.ADDRESS, address)
                .set(CUSTOMER.ZIPCODE, zipcode)
                .set(CUSTOMER.PHONE, phone)
                .where(CUSTOMER.CUSTOMER_ID.eq(customerId))
                .execute();
    }

    /**
     * Deleting a customer
     *
     * @param customerId
     */
    @Transactional(propagation = Propagation.REQUIRED,
                   isolation = Isolation.REPEATABLE_READ)
    public void delete(int customerId) {
        this.dsl.deleteFrom(CUSTOMER)
                .where(CUSTOMER.CUSTOMER_ID.eq(customerId))
                .execute();
    }
}

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

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

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

<%@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>&copy; 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>

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("&times;")
                        .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("&times;")
                        .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("&times;")
                        .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);