print oscommerce orders

a friend of mine asked if it was possible to make a very small device that could:

  1. beep when orders that did have the status "waiting" are present in the system
  2. push a button and print the order and change status to "processing"..... if more orders are waiting then take the first one in line
  3. at the end of the day push and hold button for like 5 sec and print order total for the day

when logging to the admin side of the shop the first thing shown are a menu to the left and then 2 frames show most resent added buyers in the left frame and in the right frame the below codes show orders waiting and those under processing

<?php
/*
  $Id: orders.php 1739 2007-12-20 00:52:16Z hpdl $

  osCommerce, Open Source E-Commerce Solutions
  http://www.oscommerce.com

  Copyright (c) 2007 osCommerce

  Released under the GNU General Public License
*/
?>

<table border="0" width="100%" cellspacing="0" cellpadding="4">
  <tr class="dataTableHeadingRow">
    <td class="dataTableHeadingContent"><?php echo ADMIN_INDEX_ORDERS_TITLE; ?></td>
    <td class="dataTableHeadingContent"><?php echo ADMIN_INDEX_ORDERS_TOTAL; ?></td>
    <td class="dataTableHeadingContent"><?php echo ADMIN_INDEX_ORDERS_DATE; ?></td>
    <td class="dataTableHeadingContent"><?php echo ADMIN_INDEX_ORDERS_STATUS; ?></td>
  </tr>
<?php
  $orders_query = tep_db_query("select o.orders_id, o.customers_name, greatest(o.date_purchased, ifnull(o.last_modified, 0)) as date_last_modified, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o, " . TABLE_ORDERS_TOTAL . " ot, " . TABLE_ORDERS_STATUS . " s where o.orders_id = ot.orders_id and ot.class = 'ot_total' and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and (o.orders_status = '1' or o.orders_status = '2') order by date_last_modified desc limit 6");
  while ($orders = tep_db_fetch_array($orders_query)) {
    echo '  <tr class="dataTableRow" onmouseover="rowOverEffect(this);" onmouseout="rowOutEffect(this);">' .
         '    <td class="dataTableContent"><a href="' . tep_href_link(FILENAME_ORDERS, 'oID=' . (int)$orders['orders_id'] . '&action=edit') . '">' . tep_output_string_protected($orders['customers_name']) . '</td>' .
         '    <td class="dataTableContent">' . strip_tags($orders['order_total']) . '</td>' .
         '    <td class="dataTableContent">' . $orders['date_last_modified'] . '</td>' .
         '    <td class="dataTableContent">' . $orders['orders_status_name'] . '</td>' .
         '  </tr>';
  }
?>
</table>

what i had in mind for the hardware is the smallest needed arduino and a small thermal printer

i do have and arduino mega and a redfly(wifi) shield and can program a very simple interface in to it, just need help/advice in how to do the rest... examples would be nice if this has been done before

/bo

a friend of mine asked if it was possible to make a very small device that could:

If the webinterface supports these functions you can send HTTP-GET/POST messages from the arduino to the webinterface and it will work.

Have no sample code, but the forum has several .

However it would be better to make a separate interface for the Arduino on the webserver if possible.
ad 1)
make a cgi/php script that indicates that there are waiting orders present. The script returns just the first orderID
let the arduino poll this script on a regular interval. The advantage of a dedicated script is that the Arduino does not need to parse the webpage.

ad 2)
make a cgi script that accepts and order ID as parameter and do the things you described.
The arduino can sent the orderID it got from (1) after a button press.

ad 3)
At the end of the day the arduino calls a 3rd script that does just that.

This way te most work will be done on the far more powerful webserver and will minimize the complexity on the arduino side.

If you want to let the arduino print the order you need to make a script so the arduino can just dump what it gets from the webserver.

Hope this helps

yeah its food for thought..

and placing a script is no problem...

one script to tell if there are orders waiting, that one would only indicate one order even if there are more
another script to just tell the order in raw text based on the order number from first script, of course only the fields needed
a simple script to change the order status
another simple script to just show current days order total

arduino will be pulling the first script at say 10 sec interval or whatever suits... if there is an order just print order number so if no text there is no order
if there an order start a slow beep pattern, when button is pressed short print the order and change the status

this will of course repeat until the first script stops reporting anything

at the end of the day button is pressed for 3-5 secs and the days total is printed...

