How to set a custom sequential Receipt Number
When I made a Point Of Sale system for a business unit, I was faced with the requirement that the system should be able to produce an unique receipt number. At a glance, i've just wonder to take advantage on AUTO INCREMENT feature as a primary key (in this case I use MySQL database). But the requirements (demanded by the business owner) is different of course. They asked for something like this:
- Receipt Number should have the YYYYNNNN format, where YYYY is the year of the transaction, while NNNN is the number of transactions
- Receipt Number will have to be reset when it comes into the new year of transaction, for example if the last number in 2012 was 20120986, then the system must reset the number at the beginning of the year into 20130001
of course, here, I can't use the AUTO INCREMENT feature anymore, also i can't use the "record count" method (since i can't guarantee validity of records count)
To overcome this I've created a function to generate receipt number, with a method to find maximum number of recorded receipt number's through SQL query, and then compared the YYYY information on resulted receipt number with the current year. If it found different then the year (YYYY) is converted into current year and the serial number are reset to '0001 '.
If it was the same year, the serial number will be added by 1 then added again with 10000. (so if the sequential is 55, then 55 + 10000 = 10055, but we only have to take four characters (start from 2) as a String according to requirement to produce 0055. Simple right?
Here's the function that I create (in Visual Basic)
Private Function GetNoBukti() As String Dim Skr As String Dim RcdAkhir As String Dim urut As Integer Dim Curryear As Integer Dim RS As ADODB.Recordset Dim SQL As String Set RS = New ADODB.Recordset SQL = "SELECT IF(MAX(id_bukti) IS NOT NULL, MAX(id_bukti), '0000') AS rcd FROM penjualan_header" RS.CursorLocation = adUseClient RS.Open SQL, conn, adOpenDynamic, adLockOptimistic RcdAkhir = RS!rcd Curryear = Year(Now) urut = Val(Mid(RcdAkhir, 5, 4)) If (Mid(RcdAkhir, 1, 4) < Curryear) Or (RcdAkhir = "0000") Then GetNoBukti = Curryear & "0001" Else GetNoBukti = Curryear & Mid(10000 + urut + 1, 2, 4) End If End Function
As on the SQL string described above, if the record was not found in transaction table (result NULL) then I give the dummy sequent '0000 'to handle a condition where the new system was first installed
What do you think? Do you have a more effective way? Feel free to share here