Membership System - Transaction History

Resuming the development of Membership System from previous post, we're going to introduce new feature in this post: Transaction History.

1 - Transaction History Data Model

Let's start by defining the Transaction data model.

To be able to integrate with external POS system, we'll need a key identifier for each transaction to serve as the point of integration. As per retail industry standard, every retail product has a unique identifier dubbed SKU number. We can use that for this purpose.

Besides that, we would also like to display the value / price of each transaction in the history.

Here's a question: Should we store the price of each transaction in our system? Since we've captured the SKU Number, can we just use that to retrieve the price from the product database of the POS system?

The answer is: we should store price in our system. We couldn't be relying on the external product database to retrieve the price data, at these two main reasons:

• The price from the external product database may change from time to time.
• It's performance costly to constantly retrieve data from external system.

Now let's summarise what data we will be storing for each transaction in our system:

• SKU Number
• Price / Value
• Quantity - the number of items purchased for the product in the transaction
• Date time

Let's create the database script with Artisan command to create a new DB table, and we'll name this new table as "transactions".

    php artisan make:migration create_transactions_table --table=transactions

And the definition of the migration script:

    /* database/migrations/xxxx_create_transactions_table.php */
    public function up()
    {
        Schema::create('transactions', function (Blueprint $table) {
            $table->id();
            $table->timestamps();
            $table->string('sku_number');
            $table->decimal('price', 10, 3)->default(0);
            $table->decimal('quantity', 10, 3)->default(1);
            $table->dateTime('date_time');
            $table->foreignId('member_id')->constrained();
        });
    }

Explanation:

1. The price and quantity are defined as decimal type with 3 decimal places. Although our currency MYR is only 2 decimal places, we'll still need 3 decimal places in the system to cater for any remainder upon any calculation.
2. The default() is used to define the default value when a new entry is created in the database, even though the value is not passed into the SQL insert statement.
3. The foreignId('member_id')->constrained() defines the member_id column as a foreign key with reference on "id" from "members" table.

The member_id column is also added to link up the transaction and the member. In other words, each transaction belongs to a member with that member_id. We'll define the Eloquent ORM relationship between Member and Transaction models in next section.

Let's run the migration.

    php artisan migrate
    Migrating: xxxx_create_transactions_table
    Migrated: xxxx_create_transactions_table (123.45ms)

Alright, our database is pretty much set. Let's proceed to next step.

2 - Model and Controller

Let's create a new Transaction controller and model by Artisan command.

    php artisan make:controller TransactionController --model=Transaction

As mentioned earlier, we'll build up the Eloquent relationship between Member model and Transaction model. The relationship is as such:

• A member has many transactions
• Each transaction belongs to a Member

Let's translate these English sentences into our model file in PHP.

    /* app/Models/Member.php */
    public function transactions()
    {
        return $this->hasMany('App\Models\Transaction');
    }

    /* app/Models/Transaction.php */
    public function member()
    {
        return $this->belongsTo('App\Models\Member');
    }

All good! Data model is up, relationship is formed, let's start our functionality development.

3 - Transactions of a Member

Let's say a member is checking out an item over our store-front counter. The cashier needs an interface to register that transaction into this system.

In previous post, we have created a Member Detail page. Let's add a form to this page to allow our cashier to enter the transaction data.

We'll add the following code after the member detail information.

    <!-- resources/views/members/show.blade.php -->
    <form action="{{ route('members.addTransaction', $member) }}" method="post">
        @csrf
        <div class="row">
            <div class="col-md-4">
                <div class="form-group">
                    <label for="skuNo">SKU No</label>
                    <input type="text" class="form-control" id="skuNo" name="sku_no">
                </div>
            </div>
            <div class="col-md-4">
                <div class="form-group">
                    <label for="price">Price</label>
                    <input type="number" class="form-control" id="price" name="price" min="0" step="0.01">
                </div>
            </div>
            <div class="col-md-2">
                <div class="form-group">
                    <label for="quantity">Quantity</label>
                    <input type="number" class="form-control" id="quantity" name="quantity" min="1" step="1">
                </div>
            </div>
        </div>
        <button type="submit" class="btn btn-primary">Register</button>
    </form>

Explanation:

1. The form consists of 3 input fields, SKU No, Price, and Quantity.
2. Upon submission, the data will be sent via POST method to the route route('members.addTransaction', $member), to be defined later.
3. The $member is passed as an argument above, which will be type-hinted in the controller, to be defined later.

Let's add the new POST method route.

    /* routes/web.php */
    Route::post('members/{member}/add-transaction', [App\Http\Controllers\MemberController::class, 'addTransaction'])->name('members.addTransaction');

Next, let's define the controller function.

    /* app/Http/Controllers/MemberController.php */
    use Carbon\Carbon;
    //...
    public function addTransaction(Request $request, Member $member)
    {
        $requestData = $request->all();
        $requestData['date_time'] = Carbon::now();
        $transaction = $member->transactions()->create($requestData);
        return redirect()->back();
    }

Explanation:

1. The date_time is generated by the server instead of user's entry. We use the Carbon::now() to generate the current server time.
2. Note that we've included the use Carbon\Carbon; at the beginning of the controller file.
3. $member->transactions()->create($requestData); is an example usage of the Eloquent relationship, which was defined in the Model file earlier. It inserts a new transaction data of the $member to the database. This method automatically link up the member and transaction, by adding the member's ID into the "member_id" column.
4. After the transaction is created, the controller redirects the browser back to where it comes from, the Member Detail page.

