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