February 2, 2010

HVAC System Servers Moved, New Server Ordered

Here at the Physical Plant we've done our Spring cleaning early. Actually, Allen Risen moved to the Public Safety building, so his office was converted into the HVAC control center for Paul and Steve. We affectionately nicknamed it the "Man Cave". The Man Cave now houses Andy (Andover), Sparky (Johnson Controls), and Rita (steam metering).

To allow more access to the Andover system, which controls an ever-increasing portion of our campus HVAC load, we ordered a new server which will allow two persons to configure/update the Andover system at once. Presently there is quite a bit of contention for time on the server, so this will alleviate that bottleneck. Tricia ordered us a very nice computer yesterday, which will host the new Andover software and workstation.

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

February 23, 2007

Wire Pullers R Us

The physical plant networking infrastructure is getting some attention this week. Bill Nix from Telcom is here pulling wires. So far, he has added the Mechanic's Office, Carpenter Shop, and Machine Shop onto the network. Today he is working on Dave Morris' office. Soon we should also have Stores and a few other offices all upgraded.

It turns out that a lot of the Physical Plant's wiring is pre-cat 3. This wasn't too much of a problem until our new switch was installed and these lines were jacked up to 100 Mb. It was just too much for the old wiring, so a number of users were cut back to 10 Mb just to keep their connectivity. My part is to sit and watch, then hook up the computers and make sure they're happy. Also, this week we added new computers to the Carpenter Shop and Machine Shop. The Mechanic's Office already had a computer, but it wasn't hooked to the network. Now it is.

Pretty soon everyone should have 100 Mb service right to the switch. Thanks, Bill, a very nice job!

Posted by rossm at 1:08 PM | Comments (0)

August 28, 2006

Sparky Gets An Upgrade

Sparky is a server that runs the HVAC communication program for all of campus. Because of software limitations from the vendor, Sparky has had a Windows 2000 operating system. Recently, a Windows XP version of the software became available, so we were happy to upgrade Sparky to a Windows XP environment. That means the HVAC crew does not need to use PC Anywhere to communicate with the server. They now can use Remote Desktop instead. Remote Desktop has a lot of advantages. Also, PC Anywhere used to lock Sparky up on a pretty regular basis. That seems to have been eliminated with this upgrade.

It was fun to watch Joe Crowe at work - he installed the OS, all the drivers, etc., and also set up a temporary box to use while Sparky was down. There's a guy who knows what he's doing!

All in all this upgrade went really well. One loose end is that some graphics files were lost and are in the process of being recovered from tape. Some earlier versions of the graphics are in use now, so it is just a bit of inconvenience until we can recover the newer ones.

Posted by rossm at 3:21 PM | Comments (0)

May 24, 2006

Reporting on Expenditures

Well, I just finished coding up a tricky little SWAMP report for Tom Neal. It's a report that shows what's been spent on our equipment (Cushmans, mowers, trencher, tractors, etc.), vehicles, and grounds maintenance (plantings, fertilizer, etc.) this fiscal year. The main challenge for this report was gathering all the required pieces then breaking down the huge list of details into some sort of meaningful groups.

After some extensive poking around and discussions with Dave and John K., it was decided to collect information based upon work orders and then grouped by commodity code. Some examples of commodity codes are 58120 - Tools, garden & farm and 84301 - Nursery stock. Ultimately, this report was divided into three main groups: Work order 7509 - Campus Grounds Maintenance (grouped by Commodity code); All work orders like 'A%' (A002-A256) which includes Cushmans, mowers, chipper, etc. (grouped by Work Order number - one work order for each piece of equipment); and All work orders like 'E%' - vehicles (also grouped by Work Order number).

SWAMP maintains meticulous records on all purchases and all stock issues from Stores. Collecting together these records on the above groups resulted in an overwhelming amount of detail. What Tom was wanting was a quick way to look over a summary of expenses so that he could then go to the details on groups of items where expenses might be too high.

Although I tried to amass all the data required into one huge cursor, I finally gave up and just used some queries to load portions of the data into a temporary holding table, then used the holding table to display the results. Here is an example query:

