Welcome to the Blog of Mike Ross: October 2006 Archives

« September 2006 | Main | November 2006 »

October 19, 2006

A Little Fun With Radio Buttons

Tom Neal recently asked me to modify a SWAMP report. The existing report returned information about the 05-06 Fiscal Year, and he also wanted information from the current Fiscal Year. This report is pretty long, so I wanted to keep the report to no more than one Fiscal Year. Also, this is a time frame that makes sense for this report.

I got the idea of creating a "front" page where the user could select a Fiscal Year, then click on a button and run this report for that time frame. A perfect spot for using "radio" buttons. Here's how I put the radio buttons to good use, blending JavaScript and PL/SQL.

First, the radio button is a good choice here because I don't want the user to pick more than one Fiscal Year at a time. To select more than one fiscal year at a time, checkboxes would have been the right choice. Checkboxes work pretty much the same as radio buttons.

In setting up radio buttons, JavaScript is really slick. If you give all the buttons the same name, JavaScript will make an array of them. Then you can just loop through the array looking for the selected button. Here's a look at the radio buttons I made:

Here's the code that created these buttons:

<tr>
<td>Fiscal Year 06-07</td><td><input type=radio name=fy checked=true>    </td>
</tr>
<tr>
<td>Fiscal Year 05-06</td><td><input type=radio name=fy>    </td>
</tr>
<tr>
<td>Fiscal Year 04-05</td><td><input type=radio name=fy>    </td>
</tr>

Three radio buttons with the name "fy". Notice that the first button has the added property "checked=true". This automatically selects the first radio button. In this way, I avoid having to handle the possible condition of no radio buttons checked.

So, the user has selected the Fiscal Year they want and clicked the "Prepare Report" button. We must figure out which radio button was selected, then run the report for the appropriate Fiscal Year. Here's the JavaScript function that makes it happen:

I obtained the guts of this function from here. This is the JavaScript Kit Reference page, and it is a great JavaScript resource.

What this function does is loop through the radio buttons until it finds the one that is checked, then calls a PL/SQL procedure, passing a 0,1, or 2 depending on which radio button was selected. My PL/SQL procedure then examines the value passed in and sets the date range. I did it like this (pfy is the value passed):

if(pfy = '0') then
vlodate := '01-JUL-06';
vhidate := '30-JUN-07';
elsif(pfy = '1') then
vlodate := '01-JUL-05';
vhidate := '30-JUN-06';
else
vlodate := '01-JUL-04';
vhidate := '30-JUN-05';
end if;

Here's something interesting - you can use these variable names in your procedure cursors. So a cursor could look like this:

select xxxxx from table.table
where condition
and item_date between vlodate and vhidate;

This works. Go ahead and set up your report as needed. Here's a sample of my report's output:

There you have it. Another useful JavaScript - PL/SQL combination.

Here's a comment from Summer:

I've created a
procedure that ads the javascript function to your code:

