SO.. based on one aspect of ISC –
Within the same output given, is a hyperlink to an image and price, total etc. A basic overview a particular customer order.
My idea then, was to use this presentation, modify it to fit my needs and then platform an ability to customise a printable, e-mailer friendly equivalent. I have as damn it completed this project, but think the start position presented here maybe of use for somebody to add into or use as is.
So I am deliberately keeping this script tight for this posting and you will need to replace all XX's for your own environment –
Code: Select all
<?php
//point at database server
$user_name = "XXXXXX";
$password = "XXXXXX";
$database = "XXXXXX";
$server = "XXXXXX";
//clarify longon credentials
$db_handle = mysql_connect($server, $user_name, $password);
$db_found = mysql_select_db($database, $db_handle);
if ($db_found) {
$result = mysql_query("SELECT
isc_orders.orderid,
isc_orders.ordcustid,
isc_orders.ordstatus,
isc_orders.ordtotalamount,
isc_orders.ordsubtotal,
isc_orders.ordshipcost,
isc_order_products.orderprodid,
isc_order_products.orderorderid,
isc_order_products.ordprodsku,
isc_order_products.ordprodname,
isc_order_products.ordprodid,
isc_order_products.ordprodoptions,
isc_order_products.ordprodcost,
isc_order_products.ordprodqty,
isc_order_products.ordprodoriginalcost,
isc_products.productid,
isc_product_images.imageprodid,
isc_product_images.imagefiletiny,
isc_product_images.imageisthumb
FROM
isc_orders
LEFT JOIN isc_order_products ON isc_orders.orderid = isc_order_products.orderorderid
LEFT JOIN isc_products ON isc_order_products.ordprodid = isc_products.productid
LEFT JOIN isc_product_images ON isc_products.productid = isc_product_images.imageprodid
WHERE
(isc_product_images.imageisthumb = 1 OR isc_product_images.imageisthumb IS NULL)
AND
isc_orders.ordstatus = 10
AND
isc_orders.ordcustid = 389
AND
isc_orders.orderid = 449
");
//clean and reformat tokenized string
function clean_string($string) {
$string = preg_replace('/[^a-z\\040\\.\\-\/]/i', ' ', $string);
$string = preg_replace('/\b\w\b(\s|.\s)?/', '', $string);
$string = substr_replace($string, "colour: ", 6, 8);
return $string;
}
//set tacking vars to zero
$nCurrentOrderID = 0;
$nCurrentTotal = 0;
$nCurrentSubTotal = 0;
$nCurrentShipmentTotal = 0;
while ($row = mysql_fetch_assoc($result)) {
$string = $row['ordprodoptions'];
$col = clean_string($string);
$oid = $row['orderid'];
$sel = $row['ordprodqty'];
$nam = $row['ordprodname'];
$sku = $row['ordprodsku'];
$val = number_format($row['ordprodoriginalcost'],+2);
$ods = number_format($row['ordsubtotal'],+2);
$odc = number_format($row['ordshipcost'],+2);
$toa = number_format($row['ordtotalamount'],+2);
$ord_value = number_format($sel * $val,+2);
//check for image
$showImage = '';
if($row['imageisthumb']==1) {
$imurl = "http://www.XXXXXXXXXX/product_images/";
$nmg = $imurl . "noimage.jpg";
$img = $imurl . $row['imagefiletiny'];
$showImage = "<img border='0' src='$img'>";
} else {
$showImage = "<img border='0' src='$nmg'";
}
//this is just to break up long lists a little bit
if($nCurrentOrderID!=$oid) {
//insert totals info
$strOrderTotals = '';
if($nCurrentTotal>0) {
$strOrderTotals = "
<table border='0' width='600'>
<tr>
<td width='480'> </td>
<td width='60'>Sub total:<br>Shipping:<br>Total:</td>
<td width='60'>£$ods<br>£$odc<br>£$toa</td>
</tr>
</table>
";
}
$nCurrentOrderID = $oid;
//insert order id break
$header = "
<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Transitional//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd'>
<html xmlns='http://www.w3.org/1999/xhtml'>
<head>
<meta http-equiv='Content-Type' content='text/html; charset=utf-8' />
</head>
<body>
$strOrderTotals<br><br><strong><u>Order ID: $oid</u></strong><br>
";
} else {
$header = '';
}
//collect current var
$nCurrentTotal = $toa;
$nCurrentSubTotal = $ods;
$nCurrentShipmentTotal = $odc;
$body = "
<table border='0' width='600' cellspacing='0' cellpadding='0'>
<tr>
<td width='550' rowspan='3'> <br>
$sel x $nam<br>
SKU: $sku<br>
Selected $col<br>
</td>
<td rowspan='3' valign='bottom'>$showImage</td>
<td width='64'> </td>
</tr>
<tr>
<td width='64' valign='bottom'> £$ord_value </td>
</tr>
<tr>
<td width='64'> </td>
</tr>
</table>
";
echo $header;
echo $body;
}
}
$footer= "
<table border='0' width='600'>
<tr>
<td width='480'> </td>
<td width='60'>Sub total:<br>Shipping:<br>Total:</td>
<td width='60'>£$ods<br>£$odc<br>£$toa</td>
</tr>
</table>
</body>
</html>
";
echo $footer;
mysql_close($db_handle);
?>What is important however, is the ability to fully customise a header, body and footer for the page being constructed. My example code is a second generation trial, it works, but is basic.
One feature written into the script is the ability to run a one off page or a list. Simply by changing the following determines how the script replies with any query. Remember, codes given here are mine and will not necessarily match yours for the same tabled data –
isc_orders.ordstatus = 10 <= admin status set as ‘Order Completed’
AND
isc_orders.ordcustid = 389 <= customer id
AND
isc_orders.orderid = 449 <= order made by above customer
Removing 449 will return the list of orders made by customer 389, keeping will only pull that particular order !
*** NOTE: No image refers to the fact that that particular colour/item has been sold out or discontinued since this order itself was made.
In the above output, the script referance $header provides the Order ID. The $body is looped in order to supply ‘lines’ of information or list. The $footer provides the totals for each list presented.
The inital run of the script places the header and then rolls out the list, checking for what is being held in SQL. While doing so, the loop allows -
$nCurrentOrderID
$nCurrentTotal
$nCurrentSubTotal
$nCurrentShipmentTotal
to check to see if a list has been completed. If so, outputs list totals (the footer).
If however, you use the script to list orders, the output will in fact be placed AFTER the codes detects a order ID change. That change triggers the footer. In effect retro finishing each 'section' in a listing as it returns your query.