INSERT INTO proj_test
(user, wo_num, desc,
detail_dte, qty, acct,
material, part_no, comm)
SELECT USER, B.wo_num, B.desc,
issue_date, B.qty, C.ind,
nvl(B.qty * pri,0),
B.part_no, E.comm
FROM iss_heer A,
iss_det B,
wo_nur C,
det_lst D,
part_data E
WHERE A.issue_no = B.issue_no
AND B.wo_no = C.wo_no
AND B.wo_no = D.wo_no
and B.part_no = E.part_no
AND D.enter_user = USER
and B.wo_no <= decode(end_wo,'',b.wo_no,end_wo)
and B.wo_no >= decode(beg_wo,'',b.wo_no,beg_wo)
AND A.issue_dte >= decode(beg_dte,'',A.issue_dte,beg_dte)
AND A.issue_dte <= decode(end_dte,'',A.issue_dte,end_dte)
AND A.issue_dte >= wo_eff_date
AND A.issue_dte <= wo_nchg_date;

As you can see, a pretty hairy query that returns the data on issues over a given date range and work order range. So, about six of these things loads up the temp table with all the pieces necessary for a nice report.

Here's the first page of the report:

Nice and sweet - three items and the money spent so far on each one. You will notice that each item is a link. Let's look at the Vehicles link page, which is a summary for each vehicle:

You can see how much has been spent so far on each vehicle, plus another link to the detailed expense information. In order to reduce the number of items, total expenses of less than $100 are excluded. Let's take a look at the Ford Ranger Pickup details:

Now, we've gotten the expenses down to a manageable number, and you can see what's been needed for this pickup so far this year pretty easily.

Nice report. Mostly just brute force gathering the data, then displaying it in bite sized chunks. There is one small coding trick that's interesting. The summary display for the 7509 work order is different than the display for the Equipment and Vehicles and required HTML tables of differing widths. So, I split out the HTML code that displays the header information into a separate procedure. Here's the procedure information:

procedure dsp_hdr(pwide varchar2 default '500')

Yes, that's right - you call this procedure like this:

dsp_hdr('750'); - Put in whatever width you need.

The procedure has a table definition like this:

<table align=center width='||pwide||'>

So, you can get report headers of varying widths for different parts of the report just by calling this procedure with whatever width you need. It's not rocket science, but it did work well for this report.

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

April 20, 2006

Parts Upgraded

One of the main uses for SWAMP is a detailed tracking of all parts kept in Stores. We keep track of all sorts of things such as price, quantity on hand, last cost, reorder point, how many we used over such and such a time, etc., etc. Every so often, Marsha cleans up a particular area that has become disorganized over time. For instance, certain parts are no longer needed as the equipment they were used on has become obsolete. Also, part numbers are added in to accommodate new stock items. Eventually, certain areas need to be reworked. One such area is the filters and other consumable parts for the lawn mowers and other grounds equipment. Marsha recently reorganized this entire area and assigned new part numbers to every part. It was my job to upgrade SWAMP data to reflect the new organization, without losing any of the historical data on each part. I'm glad to say it went very nicely and a new comprehensive parts list was generated today to show all the changes - it's 97 pages long and looks pretty neat! I always wanted to write a book...

Posted by rossm at 3:29 PM | Comments (0)

April 12, 2006

Physical Plant Project Tracking Revamped

The newest and most dynamic SWAMP module is the Project tracking module. We use it as the primary tool for project planning and tracking at the Physical Plant. This means it is regularly revamped and upgraded to account for the myriad changes in this area. Over the last week a substantial rewrite was once again performed. In addition to modifying the data structure (table mods), Dave Morris and I worked on aligning the project budgets, SWAMP tracking, and Banner financial data. The end goal is to fairly accurately reflect budgetary status of major funding groups in a summary type display along with the projects. We made it work. It is still far from a finished product, which will probably never happen, but the financial reporting has taken a great step forward.

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

February 23, 2006

Small SWAMP Projects Upgrade and Parts Reports

