>_ aaditya
GitHub

Web · Lessons

Reporting engines for small businesses: cron, email and AdminLTE

· · WebLaravelCronLessons

Between 2017 and 2020 most of my freelance work for small businesses ended up being the same thing in different clothes: take data that lives in a spreadsheet or in someone's head, put it in a database, and send the owner a report they do not have to ask for. This post is about three of those projects and the pattern that connects them.

The main one is city_cars, a reporting engine for a Maruti Suzuki car dealership. The other two are multilevel-marketing-app, a Laravel platform with tree algorithms for downline tracking, and creative_accounting, a JavaScript accounting app for ledgers and transactions.

The problem

A dealership generates a steady stream of small records: enquiries, test drives, bookings, deliveries, service visits. None of it is complicated. What the owner wanted was not a dashboard to log into but a daily and monthly summary in their inbox, so they could read it on a phone before the showroom opened.

That framing changed how I built it. The admin panel existed so staff could enter data. The real output of the system was an email.

What I built

city_cars is Laravel on MySQL. Staff use an AdminLTE 2 panel to enter and correct records. A set of report classes turn those records into summaries. Laravel's task scheduler, driven by a single cron entry, runs those reports on a timetable and a mailer sends them to a list of recipients. The repo was started in April 2017 and I kept maintaining it until early 2019.

I chose AdminLTE because it gave me a complete admin shell (sidebar, tables, forms, alerts) for free. For a one-person project the most expensive thing is UI polish that nobody asked for, and a stock admin theme removes that temptation.

How it works

One cron line, many schedules

The server crontab contains exactly one entry. Everything else lives in the application, under version control, where it can be changed without SSH access.

crontab
# run the Laravel scheduler every minute; it decides what is due
* * * * * cd /var/www/city_cars && php artisan schedule:run >> /dev/null 2>&1

The schedule itself is declared in the console kernel. Each report is an artisan command, so it can also be run by hand when someone asks "can you resend yesterday's numbers".

app/Console/Kernel.php
protected function schedule(Schedule $schedule)
{
    $schedule->command('report:daily')
             ->dailyAt('07:30')
             ->withoutOverlapping();

    $schedule->command('report:monthly')
             ->monthlyOn(1, '08:00');

    // remind staff about open enquiries with no follow-up
    $schedule->command('report:stale-enquiries')
             ->weekdays()->at('18:00');
}

Reports as plain queries

Each report is a class with one job: run a few aggregate queries and hand an array to a Blade template. I resisted the urge to build a generic report builder. Three specific reports written plainly were easier to change when the owner asked for "the same thing but grouped by salesperson".

app/Reports/DailySummary.php
public function build(Carbon $day): array
{
    return [
        'enquiries' => Enquiry::whereDate('created_at', $day)->count(),
        'bookings'  => Booking::whereDate('booked_on', $day)->count(),
        'by_model'  => Booking::whereDate('booked_on', $day)
                           ->select('model', DB::raw('count(*) as n'))
                           ->groupBy('model')->get(),
    ];
}

The mailer wraps a Blade view in a Laravel Mailable. HTML email is hostile to layout, so the templates are tables with inline styles and nothing else. It looked plain, which was fine; the owner read it on a phone.

The MLM app: reports over a tree

The multi-level marketing platform had the same shape (Laravel, MySQL, a Bootstrap and jQuery admin, REST web services for a mobile client) with one twist: every member sits in a tree, and the numbers that matter are sums over subtrees. Who is in my downline, how much volume did it generate this month, which legs are weakest.

I stored the parent id on each member and precomputed the ancestor path, so subtree queries become a prefix match rather than a recursive walk. Payout calculation then walks the tree once from the leaves upward.

app/Services/Downline.php
// members store a materialised path like "1/14/203/"
public function volume(Member $root, string $month): float
{
    return Sale::join('members', 'members.id', '=', 'sales.member_id')
        ->where('members.path', 'like', $root->path . '%')
        ->where('sales.month', $month)
        ->sum('sales.amount');
}

The reports were then exactly the same idea as the dealership: a scheduled command, a query, a template, an email.

The accounting app: the same reports in the browser

creative_accounting, from 2020, was a bookkeeping tool for a small business built in JavaScript rather than PHP. Ledgers, transactions and reports, with the report screens rendered client side. By then I had written the same aggregate-and-group logic enough times that moving it to another language was mostly typing. The lesson it added was that an owner who wants to explore numbers wants a screen, and an owner who wants to be told numbers wants an email. Ask which one you are building before you start.

What I learned

None of this is sophisticated, and that is the point. Small businesses rarely need a data platform. They need the right four numbers at the right time, reliably, and a way to fix bad data when they spot it. Cron, a few queries and an email cover that surprisingly well.

city_cars on GitHub >
multilevel-marketing-app on GitHub >
creative_accounting on GitHub >