Generate Unique Serial Number for invoice for a particular companies Php - Laravel

Question

I am building an Laravel application to generate sales invoices. Multiple people from multiple companies generate invoices at a time. I have a table for Invoices as invoice_table where id is primary key and number is unique

id | company_id | series |  number | amount

Now to generate invoice number what I do is; for a particular company take count and add 1 to that.

//assume $inv->prefix is a string which gives me series for particular company.
$series = Invoice::where('series', $inv->prefix)->max('number');
if(isset($series) && strlen($series) > 0){
    $series += 1; 
} else {
    $series = 1;
}
$inv->number = $series;

Now the problem is when two users tries to generate invoice for same series at a time it give me duplication error as number column in my table is unique.

Can i do something like

do{
    $series = Invoice::where('series', $inv->prefix)->max('number');
    if(isset($series) && strlen($series) > 0){
        $series += 1; 
    } else {
        $series = 1;
    }
    $inv->number = $series;
} while($inv->save())

Can anyone help me here. If i get duplicate entry exception the code should bring the count again and it should try to save the record again.

You'd better employ a global locking mechanism. E.g. in pseudo code `while lock exists for resource (company in this case i guess) sleep 100ms; then check lock again ==> when lock doesn't exists, create it ==> do atomic operation (increment) ==> release lock` This way only one client will increment the serial for a given prefix at once and concurrent attempts will be held for a short while before they can increment. — marekful, Mar 05 '20 at 11:44

score 2 · Answer 1 · answered Mar 05 '20 at 11:53

I will suggest don't just increment the number for new InvoiceId, make a pattern for that ex-

Create a utility class -

class NumberUtility
{
    /**
     * This method generates the random number
     *
     * @param int $length
     *
     * @return int
     */
    public static function getRandomNumber($length = 8): int
    {
        $intMin = (10 ** $length) / 10;
        $intMax = (10 ** $length) - 1;

        try {
            $randomNumber = random_int($intMin, $intMax);
        } catch (\Exception $exception) {
            \Log::error('Failed to generate random number Retrying...');
            \Log::debug(' Error: '.$exception->getMessage());
            $randomNumber = self::getRandomNumber($length);
        }
        return $randomNumber;
    }
}

Now create a method to get unique invoice number as below -

public function getUniqueInvoiceNumber($companyId)
{
    $randomNumber = NumberUtility::getRandomNumber(4);
    $invoiceNumber =  (string)$companyId.$randomNumber;
    $exist = Invoice::where('number', $invoiceNumber)->first();
    if ($exist) {
        $invoiceNumber = $this->getUniqueInvoiceNumber($companyId);
    }
    return $invoiceNumber;
}

Then change the random number with series. And it will solve your problem. — Keshari Nandan, Mar 05 '20 at 16:05

user8555937 · Answer 2 · 2020-03-05T12:20:22.293

You could use microtime to handle your invoices. Chances of having duplicated records are very low; though for design's sake I'd choose tables locking (see the whole answer)

$inv->number = str_replace(' ', '', microtime());
$inv->save();

Multiple people from multiple companies generate invoices at a time.

Randomizing data or incrementing it is not the way to go in this is the case. If you are generating the invoices based on a custom pattern, which uses as variables data from database, chances of generating duplicates are high. You should locks the tables you're working with for the current transaction. This will prevent any duplicated data from being inserted. I don't know your entire database structure though I'm offering a demo:

LOCK TABLES working_table WRITE, working_table2 WRITE;

INSERT INTO working_table ( ... ) 
VALUES ( ...,
    (SELECT number
    FROM working_table2
    WHERE series = 'prefix'
    ORDERBY number DESC
    LIMIT 1)+1
);

UNLOCK TABLES;

You can then call the sql statement like this:

DB::select("
    LOCK TABLES working_table WRITE, working_table2 WRITE;

    INSERT INTO working_table ( column ) 
    VALUES (
        (SELECT number
        FROM working_table2
        WHERE series = ?
        ORDERBY number DESC
        LIMIT 1)+1
    );

    UNLOCK TABLES;
", [ 'param1' ]) // these are the parameters to prepare

Note: If you are using persistent database connections you must handle all thrown errors from you Laravel application, because unhandled errors will halt the script without halting the child child process which keeps the connection to the database alive. Unhalting the child process keeps the tables locked! as your database is locking them for connection session. Please read this post for detalied information regarding this behaviour.

Then use tables locking. – user8555937 Mar 05 '20 at 12:47 — user8555937, Mar 05 '20 at 12:47

score -1 · Answer 3 · answered Mar 05 '20 at 12:33

-1

You can use uniqid() function of PHP:

$series = uniqid(); //current time in microseconds

answered Mar 05 '20 at 12:33

Manash Kumar

995
6
13

Generate Unique Serial Number for invoice for a particular companies Php - Laravel

3 Answers3