<pre>procedure getradio is
begin
htp.p('
<script language="javascript">
function getCheckedValue(radioObj) {
if(!radioObj)
return "";
var radioLength = radioObj.length;
if(radioLength == undefined)
if(radioObj.checked)
return radioObj.value;
else
return "";
for(var i = 0; i < radioLength; i++) {
if(radioObj[i].checked) {
return radioObj[i].value;
}
}
return "";
}
<script>');
end getradio;</pre>

Posted by rossm at 8:36 AM | Comments (0)

October 4, 2006

Dynamic HTML Query - Are You Ready For This?

I just finished a small modification to a SWAMP form, allowing for an additional search item (unit price). Now, you can search our receipt data by unit price - it comes in handy once in a blue moon. The modification wasn't too tough, but the code that handles these queries is really something. I thought I'd share with you.

First, let me set the stage. What we're trying to accomplish is to create a form that is initially all null values. The user is then allowed to fill in one or more of the text boxes and then click on the "query" button. We will determine which fields have been filled, create a dynamic query, execute the dynamic query, then display the results. Get ready for a rough boat ride.

First, let's take a look at the blank query form. This particular form allows us to query the receipts data. This means we will look through about 250,000 records of items that have been received. Here's the form:

As you can see, there are a lot of options for the user. For our purposes here, let's pretend that we only have the first three options, that is, Invoice No., PO No., and WO No. The code would be the same, but with added elements for the other items that we're going to ignore. So, for our example, we'll be able to search for only three items instead of 20 or so.

The first step is to display the "blank" query form. This is accomplished by a call to a javascript function upon clicking the "Query" button. The javascript function looks like this:

function queryLines(){
var Addy = "http://www.yourdomain/yourdad/schema.form_receipts.query_lines";
var newWindow = window.open(Addy,"SWAMP","scrollbars,resizable,WIDTH=780,HEIGHT=500,left=0,top=0");
newWindow.focus();
}

This function calls our PL/SQL procedure query_lines. The procedure header (shortened for three items only) looks like this:

procedure query_lines(pinvoice_no varchar2 default NULL,ppo_number varchar2 default NULL,pwo_number varchar2 default NULL)

The "default null" behind each form element allows us to make the call to this procedure as in the javascript function - we don't need to send a null value for each element - very handy. Next, we need to check to see whether this is the initial display of the form (all records have null values) or whether a query has been requested (one to three not null values). Display the blank form using normal HTML if ALL values are null, otherwise run a query. Our if statement will look like this:

if(pinvoice_no is not NULL OR ppo_number is not NULL OR pwo_number is not NULL)

If true, run a query, if false display initial page. OK, I'm going to let you figure out how to display your own page. Let's work on the dynamic query - by that I mean we're going to build a query based upon the values passed in to our procedure. If one, two, or three of our values passed in is not null, then we will run a query. Here we go!

We've created a variable vblock varchar2(10000). This string variable will actually contain all the text for a complete PL/SQL procedure. It will be built a little at a time using string manipulation. This notion will seem very odd and frail to you at first, but it does work and it has proven to be quite stable over time. The only real downfall is that debugging is very difficult.

Let's build our string. We know that the following will be necessary for every query, so we add it in first.

vblock := '
DECLARE
BEGIN

for j in (select a.rec_number,b.po_number,wo_number,part_number,building_number,quantity,um,unit_price,discount,commodity,description,
vendor_no,brand_no,brand_name,catalog_no,preq_no,requestor,a.enter_date,b.enter_user
from rec_header b,rec_detail a
where a.rec_number=b.rec_number
and a.enter_date=b.enter_date';

We have started our procedure and set up our for loop. The for loop is also our cursor, kind of a neat PL/SQL trick. But we haven't finished with our search conditions yet. Here's how we'll add them in:

if(pinvoice_no is not null)then
vblock := vblock||' and b.rec_number like UPPER('''||pinvoice_no||''')';
end if;
if(ppo_number is not null)then
vblock := vblock||' and b.po_number like UPPER('''||ppo_number||''')';
end if;
if(pwo_number is not null)then
vblock := vblock||' and a.wo_number like UPPER('''||pwo_number||''')';
end if;

This is mighty slick! For each variable that has a value, we add to the vblock string another search condition. Look at all those ''' marks - the bane of this kind of programming. What we are doing is popping in and out of PL/SQL and javascript to build a proper query string. Are you still with me? This is one of those things you might have to look at a couple of times before it makes sense to you.

Next, we'll tack on an order by clause and start our loop:

vblock := vblock||' order by a.enter_date desc) loop

If you see what we're doing here, move to the front of the class:

htp.print(''
<table>
<tr>
<td><input type=text name=pinvoice_no size=15 maxlength=20 value=''||j.rec_number||''></td>
<td><input type=text name=ppo_number size=12 maxlength=15 value=''||j.po_number||''></td>
<td><input type=text name=pwo_number size=8 maxlength=10 value=''||j.wo_number||''></td>

Correct. Not only is this a dynamic query, it will also print out it's results. That's pretty cool, I don't care who you are... Now we'll do a bit of cleaning up so our string contains a complete procedure (this is a continuation of the above code):

'');

end loop;
htp.print(''

'');
END;
';

And now, let's see if the darn thing works.

execute immediate vblock;

Bryan Hockett wrote these things for SWAMP, and it took me a good year to learn all the little intricacies involved to modify them and keep them working. They are quite robust even though they don't look it. Here's a sample of the form displayed for a query on Invoice Number B000617084:

Nice display of two records that matched our search criteria. You will notice that I've omitted some housekeeping chores from this discussion such as error checking, and checking for a display condition. I hope that dissecting this code will assist you in developing your own dynamic queries in your forms.

Posted by rossm at 9:46 AM | Comments (0)

October 2, 2006

Bookstore Rush Week - No Problem!

Last week was rush week at the Bookstore. This is a time when our system is put through its paces, and also has been a time when we have had some hardware problems. This year, though, we lucked out. Earlier this summer we noticed a substantial slowdown at our cash registers. This was odd since there was very little activity either at the bookstore or in the WUC center. Well, a chance visit to the computer lab downstairs revealed about six kids playing "Rune" on the thin clients there in the lab. Whoa, we don't want six lab machines to slow down the entire network. Bill and Paul helped me to sort out the source of the problem, and Bill upped the switch capacity from 100 Mb to 1 Gb and that was that. There was no noticeable slowdown in the network during the entire rush week - Yes!

The second thing that happened - during new student week this time - was that yet another of our Zoom modems malfunctioned. This time the power switch broke and would only stay in the off position. It worked fine so long as you were willing to stand there and hold the button in, but of course that wasn't very practical. We have two of these modems - one is used for credit card verifications and the other for MBS to dial into our POS system. So, Kristi switched the dial in modem over to the credit card system and we were back up and running. You may recall that about two years ago this same thing happened but the back up modem only lasted about one hour, then it also failed. And that was during rush week - what a mess! Well, we overnighted a replacement in and there were no problems.

Of course this story wouldn't be complete if I didn't mention that one of our spare cash registers refused to boot up. After making a few phone calls it was determined that the receipt printer was sending out an error message. I replaced the paper roll and did a few other tweaks and got that last register online and it performed quite well this week. One of our goals is to replace these registers over time with some newer ones.

Bottom line - the system ramped up and met the load of rush week without any problems. I love it when a plan comes together!

Posted by rossm at 9:05 AM | Comments (0)