[inv 1.5] Inventory Purchase and Control via PHP

[QMcKay]

I wanted a friendlier interface into making Inventory Purchase Documents and Transactions, and making Payouts to my Suppliers, so I spent some time putting together a PHP-based system.

The PHP code for SambaPOS v4.1.73 or later : (Additional Units support)
inv1.5.zip (99.9 KB)

The PHP code for SambaPOS v4.1.70 or later :
inv1.4.zip (83.2 KB)

The PHP code for SambaPOS v4.1.69 or earlier:
inv1.3.zip (83.1 KB)

Tested with:
PHP 5.6 NTS (non-thread safe) x86 on IIS 7.0 on Windows 7 x64
MS SQLExpress 2014 x64, 2012 x64 and 2008R2 x64
SambaPOS 4.1.73 or later (inv1.5)
SambaPOS 4.1.70 to 4.1.72 (inv1.4)
SambaPOS 4.1.58 to 4.1.69 (inv1.3)

---

I’m releasing this code to illustrate what can be done using PHP when attached to the SambaPOS Database (SQLExpress). I have done my best to make it as generic as possible for those who want to use it, but inevitably, there are some specific functions within it that apply to the way I have SambaPOS configured, and the way I think Inventory should operate.

---

Set Up:

You should also read the file !readme.txt for more information.

Ensure you modify the configuration file to set your DB username and password, located here:

/inv/html/zconfigs/configdb.php
/inv/html/zconfigs/configbuttons.php

You can also look at config.php which contains simple things like Site Name.

Requirements:

• PHP installation on some type of Webserver… There are many methods to setting this up - here is a Tutorial that I recommend: PHP setup on IIS
• the PHP uses an extension called sqlsrv to perform operations on MSSQL Databases. This extension needs to be downloaded and used on later PHP versions since built-in support for MS databases was dropped in PHP as of version 5.3. There is an official MS version of this extension that has support for PHP 5.3.6 thru 5.4, but I use an unofficial version which supports PHP 5.3 thru 5.6. There is a link in the required folder where you can download the unofficial version.
• other support files mentioned in !readme.txt, namely SQL Native Client and VC++ Redistributables are required for all of this to work.
• SQL Express 2008, 2012, or 2014
• SambaPOS 4.1.70 or greater
• Have a look at the 2nd post in this thread to see how I have SambaPOS configured, since a good deal of the PHP code is based on it.

DISCLAIMER:
Use it at your own risk on a non-production system until you understand how it works. It will insert, update, delete records in the database.

CODE:
The code is fairly clean, but poorly documented, and there may be portions of it that are unused or deprecated… I’ll work on cleaning it up over time. It’s pretty “old school” and linear. I make no claims of being a good coder, and I admit I’m exposing myself and my bad habits in this regard - laugh if you want, complain you must, offer suggestions if you please - it’s a work-in-progress, even though I am actively using it in Production. Play around with it yourself and make it work for you, scoff at it, change it, improve it, share it, delete it, light it on fire…

SUPPORT: (what?)
I probably won’t provide much support for this (especially how to get PHP working), but feel free to ask questions anyway.

NOTES:

My SambaPOS configuration uses a Warehouse for every Supplier that I have. Each Supplier Entity has it’s own Warehouse mapped to it, and for simplicity, the Supplier Entity Name and its associated Warehouse have the same name. These are all categorized as Warehouse Type Supplier Warehouses.

I also have 2 Warehouses of Type Local Warehouses. One is On-site where I receive my deliveries called SHOP. The other is off-site where I store extra stock called BODEGA. These 2 Warehouses are the foundation of the way this system operates.

Purchases are made from the PURCHASE screen, and Inventory is inserted into the SHOP Warehouse. An Account Transaction is also made when Payment is made. I then transfer extra stock from SHOP to the BODEGA using the STOCK screen. Later, requests are made from the Shop using the REQUEST screen to have Inventory brought from the BODEGA. When I Fulfill a Request, inventory is transferred from BODEGA to SHOP.

---

SCREENSHOTS

Buttons in SambaPOS to open the PHP in a HTML Widget (you need to make these yourself):

The REQUEST screen:

The PURCHASE (Payout) screen:

The STOCK screen: (for moving and updating stock in Warehouses)

The II screen: (for Adding and Updating Inventory Items)

[QMcKay]

This is how I have SambaPOS set up, so some of the code is based on (naming) conventions found here:

Warehouses:

Inventory Transaction Types:

Inventory Document Types:

Supplier Entities: (mapped to an associated Warehouse and Account)

Account Transaction Types:

Account Document Types: (these appear as buttons on the PURCHASE screen)

Department Warehouse:

[QMcKay]

Authentication was added in v1.2. The authentication is mapped to Users and Roles in SambaPOS, and can be used to control the Buttons that are shown at the top of the screen in the PHP system, via this config file:

/inv/html/zconfigs/configbuttons.php

---

There is an optional Auto-Login feature which requires the following Action and Rules to store a Program Setting value for the current logged-in User. If you don’t need/want the Auto-login feature, there is no need to follow the setup shown below.

Action - Update Program Setting InvUser:

Rules - fire the Action to update the Program Setting for InvUser:

Custom Entity Screen HTML Viewer Widget showing the URL setting:

Set the directives in /inv/html/zconfigs/configdb.php for Auto-login:

