Takeaway Orders

Hi John

I saw that post before about deleting transactions but I am just not sure of the actuall procedure and what I need to do.

Thanks

@JohnS

Hi John
I was wondering if it was possible if you could talk me through what I need to do to get rid of previous transactions. I am not familiar with SQL system. I just want to get rid of all tester transactions without loosing any items or settings we put in.

Than you

Firstly, are you using CE or SQL Server for you database ?
Look here for help working it out -> Zero all accounts

@JohnS

Hi john sorry for late reply it is CE

Instructions to backup and wipe transactional data in a Compact SQL 4.0 Database.

1… Find where your Compact SQL database file is. It could be in any of the following

C:\Users\{user}\Documents\SambaPOS4
C:\ProgramData\SambaPOS\SambaPOS4
C:\Users\{user}\AppData\Roaming\SambaPOS\SambaPOS4

or do a search for *.sdf files.
The directory where the sdf file is located is where all the other files will need to be placed.

My Compact SQL Database for this example is test.sdf and the file is in C:\Users\John\Documents\SambaPOS4.

The following files will be placed in C:\Users\John\Documents\SambaPOS4.

2… Download SqlCeCmd40.exe from http://sqlcecmd.codeplex.com/downloads/get/635167

3… Create a file called Clear_CE_Data.bat and place the following in it

@echo off
REM Set your Database name here.
set DATABASENAME=test.sdf

set DATESTAMP=%DATE:/=-%_%TIME::=-%
set DATESTAMP=%DATESTAMP: =_%
set BACKUPFILENAME=%CD%\%DATABASENAME%-%DATESTAMP%.bak
pause
echo -- BACKUP DATABASE --
copy %DATABASENAME% %BACKUPFILENAME%
echo -- CLEAR DATABASE --
SqlCeCmd40 -d "Data Source=%DATABASENAME%" -i V4_dtrans.sql
pause

4… Create a file called V4_dtrans.sql and place the following in it

DELETE FROM [TicketEntities]
GO
DELETE FROM [Tickets]
GO
DELETE FROM [AccountTransactionDocuments]
GO
DELETE FROM [AccountTransactions]
GO
DELETE FROM [AccountTransactionValues]
GO
DELETE FROM [Calculations]
GO
DELETE FROM [CostItems]
GO
DELETE FROM [InventoryTransactionDocuments]
GO
DELETE FROM [InventoryTransactions]
GO
DELETE FROM [Orders]
GO
DELETE FROM [PaidItems]
GO
DELETE FROM [PeriodicConsumptionItems]
GO
DELETE FROM [PeriodicConsumptions]
GO
DELETE FROM [ProductTimerValues]
GO
DELETE FROM [Payments]
GO
DELETE FROM [WarehouseConsumptions]
GO
DELETE FROM [WorkPeriods]
GO
UPDATE [Numerators] SET Number = 0
GO
UPDATE [EntityStateValues] SET EntityStates = '[{"S":"Available","SN":"Status"}]' Where EntityStates like '%Status%'
GO

So in C:\Users\John\Documents\SambaPOS4 I have 3 files

  • SqlCeCmd40.exe
  • Clear_CE_Data.bat
  • V4_dtrans.sql
    Plus my database file
  • test.sdf

5… To backup and delete transactional data from your database, you only need to run Clear_CE_Data.bat

** UPDATE ** - CLear_CE_Data.bat file has been updated to correct date & time stamp.

@JohnS

Hi John

Did all that and when I ran Clear this came up

Can you post a screen shot of the bat file and the directory please.

Also, what version of .NET are you running?

Ok where do i find all that. Sorry its just a little bit over my head

Ok . net version i think is 4.0.3

@JohnS

Does this help

I need to know what you have in the Clear_CE_Data.bat

I also need to see the files in the SambaPOS4 directory where your .sdf file is

@JohnS

Samba Folder

Yeah I think I went wrong as i just copied directly what you had put here so do i set database name to SambaPOS4 as that is the .sdf file that came up and then delete the extra bits as in the quote section

Yes you need to change the test.sdf to your database name.
Is it working now?

No Still coming up unable to find a version of runtime

@JohnS

This is what comes up

I need to see what’s in your Clear_CE_Data.bat file.

Second line should be

set DATABASENAME=SambaPOS4.sdf