hmmm now i just need to figure the scripts.... hardest part

but first i need to translate the basic oscommerce shop from english to danish :astonished:

Think you got the idea

hmmm now I just need to figure the scripts.... hardest part

webserver side or arduino side?

but first i need to translate the basic oscommerce shop from english to danish

google translate is your friend :wink: to get started ..

google translate :~ nah, the trouble is to translate so that it gives the correct meaning and so that the overall context is the same...

as for the scripts i was mostly thinking the server side, but also the arduino side, i mean i can make a sketch for a arduino and wifi shield that just says hello and link to change wifi settings, and then i can take small bits of code and put them together... but sure not the best level to tackle such a task as this.

i have the hardware thou so i can test the sketch

the first should look something like this: - stripped the one you posted -

<?php
  $orders_query = tep_db_query(
      "SELECT s.orders_id 
      FROM " . TABLE_ORDERS_STATUS . " s 
      WHERE o.orders_status = '1'  
      LIMIT 1");
  $orders = tep_db_fetch_array($orders_query))
  echo $orders['orders_id']';
?>

thanks, will test that one once i get everything translated

(you need to fix the query as it definitely contains errors ...)

tried with the below code and got:

Fatal error: Call to undefined function tep_db_query() in C:\ftp\bo\testwebshop\arduino1.php on line 2

<?php
  $orders_query = tep_db_query("select o.orders_id, o.customers_name, greatest(o.date_purchased, ifnull(o.last_modified, 0)) as date_last_modified, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o, " . TABLE_ORDERS_TOTAL . " ot, " . TABLE_ORDERS_STATUS . " s where o.orders_id = ot.orders_id and ot.class = 'ot_total' and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and (o.orders_status = '1' or o.orders_status = '2') order by date_last_modified desc limit 6");
  while ($orders = tep_db_fetch_array($orders_query)) {
    echo '  <tr class="dataTableRow" onmouseover="rowOverEffect(this);" onmouseout="rowOutEffect(this);">' .
         '    <td class="dataTableContent"><a href="' . tep_href_link(FILENAME_ORDERS, 'oID=' . (int)$orders['orders_id'] . '&action=edit') . '">' . tep_output_string_protected($orders['customers_name']) . '</td>' .
         '    <td class="dataTableContent">' . strip_tags($orders['order_total']) . '</td>' .
         '    <td class="dataTableContent">' . $orders['date_last_modified'] . '</td>' .
         '    <td class="dataTableContent">' . $orders['orders_status_name'] . '</td>' .
         '  </tr>';
  }
?>

progress

<?php
$con = mysql_connect("localhost","webshoptest","1234");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("webshoptest", $con);

$result = mysql_query("SELECT * FROM orders WHERE orders_status='1' LIMIT 0, 1");

while($row = mysql_fetch_array($result))
  {
  echo $row['orders_id'] . " " . $row['orders_status'];
  echo "
";
  }

mysql_close($con);
?>

list oders with status 1 and limits results to 1 line. 1=Pending

Very good!
Because the result set is limited to 1 record you may use an if instead of while in the php but for robustness sake I would use a while :slight_smile:

Think you should add something like sorting on date/time
something like

SELECT * FROM orders WHERE orders_status='1' ORDER BY orders.date_purchased LIMIT 0, 1

as you want to process the customers in order of entrance (I assume)

Keep on going, you can do it !

next bit is to update orders.... in oscommerce this http://pastebin.com/sny0rPjg shows orders

if you add: oID=5230&action=edit it will show order 5230 and allow the status to be updated if you change the status dropdown and click update...

of course there is a lot to clean out as its not needed... but would be nice for the page just to show the basics like delivery address and what ordered and how its paid

but its a bit over my head to change the page....

i was thinking direct edit in database could be done but then the customer would not be noted on status change via e-mail

/bo

yeah i could sort by time, but dont think it will be a problem...

most oders will be printed within 1 min after the box starts to beep... and even if there are 2-3 orders within the same time, which is rare, they will all be delivered on the same drive from the shop, but yes could be added with no problem if the problem gets there

but its a bit over my head to change the page....

You should not change it but write it from scratch,

Best start by going through a PHP tutorial which shows how to update tables in a database.
You must spent time to learn PHP otherwisae you will have a system you do not understand.

more progress today, below code allows to print out the order if you add: ?o=xx where xx is the order number from the first page

