A New Ship-to Report, Adding it to the Reports Screen

In my previous post, A New Ship-To Report, From a Development View, I walked through my method of developing queries in SQL Server Management Studio. Today, we’ll take that as a starting point to add it to our reports screen.

As it happens, we’ll be adding a third screen today. We started with Warehouse Reports and Contract Reports, and we’ll be adding Customer Reports, Ship-to being our first one.

If you want to know the honest truth of how I develop (and what I do isn’t uncommon, I suspect), it’s simple: I try to develop once and then copy and modify what came before. To add Customer Reports, I started with Contract Reports, changed the headers and stripped out the content, and there we go – we have a brand-new interface:

Click for full size

As you can see, I’ve already added the relevant JavaScript file, which is why the buttons are already styled. This is also where I realized I have redundancies in the JavaScript files for the reports screens – the Print, Collapse, and Accordion functions are identical in all of them. That is the root of all evil in programming – the goal is to eliminate duplication wherever possible. To that end, I’ll be fixing that soon, and moving those functions to a separate file we’ll then include in the report screen files.

Now, as I said, I copy and modify wherever I can. In this case, our Vendor Contract report is an excellent candidate, as it also uses the jMenu button to allow options for your selection criteria. Essentially, all that’s needed is to grab that entry from the Contract Reports screen and change the names of the variables, etc. However, notice there’s another button on the Ship-to Report, the “w/Closed Depts” button. This is actually a restyled checkbox, and figuring out how to get this to work was a drag. Once I figured it out, it looks like so:

<div><input type="checkbox" name="chkClosed" id="chkClosed" class="ui-helper-hidden-accessible"/>
<label for="chkClosed" aria-pressed="false" class="ui-button ui-widget ui-state-default ui-corner-all ui-button-text-only" role="button" aria-disabled="false">
<span class="ui-button">w/Closed Depts</span>
</label>
</div>

As you might guess, we use this to toggle between including closed departments when we run a ship-to report, with a bit of back-end trickery in our PHP.

Now, you might also have noticed that our field that normally shows us our ‘Last Modified’ time is throwing an error. That’s because we have no files behind this. That is, select what you want, include closed departments, etc, but the buttons that run the reports don’t do anything at the moment. Once again, we’ll take a previously-created file and hack it into shape to serve as our ship-to file.

We’ll start with the View file, so the Customer Reports screen will stop throwing that error. Reaching blindly into the Contracts folder, we’ll use the contract_by_item.php file as the basis for our ship-to report. I’ve learned the hard way that the first thing to check is the check_submit value. For me, at least, if everything looks right but the report won’t run, it’s because I forgot to correct the check_submit value. In this case, we’ve set it at 3 in our JS file, so we’ll change it from the 4 contract_by_item was using.

Next, we fix the $_POST values. contract_by_item had

$mfg = sanitize($_POST['mfg']);
$item = sanitize($_POST['item']);

so we’ll delete $item and change $mfg to $criteria, then change our $_POST value to ‘stInput’ (which is the input field on our interface.)

Next up, the SQL query itself gets replaced. Here it gets a little trickier, as we’ll be changing the SQL query based on our selection criteria. But, the basics of it are easy enough to get in place of the existing query. From the New Ship-To file in SMSS, we’ll grab the query down to the where clause and paste it into our new file.

If we were to run this right now…well, it wouldn’t go well, seeing as how we haven’t changed anything lower down in the file. However, we’re nearly to the point of needing our first test, as we need to finish off the query by adding in the variables that make up our where clause. As this is the View version of ship-to, we’ll return a fairly stripped-down version of the results, as we have a limited amount of space on-screen. After deciding what I want to return, I’ll change the block that creates the table headers:

<thead>
<tr><td style='font-size:12px;font-weight:bold;color:#000000;'>Account</td>
<td style='font-size:12px;font-weight:bold;color:#000000;'>Name</td>
<td style='font-size:12px;font-weight:bold;color:#000000;'>Department</td>
<td style='font-size:12px;font-weight:bold;color:#000000;'>Suite</td>
<td style='font-size:12px;font-weight:bold;color:#000000;'>Street</td>
<td style='font-size:12px;font-weight:bold;color:#000000;'>City</td>
<td style='font-size:12px;font-weight:bold;color:#000000;'>State</td>
<td style='font-size:12px;font-weight:bold;color:#000000;'>ZIP</td>
</tr></thead><tbody>"

This helps me keep straight what needs to go where when I’m changing what I’m taking from the results of the SQL query itself. As we do a SQLSRV_FETCH_NUMERIC on the query, we’ll need to count out where our columns are as we change the column data. Once that’s done, it looks like so:

