SQL Scripts for Account Statement
@@LoadEntityVariables
declare @entityType varchar(255) = 'Customers'
declare @entityId int = 0
declare @entityName varchar(255) = ''
declare @accountId int = 0
declare @accountName varchar(255) = ''
declare @zeroBalanceDate datetime = '2000-01-01T00:00:00.000'
declare @zeroBalanceDatecheck datetime = '2000-01-01T00:00:00.000'
SET @entityId = (SELECT [Value] FROM [ProgramSettingValues] WHERE [Name]='AS Entity Id')
SET @entityName = (SELECT [Name] FROM [Entities] WHERE [Id]=@entityId)
SET @accountId = (SELECT [AccountId] FROM [Entities] WHERE [Id]=@entityId)
SET @accountName = (SELECT [Name] FROM [Accounts] WHERE [Id]=@accountId)
UPDATE [ProgramSettingValues] SET [Value]=@entityName WHERE [Name]='AS Entity Name'
UPDATE [ProgramSettingValues] SET [Value]=@accountId WHERE [Name]='AS Account Id'
UPDATE [ProgramSettingValues] SET [Value]=@accountName WHERE [Name]='AS Account Name'
SET @zeroBalanceDatecheck =
(
select max(date) from
(select date,sum(debit-credit) over (order by date) as balance
from AccountTransactionValues
where AccountId = @accountId) sub
where balance = 0
)
IF (@zeroBalanceDatecheck > @zeroBalanceDate) SET @zeroBalanceDate = @zeroBalanceDatecheck
UPDATE [ProgramSettingValues] SET [Value]=convert(varchar(30),@zeroBalanceDate,126) WHERE [Name]='AS Entity Date_ZeroBalance'
@@GetStatement
declare @entityType varchar(255) = 'Customers'
declare @entityId int = 0
declare @dateFilterBeg datetime = GETDATE()
declare @entName varchar(255) = '@1'
declare @entId int = 0
SET @dateFilterBeg = (SELECT [Value] FROM [ProgramSettingValues] WHERE [Name]='AS Date Filter Beg')
SET @entId = (SELECT isnull([Id],0) FROM [Entities] WHERE [Name]=@entName)
IF @dateFilterBeg = ''
BEGIN
SET @dateFilterBeg = '2000-01-01'
END
SET @entityId = (SELECT [Value] FROM [ProgramSettingValues] WHERE [Name]='AS Entity Id')
IF @entId > 0 SET @entityId = @entId
declare @txcount int = 0
declare @i int = 1
declare @balance decimal(7,2) = 0.00
declare @lastZeroDate varchar(50) = '2000-01-01'
declare @tbl_tx_all table (
[Id] INT IDENTITY(1,1) NOT NULL
, [TxDate] Datetime null
, [TicketId] int null
, [TicketNo] int null
, [Description] varchar(255) null
, [Amount] decimal(6,2) null
, [Balance] decimal(7,2) null
)
declare @tbl_tx_filtered table (
[Id] INT IDENTITY(1,1) NOT NULL
, [TxDate] Datetime null
, [TicketId] int null
, [TicketNo] int null
, [Description] varchar(255) null
, [Amount] decimal(6,2) null
, [Balance] decimal(7,2) null
)
INSERT INTO @tbl_tx_all ([TxDate], [TicketId], [TicketNo], [Description], [Amount], [Balance])
SELECT
tv.[Date] as [Date]
,tkt.[Id] as [TicketId]
,CASE
WHEN tkt.[TicketNumber]>0 Then tkt.[TicketNumber]
ELSE d.[Id]
END as [TicketNo]
,CASE
WHEN [Credit] > 0 Then 'Payment [' + d.[Name] + '] ' + tx.[Name]
WHEN [Debit] > 0 Then 'Purchase'
END as [Desc]
,[Debit]-[Credit] as [Amount]
,0 as [Balance]
FROM [AccountTransactionValues] tv
LEFT JOIN [AccountTransactions] tx on tx.[Id] = tv.[AccountTransactionId]
LEFT JOIN [AccountTransactionDocuments] d on d.[Id] = tv.[AccountTransactionDocumentId]
LEFT JOIN [AccountTransactionTypes] tt on tt.[Id] = tv.[AccountTransactionTypeId]
-- Accounts
LEFT JOIN [AccountTypes] at on at.[Id] = tv.[AccountTypeId]
LEFT JOIN [Accounts] a on a.[Id] = tv.[AccountId]
-- Ticket
LEFT JOIN [Tickets] tkt on tkt.[TransactionDocument_Id] = d.[Id]
LEFT JOIN [TicketEntities] te on te.Ticket_Id = tkt.[Id] and te.[EntityTypeId] IN (SELECT [Id] FROM [EntityTypes] WHERE [Name]=@entityType)
-- Entity
LEFT JOIN [Entities] e on e.[Id] = te.[EntityId]
LEFT JOIN [EntityTypes] et on et.[Id] = e.[EntityTypeId]
WHERE 1=1
--AND tv.[AccountId] in (SELECT TOP 1 [AccountId] FROM [Entities] WHERE [Name] = @entityName)
AND tv.[AccountId] in (SELECT TOP 1 [AccountId] FROM [Entities] WHERE [Id] = @entityId)
ORDER BY [Date] ASC
-- get count of records
SELECT @txcount = count([Id]) FROM @tbl_tx_all
-- loop all records to set Last 0 Balance Date
WHILE @i<=@txcount
BEGIN
SET @balance = @balance + (SELECT [Amount] FROM @tbl_tx_all WHERE [Id]=@i)
UPDATE @tbl_tx_all SET [Balance] = @balance WHERE [Id]=@i
--IF @balance = 0 UPDATE [ProgramSettingValues] SET [Value]=(SELECT [TxDate] FROM @tbl_tx_filtered WHERE [Id]=@i) WHERE [Name]='AS Entity Date_ZeroBalance'
IF @balance = 0 SET @lastZeroDate = replace((SELECT convert(varchar(50),[TxDate],126) FROM @tbl_tx_all WHERE [Id]=@i),'T',' ')
SET @i = @i + 1
END
UPDATE [ProgramSettingValues] SET [Value]=@lastZeroDate WHERE [Name]='AS Entity Date_ZeroBalance'
--SELECT isnull(max([TxDate]),'2000-01-01') FROM @tbl_tx_all WHERE [Balance] = 0.00
--print '------------------------------------------- ' + @lastZeroDate
-- loop filtered records to get Balance Brought Forward previous to @dateFilterBeg
SET @balance = 0.00
SET @i = 1
WHILE @i<=@txcount
BEGIN
IF @dateFilterBeg > (SELECT [TxDate] FROM @tbl_tx_all WHERE [Id]=@i)
BEGIN
SET @balance = @balance + (SELECT [Amount] FROM @tbl_tx_all WHERE [Id]=@i)
UPDATE @tbl_tx_all SET [Balance] = @balance WHERE [Id]=@i
END
SET @i = @i + 1
END
-- insert Balance Brought Forward
IF @dateFilterBeg > (SELECT MIN([TxDate]) FROM @tbl_tx_all)
BEGIN
INSERT INTO @tbl_tx_filtered ([TxDate], [TicketId], [TicketNo], [Description], [Amount], [Balance])
SELECT @dateFilterBeg, 0, 0, 'Balance Brought Forward', @balance, @balance
END
-- insert Filtered Transactions
INSERT INTO @tbl_tx_filtered ([TxDate], [TicketId], [TicketNo], [Description], [Amount], [Balance])
SELECT [TxDate], [TicketId], [TicketNo], [Description], [Amount], [Balance] FROM @tbl_tx_all WHERE [TxDate] >= @dateFilterBeg
-- loop records to use filtered amounts and Balance Brought Forward
SELECT @txcount = count([Id]) FROM @tbl_tx_filtered
SET @i=1
SET @balance = 0.00
WHILE @i<=@txcount
BEGIN
SET @balance = @balance + (SELECT [Amount] FROM @tbl_tx_filtered WHERE [Id]=@i)
UPDATE @tbl_tx_filtered SET [Balance] = @balance WHERE [Id]=@i
SET @i = @i + 1
END
select [TxDate], [TicketId], [TicketNo], [Description], [Amount], [Balance] from @tbl_tx_filtered ORDER BY [Id] DESC
JScript for DB Functions
db.ExecStoredScript() and db.getRow()
function ExecStoredScript(script) {
var r = sql.Exec(script);
return r;
}
function getRow(sqlcmd) {
var r = sql.Query(sqlcmd).First;
return r;
}
JScript for Payment Processing
pay.UpdateDescription()
function UpdateDescription(creditPayment) {
creditPayment = typeof creditPayment !== 'undefined' ? creditPayment : '';
creditPayment = creditPayment.replace('Customer ','');
creditPayment = creditPayment.replace(' Payment','');
var dt = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff");
var paymenttype = Data.Get("paymentTypeName");
var paymentinfo = dt + " [" + paymenttype + "]";
var cctype='';
var ccdigits='';
if (paymenttype=='Credit Card' || paymenttype=='Credit Card USD' || creditPayment=='Credit Card' || creditPayment=='Credit Card USD') {
cctype = dlg.AskQuestion("Choose Credit Card type","Amex=AMEX,Master Card=MAST,Visa=VISA,Discover=DISC,Other=OTHR,CANCEL=CANCEL");
if (cctype=="CANCEL") {
Data.Set("canContinue",false);
dlg.ShowMessage("Payment Cancelled");
return 1;
}
ccdigits = dlg.EditValue("Last 4 CC Digits;.{4};;ON","");
paymentinfo += " (" + cctype + " " + ccdigits + ")";
}
creditPayment = (creditPayment != '' ? dt + ' [' + creditPayment + ']' : '');
if (cctype!='') {
creditPayment = (creditPayment!='' ? creditPayment + ' (' + cctype + ' ' + ccdigits + ')' : '');
}
if (paymenttype=='Customer Account' || paymenttype=='Gift Certificate') {
var accountname = Data.Get("accountName");
paymentinfo += " (" + accountname + ")";
}
Data.Set("description", paymentinfo);
//dlg.ShowMessage("Payment Processed\r"+paymentinfo);
if (creditPayment != '') {
return creditPayment;
} else {
return paymentinfo;
}
}
Requires SambaPOS 5.1.56 or higher
Now use one of the Automation Command buttons to navigate to the Entity Screen, then right-click and select 
