Hi guys,
I am looking into changing Ticket Number Generator. First time this came up so I am not entirely sure what are the possibilities. Mainly what the requirement is:
Should start with a year and month and then continue with a ticket number.
Something along the line of 2021-10-999
What are options or formats number generator can support?
After checking database tables I realized that it might not be possible because of - characters as the Number column is specifically integer.
So I guess my questions is, would it break the numerator feature if I changed it to nvarchar?
The issue with changing that is that the model that entity framework populates is expecting an int as the type. This could cause all sorts of unintended fun.
I had to do something similar for our convoluted receipt numbers. I used a combination of a sequence and stored procedure and then a ticket tag - or ticket state if it’s not important for non admin users to see it.
Sequence:
/* Sequence for ticket number generation */
IF OBJECT_ID('dbo.s_TicketNumberGen') IS NOT NULL
DROP SEQUENCE dbo.s_TicketNumberGen;
GO
CREATE SEQUENCE s_TicketNumberGen
AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 99999999
CYCLE
NO CACHE;
GO
Stored procedure:
/* Generate custom ticket number */
IF OBJECT_ID('dbo.p_GenerateCustomTicketNumber') IS NOT NULL
DROP PROCEDURE dbo.p_GenerateCustomTicketNumber;
GO
CREATE PROCEDURE dbo.p_GenerateCustomTicketNumber
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SeqNr INT = NEXT VALUE FOR s_TicketNumberGen;
DECLARE @YearMonth NVARCHAR(7) = CONVERT(VARCHAR(7), GETDATE(), 126);
SELECT CONCAT(@YearMonth, '-', @SeqNr) AS TicketNumber;
END;
GO
You could use a script to call the stored procedure and return its value to update ticket tag/state or inline it with REPORT SQL DETAILS. The column name returned is TicketNumber.
The sequence as it is should guarantee sequential generation. If it’s not that big of a deal, for better performance you can change NO CACHE to CACHE x where x is an int greater than 1. The db engine will cache x values and return those instead of calculating the value every time (hitting the db). But if the db engine is restarted, those cached values go away so you could wind up with gaps in the sequence.
If you need to reset the sequence, say on a monthly basis, execute ALTER SEQUENCE s_TicketNumberGen RESTART;.
Yeah, thats what I was thinking too.
But I thought relation between Ticket and Ticket Entities was TicketId not TicketNumber? TicketNumber is declared as nvarchar too.
Do you reckon I could then leave the ticket generator as is i.e. #3683 but then through SQL update TicketNumber in a separate Ticket Closing rule? {DATE:yyyy-MM-}+Number
I am guessing TicketNumber being declared as nvarchar should allow it to work just fine with other dependencies too.
Yep, dbo.Tickets.TicketNumber is NVARCHAR(MAX). I don’t see why you couldn’t update it with whatever you wanted.
AFAIK, ticket number exists only in the Tickets table and isn’t referenced anywhere else in the db.
You could also automate it all with a trigger on the tickets table on insert and then some scheduled task to reset sequence if needed. But use whichever way works for you.
The only issue I can see, for now, is guaranteeing uniqueness. If somewhere in the SambaPOS code it’s looking for the ticket number and Single() is called and there’s more than one object found, an exception will be thrown and SambaPOS will crash.
I try not to mess with the default operation of SambaPOS unless absolutely necessary which is why I went with the ticket tag/state route.
If anyone reaches this please let me know so we can understand what we can do to help you without having to resort to such extreme.
Yeah, I will have to test this to the best of my abilities before I implement this anywhere. Possibly keeping it as simple as possible after too.
Same, always following KISS. 
But its a very specific requirement so will try to go forward. Its late now but Ill give it a go tomorrow and post my progress here.
The client in question is basically a distillery (different system for that) and retail. Due to the tax and customs (yes they export) it is really important that all details be clearly specified on the tax invoices (a bit more than just what normal F&B would use or need) and that invoices have to be dated invoice numbers YYYY-MM-# in this format.
As of now they use Odoo’s POS module but they cant customize it (well, it is possible but Odoo API is no small task) to match exactly what they need, where SambaPOS can do what they asked but with some modifications. The only extreme one is this sequence format everything else can be sorted with already existing features.
I use a custom ticket number via script and ticket tag, I used this post as reference: Ticket Number Control (Custom Invoice Numbers with Reset)
I cannot use the default ticket number generator because it skip a number if a ticket is not created for example, and for taxes I need a correlative ticket number.
You should use Ticket Id for that then. Ticket Id will not skip. No need to automate it if you use Ticket Id
@Memo So this is what I have in mind executing in a separate Ticket Closing Rule. I just need a bit of extra foresight, do you see something happening I do not? 
Next step is then to figure out logic when the Numerator Number is reset to 0. I am thinking on Work Period Start event when {DATE:dd} is 01. And figure out how to solve the problem in case someone decides to open work period more than once in one day when {DATE:dd} is 01.
WARNING! USE AT YOUR OWN RISK
This is not a ready to go solution at this stage and if you are reading this and planning on using this without fully understanding what is happening, you are likely to create irreversible problems for yourself.
function updateTicketNumber(ticketId,currentDate){
// ----------------------------------------------
// -- Select Number Generator Number Value
// ----------------------------------------------
var qrySelectNumNumber = "SELECT [Number] FROM [Numerators] WHERE [Name] = 'Test Number Generator'";
var qryNumNumber = sql.Query(qrySelectNumNumber).First;
// ----------------------------------------------
// -- Checks if there is a ticket with the ticket ID we passed to the script during Ticket Closing Rule
// ----------------------------------------------
var qryTicketIdExists = sql.Exists("SELECT [Id] FROM [Tickets] WHERE [Id] = "+ticketId+"");
// ----------------------------------------------
// -- Checks if there is a TicketNumber contains dash character - i.e. YYYY-MM-#
// -- If the Ticket Number contains - character then indexOf() will return 4, in this case we will do absolutely nothing.
// -- If the Ticket Number does NOT contain - character then indexOf() will return -1, in this case we will update TicketNumber column in the database.
// ----------------------------------------------
var qryTicketNo = sql.Query("SELECT [TicketNumber] FROM [Tickets] WHERE [Id] = "+ticketId+"").First;
var qryTicketNoExists = qryTicketNo.indexOf("-");
if(qryTicketIdExists == true && qryTicketNoExists==-1)
{
// ----------------------------------------------
// -- If the ticket exists it updates the TicketNumber in YYYY-MM-# format
// ----------------------------------------------
var newTicketNo = currentDate+'-'+qryNumNumber;
sql.ExecSql("UPDATE [Tickets] SET [TicketNumber] = '"+newTicketNo+"' WHERE [Id] = "+ticketId+"");
} else {
}
}
It skip numbers too, when a ticket is merged or unmerged so I void that behavior with a custom number generator.
If you want to use the ticket number that’s generated by SambaPOS and change just prepend the date, I would use a db trigger that fires after insert.
But back to my original comment about default operation of SambaPOS, I would use a ticket tag or state.
What is your main concern, why would you use trigger instead of updating it as its inserted into the database from SambaPOS?
A trigger after instert would update the ticket number automatically. No need for automation, constraints, hitting the db for various queries, etc. Example coming in a bit.
/* Update with custom ticket number */
IF OBJECT_ID('dbo.trg_Tickets_Insert') IS NOT NULL
DROP TRIGGER dbo.trg_Tickets_Insert;
GO
CREATE TRIGGER dbo.trg_Tickets_Insert
ON dbo.Tickets
AFTER INSERT
AS
BEGIN
DECLARE @TicketId INT;
DECLARE @TicketNr NVARCHAR(MAX);
DECLARE @YearMonth NVARCHAR(7) = CONVERT(VARCHAR(7), GETDATE(), 126);
SELECT @TicketId = Inserted.Id,
@TicketNr = Inserted.TicketNumber
FROM Inserted;
UPDATE dbo.Tickets
SET TicketNumber = CONCAT(@YearMonth, '-', @TicketNr)
WHERE Id = @TicketId;
END;
GO
fresh install with default db and the above trigger:
if you wanted to use the sequence I posted above to (almost) guarantee sequential numbers:
/* Update with custom ticket number */
IF OBJECT_ID('dbo.trg_Tickets_Insert') IS NOT NULL
DROP TRIGGER dbo.trg_Tickets_Insert;
GO
CREATE TRIGGER dbo.trg_Tickets_Insert
ON dbo.Tickets
AFTER INSERT
AS
BEGIN
DECLARE @TicketId INT;
DECLARE @SeqNr INT = NEXT VALUE FOR s_TicketNumberGen;
DECLARE @YearMonth NVARCHAR(7) = CONVERT(VARCHAR(7), GETDATE(), 126);
SELECT @TicketId = Inserted.Id
FROM Inserted;
UPDATE dbo.Tickets
SET TicketNumber = CONCAT(@YearMonth, '-', @SeqNr)
WHERE Id = @TicketId;
END;
GO
and hell, you could even use the ticket id:
/* Update with custom ticket number */
IF OBJECT_ID('dbo.trg_Tickets_Insert') IS NOT NULL
DROP TRIGGER dbo.trg_Tickets_Insert;
GO
CREATE TRIGGER dbo.trg_Tickets_Insert
ON dbo.Tickets
AFTER INSERT
AS
BEGIN
DECLARE @TicketId INT;
DECLARE @YearMonth NVARCHAR(7) = CONVERT(VARCHAR(7), GETDATE(), 126);
SELECT @TicketId = Inserted.Id
FROM Inserted;
UPDATE dbo.Tickets
SET TicketNumber = CONCAT(@YearMonth, '-', @TicketId)
WHERE Id = @TicketId;
END;
GO