Real life use example in Unit Tests

The following example depicts a logic used inside an On-Line Accounting Suite that is used for creating new company record, populating its clients, invoices, payments and other information.

// Create a new company
$company = new Model_Company($db);
$company->set([
    'name'=>'Test Company 1',
    'type'=>'Limited Company',
    'vat_registered'=>true
]);
$company->save();

// Create two new clients, one is sole trader, other is limited company
$client = $company->ref('Client');
list($john_id, $agile_id) = $m->insertMany([
    ['name'=>'John Smith Consulting','vat_registered'=>false],
    'Agile Software Limited',
]);

// Insert a first, default invoice for our sole-trader
$john = $company->load($john_id);
$john_invoices = $john->ref('Invoice');
$john_invoices->insert([
    'ref_no'=>'INV1',
    'due_date'=>(new Date())->add(new DateInterval('2w')), // due in 2 weeks
    'lines'=>[
        ['descr'=>'Sold some sweets', 'total_gross'=>100.00],
        ['descr'=>'Delivery', 'total_gross'=>10.00]
    ]
]);

// Use custom method to create a sub-nominal
$company->ref('Nominal')->insertSubNominal('Sales', 'Discounted');

// Insert our second invoice using set referencing
$company->ref('Client')->id($agile_id)->refSet('Invoice')->insert([
    'lines'=>[
        [
            'item_id'=>$john->ref('Product')->insert('Cat Food'),
            'nominal'=>'Sales:Discounted',
            'total_net'=>50.00,
            'vat_rate'=>23
            // calculates total_gross at 61.50.
        ],
        [
            'item_id'=>$john->ref('Service')->insert('Delivery'),
            'total_net'=>10.00,
            'vat_rate'=>'23%'
            // calculates total_gross at 12.30
        ]
    ]
]);

// Next we create bank account
$hsbc = $john->ref('Account')->set('name','HSBC')->save();

// And each of our invoices will have one new payment
foreach($john_invoices as $invoice) {
    $invoice->ref('Payment')->insert([
        'amount'=>10.20,
        'bank_account_id'=>$hsbc
    ]);
}

// Now let's execute report
$debt = $john->add('Model_Report_Debtors');

// This should give us total amount owed by all clients: 
// (100.00+10.00) + (61.50 + 12.30) - 10.20*2
$this->assertEquals(163.40, $debt->sum('amount'));

Next, I'll go through the code and explain what does it do:

// Create a new company
$company = new Model_Company($db);

$db is our persistence connection. For the purposes of our test, this can be either a MySQL, Oracle, MongoDB database or even Array, all of the remaining code will work consistently producing the same outcome.

$company->set([
    'name'=>'Test Company 1',
    'type'=>'Limited Company',
    'vat_registered'=>true
]);
$company->save();

Here we are setting default values for the company and using a save() method, which stores data into $db. After data is stored, the active record of $company is set to Test Company 1, which means all the further operations will be done on this specific record.

// Create two new clients, one is sole trader, other is limited company
$client = $company->ref('Client');
list($john_id, $agile_id) = $m->insertMany([
    ['name'=>'John Smith Consulting','vat_registered'=>false],
    'Agile Software Limited',
]);

Company model is (hasMany) related to other entities such as 'Client', 'Product', 'Service'. By traversing reference 'Client' we get a $client object. The DataSet here is limited to only clients of our active company. We won't be able to access, create or delete clients for any other companies.

Calling insert() method allows us to insert multiple records into the database, generating fewer queries then save(). Ideally, insert() should be able to create a single query that inserts two rows and returns two IDs.

// Insert a first, default invoice for our sole-trader
$john = $company->load($john_id);
$john_invoices = $john->ref('Invoice');

for our first company we use load() method. This one will load John's company and then we are using ref() to traverse into John's invoices.

$john_invoices->insert([
    'ref_no'=>'INV1',
    'due_date'=>(new Date())->add(new DateInterval('2w')), // due in 2 weeks
    'lines'=>[
        ['descr'=>'Sold some sweets', 'total_gross'=>100.00],
        ['descr'=>'Delivery', 'total_gross'=>10.00]
    ]
]);

Model_Invoice which describes $john_invoices object contains a post-insert hook. Once Object is inserted, it will look inside 'lines' property where it will find multiple invoice lines.

// Use custom method to create a sub-nominal
$company->ref('Nominal')->insertSubNominal('Sales', 'Discounted');

Model_Nominal implements a tree-like structure for the nominals. There are some default ones, which were created automatically when new company has been saved, such as 'Sales' or 'Assets'. It's possible to add more nominals using a custom method insertSubNominal(). You would specify the name of the parent nominal as well as the name of a new nominal.

// Insert our second invoice using set referencing
$company->ref('Client')->id($agile_id)->refSet('Invoice')->insert([
    'lines'=>[
        [
            'item_id'=>$john->ref('Product')->insert('Cat Food'),
            'nominal'=>'Sales:Discounted',
            'total_net'=>50.00,
            'vat_rate'=>23
            // calculates total_gross at 61.50.
        ],
        [
            'item_id'=>$john->ref('Service')->insert('Delivery'),
            'total_net'=>10.00,
            'vat_rate'=>'23%'
            // calculates total_gross at 12.30
        ]
    ]
]);

Invoice relies on hasOne() to create a relationship with the nominal, therefore there are actually two fields: 'nominal' and 'nominal_id' present in the model. If 'nominal_id' is specified, then it will be inserted when saved. If 'nominal' is specified instead, then a sub-query will be used to determine the ID of selected nominal by attempting to load it by name.

ref_no and due_date are omitted and therefore default values will be used. When defining 'lines' we are specifying vat_rate and total_net. The total_gross amount is automatically calculated before saving.

I've used insert() method to quickly create new Product and new Service. Both of those models are extending 'Model_Item', so they are both OK to be specified for item_id in the invoice line. Because we only specify non-array argument to Invoice, then it will assume that we have specified a $title_field only, and keep all the other fields to their default values.

// Next we create bank account
$hsbc = $john->ref('Account')->set('name','HSBC')->save();

Here we are using save() to create a new bank account with name HSBC. While we can specify references by ID or Name, we can also specify them using the model such as $hsbc.

// And each of our invoices will have one new payment
foreach($john_invoices as $invoice) {
    $invoice->ref('Payment')->insert([
        'amount'=>10.20,
        'bank_account_id'=>$hsbc
    ]);
}

The above code iterates through the list of our added invoices. At this point we should already have 2 invoices, therefore, for each of those invoices, we will create a new payment with specified amount.

Note that we have Model_Invoice and Model_Payment. Those two relate in a many-to-many fashion where multiple invoices can be covered by multiple payments. The invoice->ref('Payment') is defined in such a way so that if you add invoice like that, it will be automatically linked with the Invoice for the full amount.

With the code above, we should have the following records added in the database:

• 1 new company
• 2 new clients
• 2 new invoices
• 4 new invoice lines
• 1 new product
• 1 new service
• 1 new nominal
• 2 new payments

The records will also relate to each-other properly.

// Now let's execute report
$debt = $john->add('Model_Report_Debtors');

Here we are creating a report model. Debtor report is based on Invoice and Payment records that are connected together through UNION. The 'amount' would be positive for invoice and negative for payment.

// This should give us total amount owed by all clients: 
// (100.00+10.00) + (61.50 + 12.30) - 10.20*2
$this->assertEquals(163.40, $debt->sum('amount'));

With the new model we can perform a sum() operation that calculates a sum of all amounts. Based on the information that we have inserted into the database so far, it should return 163.40.