Auto-add WiFi passcode when Adding Order

swordzorro · April 22, 2015, 6:42am

hi there,

I’m SambaPOS’s newbie, I’m using Samba for my Coffee Shop.
My scenario is: I have a list of WIFI pass-code (passcode.txt) contain a thousand pass-codes, Each pass-code is unique and can use 1 time only. And the pass-code will be provide to each customer who buy a coffee.
I would like to make the auto action like: if I select the order, Samba will auto pick 1 pass-code from my list (passcode.txt) and add into bill. If 1 bill have 2 products ( 2 cup of coffee) samba will add 2 pass-codes into bill (like Starbucks Coffee Shop)
I’m using SambaPOS v4 and Myhotspot for wifi hotspot.

Thanks and Regards

QMcKay · April 22, 2015, 5:07pm

V4 does not have capability to read from file. V5 does have this capability, though the feature would need some improvement to support this.

That said, this could probably be done via BAT and some SQL to load the codes into the database, probably as Program Settings, then use Actions and Rules to read those settings. Managing the load and then the expiration of codes would be an interesting challenge though.

emre · April 23, 2015, 12:04am

Well maintaining a password list properly across multiple terminals seems like an another challenge to me. Using SQL might be a better solution.

swordzorro · April 23, 2015, 1:29am

I think about import those codes to entity, and if the entity’s state was used, samba will not pick them up. But I don’t know how to start to make it process ^^

QMcKay · April 23, 2015, 1:57pm

How often do you generate the 1000-code file?

Using an Entity is an “ok” idea, but eventually you will have thousands of “Passcode Entities” in your system that you cannot get rid of. Once they are assigned to a Ticket, you cannot delete the Entity. Also, you can only assign 1 Entity per Type to a Ticket. So for your Tickets where you want 2 passcodes, this will not work.

Honestly, I would be tempted to create a separate table in the DB to manage this using SQL, but v4 has limited scripting support (i.e. I don’t think we can read a value from the DB, that is, there is no support for “return” values).

In v5, this could be done quite easily since we have many more helper objects to read/write files and read/update the DB through SQL.

swordzorro · April 24, 2015, 1:27am

hi QMcKay,

Appreciate for your serious looking at my problem.
1000 codes just can use for 2 days. I can generate unlimited codes in a file.
As you say, If the V5 will support the file reading, I can wait until the V5,
But I have no idea when V5 will be released ^^.
I’m looking to the reverse way, Samba will auto generate the code when we make the order, and then I’ll find some way to insert the code back to the hotspot database ( using MDB file - Access database).
Samba will print the codes into bill, and hotspot will have that code to authenticate. The problem is, when hotspot is running, it block the database LOL. Another dead end

Jesse · April 24, 2015, 1:29am

Samba can generate Random numbers with {RANDOM} {RANDOM:X} tags. First one is Date based, Second one is Random with specific # of characters example {RANDOM:5) produces a Random 5 character number. Example: 32JSL

Another Example {RANDOM:10} - JL2322XKJH

This might help you because you can store these numbers in program settings.

RickH · April 24, 2015, 1:33am

@Jesse you just got in before me i was going to say the exact same thing lol

swordzorro · April 24, 2015, 3:19am

thanks you guys,

that was what I figured out,
but How can I insert immediately that code into my Access Database whenever I make the product order?
Assumption I have a script: update.vbs “code” (where code is the value of the RANDOM:10)
the update.vbs will insert the code into the pass-code table of Access DB,
I don’t family with the action of Samba, I just know samba is exist 1 weeks ago T_T

Jesse · April 24, 2015, 3:32am

V4 can write to file, it just cant read from file. Look at the action Add Line to Text File.

swordzorro · April 24, 2015, 7:16am

I wrote the dirty power script to test the record insert to the myhotspot database. I can insert successfully when run wificode.ps1 aabbccddeeff
customer can use the aabbccddeeff to access internet immediately.
Now how can I replace the “variable” by the SambPos RANDOM tags.
Surely I’ll looking for some TUT in forum to do that.
Finally I can see the light of the end the tunnel,
the reverse way seem easier than the straight way ^^,
I greatly appreciate you guys support,

JTRTech · April 24, 2015, 9:15am

That sounds like a great setup, a hotel I worked at had at one point a ticket based wifi code system,
But they dropped it in the end as it become hastle some managing the codes.
We looked to see a way to do exactly what you seem to have done but their software was no where as versatile as samba so was impossible.

I know it’s a bit off toppic of samba but I am very interested and sure your setup could be ideal for many people, can I ask what hotspot setup you use? I imagine it is radius based or captive portal type firewall?

swordzorro · April 24, 2015, 9:53am

hi JTRTech,

it’s very simple application, and it’s free, you can use myhotspot to sale the prepaid code for internet using (like your hotel), it can authenticate via social account, or customer account, or prepaid code.
http://mypublicwifi.com/myhotspot/en/index.html
it isn’t open source but I can find some way to interact with the database. I stuck in generate more than 1 pass-code to print the bill. Samba don’t have any array or something, T_T

