SambaPOS5 Web Interface for Reports

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: http://www.wampserver.com/en/

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

5 Likes

Just curious but why avoiding using GQL api?

Well i also got some sambapos4 and there is no GQL support, and i am not so familiar with GQL i see that @QMcKay posted something in here GQL Modules - GraphQL Modules for Remote Client Browsers but it seems to be tested with version 5.1.61 ( i tried testing with 5.1.60 but the auth is not working ) and i do not have that as i think thats for devs only.

I’ve manage to test the GQL from the local browser using localhost:9000 and its working really nice, but i dont seem to find a way to inner join some sql tables. I think i can do that by creating a sql report and grab that using GQL but still have to read on how to do that as i dont know yet :slight_smile:

Using PHP and MSSQL queries will be much more simple for me and also for other users and this also can work with lower versions of Sambapos as not all users use the latest version.

LATEST UPDATES:

  1. Stock Report is finished
  2. Sales Report per Single Day is Finished
  3. Transaction Documents per Period with transaction document number, date and company provider

I used bootstrap for the datepicker and page arangement
Here is how it looks till now, i will upload the code to GitHub next week

I need to make the css for the print page so that you just hit ctrl + P and print the documents and also fix the site responsiveness so that it will work on any type of device. I am also looking into making a item cost correction page so you can easily correct the items cost so you will not need to empty stock and after that create another transaction document, etc. The script will do all for you. that will be in the near future.

5 Likes

The Last report that i need to create for this to be done will be: Single Sold Items Report which will include in a single page all sold items and each item will have its cost report with recipe items, quantity used, item unit, item cost and total cost.
Also i am planing to include in this a PHP POS based on same system structure MSSQL+SQL SRV Module+PHP this way you dont need to open Messaging server, you will only use SambaPOS and MSSQL Server. The Wamp server can be hosted on another PC.

My currently Setup is the following:
1 PC for SambaPOS and MSSQL Server + FREE SQL Backup Tool (http://sqlbackupandftp.com/) that will backup the DB to an FTP every day in case the HDD will fail (for this PC i also am using an UPS with management that is stabilizing the current intensity and don’t let the PC shutting down on power failure it will send shutdown command after 5 minutes of power failure)
1 PC for WAMP Server and SQLSRV module so i don’t overload the 1st PC
2 Tablets that are connected to the 1st PC - I am trying to lose the remote connection to the 1st PC which have the DB and use browser based POS instead of connecting remotely via tablets to the 1st pc this way the 1st PC will work like a charm.

I will also develop an Android APP for the Web BASED POS so in case the 2nd PC goes down the 3rd Backup PC will come in ( the 3rd PC is my personal home PC which is always opened - this will be only used for emergency situation )

I also am installing sambapos to a new location and all this setup will cost me around 1000$ ( PC + TouchScreen Monitor + UPS + Gigabit router + Router Range Extender + 2 Thermal Printer (1 for kitchen and 1 for customers) + 1 Tablet ) — I will keep this post updated with my progress :stuck_out_tongue:

3 Likes

Hey all i’ve uploaded the code to GitHub, if you got any question please let me know and sorry for the messy code that i provide, i will keep working on that when i will be a little bit free.

2 Likes

Hi,

I 'm using PHP 5.5 and i added tp my php.ini under apache/conf
extension=php_pdo_sqlsrv_55_ts.dll
extension=php_sqlsrv_55_ts.dll

But i have this error :

Fatal error: Call to undefined function sqlsrv_connect()

Note i’m using Windows7 64Bit Macine and Wampserver 2.5

That probably relates with one of these steps.

3 Likes

New UPDATE

What is new:

  1. Added htacces and htpasswd file for adding a little bit of security to the php script. Later i will implement autenthication using the SambaPOS user and pass but for now this works just fine. The user and password used in the htpasswd file that i provided on github are (User: a || Pass: mama)

  2. Added print version of the report (print page version so that when printing in chrome or other browser the print will be nicely arranged so you dont need any other editing text program)

  3. Added bootstrap menu and a little bit of responsiveness so it will work nicely on mobile too.

  4. This is a preview of how everything looks now

Few things you should know before configuring my reports:
I setup a VAT for each product and a default cost for inventory item if there were no inventory transaction added.


This not how samba should be configured but i needed it this way so i can bypass recipe calculation if there is no transaction and also this bypasses the stock modification from end of day period ( what i mean by this is that you will not need to add transaction documents for the avg price to be shown properly ). This is the way i do it so do not judge me :slight_smile:
Also for everything to work smooth all items should have a recipe and the recipe name should be the same with the product name
Also for the transaction item report please use the name of the transaction as following:
TRANSACTION_Number / Company_that_sold_the_item / Date
Eg: 321312312 / MY NEW COMPANY / 20.31.23
Eg2: 3213184 / COMPANY TEST 2 / 20-31-23

For any other question do not hesitate to write, possible to respond a little bit harder but i will eventually

Here are the reports explanation in english:
Vanzari Periodice = Sales Report by period ( here i added a custom code for product name = CUSTOMCODE+ProductId )
Vanzari Zilnice = Sales report by day ( just for controlling the staff - you will got here only ammount of sold items)
NIR = Transaction Items reports
Consum = Sales report per product name with product recipe and sold quantities of inventory items and prices
Stoc = Stock report

3 Likes

@Marian_Buru i try on my pc but its not working




and menu also not working

please help me.
Thanks

as it seams from url that it need to add /vanzari.php to view the page

Please give me your skype so i can help you if you still need help

2 Likes

Started working on a complete web interface with all month report, best selling product, most profitable products, employee of the month with most sales and a lot more coming, will update this when i have the beta version.

4 Likes

where do i install the php drivers files from the folder?

Please use the default instalation folder for php and for sql driver and just edit the php.ini with the extension lines. That should be all

If you can not handle please give me your skype id so i can help you

this is my config php
i insert the php lines in php.ini
but still no luck, im using wamp 3.0.6 x64

<?php
/* Server Configuration */
$serverHost = "LOCALHOST\SAMBAPOS"; 
$databaseName = "SambaPOS5";
$userName = "sa";
$password = "samba";
/* End Server Configuration */

/* Business Configuration Strings */
$BusinessName = '';
/* End Business Configuration Strings */


$connInfo = array("Database"=>$databaseName, "UID"=>$userName, "PWD"=>$password);
$conn = sqlsrv_connect($serverHost, $connInfo);
if($conn){
 echo "<div class='connected'>Connected.</div>";
}else{
 echo "<div class='unconnected'>Conexiunea nu poate fi deschisa</div>";
 die( print_r(sqlsrv_errors(), true));
}
?>

What error do you get when you enter on the reports link ?

More tutorial on the subject can be found on the visual

2 Likes