[Addon] Customisable HTML output pages for ISC

For Articles relating to more than one ISC version
Post Reply
Snooper
Posts: 264
Joined: Sat Jun 26, 2010 9:22 pm

[Addon] Customisable HTML output pages for ISC

Post by Snooper »

My needs as a wholesaler, are to be able to print off a ‘pick’ list, an invoice and anything else that meet MY requirements. I also need a platform of sorts that allows me to create on the fly reports outside the mess ISC currently provides.

SO.. based on one aspect of ISC –

Image

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'>&nbsp;</td>
		<td width='60'>Sub total:<br>Shipping:<br>Total:</td>
		<td width='60'>&#163;$ods<br>&#163;$odc<br>&#163;$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'> &#163;$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'>&nbsp;</td>
		<td width='60'>Sub total:<br>Shipping:<br>Total:</td>
		<td width='60'>&#163;$ods<br>&#163;$odc<br>&#163;$toa</td>
	</tr>
</table>
</body>
</html>
";
    echo $footer;
mysql_close($db_handle);

?>
What the code is doing, is simply going through 4 SQL tables taking selected ‘calls’ relevant to a customer and their order. This includes pulling the ‘tiny’ image from images folder if one is present. Much of what is doing what, has been (sort of) described in the code. This said, much of what happens will become obvious when run.

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 !

Image

*** 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.
Last edited by Snooper on Fri Jul 20, 2012 9:14 pm, edited 15 times in total.
ISC 5.5.4 Ultimate : Being used here -- http://www.kdklondon.com
Snooper
Posts: 264
Joined: Sat Jun 26, 2010 9:22 pm

[Addon] Customisable HTML output pages for ISC - Pt2

Post by Snooper »

Next we need to be able to chose how we call information from the script above. To this end, there are two options that allows this and so hopefully one will fit into your needs

In the SQL script -
isc_orders.ordstatus = 10
AND
isc_orders.ordcustid = 389
AND
isc_orders.orderid = 449


I assume I want to search for 'Completed' orders. The line isc_orders.ordstatus = 10 stays unchanged.
I want to select my customer by code. The line isc_orders.ordcustid = 389 is changed. I used (The choice of variable is arbitory) - isc_orders.ordcustid = $formID
I want to select one order made by my selected customer. The line isc_orders.orderid = 449 is changed - isc_orders.orderid = $formNum

Now we have two ways forward as shown below - The form method and the link -

Image

Route 1)
If you want to have a 'form' based query, then append to the top of the script (from above post) with the following -

Code: Select all

<?php
$formID = $_POST['formID'];
$formNum = $_POST['formNum'];
Now to pass our request, we script in HTML (I assume we have saved the above script as list.php) -

Code: Select all

<form action="list.php" method="post">
  Customer ID&nbsp;&nbsp;&nbsp;
  <input type="text" name="formID" maxlength="50"  /><br>
  Order Number
  <input type="text" name="formNum" maxlength="50"  /><br><br>
<input type="submit" name="formSubmit" />
</form>
Route 2)
If you are thinking of using the link based query (lower left of above image), then append the script with -

Code: Select all

<?php
$formID = $_GET['$formID'];
$formNum = $_GET['$formNum'];

Code: Select all

<a href="list.php?formID=389&formNum=449">Submit</a>
*** NOTE: Of the two options, the link assumes you are making a query based on a layer process.

I have not coded any checking for inaccurate selections in my code example. My use of the script is based established information controlled real time and does not require my manually entering any numbers.

Below is an example of useage I require, this being an order picking list (sample incomplete) -

Image
ISC 5.5.4 Ultimate : Being used here -- http://www.kdklondon.com
Post Reply