The Physical Plant's SWAMP application received a minor modification to the projects module over the past 3-4 weeks. I've added in two new pages to the main Projects tracking page. The first is a Miscellaneous page that tracks projects that have been formally approved, but are not yet definite with their funding source. For instance, there are quite a few projects that will likely fall under the "Tier 1" funding source, but that money is not yet available. We cannot wait until the last minute to identify and plan for these projects, so they have been approved, but have no funding yet.

The second page is an informal list of projects, or possible projects, that project managers have identified as needing to be addressed in the near future. These projects have no identified funding source, and have not been formally approved yet. This new list includes projects such as roof repairs, EMCS upgrades, and upcoming "Smart Room" remodels.

These two new pages are designed to assist in planning for upcoming projects. By the way, each project has a direct link to the SWAMP work order assigned to it, so a project manager can see what has been spent on a project to date with one click - pretty cool (but not new).

Finally, Marsha has been upgrading her paperwork with regard to various groups of stock items. For example, one area that's heavily used is janitorial/cleaning supplies. Over time items are added and other items are removed, so the list gets outdated. So, I have generated some newer reports reflecting current stocking information. SWAMP keeps excellent information on each item in stock, plus an abundance of historical data on obsolete items, so it was pretty easy to generate the information needed. Still, the reports were pretty nice.

Posted by rossm at 1:37 PM | Comments (0)

October 26, 2005

SWAMP Products Module Comes Together

Continuing with the Projects module rebuild. I've got a decent looking Beta of the main form put together, which combines the old form we used with the new financial data that needs to be tracked.

Here's a screen shot. (If you go to the Physical Plant category, you can see the whole thing.)

The financial data is primarily going to be entered by hand for now, and we will attempt to automate that process as we move forward. The form accumulates a total at the bottom which can be easily compared to the total funds available. Not bad!

Posted by rossm at 1:58 PM | Comments (0)

October 13, 2005

SWAMP Projects Module to Be Revamped

One of the things I pride myself on with SWAMP is it's flexibility. Whenever there is a need for modification, this can usually be accomplished with a reasonable amount of effort. Well, the Projects module is putting me to the test!

Our director, Tom Neal, has been pushing for a Projects form that can show him the overall financial status of a funding source, in addition to the usual project data that we track. I've written before on the myriad problems that make this a deceptively complex goal.

Another thing has also been going on over the past 4-6 months, and that is that no new projects are being entered into the SWAMP module. So the information has become stale. I believe this is mostly due to the rather cumbersome project initiation process we currently use.

These two things tie together, because there is no way that we'll ever get a good financial summary if we don't track EVERY project under a particular funding source.

So, in a moment of weakness, I decided to revamp the whole Projects module, from A to Z. Some work has already been done revamping the project initiation process, which will be put to the project managers next week. Also, a preliminary layout of the new Summary form has been developed for their review. It has some new graphics for displaying project status, like these:





We'll see how it goes!

Posted by rossm at 2:58 PM | Comments (0)

September 27, 2005

AutoCad Upgrade

Thanks to Donna's persistence, the planning department here at the Physical Plant was able to purchase the very latest version of AutoCad - now known as Autodesk Architectural Desktop 2006. (Didn't you like AutoCad better?) Two licenses of AAD 2006 were purchased and installed at the Planning Department, which upgraded our old version (AAD 3.3).

One of the major benefits of this upgrade is that it will allow the planning department to view drawings from any version of AutoCad/AAD. Since they receive drawings from many sources, in many differing versions, this will eliminate the hassle of converting individual drawing files to the 3.3 format.

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

August 30, 2005

More SWAMP Fun - Closing Work Requests

Who doesn't like a good challenge? This week Tom Neal requested a modest modification to the campus-wide Work Request system. He wanted an easy way to track open vs. closed work requests. The goal was to spend a minimal amount of time to keep track of the status of work requests to make sure none of them fall between the cracks.

So, I slapped a new column on the main table - "status", and marked all but a few work requests as 'C' (for closed). In no time, I had a nice view of work requests that were still '' (null = open). Nice.

