Auto generate number is not working at the start of new year

PHP
1

I am trying to generate my bill no in following sequence in php L5- 0124-0001 (where L5 is company name, 01 representing month and 24 representing year, where as last four digit is for serial number) , I was using this code since October 2023, everything was working fine until end of year 2023, but as today year 2024 start, its stop working, (company name is correctly display, year month as well but last four digit are not incremented when new bill need to be generated. it remain 0001 for all the bills, where as it should L5-0124-0002 and so on … below is the code

$CI = "L5"; //Example only
$CIcnt = strlen($CI);
$offset = $CIcnt + 6;

// Get the current month and year as two-digit strings 
$month = date("m"); // e.g. 09 
$year = date("y"); // e.g. 23  

// Get the last bill number from the database 
$query = "SELECT patientno FROM iap2 ORDER BY patientno DESC LIMIT 1"; 
$result = mysqli_query($con,$query); 
// Use mysqli_fetch_assoc() to get an associative array of the fetched row 
$row = mysqli_fetch_assoc($result); 
// Use $row[‘patientno’] to get the last bill number 
$lastid = $row['patientno'];     

// Check if the last bill number is empty or has a different month or year
if(empty($lastid) || (substr($lastid, $CIcnt + 1, 2) != $month) || (substr($lastid, $CIcnt + 3, 2) != $year)) { 
    // Start a new sequence with 0001 
    $number = "$CI-$month$year-0001"; 
} else { 
    // Increment the last four digits by one 
    $idd = substr($lastid, $offset); // e.g. 0001 
    
    $id = str_pad($idd + 1, 4, 0, STR_PAD_LEFT); // e.g. 0002 
    $number = "$CI-$month$year-$id"; 
}
2

Code works for me. You sure your php server thinks its the new year?

What happens when you echo date(DATE_RSS); ?

3

It cannot work as you order by month/year instead of year/month so 1223 is greater then 0124 and therefor you will always get the 12/23 bill as the last in the database and create the next as 01/24.

You alway need to store a date in format year-month-day to be sure to have a good order if you need it

1 Like
4

let me explain sir!
i started entering first record in October 2023, when month change , for example November 23 , it still worked, now the year is changed and its stop working, i thnk its unable to detect lastid on the year change, (the code is working for you because you entered first record or last record in 2024, thats why it keep incremented. Just try to change year after entering few records, then you will see it stop working.

5

Well i do see part of a problem forming…

$query = “SELECT patientno FROM iap2 ORDER BY patientno DESC LIMIT 1”;

Right… so you’re sorting by patientno.

patientno is of the format
"$CI-$month$year-$id";

So when I sort by that, i’m going to get January’s patients first. Regardless of the current year. You’ve sorted by month before year.

You should probably SELECT patientno FROM iap2 WHERE patientno LIKE “$CI-$month$year%” ORDER BY patientno DESC" (and then catch the possibility that there are no results in the return)

EDIT: And yes, before the “but parameterized queries” crowd shows up, should you? Probably, but in this case there’s no user input involved, so it’s relatively safe.

1 Like
6

oh I also found that issue! I have 6000 plus record, now have to change all of them ??? is there any other way to handle situation ?

7

Thanks alot sir! so i have to year before month is my bill no sequence but the issue is i already have more than 6000 plus record stored in database

8

I… have already given you the other way to handle the situation…

9

ok let me try this.hopefully it will work thanks alot sir

10

If only that had been mentioned when you first asked about auto-generating the number.

1 Like
11

absolutely right solution is this one, year first then month, but at that time i didn’t consider this point,

12

SELECT patientno FROM iap2 WHERE patientno LIKE “$CI-$month$year%”

will it Un-optimised the sql query as it will scan whole mysql table. Should I rearrange year before month in my bill number, so that i dont face any major issue after few months ???

13

Should you? Yes. That would be the correct fix.

Can you? Depends. Is this number on official invoices that have already been sent to customers? Are there tax or other legal requirements that are impacted by this action?

1 Like
14

i already closed the year, i just have to change today bill, which will not be big deal, Thanks alot for your guidance, i am going to do this after office closing today evenning

15

As an alternative solution, store the month/year/counter as separate columns in your DB and just format them as you desire when presenting the bill on the page. Then you can properly index the columns and query by them when needed.

1 Like
16

On this note, you may want to do some tests. Depending on your version of software and use case, you may find that COUNT(id) is faster than LIMIT 1. (After all, you start at 1, you want the next number; so the next number is count+1.)

1 Like
17

i done this by adjusting year before month and I created one dummy entry (L5-2401-0016) so that when front desk will enter real data then newbill number should become L5-2401-0017 and once this entry done, i will delete dummy entry. This will keep previous entered data as it is.

$CIcnt = strlen($CI);
$offset	= $CIcnt + 6;

// Get the current month and year as two-digit strings 
$month = date("m"); // e.g. 09 
$year = date("y"); // e.g. 23  

// Get the last bill number from the database 
$query = "SELECT patientno FROM iap2 ORDER BY patientno DESC LIMIT 1"; 
$result = mysqli_query($con,$query); 
// Use mysqli_fetch_assoc() to get an associative array of the fetched row 
$row = mysqli_fetch_assoc($result); 
// Use $row[‘patientno’] to get the last bill number 
$lastid = $row['patientno']; 	 

// Check if the last bill number is empty or has a different month or year
if(empty($lastid) || (substr($lastid, $CIcnt + 1, 2) != $month) || (substr($lastid, $CIcnt + 3, 2) != $year)) { 
	// Start a new sequence with 0001 
	$number = "$CI-$month$year-0001"; 
} else { 
	// Increment the last four digits by one 
	$idd = substr($lastid, $offset); // e.g. 0001 
	
	$id = str_pad($idd + 1, 4, 0, STR_PAD_LEFT); // e.g. 0002 
	$number = "$CI-$month$year-$id"; 
} 
type or paste code here
21

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.