I am planing to create a web interface for custom reports (stock, sales, inventory transactions) without using the GQL app that was currently added to the latest version of Samba. Also i am looking for a way to easily correct Items Cost and Inventory ( this will be possible to correct after the workperiod is closed, the script will change the inventory to 0 and after that will add a transaction document with the actual stock amount and price ). Most of the reports will be setup for Romania but i will explain also from where you can edit them.
GitHub Link:
What you will need for setting this up:
1. SambaPOS3,4,5
2. SQL Server 2012 or later version (possible to work with lower versions too)
3. Wamp Server
You can download it from here: WampServer, la plate-forme de développement Web sous Windows - Apache, MySQL, PHP
4. Microsoft Drivers for PHP for SQL Server
You can download it from here: https://www.microsoft.com/en-us/download/details.aspx?id=20098
I used version 3.2 which is compatible with PHP 5.4 and later till PHP 7
After installing you can include the extension by adding these lines to php.ini ( in case you are using PHP 5.6 as i am):
ension=php_pdo_sqlsrv_56_ts.dll
extension=php_sqlsrv_56_ts.dll
5. Microsoft® ODBC Driver 11 for SQL Server® ( this is for PHP 5.6 also )
You can download it from here: https://www.microsoft.com/en-us/download/details.aspx?id=36437
After setting up all the programs above you can test your PHP to MSSQL connection using this code in a php file thats on your www root folder:
<?php $serverName = "sqlserver.hostname.or.ip.address.com"; $connInfo = array("Database"=>"DatabaseName", "UID"=>"DBuser-default-is-sa", "PWD"=>"DBpassword"); $conn = sqlsrv_connect($serverName, $connInfo); if($conn){ echo "SAMBAPOS5 Database Connection established.<br />"; }else{ echo "Something in your setup is not setup well.<br />"; die( print_r(sqlsrv_errors(), true)); } ?>
=========================================================
Here you will find a complete sqlsrv list of commands
http://php.net/manual/ro/book.sqlsrv.php
As i keep continue working on this i will update this post with all other files.
Update1: You can find on GitHub the Stock Report. Sorry that i did not manage to style that yet, but i’m still working on the code.
You have now 3 files: config.php, raport1.php and stoc.php
You will need to add your db info in config.php
raport1.php handle the SQL Query
stoc.php display the sql query in a table
Update2: I uploaded the interface files to GitHub you can grab them from there.
You now have 4 reports:
Stock Report which is called Stoc in Romanian
Sales Report which is called Vanzari in Romanian
Transaction Docs which is called NIR in Romanian
Consumption per item which is called Consum in ROmanian
Transaction document is setup to split the SambaPOS Transaction name by “/” delimiter so for example in my case i needed the report to show me the document number, seller name and date when the sale was mad so i put in the transaction document name the following synthax: DOCNR312312 / SellerName / 13.20.2016 … this will split the name and arrange it nicely in my report
For the consumption the item cost is calculated from the transaction documents, it grabs all the transaction documents and make the avarage of the items, if there is no transaction for that particular item then it will use the default transaction cost from inventory item editor ( in my case i dont use that field so i used it now to have a fixed item cost )
And here is how it looks