Using SQL Script to Clear Payment and Account Entries

V5 Question
1

Hi People,

So I’m into SambaPOS v5 and I’ve built this custom module to track tips attached to a payment type (for a north american application).

When I try to use the default “Clear Ticket Payments” action, it will remove SOME of the tips, but not all of the tips collected.

I tried to remedy this using a SQL script in the Automation > Scripting section.

image
image860×296 13.2 KB

Then hooked it up with this action:
image

I’ve drilled the rule execution down to the last item, its definitely when my SQL gets called. — I’ve even substituted the ‘{TICKET ID}’ code for a @1 argument substitution.

Any ideas?

Below is the output of the error message.
I get “Index was outside of the bounds of the array” when my Execute Script custom action is executed.

[General Info]

Application: SambaPOS
Version: 5.2.7
Region: en
DB: SQ
Machine: DESKTOP-MCT1NQH
User: pepin
Date: 8/27/2018
Time: 5:39 AM

User Explanation:

pepin said “”

[Exception Info 1]

Top-level Exception
Type: System.IndexOutOfRangeException
Message: Index was outside the bounds of the array.
Source: Samba.Services
Stack Trace: at Samba.Services.Implementations.ExpressionModule.ExpressionService.InvokeScript(String handlerFunction, Object dataObject) in C:\Users\vehbi\Source\Repos\sambapos-v5-pro\Samba.Services\Implementations\ExpressionModule\ExpressionService.cs:line 42
at System.Dynamic.UpdateDelegates.UpdateAndExecute3[T0,T1,T2,TRet](CallSite site, T0 arg0, T1 arg1, T2 arg2)
at Samba.Presentation.ClientLibrary.Modules.AutomationModule.ActionProcessors.ExecuteScript.Process(ActionData actionData, RuleExecutionContext context) in C:\Users\vehbi\Source\Repos\sambapos-v5-pro\Samba.Presentation.ClientLibrary\Modules\AutomationModule\ActionProcessors\ExecuteScript.cs:line 47
at Samba.Services.Common.RuleActionTypeRegistry.ProcessAction(String actionType, ActionData actionData, RuleExecutionContext context) in C:\Users\vehbi\Source\Repos\sambapos-v5-pro\Samba.Services\Common\RuleActionTypeRegistry.cs:line 39
at Samba.Services.Implementations.AutomationModule.AutomationService.ProcessAction(String actionType, ActionData actionData, RuleExecutionContext context) in C:\Users\vehbi\Source\Repos\sambapos-v5-pro\Samba.Services\Implementations\AutomationModule\AutomationService.cs:line 42
at Samba.Services.Implementations.AutomationModule.RuleExecutor.ExecuteWithoutLogging(Object dataParameter) in C:\Users\vehbi\Source\Repos\sambapos-v5-pro\Samba.Services\Implementations\AutomationModule\RuleExecutor.cs:line 110
at Samba.Services.Implementations.AutomationModule.RuleExecutor.ExecuteWith(Object dataParameter) in C:\Users\vehbi\Source\Repos\sambapos-v5-pro\Samba.Services\Implementations\AutomationModule\RuleExecutor.cs:line 62
at Samba.Services.Implementations.AutomationModule.NotificationService.NotifyEvent(String eventName, Object dataParameter, AppState appState) in C:\Users\vehbi\Source\Repos\sambapos-v5-pro\Samba.Services\Implementations\AutomationModule\NotificationService.cs:line 26
at Samba.Presentation.Services.Implementations.AutomationModule.NotificationClient.NotifyEvent(String eventName, Object dataObject)
at Samba.Presentation.ClientLibrary.Modules.PosModule.Payments.CommandButtonsViewModel.OnExecuteAutomationCommand(AutomationCommandData obj) in C:\Users\vehbi\Source\Repos\sambapos-v5-pro\Samba.Presentation.ClientLibrary\Modules\PosModule\Payments\CommandButtonsViewModel.cs:line 95
at Microsoft.Practices.Prism.Commands.DelegateCommand`1.<>c__DisplayClass6.<.ctor>b__2(Object o)
at Microsoft.Practices.Prism.Commands.DelegateCommandBase.Execute(Object parameter)
at Microsoft.Practices.Prism.Commands.DelegateCommandBase.System.Windows.Input.ICommand.Execute(Object parameter)
at MS.Internal.Commands.CommandHelpers.CriticalExecuteCommandSource(ICommandSource commandSource, Boolean userInitiated)
at System.Windows.Controls.Primitives.ButtonBase.OnClick()
at System.Windows.Controls.Button.OnClick()
at System.Windows.Controls.Primitives.ButtonBase.OnMouseLeftButtonUp(MouseButtonEventArgs e)
at System.Windows.UIElement.OnMouseLeftButtonUpThunk(Object sender, MouseButtonEventArgs e)
at System.Windows.Input.MouseButtonEventArgs.InvokeEventHandler(Delegate genericHandler, Object genericTarget)
at System.Windows.RoutedEventArgs.InvokeHandler(Delegate handler, Object target)
at System.Windows.RoutedEventHandlerInfo.InvokeHandler(Object target, RoutedEventArgs routedEventArgs)
at System.Windows.EventRoute.InvokeHandlersImpl(Object source, RoutedEventArgs args, Boolean reRaised)
at System.Windows.UIElement.ReRaiseEventAs(DependencyObject sender, RoutedEventArgs args, RoutedEvent newEvent)
at System.Windows.UIElement.OnMouseUpThunk(Object sender, MouseButtonEventArgs e)
at System.Windows.Input.MouseButtonEventArgs.InvokeEventHandler(Delegate genericHandler, Object genericTarget)
at System.Windows.RoutedEventArgs.InvokeHandler(Delegate handler, Object target)
at System.Windows.RoutedEventHandlerInfo.InvokeHandler(Object target, RoutedEventArgs routedEventArgs)
at System.Windows.EventRoute.InvokeHandlersImpl(Object source, RoutedEventArgs args, Boolean reRaised)
at System.Windows.UIElement.RaiseEventImpl(DependencyObject sender, RoutedEventArgs args)
at System.Windows.UIElement.RaiseTrustedEvent(RoutedEventArgs args)
at System.Windows.UIElement.RaiseEvent(RoutedEventArgs args, Boolean trusted)
at System.Windows.Input.InputManager.ProcessStagingArea()
at System.Windows.Input.InputManager.ProcessInput(InputEventArgs input)
at System.Windows.Input.InputProviderSite.ReportInput(InputReport inputReport)
at System.Windows.Interop.HwndMouseInputProvider.ReportInput(IntPtr hwnd, InputMode mode, Int32 timestamp, RawMouseActions actions, Int32 x, Int32 y, Int32 wheel)
at System.Windows.Interop.HwndMouseInputProvider.FilterMessage(IntPtr hwnd, WindowMessage msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
at System.Windows.Interop.HwndSource.InputFilterMessage(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
at MS.Win32.HwndWrapper.WndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
at MS.Win32.HwndSubclass.DispatcherCallbackOperation(Object o)
at System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs)
at System.Windows.Threading.ExceptionWrapper.TryCatchWhen(Object source, Delegate callback, Object args, Int32 numArgs, Delegate catchHandler)

[Assembly Info]

mscorlib, Version=4.0.0.0
Samba.Services, Version=1.0.0.0
Samba.Domain, Version=1.0.0.0
Samba.Infrastructure.Data, Version=1.0.0.0
System.ComponentModel.Composition, Version=4.0.0.0
Samba.Presentation.Services, Version=1.0.0.0
System.Core, Version=4.0.0.0
PresentationCore, Version=4.0.0.0
DevExpress.Xpf.LayoutControl.v16.2, Version=16.2.6.0
System.Xml, Version=4.0.0.0
DevExpress.Xpf.Grid.v16.2, Version=16.2.6.0
System, Version=4.0.0.0
DevExpress.Xpf.Grid.v16.2.Core, Version=16.2.6.0
WindowsBase, Version=4.0.0.0
System.Xaml, Version=4.0.0.0
PresentationFramework, Version=4.0.0.0
Samba.Infrastructure, Version=1.0.0.0
Microsoft.Practices.Prism, Version=4.0.0.0
System.Runtime.Serialization, Version=4.0.0.0
Microsoft.Practices.Prism.MefExtensions, Version=4.0.0.0
DevExpress.Xpf.Core.v16.2, Version=16.2.6.0
System.Windows.Forms, Version=4.0.0.0
System.Drawing, Version=4.0.0.0
Samba.Persistance, Version=1.0.0.0
Stateless, Version=1.0.0.0
PropertyTools, Version=2012.4.14.1
Samba.Localization, Version=1.0.0.0
ReachFramework, Version=4.0.0.0
EntityFramework, Version=6.0.0.0
FluentValidation, Version=3.4.0.0
Microsoft.CSharp, Version=4.0.0.0
Omu.ValueInjecter, Version=2.3.0.0
Microsoft.Practices.ServiceLocation, Version=1.0.0.0
DevExpress.Data.v16.2, Version=16.2.6.0
WindowsInput, Version=1.0.4.0

[System Info]

Operating System
-Microsoft Windows 10 Pro
–CodeSet = 1252
–CSDVersion =
–CurrentTimeZone = -240
–FreePhysicalMemory = 5460812
–OSArchitecture = 64-bit
–OSLanguage = 1033
–ServicePackMajorVersion = 0
–ServicePackMinorVersion = 0
–Version = 10.0.17134

Machine
-DESKTOP-MCT1NQH
–Manufacturer = VMware, Inc.
–Model = VMware7,1
–TotalPhysicalMemory = 8588742656
–UserName = DESKTOP-MCT1NQH\pepin

2

You can do it via Sql Management Studio

3

You can also modify the script in db tools by editing the file located in Documents/Sambapos5/Database Tasks

Modify the [CBL]Clear Database Transactions.sql file.

4

Hi.
Thanks for the feedback. Yes, I composed the SQL action via SQL Management Studio to make sure it was syntactically correct.

But I’m not trying to clear ALL transactions, what i’m trying to do is make an automation command (button) that you can click, to clear all payments & tips recorded to an individual ticket (in the case of an error).

Because, Clear Ticket Payments doesnt correctly remove the tips posted to the TIP subaccounts. So, either I’ve made an issue with how I write the tips to the subjournals (and they’re not linked properly) – or … I have to use this SQL script.

So, the following questions:

First is @1 @2 @3 the way to pass arguments into the script?

Second, where do I pass those arguments at call time? (is my syntax correct, using the parameters field of the Execute Script action?)

Third, can anyone explain what the INDEX out of bounds error is related to? Is it because I actually wiped that TX properly and samba got ‘confused’?

5

@Jesse I read this posting, which is something closer:

How do I void tips after using Cancel Payment

And I saw that you were commenting that “are you giving that customer their money back”

What issue I’m having is is more like:
I have a ticket with 200.00 outstanding
I press the divide/x button and have a 100.00 balance… I enter 120.00, press cash, the 100 goes to the cheque, and the 20 is put in to a ‘cash tips’ account.
Then, I press 120 again, and push visa… 100.00 goes to the ticket, and the remaining 20.00 is in visa tips.

Now, Whatever reason, we need to cancel payments… the 200.00 to the ticket disapears… the 20.00 to the cash tip account disappears (it was the first tip)… but the 20.00 to the visa tip account hangs around.

So, its either a bug in ‘Cancel Ticket Payments’, or its something else.

I tried to remedy this with some custom SQL script, being:
(Note: $ used because ‘AT’ is a forum reserved symbol)

DECLARE $TempTXID AS int;
SELECT $TempTXID = [AccountTransaction_AccountTransactionDocumentId] FROM [Payments] WHERE [TicketID]=‘$1’;
DELETE FROM [AccountTransactions] WHERE AccountTransactionDocumentId = $TempTXID;
DELETE FROM [AccountTransactionDocuments] WHERE Id = $TempTXID;
DELETE FROM [Payments] WHERE AccountTransaction_AccountTransactionDocumentId = $TempTXID;

But that didn’t help, or seem to execute either.

Perhaps I should submit an issue report for Cancel Ticket Payments?

6

How are you doing tips? It’s likely that tip is not a payment.

7

Tips are posted as an account transaction, along side the payments table.

When you enter the payments screen, I collect the payment and account type, and usual a tip amount.

At which point, a tip is lodged using account transaction, against a named tips account.

What I’ve noticed is, the 1st payment + tip posts fine, and can be cleared fine.

If a partial amount was posted on that ticket (i.e $100.00 ticket, $50 paid in tx 1, $50 paid in tx 2) – the second payment will also clear, but the second tip wont.

I suspect the clear payments SQL query is doing something like, finding the TX ID of the first payment on the ticket, then clearing all transactions with that ID.

I think it should query all TX ID’s, and for each TX ID, remove all associated account transactions.

How can I confirm my concept, and perhaps get this reviewed?

8

The script like that is plain SQL rather than JScript which works differently which I have never done myself.

See here for an example of roughly how to do direct SQL, although its a question with an issue.

To so SQL direct using script you use @@ in handler.

I have always run my SQL through JScript like this;
image