Use models
Let's use the models completed in the previous chapter.
In this chapter, you can learn about the following contents:
- How to use snapshot
Invoice operations
Insert
Make two sales and a payment.
try
{
$cust = Customer::find(1);
$inv = new Invoice;
$inv->customer()->associate($cust);
$inv->addSalesLine('APPLE1', 12);
$inv->addSalesLine('ORANGE1', 2);
$inv->addPaymentLine(4579, 'Cash');
$inv->saveWithTransaction();
// Show the balance
echo 'The balance of ' . $cust->name .' is \\' . $inv->amount->balance. ' .' . PHP_EOL;
} catch(Exception $e) {
echo 'An error occurred. Detail is : [' . $e->message() . '].' . PHP_EOL;
}The balance of Yamaha is \0 .Read
Invoice record contains values such as the total amount of items. Invoice and InvoiceItem are consistent by reading the set written in the transaction.
To read the set written in the transaction, read it with snapshot as follows:
try
{
DB::beginSnapshot(); // Start CONSISTENT_READ
$inv = Invoice::find(1); // Read Invoice
if ($inv === null) {
throw new Exception('Invoice number 1 has not been created or is deleted.');
}
$items = $inv->items; // Read InvoiceItem
$inv->endSnapshot();
echo 'The number of items in Invoice ' . $inv->id . ' is ' . $items->count() . '.' . PHP_EOL;
} catch(Exception $e) {
$inv->endSnapshot();
echo 'An error occurred. Detail is : [' . $e->message() . '].' . PHP_EOL;
}I will explain in what situations inconsistency will occur. Please see the following figure:
Reading the invoice with items is result in two reads, access to Invoice record and access to InvoiceItem records. At the time as shown in the figure, if another user updates the invoice, there is a possibility that Invoice and InvoiceItem may read before and after change.
If you use snapshots, you can read Invoice and InvoiceItem as consistent snapshot by InnovDB's MVCC feature.
For more information on snapshots, see InnoDB lock and its control.
Note: The problem of inconsistency in reading is not unique to Transactd PHP ORM. Even with SQL, if Invoice and InvoiceItem are read by individual SQL statements, the same problem may occur. There is no beginSnapshot in SQL, so use BEGIN or START TRANSACTION.
Update
Delete the second line of the invoice and save it.
try
{
$inv = Invoice::find(1);
if ($inv === null) {
throw new Exception('Invoice number 1 has not been created or is deleted.');
}
$inv->items->remove(1); // Delete the second line
$inv->saveWithTransaction();
echo 'The balance of ' . $cust->name .' is \\' . $inv->amount->balance. ' .' . PHP_EOL;
} catch(Exception $e) {
echo 'An error occurred. Detail is : [' . $e->message() . '].' . PHP_EOL;
}The balance of Yamaha is \-691 .Delete
Delete invoice number 1.
try
{
$inv = Invoice::find(1);
if ($inv === null) {
throw new Exception('Invoice number 1 has not been created or is deleted.');
}
$inv->deleteWithTransaction();
$echo 'Invoice number 1 has deleted.' . PHP_EOL;
} catch(Exception $e) {
echo 'An error occurred. Detail is : [' . $e->message() . '].' . PHP_EOL;
}Invoice number 1 has deleted.Show the balance
In this example, look for the last invoice of customer_id = 3 in date order.
Look for the last invoice by specifying one higher customer_id for keyValue and backforward-searching for direction.
try
{
$customer_id = 3;
$invs = Invoice::index(1)->keyValue($customer_id + 1)
->where('customer_id', $customer_id)
->limit(1)->direction(Nstable::findBackForword)
->get();
$inv = count($invs) ? $invs[0] : null;
if ($inv === null) {
throw new Exception('There is no transaction for customer number 1.');
}
echo 'The balance of ' . $cust->name .' is \\' . $inv->amount->balance. ' .' . PHP_EOL;
} catch(Exception $e) {
echo 'An error occurred. Detail is : [' . $e->message() . '].' . PHP_EOL;
}The balance of Yamaha is \0 .Aggregate transactions for a month
Calculate monthly sales of customer_id = 3.
try
{
$customer = Customer::find(3);
$invs = $customer->transactions('2016-12-01', '2016-12-31');
$total = 0;
if (count($invs) > 0) {
foreach($invs as $inv) {
$total += $inv->amount->sales;
}
}
echo 'The monthly sales of ' . $cust->name .' is \\' . $total . ' .' . PHP_EOL;
} catch(Exception $e) {
echo 'An error occurred. Detail is : [' . $e->message() . '].' . PHP_EOL;
}The monthly sales of Yamaha is \4240 .Get daily sales and payments
Get daily sales and payments on 2016-12-02.
Since there is DailySummary, it can be got with one record access.
$date = '2016-12-02';
$summary = DailySummary::find($date);
if ($summary === null) {
echo 'There is no transaction on ' . $date . PHP_EOL;
} else {
echo $date . ' sales : \\' . $summary->sales . ' payment : \\' . $summary->payment . PHP_EOL;
}2016-12-02 sales : \4240 payment : \4579Get invoices on the day
Get all invoices on 2016-12-02. Use the relationship of DailySummary.
$date = '2016-12-02';
$summary = DailySummary::find($date);
if ($summary !== null) {
$invoices = $summary->invoices;
}
foreach($invoices as $invoice) {
// (omitted)
}Get the total sales for the specified period
Get the total sales of 2016-12.
You can calculate the monthly sales by reading up to 31 DailySummary records.
$startDate = '2016-12-01';
$endDate = '2016-12-31';
$summaries = DailySummary::keyValue($startDate)->where('date', '<=', $endDate)->get();
$total = 0;
foreach($summaries as $summary) {
$total += $summary->sales;
}
echo 'The total sales from ' . $startDate . ' to ' . $endDate . ' is \\' . $total . ' .' . PHP_EOL;The total sales from 2016-12-02 to 2016-12-31 is \4240 .Retrieve product price
Show the price of APPLE1.
$code = 'APPLE1';
$product = Product::find('APPLE1');
echo 'The price of ' $code . ' is \\' . $product->price . ' .' . PHP_EOL;The price of APPLE1 is \300 .Look up stock quantity
Show the stock quantity of APPLE1.
$code = 'APPLE1';
$stock = Stock::find('APPLE1');
if ($stock === null) {
echo 'There is no stock of ' $code . ' .' . PHP_EOL;
} else {
echo 'The stock quantity of ' $code . ' is ' . $stock->quantity . ' .' . PHP_EOL;
}The stock quantity of APPLE1 is 108 .Stock list
Show the stock list.
$stocks = Stock::all();
foreach($stocks as $stock) {
echo sprintf('%-15s : %8d', $stock->code, $stock->quantity) . PHP_EOL;
}APPLE1 : 108
APPLE2 : 240
BIKE650 : 5
CAR123 : 1
ORANGE1 : 52
WATCH777 : 10Product stock list
Show the product stock list. Process with high speed using with method.
function showProductList($products)
{
echo sprintf('%-15s %-15s %8s %8s', 'product_code', 'description',
'price', 'quantity') . PHP_EOL;
echo '---------------------------------------------------' . PHP_EOL;
foreach($products as $product) {
echo sprintf('%-15s %-15s %8d %8d',
$product->code,
$product->description,
$product->price,
($product->stock === null) ? 0 : $product->stock->quantity)
. PHP_EOL;
}
$products = Product::with('stock')->all();
showProductList($products);product_code description price quantity
---------------------------------------------------
CAR123 CAR 123 5000000 1
BIKE650 BIKE 650 320000 5
WATCH777 WATCH 777 198000 10
APPLE1 APPLE FUJI 300 108
APPLE2 APPLE SHINANO 280 240
ORANGE1 ORANGE UNSYUU 320 52To JSON
Convert the invoice number 2 to JSON.
$inv = Invoice:find(2);
$inv->items; // Lazy load items
$json = $inv->toJson();
echo $json;Formatted JSON
{
"id": 2,
"date": "2016-12-18",
"update_at": "2016-12-19 16:35:24.627492",
"amount": {
"oldBlance": null,
"sales": 198000,
"tax": 15840,
"payment": 10000,
"balance": 203840,
"className": "InvoiceAmount"
},
"className": "Invoice",
"customer_id": 3,
"note": "",
"items": {
"saveOprions": 6,
"className": "Transactd\\Collection",
"array": {
"0": {
"className": "InvoiceItem",
"invoice_id": 2,
"row": 1,
"line_type": 0,
"product_code": "WATCH777",
"product_description": "WATCH 777",
"price": 198000,
"quantity": 1,
"amount": 198000,
"tax": 15840,
"note": "",
"update_at": "2016-12-19 16:35:24.630626"
},
"1": {
"className": "InvoiceItem",
"invoice_id": 2,
"row": 2,
"line_type": 1,
"product_code": "PAYMENT",
"product_description": "Cash",
"price": 0,
"quantity": 0,
"amount": 10000,
"tax": 0,
"note": "",
"update_at": "2016-12-19 16:35:24.630626"
}
}
}
}From JSON
Convert above JSON string to object, and save it.
$inv = Invoice::fromJson($json);
$inv->saveWithTransaction();