Update menu prices from website

Update item prices from remote website.

I have made this idea as I was not happy with the current method in which managers have to change prices.
I also created this as I realised it could be done.

The setup needs 3 componetnts setup

  • [MySQL db table](https://forum.sambapos.com/t/update-menu-prices-from-website/10804/2?u=the133448)
  • [Webserver code](https://forum.sambapos.com/t/update-menu-prices-from-website/10804/3?u=the133448)
  • [Samba Setup](https://forum.sambapos.com/t/update-menu-prices-from-website/10804/4?u=the133448)

The website I created looks like this and you can edit the prices from anywhere you have internet. Its also responsive to allow you to edit prices from mobile.

GIF animation showing workflow You can see in the later part, how I struggle whilst I wait for Samba to update the pricelist from the database, which I asked about in this topic How to refresh database I also wanted to show some of the validation that I have done.
http://i.imgur.com/tzCY8BW.gif

MySQL DB setup:

I am storing a mySQL database on my webserver to store prices/names.
This is the datbase layout I went with

Table Name: Prices
╔═══════╦════════════╦
β•‘ field β•‘  type      β•‘
╠═══════╬════════════╬
β•‘ Name  β•‘ VarChar(90)β•‘
β•‘ Price β•‘ double     β•‘
β•šβ•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•β•β•β•β•β•©
Primary Key is set on Name (so no duplicates)

I also use the variables to connect to the database in the php code below.

Web Server Setup:

I needed 2 php file. The first controls the UI and the second handle the requests from Samba for the prices.

The file which handle Samba to access the DB is below, all it does is JSON the mysql db and send it back as a return statement.
I didn’t worry about authentication, as I really do not care if someone can access the price list.

[hide=

getprices.php

]

<?php
$input = file_get_contents('php://input');


$con = mysqli_connect('mysql9.000webhost.com','a1343640_daniel','test123','a1343640_test');

if (!$con) {
    die('Could not connect: ' . mysqli_error($con));
}
if($input ==0){
$sql = "SELECT * from Prices";
$result = mysqli_query($con,$sql);

$rows = array();
while($r = mysqli_fetch_assoc($result)) {
    $rows[] = $r;
}
$json = json_encode($rows);
echo $json;
} else if($input==1) {
    $sql = "TRUNCATE TABLE  `Prices`";
    mysqli_query($con, $sql);
}
?>

[/hide]

The other php file handled the web interface, I made the decision to make it responsive by using bootstrap, as I see myself changing the prices from my phone if I need to.
Note I havent put any protection agianst sql injection. This file is hosted in an admin panel of my website which also includes custom reports.
The design also takes into account error and success statments.

[hide=

welcome.php

]

<?php $dbhost = 'mysql9.000webhost.com';
$dbuser = 'a1343640_daniel';
$dbpass = 'test123';
$dbname = 'a1343640_test';
$tablename = 'Prices';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
$price = $_POST["price"];
$name = $_POST["item"];
$suc = false;
if (!is_null($price)) {
    if (is_numeric($price)) {

        if (!$conn) {
            die('Could not connect: ' . mysql_error());
        }
        $sql = "REPLACE INTO `".$dbname."`.`".$tablename."` VALUES ('".$name."', '".$price."');";

        mysql_select_db('test_db');

        $retval = mysql_query($sql, $conn);

        if (!$retval) {
            die('Could not enter data: ' . mysql_error());
        }
        mysql_close($conn);
        $succ = true;
    } else {
        $succ = false;

    }
}

?>



<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <!-- The above 3 meta tags *must* come first in the head; any other head content must come *after* these tags -->
    <meta name="description" content="">
    <meta name="author" content="">
    <link rel="icon" href="../../favicon.ico">

    <title>Samba Price Changer</title>

    <!-- Bootstrap core CSS -->
    <!-- Latest compiled and minified CSS -->
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css"
          integrity="sha384-1q8mTJOASx8j1Au+a5WDVnPi2lkFfwwEAa8hDDdjZlpLegxhjVME1fgjWPGmkzs7" crossorigin="anonymous">

    <!-- Optional theme -->
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap-theme.min.css"
          integrity="sha384-fLW2N01lMqjakBkx3l/M9EahuwpSfeNvV63J5ezn3uZzapT0u7EYsXMjQV+0En5r" crossorigin="anonymous">

    <!-- Latest compiled and minified JavaScript -->
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"
            integrity="sha384-0mSbJDEHialfmuBBQP6A4Qrprq5OVfW37PRR3j5ELqxss1yVqOtnepnHVP9aJ7xS"
            crossorigin="anonymous"></script>

    <!-- Custom styles for this template -->
    <link href="jumbotron-narrow.css" rel="stylesheet">

    <!-- Just for debugging purposes. Don't actually copy these 2 lines! -->
    <!--[if lt IE 9]>
    <script src="../../assets/js/ie8-responsive-file-warning.js"></script><![endif]-->
    <script src="../../assets/js/ie-emulation-modes-warning.js"></script>

    <!-- HTML5 shim and Respond.js for IE8 support of HTML5 elements and media queries -->
    <!--[if lt IE 9]>
    <script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
    <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
    <![endif]-->
</head>

<body>
<nav class="navbar navbar-inverse">
    <div class="container-fluid">
        <div class="navbar-header">
            <a class="navbar-brand" href="#">Coffee Mange</a>
        </div>
        <ul class="nav navbar-nav">

            <li><a href="#">Home</a></li>
            <li><a href="#">Reports</a></li>
            <li class="active"><a href="#">Prices</a></li>
            <li><a href="#">Admin</a></li>
        </ul>
    </div>
</nav>
<div class="container">

    <div class="jumbotron">
        <h1>Change Samba Prices</h1>
        <p class="lead">Change POS Prices from here.</p>

    </div>

    <div class="dropdown">


        <form role="form" action="welcome.php" method="post">

            <div class="form-group">
                <label for="sel1">Select Item:</label>
                <select class="form-control" id="item" name="item">
                    <option>Choclate Milkshake</option>
                    <option selected>Large Flat White</option>
                    <option selected>Small Hot Chocolate</option>

                </select>
            </div>
            <div class="form-group">
                <label for="price">Price:</label>
                <input type="text" class="form-control" id="price" name="price">
            </div>


            <button type="submit" class="btn btn-default center-block">Change</button>
        </form>
        <?php
        if (!is_null($price)) {
            if (!$succ) {
                echo '<div class="alert alert-warning pagination-centered" role="alert">
            <strong>Error!</strong> Couldnt update ' . $name . ' to: $' . $price;
            } else {
                echo '<div class="alert alert-success pagination-centered" role="alert">
            <strong>Success!</strong> Changed ' . $name . ' The new price is: $' . $price;
            }
        }
        ?>



    </div>


    <footer class="footer">
        <p>by the133448.</p>
    </footer>

</div> <!-- /container -->


<!-- IE10 viewport hack for Surface/desktop Windows 8 bug -->
</body>
</html>

[/hide]

Note that I only have 3 items in here, to add more create a new line and put in the name


For it to work it has to be the exact same name as in Samba


You replace the options here with your product name in Samba (its in welcome.php)

                     <option>Choclate Milkshake</option>
                       <option>Large Flat White</option>
                    <option selected>Small Hot Chocolate</option>

You could create some php to automatically create the product options, but I didn’t feel the need to add this.

Samba Componenets


Script name: updatePrices
Script handler: up

[hide=updatePrices (script)]

function updatePrices()
{
    var url = "yourWebsite.com/getprices.php";
    var result = web.PostData(url,"0");
    var myResult = result.split("<!--");

    result = myResult[0];

    var noJson = JSON.parse(result);

    var items = "";

    for (i = 0; i < noJson.length; i++) {
        var name = noJson[i].Name;
        var price = noJson[i].Price;
        var qry = "";
        if (price != -1){
            qry += "UPDATE p SET [Price] = ";
            qry += price;
            qry += " FROM   [MenuItems] i INNER JOIN [MenuItemPrices] p ON i.id = p.id WHERE  i.Name = '";
            qry += name;
            qry += "'";

            sql.ExecSql(qry);

            items+=name;
            items+=", ";
        }
    }
    if (items.length == 0){
        items = "No price updates found";
    }
    else {
        web.PostData(url,"1");
    }

    return items;
}
return count;
}

[/hide]
The script simply connects to the web server and listens for the response. I utilised a helper which does require a data string which I track in the getPrices.php so that the script can remove everything from the table after it has updated the prices in the Samba DB.
Note also I split the string at <!-- my webhost injects analytics code in every web page, and this just splits the string before the analytics code comes in.
It also enters the data from the website into the table, the nature of the JSOSN is that if the data is incorrect then it wont put bad data into the database.

To manually change the prices I used a AMC mapped to an admin role on the navigation menu.

I then used a rule to check fo the this AMC to be executed (Ive changed it so, it would show if any prices are updated, it will list the updated prices)

Show warning is an ask question action (Sorry about the spelling)

I also included a Trigger, to auto check for price updates twice an hour

For that I added a new rule checking when the trigger is run, and instead of having a popup message, I just put the messge in the subtitle.

The message is: Prices updated: {CALL:up.updatePrices()}

2 Likes

Update 16/05/2016
Ive changed around the way in which the data is stored, so that once Samba retrieves it, it clears it from the mySQL db. This allows a custom message to be generated informing the user what products prices have been changed.


Reserved for updates.


1 Like

Whoa!

Imagine all aspects being done via a website! Add new products? Add users?

Awesome work, I’m not great at php but I think ill give it a bash for the sake of giving it a bash!

Matt

Thanks, i spent way to long working on this hahah. If you know pretyt basic web server skills, you should figure out what Ive done and how to implement this, let me know if you come across any problems.

I have a feeling that is the way V6 is headed.

I did this with my PHP Inventory system. Add new Inventory Items, change pricing, change vendors, Inventory Payouts from Drawer, Safe, etc. I have been using it for a couple years now, and still don’t use the in-built methods. It alters the SambaPOS DB directly by doing Updates and Inserts, etc, Does not use MySQL. Also incorporates the User/Role system of SambaPOS to control who can do what.

I’d like to learn about this. It would be a great way to alter things from home!

How have I never seen that post before? Nonetheless, I feel my implementation was a bit different as I aimed to make it work if Samba was not running, So at night time, if the manager shut down the registers, I can still change settings and have it update when Samba turns on.

1 Like

Out of interest @GreatShakesBar did you have a go at getting it working?

Not yet. Today will be my play with samba day

Matt

Again lol, wondering if you got it to work?

Using this technique, Ive got another function of samba up on the website for control :slight_smile:

Would anyone be interested in seeing the source?

3 Likes