• Using Transactd PHP ORM
• Sample application
• Calculate and save sales invoices

Calculate and save sales invoices

In this chapter, you can learn about the following contents:

• Numbering of invoices
• How tow use server cursor
• Numbering to items in collection
• Override save method
• Conflict detection by using timestamp
• Suppress automatic updating of timestamp field

Extend InvoiceAmount

Total of the invoice is held in $amount in Invoice. Add following methods to InvoiceAmount:

• calc : Calculate the total and balance with InvoiceItem collection and the last balance.
• increment and decrement : Increase or decrease the amount with InvoiceItem instance.

class InvoiceAmount
{
    // (omitted)

    private $oldBlance = null;

    public function calc(Collection $rows, $base)
    {
        if ($this->oldBlance === null) {
            $this->oldBlance = $this->balance;
        }
        $this->reset();
        $ar = $rows->getNativeArray();
        foreach($ar as $row) {
            $this->increment($row);
        }
        $this->balance = $base + $this->total();
    }

    public function increment(InvoiceItem $row)
    {
        if ($row->line_type === InvoiceItem::SALES) {
            $this->sales += $row->amount;
            $this->tax += $row->tax;
        } elseif ($row->line_type === InvoiceItem::PAYMENT) {
            $this->payment += $row->amount;
        }
    }

    public function decrement(InvoiceItem $row)
    {
        if ($row->line_type === InvoiceItem::SALES) {
            $this->sales -= $row->amount;
            $this->tax -= $row->tax;
        } elseif ($row->line_type === InvoiceItem::PAYMENT) {
            $this->payment -= $row->amount;
        }
    }
}

$oldBlance will be used to calculate the balance difference when read, edit and save an existing invoice.

In foreach in calc method, we get the PHP native array from the collection by getNativeArray and use it for loop. It is faster than passing the collection to foreach directly.

Numbering of invoices

Before calculating the last balance, we are going to assign the invoice number. Because it is impossible to identify the previous invoice if the invoice number is not decided.

In this example, we use the invoice number which is same as auto-incremented number. Originally it can be assigned by auto-increment, but we assign numbers for our study.

Add assignInvoiceNumber method to Invoice class:

class Invoice extends Model
{
    // (omitted)

    private function assignInvoiceNumber()
    {
        // Search for the last invoice in order of primary key
        $it = Invoice::serverCursor(0, QueryExecuter::SEEK_LAST);

        // Set +1 number if found
        if ($it->valid()) {
            $inv = $it->current();
            $this->id = $inv->id + 1;
        } else {
            $this->id = 1;
        }
    }

I'm going to explain about lock here.

In this case, we specify MULTILOCK_GAP for beginTransaction in saveWithTransaction method. Therefore, access to the last invoice by serverCursor exclusive-locks that record and supremum behind it.

Also, since it is MULTILOCK, the lock will not be released until the transaction is finished. In other words, no one else can insert invoice until the transaction is finished. Therefore, it is guaranteed that the invoice number got here is unique.

Get the last balance when saving

When saving the invoice, calculate the final balance by finding the previous balance. The process is as follows:

1. Get the previous balance.
2. Calculate the total of the invoice.
3. Calculate the final balance.
4. Save them.

class Invoice extends Model
{
    // (omitted)

    private $baseBalance = 0;

    // Get the previous balance
    private function readBaseBalance()
    {
        $this->baseBalance = 0;

        // Get the server cursor and seek previous invoice
        $it = $this->serverCursor(1, QueryExecuter::SEEK_LESSTHAN, Transactd::ROW_LOCK_S);

        // Check if the customer is the same as self and get the balance
        if ($it->valid()) {
            $inv = $it->current();
            if ($inv->customer_id === $this->customer_id) {
                $this->baseBalance = $inv->amount->balance;
            }
        }

        // return server cursor
        return $it;
    }
}

QueryExecuter::SEEK_LESSTHAN seeks the previous record of keyValue record in the specified index order. If the previous record is the same customer, read the balance. If not, it is first invoice of the customer, then the balance is 0. Also, since this process only reads the record and does not update it, specify Transactd::ROW_LOCK_S.

In this process, using Transactd::ROW_LOCK_S does not improve the simultaneous effectiveness. However, we show this example to make habits to avoid unnecessary exclusive locks whenever possible.

Modify to use server cursor to save Invoice

We got the server cursor when get the last balance. Let's change save method using this. We implement inserting and updating separately. Also, assign the line number in case of inserting. And update the cache of this Invoice.

class Invoice extends Model
{
    // (omitted)

    public function save($options = 0, $forceInsert = false)
    {
        InvoiceItem::$handler = new InvoiceItemSaveHandler();

        // Invoice Numbering
        if ($this->id === 0) {
            $this->assignInvoiceNumber();
            $forceInsert = true;
        }

        // Get the last balance
        $it = $this->readBaseBalance();

        // Calculate the total
        $this->amount->calc($this->items, $this->baseBalance);

        // Save this invoice with server cursor
        if ($forceInsert) {
            // Row Numbering
            $this->items->renumber('row');
            $it->insert($this);
        } else {
            $it = $this->serverCursor(1, QueryExecuter::SEEK_EQUAL);
            $it->validOrFail();
            $it->update($this);
        }

        // Save rows
        $this->items->save();
        $this->updateCache(); // Update caches
    }

Modify delete method in the same way.

