Point of Sale - revamped with SQL storage

[dmob]

d2addict4's Point of Sale script was exactly what I needed in our li'l printing and internet cafe, so I added some features that we needed, using UDF's and functions from other experienced coders. I have credited most, but have forgotten some whose code I used. I do apologise to those scripters.

I am not sure if this is the right forum to post, but I thought some might learn a thing or two , especially the SQLite stuff (took me a while to get it to work).

Added:

- SQLite storage, (using ptrex's wonderful UDF) for Products / services sold, minor shop expenses, small cash withdrawals and Product Dept / groups

- discounts for bulk quantities for 4 customer types; auto lookup and calculate

- all product items/services available from drop list - no manual entries necessary

- Receipt printing to a generic text printer using martin's excellent PrintMGv2 UDF

- On-screen Reports using various filters (no print capability added / required yet)

- Number buttons can be used for:

1. Till mode - to enter quantities instead of entering same item multiple times;

2. Calculator mode - a simple integer calculator (no support for decimals)

- Nothing is written to the registry

I didn't have time / was too lazy to :

1. fix the flicker when loading the lists; I found out how to avoid it after I had written the code. The script works for our needs right now so that's how it is until I have more time or someone improves it, whichever comes first.

2. check if I needed all the includes

3. add extensive error checking (I did say our shop is small - a controlled environment)

I have included a sample PosConfig.txt file with the products we use and a second script Prod2SQL.au3 to create the tables and import the products from the txt file to SQL.

This was a rushed job, so the code needs some cleaning up and some sections could be improved. I am eagerly waiting on the experienced members to advise on many issues, e.g. global/local use of variables, esp. within functions, using hotkeys to activate buttons, etc and look forward to suggestions/criticism and direction.

I am not a programmer by profession or training, although I have written a few applications using a certain excellent 4G database language. I just enjoy the challenge with coding, and automating the work we do. I would like to improve my skills in AutoIt as I see great potential with it, on its own and when combined with our applications.

This is "my" second useful script. I was able to learn AutoIt very quickly from the examples forum, the help forum and bits of code from other scripters. I must say the excellent community and help (and patience) I saw in the forums was inspiring.

EDIT: Following lessons I learnt from JasonB's improvements (see post #9), I have added keyboard support for the control buttons and cleaned up the code a little. I have a problem with the entry of numbers via kbd, key is repeated.

2. Added a qty column on the listview - highlight item then click 'x' and the quantity required then click 'Enter' button to accept qty.

3. Close the transaction by clicking 'Tender Cash' button. Enter the amount, the change will be calculated or simply click Ok and the exact sale amount is assumed. The transaction will then be saved to database.

4. Moved some of the code into functions.

_ArrayX UDF is no longer required - removed from post.

I have also attached JasonB's LoadConfig, which does the same thing as Prod2SQL, only more user-friendly.

PosConfig.txt

Load_Database.au3

CashUp.au3

Edited June 27, 2008 by dalisuit

[gseller]

First rattle out of the bag, Were is _ArrayX.au3 ? What version did you work in?

Thank You

[dmob]

First rattle out of the bag, Were is _ArrayX.au3 ? What version did you work in?

Thank You

Sorry, AutoIt 3.2.12.0

I have attached Jos van der Zande's _ArrayX UDF.

[gseller]

SWEET! Nice additions!! Next thing it needs is someone to tie the number keys and calculation keys to the catagories to where when someone buys multiple items the register can be used.. Love The SQL tho...

[dmob]

SWEET! Nice additions!! Next thing it needs is someone to tie the number keys and calculation keys to the catagories to where when someone buys multiple items the register can be used.. Love The SQL tho...

But it does, if I understand you right. If you click on product item, then click the 'x' button and follow with the quantity and enter button, the qty minus one is added to the list. I did it this way to simplify reading the list when closing the transaction, prior to writing to SQL.

The idea was to allow to use touch-sensitive screen (which we can't afford yet; stuff is expensive in SA).

Thank you for your comments.

[dmob]

Sorry I have to leave, its 8:30pm here (SA) and I must go home else I will be spending night on couch, lol.

[gseller]

Sorry, I see it now.. Very Nice...

[dmob]

Thank you, gseller.

[TehWhale]

I love this program! Heres my addition:

http://www.sendspace.com/file/r62stj

Its a zip file containing the modified files! Enjoy!

THANK YOU FOR THIS PROGRAM THANK YOU THANK YOU THANK YOU!! THIS PROGRAM IS AWESOME!! THANK YOU!!!

Edited June 15, 2008 by JasonB

[dmob]

Thank you, JasonB.

I luv the changes you made! I ddnt know how to tie in the Keyboard shortcuts / hotkeys and you just taught me how.

I was thinking of adding tabs for the buttons to accommodate the extra items, instead of using the Manual button, but I don't have the time yet.

Big up to you.

[dmob]

Sorry folks, we actually don't need the _ArrayX UDF, I only needed it for the _ArraySearch function, and I forgot that I used the function built-in to AutoIt.

The _ArrayX UDF does contain some useful functions though....

[emoyasha]

ok well so far i get these errors,

>"C:\Program Files\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\Documents and Settings\Admin\My Documents\My Pictures\CashUp.au3"

C:\Documents and Settings\Admin\My Documents\My Pictures\CashUp.au3 (21) : ==> Error opening the file.:

#include <printMGv2.au3>

>Exit code: 1 Time: 0.553

>"C:\Program Files\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\Documents and Settings\Admin\My Documents\My Pictures\Prod2SQL.au3"

Rec# 1: 1|Photocopy|0.50|Print

Rec# 2: 2|Fax Loc|5.00|Tel

Rec# 3: 3|Fax Nat|6.50|Tel

Rec# 4: 4|Fax 086|6.50|Tel

Rec# 5: 5|Scan|4.00|PC Use

Rec# 6: 6|CV Make|7.00|Type

Rec# 7: 7|CV Edit|3.00|Type

Rec# 8: 8|CV Cover|4.00|Type

Rec# 9: 9|Customer Self-Type|3.00|PC Use

Rec# 10: 10|General PC Use 30min|6.00|PC Use

Rec# 11: 11|Letter|6.00|Type

Rec# 12: 12|Assigment|8.00|Type

Rec# 13: 13|Program|25.00|Type

Rec# 14: 14|LetterHead|8.00|Type

Rec# 15: 15| |0.00|

Rec# 16: 16|Print Black Text|1.50|Print

Rec# 17: 17|Print Blk Txt Grey Picture|2.00|Print

Rec# 18: 18|Print Blk Txt Colour Picture|4.00|Print

Rec# 19: 19|Glossy Paper|2.00|Stationery

Rec# 20: 20|Print Photo A4|6.00|Print

Rec# 21: 21|Print Colour Text|2.00|Print

Rec# 22: 22|Print Col Txt Grey Picture|2.50|Print

Rec# 23: 23|Print Col Txt Colour Picture|4.00|Print

Rec# 24: 24|Print Photo A5|6.00|Print

Rec# 25: 25|Print Photo A6|10.00|Print

Rec# 26: 26|Email 15min|6.00|INet

Rec# 27: 27|Internet 30min|12.00|INet

Rec# 28: 28|Upload 1MB|2.00|INet

Rec# 29: 29|Download 1MB|1.00|INet

Rec# 30: 30|Memory Stick 1GB|100.00|Stationery

Rec# 31: 31|Envelope A4|2.50|Stationery

Rec# 32: 32|Plastic Jacket|0.50|Stationery

Rec# 33: 33|Stiffy Disk|5.00|Stationery

Rec# 34: 34|CD Empty|8.00|Stationery

Rec# 35: 35|DVD Empty|10.00|Stationery

Rec# 36: 36|CD Copy|5.00|PC Use

Rec# 37: 37|Envelope A5|1.50|Stationery

Rec# 38: 38|Memory Stick 1GB|100.00|Stationery

Rec# 39: 101|Computer Repair|150.00|Repair

Rec# 40: 102|Install Software|150|Repair

Rec# 41: 201|Virus Clean - Standard|150|Virus

Rec# 42: 202|Virus Clean - Reformat|350|Virus

Rec# 43: 205|Kaspersky AntiVirus CD|220|Virus

Rec# 44: 206|Kaspersky Install & Activate|250|Virus

Rec# 45: 501|Bread|7.50|House

Rec# 46: 501|Spreads|0.00|House

Rec# 47: 505|Food|0.00|House

Rec# 48: 510|Airtime|389.00|INet

Rec# 49: 510|Petrol|100|Travel

Rec# 50: 550|Telkom|1|Tel

Rec# 51: 560|Electricity|1|Power

Rec# 52: 901|Student Discount|0.00|Discount

Rec# 53: 902|Client Discount|0.00|Discount

Rec# 54: 903|Frequent Customer Discount|0.00|Discount

Rec# 55: 904|Public Discount|0.00|Discount

Rec# 56:

C:\Documents and Settings\Admin\My Documents\My Pictures\Prod2SQL.au3 (56) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.:

_SQLite_Exec(-1 , "INSERT INTO Products (ItemCode, Description, Price, Dept) " & "VALUES ('" & $aRow[1] & "', '"& $aRow[2] & "', '" & $aRow[3] & "', '" & $aRow[4] & "' );")

_SQLite_Exec(-1 , "INSERT INTO Products (ItemCode, Description, Price, Dept) " & "VALUES ('" & $aRow[1] & "', '"& ^ ERROR

>Exit code: 1 Time: 6.775

[emoyasha]

ok those errors fixed, it wont keep a log of transactions and the reports button errors out.

[TehWhale]

In my version, you must have the PoSConfig.txt, and run the: Load Database.au3, then run the CashUp.au3.

Also, you can just search that include name and you will find it.

In the OP's version, you must run the Prod2SQL.au3, and then run the CashUp.au3.

@dalisuit:

I didn't know how exactly you wanted the buttons (the discount and stuff) to be in the actual point of sale, I might be able to work on it more if you give some things you need. Be very descriptive! Thanks though.

EDIT: Just saw emoyashas post:

The Transactions as of right now, seem to do nothing, the Reports button does NOT error out for me, and thats where the 'transactions' sort of, are stored for right now.

Edited June 15, 2008 by JasonB

[emoyasha]

ok i removed the _ on stringsplit

but now i get this error

>"C:\Program Files\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\Documents and Settings\Admin\My Documents\My Pictures\CashUp.au3"

C:\Program Files\AutoIt3\Include\Array.au3 (712) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.:

Local $pivot = $array[int(($left + $right) / 2) ][$sortIdx]

Local $pivot = ^ ERROR

>Exit code: 1 Time: 5.330

[TehWhale]

What exactly are you doing? This program works fine, there's no need to remove anything.

AutoIt: Beta .12

OS: Windows XP Home SP2

[emoyasha]

ok _stringsplit() was used, however my version (my includes) define it as stringsplit() so that was an error but now i fixed it and i have the error i posted.

[TehWhale]

I have no idea why this is happening. I just tried to get your error, and it won't give me one.

[emoyasha]

well i showed u the exact error. do u want the source i have?

Edited June 16, 2008 by emoyasha

[TehWhale]

THat would help.