My idea was to display a list of all open work requests, one record per line, on a form. Each record would have a check box at the end of the record. To close one or more work requests you could check the appropriate box(es) and press a button. Easily said, but boy did I have a time with making it happen. But now it works, and maintaining the "status" should be a breeze. Thanks Will for your advice on handling the array!

Here's a screen shot.

One button click will close the 5 selected work requests.

If you would like to see the code that makes it happen, look for my forthcoming entry under the DHTML and PL/SQL Coding category. I will place some code snippets out there for your review. By adding new code on a regular basis I'll eventually get a nice list of useful code, plus some feedback on improvements or alternative coding ideas from others. Sounds good - look for it!

By the way - did you know that Troy got PHP up and running on our test instance of Oracle 10g? Sweet!

Posted by rossm at 4:23 PM | Comments (0)

August 18, 2005

SWAMP Projects Module Gets a Facelift

Two new areas of functionality are being added to the SWAMP Projects module. The first upgrade is a form that sorts current projects by building, rather than the historical practice of sorting projects by funding source. The new form is 90% done(!), and works - in a beta kind of way. We might add a front page that lets the user select the kind of sort they want on the projects. The front page would allow a user to select the kind of sort they want, and also permit filtering, such as a list of only projects with a given Project Manager, etc. Yes, things can always be made more complex! Right now, the beta version of the building sort works, and the front page portion is just an idea, but a good idea.

The second Projects project is an exerted effort to compile all project financial data under a given funding source, with output being a very good guess of the spending of those funds to date plus the "encumbered" amount of those funds. Then, a compilation of all projects and all funding sources would give a good bird's eye view of the current financial status of all project funding. This has proven to be a tricky devil as it is relatively easy to generate data that is not accurate, and relatively difficult and time consuming to generate data that is accurate. For example, the townhouse project required a 29 page spread sheet to accurately show all the expenditures. Such items as contract expenditures, invoices, materials, in-house labor, planning fees, 1% for art costs, and so on must all be accounted for, and this must be accomplished for all projects. This upgrade is nowhere near even a beta yet, but it's coming along nicely. When done, it will provide a valuable planning tool. This project is an expansion of the management tool described in an earlier post.

Posted by rossm at 11:30 AM | Comments (0)

August 5, 2005

SWAMP Gets Another Facelift

Another project management module is under development for SWAMP.

Funding Status will display a collection of all projects under a particular funding source such as "Capital Repairs 03-05". Managers will have a bird's eye view of total spending for each funding source. This is a necessary, but very tricky, bit of information. Any particular project that comes in over or under budget affects the bottom line. Also, a manager will be able to "drill down" and see the monies spent on any particular project. (This is a very cool feature, but easy to implement since it already exists in the project management module.)

Once again, the flexibility of SWAMP allows for the creation of new modules that build upon the core functionality!

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

June 10, 2005

Physical Plant Website

The Physical Plant website was recently added to the main WOU website. The site contains information on these major areas:

In addition, you will find a comprehensive list of Physical Plant contacts, and of course a link to the Work Request Form.

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

June 2, 2005

Physical Plant Work Request

The Physical Plant Work Request system is "the" way to submit work requests to the physical plant via the web.

Work requests are used for all sorts of things from requesting an estimate for a project such as a remodel to having light bulbs replaced, locks fixed, or chairs set up.

A work request can be created by anyone authorized by their department. You must be entered into the system as an authorized user before you are able to create a work request. If you need help with this, call me at 88046, it takes only a minute or two.

Once you are authorized and log in to the system, you will see a short form to fill out. You must enter a valid "Index to Charge" (in the form ABC123 - no spaces or dashes), and at least one character in the "Description of Work Requested" field. (A more detailed description of the work you need done is appreciated, of course.)

You can print a copy of your work request using the button at the bottom of the page. Upon successful submission of a work request, you will be given a distinct work order number.

That's it! You have successfully submitted your request, and it will be promptly routed to the appropriate personnel for action.

Posted by rossm at 4:04 PM | Comments (0)