JTRTech · April 24, 2015, 9:55am

Will check it out, it’s it a local software or cloud based?

QMcKay · April 24, 2015, 1:39pm

There is an Action called Start Process that allows you to run an external program. This program could be an EXE, BAT, VBS or whatever. Using your example, try this:

… this is the result …

a look into the DB to see what is happening after 3 Coffees added …

Add this somewhere in your Printer Template:

[{SETTING:WiFiCode1}]
[{SETTING:WiFiCode2}]
[{SETTING:WiFiCode3}]
[{SETTING:WiFiCode4}]

Only codes that have a value will be printed.

This is simply proof of concept to give you an idea on how this could work, and may require some tweaking. I am interested to know the content of your file called wificode.ps1 ?

It could be simplified to not store multiple codes as program settings. I will post the simplified setup shortly - it will be easier to manage.

QMcKay · April 24, 2015, 2:11pm

Here is the simplified setup … it does not use an iterator; instead it uses a single Program Setting that is overwritten every time a Coffee is added to the Ticket, then it fires the Start Process Action to feed that value into your script/program.

This alternative would be to make another Template that only contains this:

[LAYOUT]
Code: {SETTING:WiFiCode}

You would add an Execute Print Job Action to the Rule to print the code by itself (no orders shown), and hand that to the Customer.

QMcKay · April 24, 2015, 2:23pm

So now you need to make a choice. If you don’t store multiple codes in multiple Program Settings, you will only be able to print a single Code on a Ticket which would be {SETTING:WiFiCode}. But that setup is much easier to manage than using multiple codes per Ticket.

The issue with multiple codes should be obvious: how many do we allow for? My example uses 4 values, but who’s to say that you won’t need 10 per Ticket?

This is why I would go for the simplified method - store 1 code and print that code whenever a Coffee is added to the Ticket.

emre · April 24, 2015, 2:52pm

On older versions I’ve added powershell script support (ps1) but removed later as it generates errors if powershell not already installed. If needed I can improve that.

swordzorro · April 24, 2015, 2:53pm

hi QMcKay,

you are so awesome the your 2nd solution, I already did it this morning, but can’t use it,
I will test your 1st solution after I reply this message,
The problem is the wifi-passcode can’t use concurrence, 1 pass-code can only use for 1 customer in 2 hours
if 5 customers order on 1 bill, I have to print out 5 pass-code for 5 cups of coffee.
some bill we have 10 cups.
below is my dirty-script: bcause I’m not a coder LOL

param([string]$code)

$adOpenStatic = 3
$adLockOptimistic = 3

$conn=New-Object -com “ADODB.Connection”
$rs = New-Object -com “ADODB.Recordset”
$conn.Open(‘Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Program Files (x86)\MyHotspot\data’)
$rs.Open(“SELECT * FROM CodeAccounts”,$conn,$adOpenStatic,$adLockOptimistic)

$rs.movelast()
$codeID=$rs.Fields.Item(“codeid”).value+1

$rs.Addnew()
$rs.Fields.Item(“CodeID”).value =$codeID
$rs.Fields.Item(“PricingType”).value =“0”
$rs.Fields.Item(“SessionPricingID”).value =“1”
$rs.Fields.Item(“CodeGrpID”).value =“5”
$rs.Fields.Item(“Active”).value =“True”
$rs.Fields.Item(“Sold”).value =“True”
$rs.Fields.Item(“Code”).value ="$code"
$rs.Fields.Item(“Credit”).value =“3”
$rs.Fields.Item(“CanExpireFromFirstLogin”).value =“TRUE”
$rs.Fields.Item(“CanExpireFromLastLogin”).value =“FALSE”
$rs.Fields.Item(“CanExpireFromDate”).value =“FALSE”
$rs.Fields.Item(“CanExpireFromCreatedDate”).value =“FALSE”
$rs.Fields.Item(“OneUsing”).value =“FALSE”
$rs.Fields.Item(“FirstLoginExpireDays”).value =“1”
$rs.Fields.Item(“CreatedDate”).value =“21-Apr-15”
$rs.Fields.Item(“ExpireDate”).value =“21-Apr-16”
$rs.Update()
$conn.Close
$rs.Close

QMcKay · April 24, 2015, 2:59pm

I just thought of another idea - use an Order Tag, like this:

Order Tag:

Tag Order Action:

Rule:

Printer Template:

[ORDERS]
<J00>{QUANTITY} {NAME}|{PRICE} {TOTAL AMOUNT}
<L00>{ORDER TAGS}

[ORDER TAGS]
<J00>  {ORDER TAG QUANTITY} {ORDER TAG NAME}| {ORDER TAG PRICE}

[ORDER TAGS:WiFiCode]
[<L00>{ORDER TAG NOTE}]

Sample Output: