Shop Schedule (Bison Transport Software, Part 3 of 10)

At Bison, we run a pretty large maintenance shop and try to do most maintenance work in house.  Our main vertical app for the shop is a product called Maximo, by MRO which is now owned by IBM.  Honestly, its an average piece of software at best.  It has a lot of limitations, including the fact that it connects to an oracle database (we are not pro Oracle here).

Anyway.  One thing Maximo doesn't do at all is scheduling (unless you count the 2 fields called scheddstart and schedcomplete).  So, we set out to create our own.  It took a few iterations to come up with something that worked, partly because we didn't fully understand how the shop operated and partly because the shop didn't know what it wanted.

The shop schedule program, basically, present workorders in 3 different stages, and those stages really determine how the shop interacts with the work.  These 3 statuses are are separated into the 3 grids making up the left side of the screen.

First we a list of unscheduled workorders.  This is everything from campaigns to emergency work to deferred work to upcoming PM's.  For PM type workorders, the system will use a combination of the last recorded odometer, plus the mileage since them from our TMWSuite program to build an estimated mileage.  The shop planners then use those estimated odometers to know roughly when to schedule equipment for maintenance.  They can then double-click a workorder to plan it.

 

One nice feature is that the user can now see not just that workorder, but all available work for that equipment.  So the trailer might be coming in for a PM, but they can see that other repairs are needed and schedule those at the same time.

Next is the scheduled workorders.  These are the workorders that have already been given a scheduled start/end time and a bay the work will likely be performed in. 

And finally we have the In Progress workorders.  These are the workorders that are currently currently being serviced by mechanics on the floor. 

The right side of the window is dominated by a large colored grid.  Each of those columns represents a bay in our shop.  And each of those colored blocks is a piece of equipment being serviced.

One thing to note about our shop schedule program is that most of its functionality is just grabbing information from our maintenance and dispatch systems and presenting them to the user in a way that makes their job easier.  There are very few fields on the Maximo workorder table that we need to modify, the scheduled start, the scheduled complete, the bay# the equipment is planned into and maybe a couple more to record who changed the plan and when.

Some other notable features include a view of incoming equipment, so a planner who needed to get a PM done on a trailer would see when the trailer was coming into Winnipeg and how it was going to be available and could decide if the work should be done.  There is also a screen to track work at outside repair shops and integration into a custom "followup" application for keeping track of communications with drivers, dispatch and the outside repair companies.

US Inbound (Bison Transport Software, Part 2 of 10)

We've been using TMWSuite since April of 1999, and back then, there were a lot features we'd become used to in our old software that just didn't exist in the new program.  Some were features of the old software and some were programs we developed.

