Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

vidhyaprakash85's avatar

Generating unique invoice number based on financial year

Hi friends,

I need one help in generating invoice number

I need this format. For example in they financial year 01-04-2022 to 31-03-2023 I have generate number like this R2022/2023-00001 and so on.

In the next year 01-04-2023 to 31-03-2024 it should be like this R2023/2024-0000

How to setup migration and how to generate in the controller.

0 likes
20 replies
Swaz's avatar

You can use sprintf or vsprintf to format the number.

https://www.php.net/manual/en/function.sprintf.php

// R2022/2023-00001
sprintf('R%s/%s-%05d', 2022, 2023, 1);

Then define a model event to generate the number when an invoice is created.

https://laravel.com/docs/9.x/eloquent#events-using-closures

Example

// migration 
public function up()
{
    Schema::create('invoices', function (Blueprint $table) {
        $table->id();
        $table->string('number')->nullable();
        $table->date('date');
        $table->timestamps();
    });
}

// controller
class InvoiceController extends Controller
{
    public function store(Request $request)
    {
        Invoice::create([
            'date' => $request->date('date'),
        ]);
        
        // ...
    }
}

// model
class Invoice extends Model
{   
    protected $casts = [
        'date' => 'date',
    ];
    
    protected static function booted()
    {
        static::created(function ($invoice) {
            $invoice->number = $invoice->generateNumber();
            $invoice->save();
        });
    }
    
    public function generateNumber()
    {
        return vsprintf('R%s/%s-%05d', [
            $this->date->format('Y'),
            $this->date->addYear()->format('Y'),
            self::whereBetween('date', [
                $this->date->startOfYear(),
                $this->date->endOfYear()
            ])->count() + 1,
        ]);
    }
}
Snapey's avatar

be aware that this method might give two users the same invoice number

1 like
Swaz's avatar

@Snapey if an invoice is deleted? How would you approach this? Here is my attempt:

public function generateNumber()
{
    $count = self::whereBetween('date', [
        $this->date->startOfYear(),
        $this->date->endOfYear()
    ])->count() + 1;

    $number = $this->formatNumber($count);
        
    while (self::where('number', $number)->exists()) {
        $number = $this->formatNumber(++$count);
    }

    return $number;
}

private function formatNumber($count)
{
    return vsprintf('R%s/%s-%05d', [
        $this->date->format('Y'),
        $this->date->addYear()->format('Y'),
        $count,
    ]);
}
1 like
Snapey's avatar

@Swaz don't let it e deleted, mark it deleted only with soft delete, and add withTrashed() to the count

My point was that two users can be counting the records at the same time and both get the same invoice number, therefore the implementation is flawed

Your second attempt is extremely inefficient making it more likely that the same number given out twice

1 like
Swaz's avatar

@Snapey Oh gotcha, thanks for the info :). I've never had to deal with something like this, I would love to see what a proper implementation looks like. Would it involve table locking?

Snapey's avatar
Snapey
3 years ago
Best Answer
Level 122

@swaz This would be my recommended solution. It uses a table dedicated to issuing the next number.

Create a migration and a model

php artisan make:model InvoiceSequence -m

        Schema::create('invoice_sequences', function (Blueprint $table) {
            $table->id();
            $table->integer('fy');
            $table->integer('current')->default(0);
        });

Use it like this;

// get the right year for previous 1st April
$dt = today()->setMonth(Carbon::APRIL)->setDay(1);

if($dt>today()) {
 $dt->subYear(1);
}

// get current year's record or start a new
$invseq = InvoiceSequence::firstOrCreate(['fy' => $dt->format('Y')]);

$invseq->increment('current');

$invoiceNumber = sprintf('R%s/%s-%04u',
  $dt->format('Y'),
  $dt->addYear()->format('Y'),
  $invseq->current,
);

This works using two techniques. The firstOrCreate ensures there is a row for the 'current' financial year. If there is not one then a new record is added with the current count at zero.

Next, the invoice sequence is incremented in a single atomic operation.

Finally, the number is output in the chosen format

1 like
Swaz's avatar

@Snapey Ooh interesting. Love it! Thanks for taking the time to write this up.

1 like
Snapey's avatar

Its not quite perfect, and I'm looking for a solution. When the first query runs, a model instance is loaded.

$invseq = InvoiceSequence::firstOrCreate(['fy' => $dt->format('Y')]);
//  <<< another thread reads the same record
$invseq->increment('current');

if another request runs the same line at the same time then they can both get the same copy of the model.

When increment runs, Laravel issues an atomic database query to update the counter, and then adds one to the same field in the loaded model.

When two threads both run the same increment function, then the field is correctly incremented twice, but both $invseq models started with the same value and both have the same value after the increment.

In theory record locking should prevent this, but its hard to simulate.

sr57's avatar

@snapey

then the field is correctly incremented twice,

Yes

but both $invseq models started with the same value

No if it is an atomic increment (one will return +1 and the other +2)

How to test ?

I don't know (for the moment) except be confident with Laravel and dbms.

That said, in the doc

https://laravel.com/docs/9.x/queries#increment-and-decrement

I don't see it's an atomic increment ...

sr57's avatar

@snapey

Test to be reviewed

> $u=User::find(1);$v=clone($u);$u->increment('id');$u->id;$v->increment('id');$v->id
// 2

should be 3 if atomic increment

Snapey's avatar

@sr57 the atomic increment works fine. but both invocations read the pre-incremented value

sr57's avatar

@snapey

Don't understand ... means finally it does not work ...

Snapey's avatar

@sr57 A truly safe version

// get the right year for previous 1st April
$dt = today()->setMonth(Carbon::APRIL)->setDay(1);