$autologinSambaUser=true;
$autologinDomain='localhost'; // should match the HTML Viewer Widget URL
$autologinDBsetting='InvUser'; // should match Program Setting name

[emre]

I’ve reviewed it and seems readme file contains all required information. I’ll try setting it up in few days and let you know if I notice something.

[QMcKay]

@emre, where in the DB is the current logged-in user stored?

EDIT: found it here, but the value is NULL

[ProgramSettingValues].[UserName]

I want to put login/authorization on this system (which I have working already), but it would be nice if I could automate the login based on the current SambaPOS user.

If it’s not in the DB, can I pass it in the URL of the HTML Widget like this?

http://localhost/?currentuser={:CURRENTUSER}

EDIT: hmm… can’t seem to get that to work… is there other syntax I can use?
@Jesse, is there something that works for TimeTrex?

[Jesse]

Yes, I am on mobile atm though so I cannot pull it up. I have timetrex authenticate using !#username= ill look it up when I get home.

[emre]

Set a widget name from Properties > Name and set URL with Set Widget Value action. You can use such tags inside rule.

[QMcKay]

Thanks for that @emre, I might try that. In the interim, I got around it by storing [:Username] in a Program Setting called InvUser after login, and clearing the setting on logout.

It brings to mind another question though: is {:CURRENTUSER} Terminal-specific? What about [:Username] ?

[emre]

If you store it in database all terminals will see same last logged in user. Maybe you can configure a setting name that includes a terminal suffix ?

{:Username} is something else that relates with my latest staff notification

[QMcKay]

Ok, I’ll bite. @emre, how do I access the value for Terminal?

[QMcKay]

EDIT: deleted post and moved to post 3

[emre]

@QMcKay sorry I think I meant something different. I meant you can create a rule that mapped to the terminal and works when a user logged in. That terminal specific rule will save {:CURRENTUSER} value to a terminal specific setting so you can query which user logged to a terminal through database.

However you’ll need to know which terminal accesses to server right? Maybe using Set Widget Value is easier to implement.

[QMcKay]

Correct, I need to know which Terminal, and I don’t know how to access that value. I haven’t seen any Terminal-specific parameters that can be used in Rules…

The way I have it working now is that only the Server Terminal will perform Auto-login, based on the HTML Widget having an URL setting of http://localhost. The PHP checks the URL and will only auto-login if the host is localhost, because we should assume that the code is running on the Server Terminal anyway.

It’s not that we can’t access the PHP remotely, but the remote-URL won’t match localhost, so the Auto-login will fail. I’m fine with that; I like it that way.

P.S. localhost could be set to 127.0.0.1 or 192.168.1.100 or whatever (it’s parameterized in the code), so long as it’s the IP of the Server with the DB and the PHP, and the Widget URL matches the directive in the PHP code ($autologinDomain).

[QMcKay]

Ok, so after all of that discussion on setting the URL value, I’m of the opinion that it’s not going to work very well in my PHP implementation without some rethinking on the Authorization and Auto-Login. I already store the User as a Session variable so we can move within the HTML from REQUEST to PURCHASE without having to GET the URL currentuser. But it complicates things a bit. I prefer it the way I have it now, by storing the Username in the DB upon Login, and letting the PHP look up the user. This saves having to Set Widget Value at all.

However, there is still the point of the Terminal. If I could access the Terminal value upon Login, I could store it along with the User and it would make things much simpler.

I find it hard to believe there is no setting for {:TERMINAL} or [:Terminal]. @emre, is there a reason for this missing setting?

[emre]

On next version you’ll be able to use {:CURRENTUSER} and {:CURRENTTERMINAL} tags in your widget’s URL setting.

@QMcKay

@emre, is there a reason for this missing setting?

Seems nobody needed it until now

[QMcKay]

Newest addition to the system: now you can Add & Update Inventory Items using this interface. Populated pick-lists for GroupCode, Base Unit, Transaction Unit so you can select from existing choices. Available in v1.3.

---

The II screen: (for Adding and Updating Inventory Items)

[QMcKay]

(2014-12-01) New version 1.5 updated to work with SambaPOS v4.1.73 or later, with support for Additional Units. Config Files have been relocated to their own folder:

/inv/html/zconfigs/configdb.php
/inv/html/zconfigs/configbuttons.php

The PHP code for SambaPOS v4.1.73 or later :
inv1.5.zip (99.9 KB)

(2014-10-20) New version 1.4 updated to work with SambaPOS v4.1.70 thru 4.1.72. Earlier versions (4.1.69 or less) will not work with this release. For earlier versions of SambaPOS, you need to use inv1.3.zip

The PHP code for SambaPOS v4.1.70 or later :
inv1.4.zip (83.2 KB)

The PHP code for SambaPOS v4.1.69 or earlier:
inv1.3.zip (83.1 KB)

[Cazz]

@QMcKay

/inv/html/zconfigs/configdb.php
/inv/html/zconfigs/configbuttons.php

how can I import these files and DB for PhP My Admin on Xampp?

[Jesse]

You should know this will not let you update previous WP purchases either. What he built was a front end for purchases but it is still bound by the same rules.

@Cazz

how can I import these files and DB for PhP My Admin on Xampp?

phpMyAdmin is a tool for managing MySQL using PHP. This does not use MySQL .