Search for ticket by order number and display ticket

This if for @Filadegs

You’ll need thee actions, two rules, one automation command, one script, and one stored procedure.

Actions

image
Action Name: TS Display Ticket
Action Type: Display Ticket
Parameters:

  • Ticket Id: [:Ticket Id]

image
Action Name: TS Execute Automation Command
Action Type: Execute Automation Command
Parameters:

  • Automation Command Name: [:Command Name]
  • Command Value: [:Command Value]

image
Action Name: TS Display Message (MSG)
Action Type: Show Message
Parameters:

  • Message: [:Message]

Rules

Rule Name: TS Search for Ticket by Order Number
Event Name: Automation Command Executed
Execute Rule If: Matches
Constraint:

  • Automation Command Name [Equals] TS Search for Ticket by Order Number

Actions for Rule

image

Command Name: TS Display Ticket
Command Value: {CALL:ts.getTicketIdFromOrderNumber('[?Order Number;\d+?;;OCN]')}


image
Rule Name: TS Display Ticket
Event Name: Automation Command Executed
Execute Rule If: Matches
Constraint:

  • Automation Command Name [Equals] TS Display Ticket

Actions for Rule

image
Constraint: [=TN('[:CommandValue]')] <= 0
Message: Ticket not found.

image
Constraint: [=TN('[:CommandValue]')] > 0
Ticket Id: [:CommandValue]

Automation Commands

image
Name: TS Search for Ticket by Order Number
Button Header: Ticket\rSearch

Mappings

Scripts

image
Name: TS Ticket Search
Handler: ts
Script:

function getTicketIdFromOrderNumber(orderNumber) {
    var q = "dbo.p_GetTicketIdFromOrderNumber @OrderNumber = '" + orderNumber + "'";
    var r = sql.Query(q).First
    
    if (r == null) {
        return '0'
    }

    return r;
}

SQL Stored Procedures

You'll want to execute the following DML in SQL Server Management Studio against your SambaPOS database:
/* Get ticket id from order number */

IF OBJECT_ID('dbo.p_GetTicketIdFromOrderNumber') IS NOT NULL
  DROP PROCEDURE dbo.p_GetTicketIdFromOrderNumber;
GO

CREATE PROCEDURE dbo.p_GetTicketIdFromOrderNumber @OrderNumber INT
AS
  BEGIN
    SET NOCOUNT ON;
    SELECT t.Id
        FROM dbo.Tickets           t
             INNER JOIN dbo.Orders o
                     ON o.TicketId = t.Id
        WHERE o.OrderNumber = @OrderNumber;
  END;
GO

Here’s it in action:

2021-11-16_14;31_1637094707_%pn

2 Likes

I forgot to add: the column storing the order number is not indexed. As such, any query will require a full table scan and will affect performance and the time to return the result.

If you’re going to be using this a lot, add an index for that column. Execute this in SSMS:

CREATE NONCLUSTERED INDEX ix_orders_order_numner ON dbo.Orders (OrderNumber);

There’s a small hit on inserts and updates (doubtful it’s be noticed), but if this will be often-used, it’s worth it.

3 Likes