if($dt > today()) {
 $dt->subYear(1);
}

// ensure there is a record for the current financial year
$is=InvoiceSequence::firstOrCreate(['fy' => $dt->format('Y')]);

// atomically increment the count AND get the value
DB::select(DB::raw("UPDATE invoice_sequences SET current = LAST_INSERT_ID(current) + 1 where id = {$is->id}"));

// retrieve the value that was read
$current = DB::getPDO()->lastInsertId();

$invoiceNumber = sprintf('R%s/%s-%04u',
  $dt->format('Y'),
  $dt->addYear()->format('Y'),
  $current,
);

inspired by a technique in this article; https://www.sqlines.com/mysql/how-to/select-update-single-statement-race-condition

sr57's avatar

@Snapey

Why not, wrap your code or @swaz 's one in a transaction works also without the need of raw sql.

Swaz's avatar

@snapey I was playing around with this, and it seems firstOrCreate does not have atomic guards. Here's how I was testing it:

Schema::create('invoice_sequences', function (Blueprint $table) {
    $table->id();
    $table->integer('year');
    $table->integer('current')->default(1);
});

Route::get('increment', function () {
    $year = 2023;
    
    $last = InvoiceSequence::firstOrCreate(['year' => $year]);

    DB::statement("UPDATE invoice_sequences SET current = LAST_INSERT_ID(current) + 1 WHERE id = {$last->id}");

    return DB::getPDO()->lastInsertId();
});

Route::get('run', function () {
    $client = new GuzzleHttp\Client([
        'base_uri' => 'http://invoice.test'
    ]);

    $promises = [
        $client->getAsync('/increment'),
        $client->getAsync('/increment'),
        $client->getAsync('/increment'),
        $client->getAsync('/increment'),
        $client->getAsync('/increment'),
    ];
    
    $responses = GuzzleHttp\Promise\Utils::settle(
        GuzzleHttp\Promise\Utils::unwrap($promises),
    )->wait();
    
    $values[] = json_decode($responses[0]['value']->getBody());
    $values[] = json_decode($responses[1]['value']->getBody());
    $values[] = json_decode($responses[2]['value']->getBody());
    $values[] = json_decode($responses[3]['value']->getBody());
    $values[] = json_decode($responses[4]['value']->getBody());

    return collect($values)->sort()->values();
});

When you hit the /run endpoint for the first time, the expected result would be [1,2,3,4,5], but you'll almost always see [1,1,2,3,4].

Solution

To fix this, I added a unique constraint to the migration and updated the /increment endpoint:

Schema::create('invoice_sequences', function (Blueprint $table) {
    $table->id();
    $table->integer('year')->unique();
    $table->integer('current')->default(1);
});

Route::get('increment', function () {
    $year = 2023;

    DB::statement("INSERT INTO invoice_sequences (year) VALUES ({$year}) ON DUPLICATE KEY UPDATE year = year, id = LAST_INSERT_ID(id)");
    
    $lastInsertId = DB::getPDO()->lastInsertId();

    DB::statement("UPDATE invoice_sequences SET current = LAST_INSERT_ID(current) + 1 WHERE id = {$lastInsertId}");

    return DB::getPDO()->lastInsertId();
});

Now the /run endpoint always returns [1,2,3,4,5] on the first try 🙌.

Resources

https://nunomaduro.com/speed_up_your_php_http_guzzle_requests_with_concurrency

https://freek.dev/1087-breaking-laravels-firstorcreate-using-race-conditions

https://gist.github.com/RuGa/5354e44883c7651fd15c#file-massinsertorupdate-php

2 likes
Snapey's avatar

@sr57 because transaction alone does nothing, you have to use record locking also which then can block the database

Snapey's avatar

@Swaz Thats some great research work you did there. Thanks.

Rohit_IN's avatar

@Swaz This solution is truly amazing, Just out curiosity I have a question.

Is it possible to also remove the auto increment ID field from database, and use the year field itself as primary key?

For example: in my case, I've named the year field as series (VARCHAR) where I store the series that being currently used. like: for orders "OD" . now()->format('Y-md')

Here's my current code:

$series = "OD" . now()->format('Y-md');

DB::statement("INSERT INTO sequences (series) VALUES (:series) ON DUPLICATE KEY UPDATE series = series, id=LAST_INSERT_ID(id)", ['series' => $series]);
$lastInsertId = DB::getPDO()->lastInsertId();

DB::statement("UPDATE sequences SET current = LAST_INSERT_ID(current) + 1 WHERE id = :id", ['id' => $lastInsertId]);
$current  = DB::getPDO()->lastInsertId();
1 like
Swaz's avatar

@Rohit_IN It should be fine as long as year is auto-incrementing. Otherwise $lastInsertId will return 0 on the first insert, which makes the value of current be 1 less than expected.

Schema::create('invoice_sequences', function (Blueprint $table) {
    $table->increments('year');
    $table->integer('current')->default(1);
});

Route::get('increment', function () {
    $year = 2023;

    DB::statement("INSERT INTO invoice_sequences (year) VALUES ({$year}) ON DUPLICATE KEY UPDATE year = year, year = LAST_INSERT_ID(year)");

    $lastInsertId = DB::getPDO()->lastInsertId();

    DB::statement("UPDATE invoice_sequences SET current = LAST_INSERT_ID(current) + 1 WHERE year = {$lastInsertId}");

    return DB::getPDO()->lastInsertId();
});

Also, while not needed for this example, you may also want to tell Laravel what the new primary key is.

class InvoiceSequence extends Model
{
    protected $primaryKey = 'year';
}

Please or to participate in this conversation.