    public function delete($options = null)
    {
        InvoiceItem::$handler = new InvoiceItemSaveHandler();
        $it = $this->serverCursor(1, QueryExecuter::SEEK_EQUAL);
        $it->validOrFail();
        $it->delete();
        $this->items->delete();
        $this->updateCache(true /* clear */); // Clear cache
    }

With this, you can number the invoice and save or delete it with the total and balance. Also, the cache could be updated.

Detect change conflict

What happens if the invoice you are editing has been modified by another user before saving?

There is no inconsistency in calculations such as invoice, stocks, and daily total. However, since the invoice is saved without reading changes, changes made by other users will be lost.

In order to prevent this, compare the update datetime between reading and updating. This detects changes by other users and throw an exception.

In updating with server cursor, the latest record is locked. So its update datetime can be compared with it at reading.

Add conflictFail method which compare datetimes and throw exceptions. Also, change $update_at property to protected so that the comparison object is not changed.

class Invoice extends Model
{
    // (omitted)

    protected $update_at;

    private function conflictFail($inv)
    {
        if ($this->update_at !== $inv->update_at) {
            throw new Exception('This invoice is already changed by other user.');
        }
    }

Add calling conflictFail after lock reading in save and delete method.

    public function save($options = 0, $forceInsert = false)
    {
        // (omitted)

        // Save this invoice with server cursor
        if ($forceInsert) {
            // Row Numbering
            $this->items->renumber('row');
            $it->insert($this);
        } else {
            $it = $this->serverCursor(1, QueryExecuter::SEEK_EQUAL);
            $it->validOrFail();
            $this->conflictFail($it->current()); // Detect change conflict
            $it->update($this);
        }

        // (omitted)
    }

    public function delete($options = null)
    {
        InvoiceItem::$handler = new InvoiceItemSaveHandler();
        $it = $this->serverCursor(1, QueryExecuter::SEEK_EQUAL);
        $it->validOrFail();
        $this->conflictFail($it->current()); // Detect change conflict
        $it->delete();
        $this->items->delete();
        $this->updateCache(true /* clear */); // Clear cache
    }

Detection example by version field

For reference, here is an example of conflict detection using version column. It only changes conflictFail method. Change this method to incrementVersionOrFail.

// Add `version` column as 2bytes `unsigned integer` to `invoicies` table beforehand.

class Invoice extends Model
{
    // (omitted)

    protected $version;

    private function incrementVersionOrFail($inv)
    {
        if ($this->version !== $inv->version) {
            throw new Exception('This invoice is already changed by other user.');
        }
        ++$this->version; // increment
    }

Update balance

When invoices are added or old invoices are changed, the customer's balance will change.

The balance is recorded in invoices table. We also need to update all invoices of the customer after the changed invoice.

When the invoice is inserted, update the balance of the same customer's all invoices after the inserted invoice. At this time, if you know increase / decrease amount, you can update Invoice without reading InvoiceItem. Therefore, get increase / decrease amount on saving invoice, and use that value to update subsequent invoices.

Calculate increase / decrease amount

In InvoiceAmount, the balance before change is saved as $oldBalance. Compare this with the changed balance to get increase / decrease amount.

class InvoiceAmount
{
    // (omitted)

    public function difference()
    {
        return $this->balance - $this->oldBlance;
    }
}

Update balance

Add updateBalanceAmount method which update the customer's all invoices after the updated invoice. Use server cursor to update.

class Invoice extends Model
{
    // (omitted)

    private function updateBalanceAmount($difference)
    {
        if ($difference !== 0) {
            // Move to next invoice of current
            $it = $this->serverCursor(1, QueryExecuter::SEEK_GREATER);
            foreach($it as $inv) {
                // Check customer_id
                if ($inv->customer_id !== $this->customer_id) {
                    break;
                }
                // Update balance
                $inv->amount->balance += $difference;
                $it->update($inv); // No cache update
                // Update cache
                $inv->updateCache();
            }
        }
    }
}

foreach will exit the loop if $it->valid() returns false. In other words, the loop will be finished when it reaches the last record. Also, the loop will be finished if we find invoice of other customer.

Add updateBalanceAmount in the save and delete methods in Invocie. Include them in one transaction.

    public function save($options = 0, $forceInsert = false)
    {
        // (omitted)

        // Update subsequent invoices
        $this->updateBalanceAmount($this->amount->difference());
    }

    public function delete($options = null)
    {
        // (omitted)

        // Update subsequent invoices
        $this->updateBalanceAmount(0 - $this->amount->difference());
    }

In this way, using the server cursor, you can check the values while iterating through PHP and write process, instead of calculating the increase / decrease by SQL.

Suppress updating timestamp

In the updateing of the balance above, the timestamp of the record will be updated. However, in this case, it is unnecessary to detect change conflicts, because updating the balance does not change the contents of the invoice. Therefore, suppress the updating of the timestamp in the case.

To suppress updating timestamp, specify Transactd::TIMESTAMP_VALUE_CONTROL to setTimestampMode in the server cursor. Modify updateBalanceAmount method with this.

    private function updateBalanceAmount($difference)
    {
        if ($difference !== 0) {
            $it = $this->serverCursor(1, QueryExecuter::SEEK_GREATER);
            try {
                $it->setTimestampMode(Transactd::TIMESTAMP_VALUE_CONTROL); // Suppress updating timestamp
                foreach($it as $inv) {
                    // (omitted)
                }
                $it->setTimestampMode(Transactd::TIMESTAMP_ALWAYS); // Disable suppression
            } catch (Exception $e) {
                $it->setTimestampMode(Transactd::TIMESTAMP_ALWAYS); // Disable suppression
                throw $e;
            }
        }
    }

Now you can manage the balance when update or delete invoices. Finally, if you add calculation of daily summary, this sample application will be completed.

Source code

The source code of this chapter can be downloaded from GitHub Gist.

1. Increase and decrease in stock quantity
2. Save daily summary