"<tr><td style='font-size:12px;color:#000000;'>$row[1]</td>
<td style='font-size:12px;color:#000000;'>$row[3]</td>
<td style='font-size:12px;color:#000000;'>$row[4]</td>
<td style='font-size:12px;color:#000000;'>$row[7]</td>
<td style='font-size:12px;color:#000000;'>$row[8]</td>
<td style='font-size:12px;color:#000000;'>$row[9]</td>
<td style='font-size:12px;color:#000000;'>$row[10]</td>
<td style='font-size:12px;color:#000000;'>$row[11]</td>
</tr>"

While we’re down near the bottom of the file, we’ll change the insert_timestamp function, as well, so we don’t skew the results of contract_by_item, by accidentally writing a ton of bogus timestamps in. This would also be the right time to jump back over to the customerreports.php file and fix the

line, to be sure what the ret_timestamp function is looking for lines up with what our shipto_view.php file is writing into our log.

All that done, we’re ready to set our first criteria for the where clause. We’ll start with Account Number, since it was top of pile in the original query. The way we do that is with another hidden submit in our customerreports.php file, named ‘checkST’, and a menuSelect function in our JavaScript file that retrieves the value selected. For that, we add $checkST = $_POST['stInput']; as a new variable, and then a block something like this:

if ($checkST == 'account') {
$limit = "Customer.Account = " . str_pad($criteria, 10, ' ', STR_PAD_LEFT);
} else {
$limit = '';
}

We want this to give us an exact match, but we can’t easily change the amount of leading whitespace on the account number as we could in SSMS. To get around that, we use the PHP function str_pad. This, as you might gather from the code above, is letting us make the string 10 characters long, using spaces to pad, and padding to the left of our input string. We’ll stick with just this one for our first test, just to see if we’re on the right track. So upload our shipto_view.php file into the /includes/customers directory and see how it goes:

Well, that’s not good. We forgot to check the filename we had last_modified looking for. Fix, save, re-upload, refresh the page and we’re all good. So, let’s actually test the report itself.

Click for full size

Mm-hmm. Didn’t we already fix that? Wasn’t that the very first thing we did? Worse, line 54 is right in the middle of the SQL query, so we don’t have a good row number to jump to so we can fix it. However, right below the query, the problem shows itself: $params = array($mfg, $item);. As we’re using parameterized queries, we need to fix what we originally had. So, we’ll change our $limit variable to $limit = "Customer.Account = " and add a separate variable, $full_crit, like so: str_pad($criteria, 10, ' ', STR_PAD_LEFT);. And in the query, we’ll add (?) immediately after our $limit variable. Let’s try this again:

Click for full size

Occasionally these SQL errors are useful. More often, essentially it says ‘blah, blah, blah, you screwed up.’ So, now we bust out our debugging. We’ll do it on the cheap the first time around and just echo what our $limit and $full_crit variables are passing. While we’re about it, let’s check the $checkST value, too:

echo 'checkST = ' . $checkST . '<br />';
echo $limit . $full_crit . '<br />';

Click for full size

Well, there we have it. I accidentally made $checkST $checkST = $_POST['stInput'];, rather than $checkST = $_POST['checkST'];. Boneheaded mistake, but one easily made and, more importantly, easily fixed. Do that, run it again, and we’ve got:

Click for full size

Perfect.

As to the other limits we query on, the procedure is essentially the same. My full block of limits for this report looks like so:

if ($checkST == 'account') {
$limit = 'Customer.Account = ';
$stInput = str_pad($stInput, 10, ' ', STR_PAD_LEFT);
} else if ($checkST == 'rep') {
$limit = 'CustomerSalesperson.UserId = ';
$stInput = str_pad($stInput, 4, ' ', STR_PAD_LEFT);
} else if ($checkST == 'city') {
$limit = 'CustomerShipTo.City = ';
} else if ($checkST == 'state') {
$limit = 'CustomerShipTo.State = ';
} else if ($checkST == 'district') {
$limit = 'Customer.Region = ';
}

However, you may notice something a little funny. That is, I need to str_pad Account Number and Rep, while I don’t for City nor District. Why this is, given that they are char (fixed-length) fields, I can’t say, to be perfectly honest. I discovered this by trial and error – in SSMS, I found by accident that querying on an exact city name worked without padding spaces. Same with district – the field is 14 characters long, but my tax districts max out at 6 digits. All that to say, sometimes you just have to play with a query until it works. I wish it were always perfectly straight-forward, but life rarely works like that, no?

Here I’ve given you the warts-and-all version of how I develop. What I hope you gather from this is that it gets a little ugly and messy at times, but you have the means to get it right. A little persistence, patience, and some quality time with a search engine will lead to great success. I find code online frequently that solves problems that at first seemed, to me, insoluble. I sometimes get the impression that some of these people are doing this right off the top of their heads. That may be true in some cases, but I would guess that frequently development goes a bit like it does above – details get forgotten, stupid mistakes get made, and code blows up left and right. Stick with it, though, and you’ll get it sorted. And there’s little more satisfying than that, when you see code you wrote giving you exactly the results you need.

As ever, I remain