This script properly converts values as expected …
---------------------------------
-- Change the Database Decimal Precision in SambaPOS before running this,
-- and ensure SambaPOS is closed. For example, I changed the precision from 2 to 6.
-- The setting is in Settings > Program Settings > General > Currency Decimals > Database Decimals
---------------------------------
-- OLD Foreign Currency Name
declare @currencyNameOld varchar(10) = 'USD'
-- settings for NEW Foreign Currency
declare @currencyName varchar(10) = 'HNL'
declare @currencyFormat varchar(10) = 'L {0:N}'
declare @currencyXR decimal (15,6) = 23.000000
declare @currencyInverted int = 1
-- the Exchange Rate of the OLD Currency
-- used to recalculate Account Transaction Values
declare @XR decimal (15,6) = 20.000000
print 'Conversion Rate / Inverted : ' + convert(varchar(20),@XR) + ' / ' + convert(varchar(20),1/@XR)
declare @tbl_ConversionItems table
(
[ID] INT IDENTITY(1,1) NOT NULL
,[TableName] varchar(255)
,[TableField] varchar(255)
,[ConvertFlag] int default 1
)
print 'Clearing ConversionItems ...'
DELETE FROM @tbl_ConversionItems
print 'Populating ConversionItems ...'
INSERT INTO @tbl_ConversionItems
VALUES
-- Calculations
('CalculationTypes','Amount',1)
,('CalculationTypes','MaxAmount',1)
,('CalculationTypes','Rounding',1)
,('Calculations','Amount',1)
,('Calculations','Rounding',1)
,('Calculations','CalculationAmount',1)
,('Calculations','Rate',0)
-- Payments
,('Payments','Amount',1)
,('Payments','TenderedAmount',1)
,('ChangePayments','Amount',1)
-- Products
,('MenuItemPrices','Price',1)
,('OrderTags','Price',1)
-- Inventory
,('Recipes','FixedCost',1)
,('CostItems','CostPrediction',1)
,('CostItems','Cost',1)
,('CostItems','AvgPrice',1)
,('PeriodicConsumptionItems','Cost',1)
,('InventoryItems','DefaultBaseUnitCost',0)
,('InventoryItems','DefaultTransactionUnitCost',0)
,('InventoryItemUnits','DefaultCost',0)
,('InventoryTransactions','TotalPrice',1)
-- Tickets/Orders
,('Orders','Price',1)
,('Tickets','RemainingAmount',1)
,('Tickets','TotalAmount',1)
-- Transactions
,('AccountTransactions','Amount',1)
,('AccountTransactionValues','Debit',1)
,('AccountTransactionValues','Credit',1)
,('AccountTransactionValues','Exchange',1)
-- Exchange Rates
,('AccountTransactions','ExchangeRate',1)
,('Tickets','ExchangeRate',1)
,('ChangePayments','ExchangeRate',1)
,('Payments','ExchangeRate',1)
,('Calculations','ExchangeRate',1)
--SELECT * FROM @tbl_ConversionItems
------------------------------------
-- Perform Conversions
------------------------------------
declare @SQL nvarchar(max) = ''
declare @tblName varchar(100) = ''
declare @tblField varchar(100) = ''
declare @pConvertFlag int = 1
declare @i int = 0
declare @maxid int = 0
declare @updateCount int = 0
SET @maxid = (SELECT max([ID]) FROM @tbl_ConversionItems)
SET @updateCount = (SELECT count(*) FROM @tbl_ConversionItems WHERE [ConvertFlag]=1)
print 'ConversionItems Count : ' + convert(varchar(10),@maxid)
print 'Field Update Count : ' + convert(varchar(10),@updateCount)
print 'Performing Field Conversions ...'
WHILE @i <= @maxid
BEGIN
SET @tblName = (SELECT [TableName] FROM @tbl_ConversionItems WHERE [ID] = @i)
SET @tblField = (SELECT [TableField] FROM @tbl_ConversionItems WHERE [ID] = @i)
SET @pConvertFlag = (SELECT isnull([ConvertFlag],0) FROM @tbl_ConversionItems WHERE [ID] = @i)
IF @pConvertFlag = 1
BEGIN
SET @SQL = 'UPDATE ['+@tblName+'] SET ['+@tblField+'] = ['+@tblField+']/'+convert(varchar(50),@XR)
SET @SQL = 'UPDATE ['+@tblName+'] SET ['+@tblField+'] = ['+@tblField+']*'+convert(varchar(50),1/@XR)
print 'EXECUTING '+convert(varchar(10),@i) + ': ' +@SQL
EXECUTE sp_executesql @SQL
END
SET @i = @i + 1
END
print 'Field Conversion Complete!'
/*
--------------------------
-- Update Foreign Currency Template and Accounts
-- this should be done prior to creating any NEW Accounts (ie. Rounding Accounts)
-- You may not want to run this part on all Accounts. You may not want to run it
-- on ANY Accounts. For example, in my case, it only makes sense to run this for
-- Expense and Supplier Accounts.
--------------------------
declare @currencyID int = (SELECT [Id] FROM [ForeignCurrencies] WHERE [Name] = @currencyNameOld)
print 'Updating OLD Currency ('+@currencyNameOld+') to NEW Currency ('+@currencyName+') ...'
-- Update Foreign Currency
UPDATE [ForeignCurrencies] SET
[Name] = @currencyName
,[CurrencySymbol] = @currencyFormat
,[ExchangeRate] = @currencyXR
,[InverseExchangeRate] = @currencyInverted
WHERE [Id] = @currencyID
print 'Swapping Account Currency parameters to use NEW Currency ...'
-- Update Accounts to swap Currency
UPDATE a SET
a.[ForeignCurrencyId] = 99999
FROM [Accounts] a
JOIN [AccountTypes] act on act.[Id]=a.[AccountTypeId]
WHERE [ForeignCurrencyId] = @currencyID
AND act.[Id] IN (SELECT [Id] FROM [AccountTypes] WHERE [Name]='Expense Accounts' OR [Name]='Supplier Accounts')
UPDATE a SET
a.[ForeignCurrencyId] = @currencyID
FROM [Accounts] a
JOIN [AccountTypes] act on act.[Id]=a.[AccountTypeId]
WHERE [ForeignCurrencyId] = 0
AND act.[Id] IN (SELECT [Id] FROM [AccountTypes] WHERE [Name]='Expense Accounts' OR [Name]='Supplier Accounts')
UPDATE a SET
a.[ForeignCurrencyId] = 0
FROM [Accounts] a
JOIN [AccountTypes] act on act.[Id]=a.[AccountTypeId]
WHERE [ForeignCurrencyId] = 99999
AND act.[Id] IN (SELECT [Id] FROM [AccountTypes] WHERE [Name]='Expense Accounts' OR [Name]='Supplier Accounts')
*/
Before and After - General
Conversion Rate used in the Script is 20.000000
I am struggling with what to do with the 2 highlighted Accounts (Cash HNL and Credit Card HNL). Accounts like this were previously configured to use the Default Currency (unassigned [HNL]), but now they are configured to use the NEW Foreign Currency of HNL.
The amounts shown are actually correct for converted values (ie. for Cash HNL 644242.47/20 = 32854.82) however, I would instead want the amounts to NOT be converted, since the proper HNL value should be L 644242.47 for that Account, but it is showing the amount converted to the now Default USD Currency.
@emre, do you have any suggestion for this? It might be a rather moot point when it comes to Payment Accounts as shown in this screen, but for other accounts such as Expense and Supplier Accounts it becomes more important.
Here is a granular view of a Sale and Payment using Cash HNL in the DB, after the conversion is run. I think I might need to constrain the conversion to omit records like this, or do something different with the [Exchange] columns for these cases.
I think what I will try is something like the following, to be run after conversion - what do you think?
UPDATE [AccountTransactions] SET
[ExchangeRate]=20.000000
WHERE [Name]='Payment Transaction [Cash]' OR [Name]='Payment Transaction [Credit Card]' OR [Name]='Change Transaction [Cash]'
UPDATE [AccountTransactionValues] SET
[Exchange]=[Exchange]*20.000000
WHERE [Name]='Payment Transaction [Cash]' OR [Name]='Payment Transaction [Credit Card]' OR [Name]='Change Transaction [Cash]'
Here are the HNL Payment Accounts after conversion, and after the update shown above:
Here is another example, this time with a Supplier Account that previously used the Foreign Currency of USD, but after conversion uses the Default Currency (undefined [USD]). The [AccountTransactions] table shows the correct amount for the Account, while the [AccountTransactionValues] table show an incorrect amount in the [Exchange] column. The Exchange for [Roatan Provision] in [AccountTransactionValues] should be 200 instead of 10.
Other Accounts show incorrect values as well, since they were using Default Currency (undefined [HNL]) and now use Foreign Currency HNL. For example, [gas for geny] and [Sol Gas] show correct USD amounts of $50 each, but the Exchange in [AccountTransactions] is 0.05 when it should be 20, and in [AccountTransactionValues] the Exchange is 50 when it should be 1000.
So this other snippet should handle Accounts which are now configured as Foreign HNL and correctly adjust the 2 “special” accounts which Invoice in USD.
UPDATE [AccountTransactions] SET
[ExchangeRate]=@XR
WHERE 1=1
AND ([Name] like '%Expense%' OR [Name] like '%Supplier%')
AND ([Name] not like '%Accountant%' AND [Name] not like '%Roatan Provision%')
UPDATE [AccountTransactionValues] SET
[Exchange]=[Exchange]*@XR
WHERE 1=1
AND ([Name] like '%Expense%' OR [Name] like '%Supplier%')
Here they are after conversion, and after the update shown above:
I am confident that I will be able to work it out eventually, but until I do, I can’t really switch the system currencies.
I have another problem (and it is a big show-stopper), and I don’t know any way to fix or workaround it.
