Hi
I have a XML feed that when I test pass it on your site it shows just 4 columns.
Order_Line
Origin
Code
Qty
One of the fields I need to get out is called Order_Number and then I want it to bring out the order number followed by the order lines as I need to re-pass the XML file to update the status in table in order_lines to processed Y.
So for example the order number will show once followed by the 4 lines for each order line. Say its 4 order lines then a new Order_Number will appear.
Whats the best way to achieve this.
Many thanks in advance for the great support.
Roy
Hi
I have tried that but its just brings out the entire order history.
Here is an example. {link saved}
The This is the order Number. You will see the feed contains three orders some single line and some multiline.
These are the order lines associated with that Order.
<SO_LINE>
<ORG_OF_ORD>9</ORG_OF_ORD>
<ART_CODE>EF_1STTRACKED</ART_CODE>
<ORD_QTY>1</ORD_QTY>
</SO_LINE>What I need is a script that says allows me to generate the xml feed as shown above and then using Magic Parser Read the feed back into the database table order_lines and mark each order line in column processed as Y if the Order Number, Art_Code match etc.
Thanks
Roy
Hi Roy,
Based on the output I can see from that script, I think you need to be using the Format String:
xml:AVXML/EBUSMSGSRQ/EBUSTRNRQ/EBUSTRNRQ/ROWADDRQ/Is that what you are currently using? The output of each record using that Format String can be browsed here.
To access each SO_LINE within each order, the easiest method in this case would be a loop generating the postfix value @1, @2 etc. that Magic Parser is using to resolve the duplicate field names. Have a go with something like this:
<?php
function myRecordHandler($record)
{
print "<p>".$record["DEL_ADDR"]."</p>";
print "<ul>";
$i = 0;
$p = "";
while(1)
{
if ($i) $p = "@".$i;
if (!$record["SO_LINE/ART_CODE".$p]) break;
$art_code = $record["SO_LINE/ART_CODE".$p];
$ord_qty = $record["SO_LINE/ORD_QTY".$p];
print "<li>".$art_code." (".$ord_qty.")</li>";
$i++;
}
print "</ul>";
}
?>
Hope this helps!
Cheers,
David.
Hi
Thanks for the great support. That looks a lot better now.
I Just need to work out some code now to tell the database to update the order_lines as processed Y where XML orderID = Datebase orderID and where xml art_code= datebase art_code and ord_qty = database order qty. This is to make sure the next time the feed runs it only brings out the new orders not that ones that have previously been in the XML.
Its one great script, and the support is great.
Roy
Hi
I am not to sure on how to create the final code and sql query to make this all happen.
My datebase table that it needs to check against is called orders_lines and contains one line of information for each order line which is orderID,code,qty,processed.
If you look at the example 2 from the link you created I need the script to check the following (This is a 4 line order in the feed and 3 lines in the database)
It will need to check this and mark each line as Y, they are currently all SET as N.
The only problem here is the first is not in the database of each order as this is generated from the XML as is the delivery option.
These are the checks it needs to carry out.
Does match the orderID
Does match code
Does match quantity
If these all match SET processed as Y and continue looping through the rest of the XML settings all order_lines as Y if they are in the XML output.
I am a bit stuck with code example you provided as dont really understand the logic I can see its going to use a counter etc but not sure of the rest and how it references the orderID with the being the orderID in the required format for the XML output.
Hopefully you can show me or explain more the logic behind how this is going to work. If it was more standard PHP SQL I could work it out but not this one.
Thanks
Roy
Hi Roy,
Within the code sample I posted, it breaks down to "order" level, and "line item" level as follows;
<?php
function myRecordHandler($record)
{
// ***********************************
// BEGIN ORDER LEVEL
print "<p>".$record["DEL_ADDR"]."</p>";
print "<ul>";
$i = 0;
$p = "";
// END ORDER LEVEL
// ***********************************
while(1)
{
// ***********************************
// BEGIN LINE ITEM LEVEL
if ($i) $p = "@".$i;
if (!$record["SO_LINE/ART_CODE".$p]) break;
$art_code = $record["SO_LINE/ART_CODE".$p];
$ord_qty = $record["SO_LINE/ORD_QTY".$p];
print "<li>".$art_code." (".$ord_qty.")</li>";
$i++;
// END LINE ITEM LEVEL
// ***********************************
}
print "</ul>";
}
?>
I would double check your database structure - from your XML it looks like it is line items that have a quanity, not individual orders.
So within the line item loop, based on the logic you need to implement, you would be looking at something like this;
<?php
function myRecordHandler($record)
{
// ***********************************
// BEGIN ORDER LEVEL
$order_id = $record["RPL_DEL"];
print "<p>".$record["DEL_ADDR"]."</p>";
print "<ul>";
$i = 0;
$p = "";
// END ORDER LEVEL
// ***********************************
while(1)
{
// ***********************************
// BEGIN LINE ITEM LEVEL
if ($i) $p = "@".$i;
if (!$record["SO_LINE/ART_CODE".$p]) break;
$art_code = $record["SO_LINE/ART_CODE".$p];
$ord_qty = $record["SO_LINE/ORD_QTY".$p];
print "<li>".$art_code." (".$ord_qty.")</li>";
$sql = "UPDATE line_items SET processed='Y' WHERE
order_id = '".mysql_escape_string($order_id)."'
AND
code = '".mysql_escape_string($art_code)."'
AND
quantity = '".mysql_escape_string($ord_qty)."'
";
// EXECUTE YOUR SQL HERE!
$i++;
// END LINE ITEM LEVEL
// ***********************************
}
print "</ul>";
}
?>
(I am assuming that RPL_DEL is the order ID in your XML, and presumably this is also present in your line items table as the foreign key - the ID that links each order to each of its line items)
Hope this helps,
Cheers,
David.
Hi
I cant seem to get it to go using this code.
You can see the errors here {link saved}
I changed your code slightly as $orderID = $record["COMMENT"]; I think is correct as this is the order number in the feed. I also changed code and qty as they are called code and qty in my database.
// Load Magic Parser
require("MagicParser.php");
// Connect to Datebase and Run Query
mysql_connect($hostname, $username, $password) or die(mysql_error());
mysql_select_db($database) or die(mysql_error());
function myRecordHandler($record)
{
//Get the Order Lines
$sql = "SELECT orderID,code,qty FROM orders_lines WHERE xmlprocessed = 'N'";
$result = mysql_query($sql) or die('Error : ' . mysql_error());
// ***********************************
// BEGIN ORDER LEVEL
$orderID = $record["COMMENT"];
$i = 0;
$p = "";
// END ORDER LEVEL
// ***********************************
while(1)
{
// ***********************************
// BEGIN LINE ITEM LEVEL
if ($i) $p = "@".$i;
if (!$record["SO_LINE/ART_CODE".$p]) break; //Is this correct
$art_code = $record["SO_LINE/ART_CODE".$p];
$ord_qty = $record["SO_LINE/ORD_QTY".$p];
$sql = "UPDATE orders_lines SET xmlprocessed = 'Y' WHERE
orderID = '".mysql_escape_string($orderID)."'
AND
code = '".mysql_escape_string($art_code)."'
AND
qty = '".mysql_escape_string($ord_qty)."'
";
// EXECUTE YOUR SQL HERE!
$i++;
// END LINE ITEM LEVEL
// ***********************************
}
}
MagicParser_parse("{link saved}","myRecordHandler","xml|AVXML/EBUSMSGSRQ/EBUSTRNRQ/ROWADDRQ/");
?> Thanks
Roy
Hi Roy,
The warnings aren't actually a problem, they are to be expected based on the way to code tests whether there is another line item for this order or not; however, since the error reporting level is turned on to include warnings on your server, a better way to do this would be to change the following line:
if (!$record["SO_LINE/ART_CODE".$p]) break; //Is this correct..to:
if (!isset($record["SO_LINE/ART_CODE".$p])) break; //Is this correctOtherwise, the only other thing I notice is that there is no call to mysql_query after generating $sql within the line item loop, so if you replace...
// EXECUTE YOUR SQL HERE!...with:
// EXECUTE YOUR SQL HERE!
$result = mysql_query($sql) or die('Error : ' . mysql_error());...that should be all it is!
Hope this helps,
Cheers,
David.
Hi David
Many thanks for helping with the code and the final fix.
I have never seen you software cope with this kind of situation before but it dealt with it very well. Obviously you need to know how to code it etc.
Without your help I would have never got this to work, again many thanks for the support and hopefully this solution will benefit others as well if they ever come across this type of situation.
Once again many thanks.
Roy
Hello Roy,
It looks like the auto-detection has picked up order_line as the repeating element (as it is probably the most frequent element in the feed), when you actually want to be parsing at, I would guess, 1 level up, e.g. "order".
When you upload your XML to the demo tool, scroll down to the "Select an alternative format string for this XML document" and choose the first option which will be one level higher - I think you will then be able to see all the information per order!
Hope this helps,
Cheers,
David.