One of the first programs we created was our US Inbound tool (also known affectionately by our users as "The Toaster", a reference to the icon it used to have - you'll notice in the screenshot below that the window title still says "Powersuite Toaster").

Anyway.  As a Canadian trucking company, one of the issues we face is getting our drivers back out of the US.  We need to find drivers loads that go direct from USA to Canada.  No intermediate trips allowed.  Depending on where in the US their trip took them, this can prove to be very easy or quite difficult.

This program will show our US customer service reps and planners what drivers are planned down into the US for the next 5 days.  They then need to find trips back into Canada for these drivers.  Notes are entered by supervisors into a window at the top, sometimes directing CSR's and planners on what to do with the drivers. 

Once drivers are planned back into Canada, they change color or drop off the list entirely, depending on a users settings.  Drivers can be reserved too, if someone knows that a trip will be coming available shortly and we need power.

This program also ties into our LegUp program which pulls data from multiple systems to try and find us the most profitable trips back.  I'll discuss that program in a future entry.

Ruby and SQL Server

Just some quick notes for me to remember. When using DBI to connect to SQL Server 2005, use a connection string like:

db=DBI.connect("DBI:ADO:Provider=SQLNCLI;Data Source=.\\SQLEXPRESS;Initial Catalog=databasename;User Id=user;Password=password")

I also ran into a problem with the ADO.rb file. I was getting the following error message when trying to run a second query:

OLE error code:80004005 in Microsoft OLE DB Provider for SQL Server
Cannot create new connection because in manual or distributed transaction mode.

It was starting transactions automatically. I'd rather do that explicitly anyways, so i created a new begin method and commented out all the default BeginTrans() calls. Seems to have solved the problem.

Appointment Queue (Bison Transport Software, Part 1 of 10)

Here at Bison Transport, we use a pretty diverse mix of purchased software and software developed in house.  As a trucking company, our primary concern is the moving of freight and paying of drivers, and the basis for that is TMW Suite from TMW Systems. Their TMW Suite is pretty much the cadillac of trucking software.  Its Windows based and runs on SQL Server.  But as good as is, it doesn't do everything we need.  It has, however, a great relational model and is easy to expand on.

One of the more interesting applications we're created is our Appointment Queue (AQ).  When we started writing AQ a few years ago, TMWSuite didn't have a way of determining whether the dates and times on pickups and deliveries were actually times agreed to by a customer, or just something our customer service people entered because it seemed like a good time.  And, our EDI requirements with quite a few of our customers require us to send pickup and delivery appointments.  So, AQ was devised and created over a period of a couple weeks.

The key feature of AQ is indeed the queue.  In fact, there are dozens on them.  Pretty much one per person.  Although we try not to name them that way because users change jobs and call in sick like to swap shifts.  And our users have crazy requirement for who books the appointments.  User A might book all the appointments for a customer except the ones out of California and Oregon, because those are handled by User B.  And appointments out of Eastern Canada are all handled by User C unless they delivery on a weekend, in which case User A must book it.  And it sometimes gets worse.  But it handles it. 

So, let me explain quickly how it works.  A user will pull up his/her queue which will show all deliveries that are waiting to be booked over the next 4-5 days (configurable, but most users keep to the immediate future).  Ones in an orange color are late, green are appointments that must be booked today and blue are for high priority loads.  The user can scroll down the list on the left and view some key information on the right side (like address, delivery instructions, hours of operation).  They can also flag record that don't require appointments.  To book an actual appointment, they double-click a record and the booking screen comes up.

On this booking screen, they would see contact name and phone number.  And once they placed the call, they would enter the appointment times and Set Appointment.  If they couldn't reach the contact, they could also click Try Again and enter a reason why the appointment couldn't be booked (busy, out of the office, etc).  This screen also shows any prior appointments or attempts to book an appointment.  They would also be able to see if there were other delivery events on that trip, and whether they were booked or not.

The window would close and if the appointment was booked, it would drop off their screen.

Creating Local Software with Ruby on Rails

Except for a year long foray into ColdFusion, and some dabbling with asp.net, I've spent the last 20 or so years writing DOS and Windows software that run on the client. Back in the early 90s, i wrote applications in Clipper. In the 2000s, it was Delphi. Actually, it's still mostly Delphi.

But i'm finding Ruby appealing. Maybe its the codeblocks. Reminds me of clipper. Well, just the { || ... } part. I don't know enough about Ruby or remember enough about Clipper to know if they are even remotely similar. I digress.

I've been toying with a very simple application, the one people always seem to write in whatever language they are playing with. A todo list. And it's fine and it works and if i spent more than 30 minutes on it, it might look better than a your most basic rails app. But it works. Heck, if i prettied it up i might even remember to use it.

Anyways, i won't. I dont like running useful programs in a web browser. And i dont have a webserver to host it on. And i really don't want to remember to start up mongrel to access my todos. And i don't want to run it as a service since i can count on one arm the number of times I'm likely to use it.

Which brings us to the whole point of this. I wrote a little Delphi program that solves some of the issues i have with these web tools. Lets sum up what it does:

1) It starts the mongrel server in the background with a CreateProcess()
2) It has a simple IE window embedded in it, and points it to the mongrel web server
3) It closes the mongrel server when you close the window.

It's painfully basic right now. It just runs the 'ruby script/server' in whatever directory its in. And the web browser just points to localhost:3000. But there is no reason why it couldn't be a little smarter.

But who knows, this may be a complete waste of time. We'll see if i find a good use for it.

MacBookPro

I've been feeling kinda blase about computers lately, so I got a MacBookPro for myself, hoping to have something new and interesting to learn.

I tried, i really really tried, to like OSX. I tried to be creative and take video of my kids and my dog and download them into iMovie and upload them somewhere so all the extended family could see wonderful stuff. But the mac wouldn't download from the camera. I thought I'd take some pictures and bring them into iPhoto and sync them to Flickr, but no, the CF Card reader i had didnt want to work with the mac. I had some old home movies that I thought I'd edit with iMovie, but alas, they were in WMV format and while i managed to find some software to convert them to .mov, it didn't do a very good job. And i tried to like Parallels and install vista and work on a VM, but for the life of me, i couldn't get the Blackberry Java Dev Tool thing to install...kept telling me I wasn't an admin of the machine.

And there are just some things i took for granted on Windows, things i just know how to do. Like mapping a drive. Like writing a batch file. And I'm not saying you cant do those things on the mac, i'm sure you can. And I'm sure some people will think the mac way is better. I'd like to agree, but I couldn't figure out the mac way. And I think i'm pretty handy with google.

Anyways, I lasted exactly 8 days before I installed bootcamp and Vista. I left the OSX partition there, just in case i want to check out the OSX-y-goodness, but honestly, i probably will never bother.

That all said, the hardware is sweet.

Banking

Another problem I've decided to tackle for myself is a program to analyze my spending.  I've used both Money and Quicken in the past, but they really were overly complicated for what i need.  I don't need to track my current balances.  I don't really need to know that I transferred money between accounts.  What I do need to know is where the hell all my money goes every week.

And KnowBal is born.  It will import OFX files (the MS Money format my bank uses) and categorize (tag?) them based on previous categories.  It will even do some renaming to strip off the transaction confirmation numbers my bank adds to the vendor name when maybe payments via their web site.

I will post screenshots and a downloadable version once i get some of the charting and analysis tools cleaned up.

Stay tuned.