Symphony.

I've started a site that includes a small price list of parts.

A static section called 'Parts defaults' includes a field called 'Default profit margin'.

A 'Parts' section includes the following fields:

• Cost price
• Fixed sales price
• Custom sales profit margin
• Sales price: (Select Box: Fixed price, Custom profit margin, Default profit margin) - this will determine which number(s) should be used to display/calculate the price

So when outputting the parts and their prices to a frontend page, I need to work out the actual prices depending on which of the pricing calculation options is chosen for each part.

At some stage, the client may wish to also take orders with payment online.

I'm thinking the proper place to do the calculating would be a custom data source. I haven't done one of these before, so before I attempt it I wanted to check it would be good practice. Perhaps I could also do it in the XSLT view (depending on the availability of math functions...), but I expect that's not in line with the separation of logic and view.

Thanks.

I'd do it with a custom datasource. I had do something similar the other day, do up an accounting report for ticket sales. The datasource was cranking out about 29000 queries to get the data (it's a stupidly complex architecture) so I could do the math in XSLT.

I managed to write one SQL query to get the data I wanted (granted it's about 80 lines long!).

The best advice I can give is use Entity Diagram and just start constructing your query in something like Sequel Pro.

Once you've got your query down, it's actually really easy to get the result as XML by using Symphony::Database()->fetch($sql) and then iterating over the result. Each of the columns in your SELECT are keys in the associative array returned, and then it's just a case of using XMLElement (or DOM) to add the values to the $result variable.

Thanks for the pointers. I'll give it a go. :)

I have the SymQL extension earmarked which looks like it could be helpful to me. If I can't manage with that, maybe I could use Database Section Views instead.

I've made some progress, but have a question on how to get at a value within the data that is automatically generated by Symphony based on what I initially created with the data source editor.

Given the following output XML (redacted for brevity):

<part-by-id>
    <section id="4" handle="parts">Parts</section>
    <entry id="22">
        <purchase-price handle="3">3</purchase-price>
    </entry>
</part-by-id>

how should I save the purchase price value (3) as a local variable for use in my custom calculation?

I'm getting lost with how to deal with/drill down into the $result array and its many child arrays.

Thanks.

This is the part and value (3) I'm trying to save as a simple variable so that I can use it to calculate:

[3] => XMLElement Object
    (
        [_name:XMLElement:private] => purchase-price
        [_value:XMLElement:private] => 3

Rather than querying the database again I thought I'd grab the value of purchase-price from the already-existing $result XMLElement object (created with Symphony's data source editor before having allowEditorToParse() return false, but I still haven't worked out how to get to it.

E.g.: $purchase_price = that there value that is buried in XMLElement objects that David doesn't know how to get to :-)

Oh yes, this is relatively easy.

If you open up the class.xmlelement.php file, you will see a function called getValue or similar (always forget the name). If you use that function on the variable you printed, it should return 3.

Thanks @creativedutchmen. How do I reference that XMLElement object so that I can use the getValue method? $purchase_price = ???->getValue();

Your data is already an XMLElement object, so you can get to it and use the getValue() function easily.

I know how to bui;d an object, but how to traverse an exsting one? Nope... no idea.

@designermonkey, thanks.

I've changed approach, and instead of starting with what I'd get from the data source editor, I've made an array by querying the Parts view provided by the Database Section Views extension. I've now got a fair bit of the XML being output, so am pleased to be making some progress.

Here's what my custom data source looks like at the moment. (Workings out of prices still to be done.) I'd be grateful if someone could take a quick look at the general approach and let me know if it's along the right lines or if it's horribly coded and should be redone a better way.

It gives me:

<parts>
     <part id="4">
      <name>Low Volume Reactor Tubes</name>
      <part-number>AC-MAN-LVT</part-number>
      <description>Pack of 10.</description>
     </part>
    <part id="17">
       <name>Low Volume Adapter Sleeves</name>
      <part-number>AC-MAN-LVAF</part-number>
       <description>Low Volume Aluminum former (pk/10)</description>
    </part>
 </parts>

You were so close before! If your XML was:

<part-by-id>
    <section id="4" handle="parts">Parts</section>
    <entry id="22">
        <purchase-price handle="3">3</purchase-price>
    </entry>
</part-by-id>

Then here are three ways you can get the value of purchase-price without an extension:

// Using XMLElement
$purchase_price = null;
foreach($result->getChildren('entry') as $entry) {
    $entry_children = $entry->getChildren();

    foreach($entry_children as $field) {
        if($field->getName() !== 'purchase-price') continue;

        $purchase_price = $field->getValue();
        break 2;
    }
}
echo $purchase_price;

// Or, using `SimpleXMLElement`:
$purchase_price = null;
$xml = new SimpleXMLElement($result->generate());
foreach($xml->xpath('//purchase-price[1]/text()') as $node) {
    $purchase_price = $node;
}
echo $purchase_price;

// Or, using `DOMDocument`:
$purchase_price = null;
$xml = new DOMDocument();
$xml->loadXML($result->generate());
$xpath = new DOMXPath($xml);
$purchase_price = $xpath->evaluate('string(//purchase-price[1])');
echo $purchase_price;

Duh, stoopid me, of course! It's an object! I completely forgot!

@brendo: thanks for the detailed and helpful list of options!

I went back to the approach of starting the data source by using the editor and used XMLElement. There's only one additional query to the database, which is to get the default profit margin (set in a static settings section). Here's how it stands at the moment. I'm sure there are ways the code could be improved and made more DRY, which I'd be intereted in, but it's working so I'm pretty chuffed.

It's giving me this XML, complete with custom-calculated prices:

<parts>
    <section id="4" handle="parts">Parts</section>
    <entry id="17">
        <name handle="blah">Blah</name>
        <part-number handle="test">TEST</part-number>
        <description mode="formatted"><p>Blah blah blah.</p></description>
        <order>1</order>
        <price>160</price>
    </entry>
</parts>

Next up: working in currencies. :-)

The currency information I need is in another section. To get the currency entries into my custom data source (which I need to do to work out price conversions), is there a way I can do it other than manually querying the database tables?

For example, for the default profit margin value I did:

$dpm_query = 'SELECT value FROM sym_entries_data_1 LIMIT 1';
$dpm_result = Symphony::Database()->fetch($dpm_query);

I could do similar for the currencies, but wondered if there was a way of getting the data that would let me use section/field names without depending on knowing which database tables to query.

Creating another data source using the data source editor and the currencies section, and then somehow importing its results into my own custom data source, perhaps? Is that possible?

Aha, it looks like Union Datasource could be just what I need, assuming I can still turn it into a custom data source.

Edit: also, I'm an idiot. I'd already found and mentioned SymQL in an above post. (I'll get there!)