In my previous post, I showed you how to add a menu to our web interface. If we had a ‘Contract Reports’ page, we could easily navigate to it with two clicks. Today, we’ll add that page and our first report so as to make it a bit useful.
Here and there, I have a need to find if an item is on contract, and if it is, what contracts and what prices are out there? While I’m about it, what’s my cost on the item, and the resultant GP?
As I usually do, I’ve put the necessary files together, available from ../../files/contracts/contract_by_item.zip. This zip file contains both the Contract Reports interface page, the necessary javascript file, and the first of our reports, Contract by Item.
I won’t get deep in the weeds with the backend code we’ve got here. After all, I’m not aiming at developers. I’m here to help people somewhat like myself: you need an easy way to run reports and aren’t necessarily deeply interested in the ‘how’ of it.
As an aside, though, I will show you the SQL behind this report, as it’s a fair demonstration of using multiple joins to pull data from more than two tables:
select CustomerPricePlan.PricePlan, CustomerPricePlanDetail.MACCode, CustomerPricePlanDetail.SKU,
CustomerPricePlanDetail.Price, ItemVendor.Cost
from (
CustomerPricePlan left join CustomerPricePlanDetail
on CustomerPricePlan.Id = CustomerPricePlanDetail.PricePlanId
) left join (
Item left join ItemVendor on Item.Id = ItemVendor.ItemId
) on CustomerPricePlanDetail.ItemId = Item.Id
where CustomerPricePlanDetail.MACCode = (?)
and CustomerPricePlanDetail.SKU = (?)
and ((Item.PurchaseVendorType = ItemVendor.VendorType
and Item.PurchaseVendorSequence = ItemVendor.Sequence)
or (Item.PurchaseVendorSequence = '0' and ItemVendor.Sequence = '1'))
What we have here is a query joining four tables. We start with CustomerPricePlan and CustomerPricePlanDetail. For whatever reason, ECI2 made the design decision to separate a number of tables this way. I’m not enough of a DBA to argue the choice as good or bad. It just drives me as a developer a bit batty needing to join tables together. There is CustomerPricePlan, which holds high-level data on contracts (ECI2 can call them what they like, I don’t know that I’ll ever come to refer to them as anything but contracts). This includes the contract code, start and end dates, pricing parameters, fixed versus flexible, etc. We’re left joining it with CustomerPricePlanDetail, which is really the guts of contracts – all the items and the corresponding pricing. We use the usual CustomerPricePlan.Id = CustomerPricePlanDetail.PricePlanId to make that happen. We left join Item to ItemVendor next. Then we take those two pairs and left join our PricePlan tables with our Item tables using ItemId to match them up.
Really, what I’d like to demonstrate here is that, assuming you don’t lose track of your tables and joins, you can join together an arbitrary number of tables. It’s a powerful thing to be able to pull data from across the system nearly disregarding the original sources. It’s a bit like a game of 6 degrees – you could likely perform a massive number of joins and return data from very nearly the system as a whole. All you need to find is a common column between two tables to join them together. Data miners might salivate at the prospect.
Open this up in a browser and search on our trusty SAN 30001, and your results should look a little something like this:
Click for full size
Only with dollar figures instead of blurs, hopefully. This might take a bit of time to run, which is why the file is using ini_set to change the maximum execution time of the script to five minutes. By default, the maximum time a script is allowed to run is 30 seconds. It’s a safety thing, along with being something that can save you some aggravation. If you have a runaway script or something with an infinite loop, it’s nice to eventually have it die so you can debug it. Otherwise you might stare at the loading screen forever.
Notice also that we have a GP figure on the furthest-right column. This is something we calculate in the backend PHP, as it isn’t something the SQL Server keeps track of itself. The way we do that in PHP is like so:
$gp = round(@(($row[3] - $row[4]) / $row[3]), 4) * 100;
$gp = number_format($gp, 2, '.', '') . '%';
That is, sell minus cost, divided by sell. Then we multiply it by one hundred and round it to two decimal places. A number_format to force two decimal places (which adds a zero in case you have a result that’s an even single-digit to the right of the decimal) and tack a decimal sign to the end and we’ve got our GP.
We do something similar with the cost and sell – concatenate a dollar sign to the front and number_format to force two decimal places. Obviously you don’t need the dollar sign, but I find I read tables like this more effectively when it’s there.
I’ve given you a report that will give a list of contracts for a given item. You can easily add other columns from any of the tables used. You could pull in results from other tables without much difficulty, if you have a column to join on one of the existing tables. Keep in mind that the cost it pulls is the Purchasing Vendor cost. I’ve realized recently realized I should be able to handle stocking items, as well, as in our model we leave a wholesaler as the P.Vendor. With a check on the item’s class, we could easily point our query that direction to handle items we bring in direct versus wholesale. I’ll leave that for a later update, however.
As always, you can reach me at 
Recent Comments