<?php
$con = mysql_connect("localhost","webshoptest","1234");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("webshoptest", $con);

$result = mysql_query("SELECT * FROM orders WHERE orders_id='".$_GET["o"]."'");

while($row = mysql_fetch_array($result))
  {
  echo "Ordrenummer: ".$row['orders_id'];
  echo "
";
  echo "
";
  echo "Bestilt: ". $row['date_purchased'];
  echo "
";
  echo "
";
  echo $row['delivery_name'];
  echo "
";
  echo $row['delivery_street_address'];
  echo "
";
  echo $row['delivery_postcode'] . " " . $row['delivery_city'];
  echo "
";
  echo "------------------------";
  echo "
";
   }

$result = mysql_query("SELECT * FROM orders_products WHERE orders_id='".$_GET["o"]."'");

while($row = mysql_fetch_array($result))
  {
  echo $row['products_quantity']. " * " . $row['products_name'];
  echo "
";
     }

$result = mysql_query("SELECT * FROM orders_status_history WHERE orders_id='".$_GET["o"]."' AND orders_status_id=1");

while($row = mysql_fetch_array($result))
  {
  echo "------------------------";
  echo "
";
  echo $row['comments'];
  echo "
";
     }

$result = mysql_query("SELECT * FROM orders WHERE orders_id='".$_GET["o"]."'");

while($row = mysql_fetch_array($result))
  {
  echo "------------------------";
  echo "
";
  echo "IP: ".$row['ipaddy'];
  echo "
";
  echo "Udbyder: ".$row['ipisp'];
  echo "
";
  echo "------------------------";
  echo "
";
  echo "Betalingsmåde: ".$row['payment_method'];
}

mysql_close($con);
?>

only problem with updating the status directly in database is that the customer is not notified..

but hey its one step less... also from today the last step... might hack the e-mail part and put it together with something of my own

only problem with updating the status directly in database is that the customer is not notified..

You can make a separate procedure to mail the customer There are PHP classes for that.

i found a problem in listing the order... i forgot to include attributes

the below code list the product like i want but then it lists the attributes under that...

and i have googled like mad for an example that allows me to list the attributes next to each item in the list

have hit a dead end on this one

<?php
$con = mysql_connect("localhost","webshoptest","1234");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("webshoptest", $con);

$result = mysql_query("SELECT * FROM orders WHERE orders_id='".$_GET["o"]."'");

while($row = mysql_fetch_array($result))
  {
  echo "Ordrenummer: ".$row['orders_id'];
  echo "
";
  echo "
";
  echo "Bestilt: ". $row['date_purchased'];
  echo "
";
  echo "
";
  echo $row['delivery_name'];
  echo "
";
  echo $row['delivery_street_address'];
  echo "
";
  echo $row['delivery_postcode'] . " " . $row['delivery_city'];
  echo "
";
  echo "-------------------------------------------------";
  echo "
";
   }

$result = mysql_query("SELECT * FROM orders_products WHERE orders_id='".$_GET["o"]."'");

while($row = mysql_fetch_array($result))
  {
  echo $row['products_quantity']. " * " . $row['products_name'];
echo " - ";
echo "
";
}


$result = mysql_query("SELECT * FROM orders_products_attributes WHERE orders_id='".$_GET["o"]."'");

while($row = mysql_fetch_array($result))
  {
  echo $row['products_options_values'];
  echo "
";
  
     }


$result = mysql_query("SELECT * FROM orders_status_history WHERE orders_id='".$_GET["o"]."' AND orders_status_id=1");

while($row = mysql_fetch_array($result))
  {
  echo "-------------------------------------------------";
  echo "
";
  echo wordwrap($row['comments'], 39, "
\n");
  echo "
";
     }


$result = mysql_query("SELECT * FROM orders WHERE orders_id='".$_GET["o"]."'");

while($row = mysql_fetch_array($result))
  {
  echo "-------------------------------------------------";
  echo "
";
  echo "IP: ".$row['ipaddy'];
  echo "
";
  echo "Udbyder: ".$row['ipisp'];
  echo "
";
  echo "-------------------------------------------------";
  echo "
";
  echo "Betalingsmåde: ".$row['payment_method'];
  echo "
";
}


mysql_close($con);
?>

can you post a text example of how you want your output to look like ?