We're done! Let's go to one of the members' detail page (http://www.example.com/members/1) to see the form being added.

Fill up the form and click "Register" button. The page would refresh and the form fields are cleared. What happened?

That's actually an expected behaviour. Here's what happened on the browser when user submitted the form:

1. The browser send the POST request via the route('members.addTransaction', $member)
2. The route to the MemberController's addTransaction() function is then being executed.
3. The controller function then register the data, and finally redirect back to the previous screen, which is the detail page itself.

So, what is missing? We need a place to display the list of transactions done by this member.

Transactions list of member

Let's add a new section below the form in the same page, to list down the transactions done by the user.

    <!-- resources/views/members/show.blade.php -->
    @if(count($member->transactions) > 0)
        <table class="table table-striped table-sm">
            <thead class="thead-dark">
                <tr>
                    <th>SKU No</th>
                    <th>Price</th>
                    <th>Quantity</th>
                    <th>Date Time</th>
                </tr>
            </thead>
            <tbody>
                @foreach($member->transactions()->orderBy('date_time', 'desc')->get() as $transaction)
                <tr>
                    <td>{{ $transaction->sku_no }}</td>
                    <td>{{ $transaction->price }}</td>
                    <td>{{ $transaction->quantity }}</td>
                    <td>{{ (new \Carbon\Carbon($transaction->date_time))->format('Y-m-d H:i:s') }}</td>
                </tr>
                @endforeach
            </tbody>
        </table>
    @else
        <p>No transaction yet.</p>
    @endif

Explanation:

1. We've used the Eloquent relationship to load the transactions of this member via $member->transactions.
2. First, we check if there is any record found. If there is, the table will be rendered and list down all the transaction.
3. At the foreach loop, we've used the orderBy('date_time', 'desc') to sort the transactions list in descending order by the date_time. In other words, the latest transaction will be shown first.
4. If there is no transaction being recorded, a message "No transaction yet." will be displayed.
5. We've used the new \Carbon\Carbon($transaction->date_time))->format('Y-m-d H:i:s') to convert the date time into more human readable format.
6. Note that we're using Bootstrap CSS class="table table-striped table-sm" to style the table with striped style and smaller row size.

Let's reload the page and see the changes. The list should be shown below the form.

Let's register a new transaction now, and the latest data should be shown at the top of the list.

4 - Overall Transactions

For an ease to overview by admin, let's create a page to list down all transactions recorded in the system from every member.

Since we've created the Transaction Controller as a Resource Controller, let's use the same convention to define the route. We'll apply this URL format: https://www.example.com/transactions.

    /* routes/web.php */
    Route::resource('transactions', App\Http\Controllers\TransactionController::class);

Now, we'll need to create a new transactions/index.blade.php file.

    <!-- resources/views/transactions/index.blade.php -->
    <table class="table table-striped table-sm">
        <thead class="thead-dark">
            <tr>
                <th>'Member</th>
                <th>'SKU No</th>
                <th>'Price</th>
                <th>'Quantity</th>
                <th>'Date Time</th>
            </tr>
        </thead>
        <tbody>
            @foreach($transactions as $transaction)
            <tr>
                <td>{{ $transaction->member->name }}</td>
                <td>{{ $transaction->sku_no }}</td>
                <td>{{ $transaction->price }}</td>
                <td>{{ $transaction->quantity }}</td>
                <td>{{ $transaction->date_time }}</td>
            </tr>
            @endforeach
        </tbody>
    </table>
    {{ $transactions->links() }}

Explanation:

1. We've use the Eloquent relationship $transaction->member->name to display the member's name of each transaction to whom it belongs to.
2. As opposed to other listing pages, we have added $transaction->links(), which is a function to render pagination of the data. This requires the controller to pass the list of transactions data in paginated manner.

And lastly, the definition of controller function.

    /* app/Http/Controllers/TransactionController.php */
    public function index()
    {
        $transactions = Transaction::orderBy('date_time', 'desc')->paginate(50);
        return view('transactions.index')
            ->with([
                'transactions' => $transactions,
            ]);
    }

Now, let's load the transactions listing page to see all transactions from everyone in a single page.

Explanation:

1. The function retrieves all the Transaction data from database, sorted by the date_time in descending order.
2. We've used paginate(50) to paginate the transactions data, with each page size up to 50 items. This is required for the pagination function to work the index.blade.php earlier.

Using Bootstrap Pagination for Laravel 8

Bootstrap has a pagination component, and the past Laravel versions have been using Bootstrap to render the pagination when we use the $data->links() in the blade files.

However, this is not the case for Laravel 8. It has changed the default UI library to Tailwind CSS. You may notice the styling of our Overall Transactions listing page is not properly done.

Anyway, Laravel is as friendly as it always be, it provides a convenient way for us to revert back the Bootstrap paginator.

Let's change the paginator to use Bootstrap by adding the following line in the app/Providers/AppServiceProvider.php.

    /* app/Providers/AppServiceProvider.php */
    public function boot()
    {
        Paginator::useBootstrap();
    }

Now refresh the page and see the effect.

Conclusion

Yep! That's another end of development journey of our new Transaction History feature. We've linked up the Transaction with the Member model.

So far, we've been touching base on the Laravel capability, how we conveniently generate the Resource Controller and Model, database migration files, and definition of Route to the Resource Controller without much explicit work.

Next step...

Up on next, we'll discuss how to optimise the user experience for our cashier, with the implementation of ReactJS to introduce more interactivity on the user interface.