A couple of years ago we have worked on a project for a branch of financial institution, to track and generate reports on sales and activity of advisors. The HQ of the financial institution would produce weekly reports in excel format, the branch wanted to have something more visual and exciting then a chart of numbers…
So the web app we built has import function to import the excel report data into the database. The second part of the web app is to take this data and display nice reports with graphs comparing month to month and year to year in visually appealing manner.The web app was built with old version of Yii PHP framework backend and angular.js based front end. Over time more functions were added:
PDF generation
Weeks since last sale
emailing reports
etc…
All was great, except the developer who built this doesn’t work for us anymore, and none of us particularly like / specialize in Yii. Another issue that we noticed is something that happens to projects that starts small, but then continuously add functions, some of the original architecture decisions were hurting us. So we made a decision that its better to “bite the bullet” now and rebuild it in a framework that we know and is vastly more popular today than continue with a framework we don’t know that well and have no experience working with, plus we will have an added benefit of having a fresh start while now understanding the full scope of the project much better. The front end wouldn’t need full reworking since angular works equally well with both PHP frameworks.
In Laravel creating a new table schema is handled by Migrations. The migrations are really well documented here, but lets just quickly go through the steps:
Step 1 – create migration: php artisan make:migration createadvisorstable --create=advisors After its done open the created file and add the fields that you require
Here what our file looked like:
<?php
use IlluminateDatabaseSchemaBlueprint;
use IlluminateDatabaseMigrationsMigration;
class CreateAdvisorsTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('advisors', function (Blueprint $table) {
$table->increments('id');
$table->string('advisor_code')->unique(); //issued by company
$table->string('name'); // auto imported from report
$table->float('investment_plan'); // assigned goal for total Investment sales
$table->float('insurance_plan'); // assigned goal for total Insurance sales
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::drop('advisors');
}
}
Step 2 – run the migration: php artisan migrate
This should have created the DB table for you. Now the part that wasn’t so well documented, was Seeding. That is the Laravel tool that is used to seed the database.
PhpMyAdmin has a couple export options for DB tables, we have decided to export the old database in JSON format as its easy to work with it in PHP, convert to PHP array and iterate over it. In the database/seeds directory we have created json-dump directory and uploaded the exported json file there.
Next step is to create the migration by running the following:
php artisan make:seeder AdvisorsTableSeeder
find the created file and write in the seeding code in our case it looks like so:
<?php
use IlluminateDatabaseSeeder;
use IlluminateSupportFacadesDB;
class AdvisorsTableSeeder extends Seeder
{
/**
* Run the database seeds.
*
* @return void
*/
public function run()
{
$json = file_get_contents(__DIR__ . '/json-dump/advisors.json');
$advisors = json_decode($json);
foreach ($advisors as $advisor){
DB::table('advisors')->insert([
'advisor_code' => $advisor->user_id,
'name' => $advisor->name,
'investment_plan' => $advisor->investment_plan,
'insurance_plan' => $advisor->insurance_plan,
]);
}
}
}
All that’s left todo is run the migration:
php artisan db:seed --class=AdvisorsTableSeeder
