Orion

I’ve been through several major revisions of my original in-house mobile app that I developed for my day job (see original post) and wanted to give an update on the current state of the project.

I count a major revision (not a point release) based on the amount of code changed and aesthetics together. I’ve learned a great deal about employee usage and needs since my original implementation and have also enhanced my ability to create efficient code. Since so much of the application revolves around live data (real-time tracking of stock levels for example), the number of database calls that I can’t cache into memory is rather high. I also feel the need to match the aesthetics to what that employee may be used to when using apps on their own smartphone while retaining usability for this specific application.

The application now has lots of visual cues (e.g. color changes) to alert the user of low stock levels, out of stock items, etc. I also handle extremely large orders (or small orders should the user choose this option for those as well) by allowing the order puller to go into a single item mode. This gives all information that the puller needs for the next item in the queue, allowing them to manually bypass it if needed and come back to it. This way the database queries only pertain to that single item thus making the application perform much faster.

My ultimate goal aside from giving employees what they need for day to day operations has been to make this (mostly) web application look and behave like a native application. I’m pleased with the results and where the current version 6.2 stands…especially when compared to early versions.

2014-10-06 08.47.30 2014-10-06 08.47.20 2014-10-06 08.47.05 2014-10-06 08.46.24 2014-10-06 08.46.07

Creating a custom company dashboard

This isn’t really going to be a post about HOW to create a custom company dashboard, but just an insight into how I did this for the company I work for.

A couple of years ago we migrated from a UNIX based ERP system that was developed in the early 90’s to something MUCH more modern…one that used SQL meaning I could do anything I wanted with the information inside.

I sort of fell into becoming a programmer, and I’m still not sure I would call myself that. I spend over half of my time ‘programming’ now and every day is a day that I learn something new! Each day I get frustrated as I can’t get something to work…then eventually figure it out, giving me a programmer’s high…then I fall on my face again because I run into a new problem. The cycle loops day in and day out which is pretty much the norm for most non-ninja like programmers.

I am DEFINITELY not a ninja programmer, but I enjoy it and I get things done. I’ve made programs that surprised my employer, my peers and most certainly me. Each app that I build for the company I work for builds on the last. Eventually I ended up with the need group these into an easily viewed format. Lots of my programs are just links on our intranet site…things like ZIP code lookup, customer lookup, shipment tracking, and countless others. What makes up the rest are one’s that are nothing but glorified reports.

I’m a big fan of the paperless office. I’m paperless myself (unless the government requires me to keep a paper copy) and I do my best to force my paperless lifestyle on others. I’m in a position where I can wield my powers in that direction a bit, but I still get lots of opposition. One way to combat this is to make real-time reports available for certain levels of management. This is something that you can’t do with paper, but you can do when you’re viewing it on a screen.

Behold my creation (a never-ending work in progress):

Screen_Shot_2013-09-17_at_4.01.37_PM

I apologize for so much pixelation…I definitely want to protect my employer (and myself). There are more pages as you can tell by the links in the upper right-hand corner of the page, but I would have to blur out even more of those pages since they’re so data-driven.

90% of what I create is done in PHP. For good or bad, I try to avoid Javascript like the plague. Part of that is my lack of more than basic knowledge of it (I do use it, just sparingly) and part wanting to keep it structured to one language for performance, code-management, whatever.

With the dashboard above, I’m using ajax to poll for updates to certain blocks like orders shipped. You can just keep the dashboard open all day and watch shipments happen or the customer count get larger in real-time. I know ajax isn’t really new and lots of folks use it these days, but you can imagine my excitement (and amazement) when my non-ninja like programming skills made this work like clockwork.

I’m now garnishing some of my older apps with ajax and hope to eventually put it in my mobile inventory and order management system that was my first major creation (and is still my biggest).

I wanted the dashboard to be easy to look at and be color-coded in some manner (hard to tell from the pixelation, but there is a method to the colors). I’m also authenticating against Active Directory which simplifies the authentication process for those who use it. I also have modules grouped into type so that I can set permissions for each user. I have to do this by editing the database directly at the moment, but I’m fine with that since we’re a relatively small company (~150 employees with only a handful using the dashboard above).

The dashboard is completely responsive so you can view it on an smartphone or a 30″ display. I’m using a heavily customized Twitter Bootstrap 2.3 template. Looking back, I probably did a lot of things incorrectly when customizing it that will haunt me later, but for now I’m not worrying about it. I run into that a lot with my ever-evolving skills and have to constantly go back and make things better. I want my creations to be the best they can be. My work is a reflection of me and I want the best content cover to cover that is possible.

I’m definitely proud of this one and think it’s a good reflection of my style. Ultimately the data is what is important here and I think I’ve made a few folks happy by giving them what was not possible with paper and simplifying the process to obtaining the information they want to see the most. I want to give my employer the tools it needs to succeed so that I can ultimately succeed. It’s a good feeling.

After several thousand orders processed and v2 deployed…

There were a few issues with the first version of my mobile order picking software, mostly revolving around my initial choice of avoiding using a real database and instead using a text file(s) as a database. With v2 I chose to completely revamp my mobile software suite and go with a MySQL database. This meant architecting several tables and attempting to predict what I would want to use in the future to extrapolate said data.

I chose to revamp the entire suite to use MySQL so I needed a database to keep a log of bin location changes, all order information, restock ‘to-do’ list items, and a user database. For my mobile order picking system I completely rewrote the code I had to read information from our core ERP database and place it into my own MySQL database for manipulation. This allows me to only store the information I need and in the manner I wish to store it for easy access.

Without going over the details of exactly how I’m doing what I’m doing (I wish to keep some things a secret anyway since it is my code), I’ll explain what gets displayed and how it is an improvement over the first version.

I’m now able to display individuals items and component items that make up a kit in-line and according to bin location. This makes picking items for an order extremely efficient since everything is in order of physical location. I also show additional bin locations for component items now. Component items are one of the most complex things I’ve worked with since you must first find out what items belong in a kit, extrapolate it, get information for the individual component items (e.g. quantity available, locations, etc) and then merge it into the master item array for that order. Maybe this is easily done for others, but I had a difficult time with it but in the end I prevailed! I also show what kit # each component item is a part of and of course do the math so that the correct quantity is picked (e.g. if someone orders three of the same kit with two items where one item has a quantity of one and the other item has a quantity of 4).

I’m also keeping track of whether or not we’re out of something on the shelf when it says we have it in the computer. This shouldn’t happen I know, but it does so I allow the user on the floor to make the needed adjustment and offer a quantity change report for such changes.

Something that I feel is really neat (but very simple to add) is that the remaining items to pick is always at the top of the screen now. This way the order puller is always reminded of how many items are left before the order is complete, which I automatically mark as soon as the last item is picked.

There are many little enhancements and additions to my program like auto time-stamping when the order was scanned and when the last item from the order was picked which is useful for some analytics that I’ll get to below.

The restock list also got a complete revamp and is now multi-user and has three modes when scanning an item. The first scan will add it to the list, the second scan will mark it in red and scanning again will remove it from the list since that is the step that the restocker would have restocked the shelf.

Since I’m using MySQL now, I can extrapolate all kinds of data from it to do some really cool things. To do this I needed to come up with an authentication system requiring managers to login since I don’t want just anyone to have access to such information. I’m using the same user database that I use for other parts of the suite…I just give access to the admin panel to mangers’ login. Once logged in I have the ability to access order picking records for all orders picked or just an individual picker for example. I added pagination to this since there are so many records (and for database load reasons obviously). Bin location change records can also be accessed from this system.

The thing that I’m most proud of is my latest addition however. I’ve been working on this for the last couple of days and I think it will prove to be really helpful! Managers can now see performance analytics for order pickers and salespersons. I use the Google chart API to display the data extrapolated from the MySQL database. This allows a manager to quickly gauge the performance of an employee which happens to be the number of items that that employee can process in any given hour for the last 30 working business days. Here’s what that chart looks like:

It’s pretty easy to see who may be slacking off or what adjustments are needed. Granted that different users may have slightly different roles and can only be compared to others in a similar grouping, but this allows a manager to do this and it’s the first time such thing has been possible in our environment.

Going forward I hope to add to this suite based on input from users and managers but within reason. I am the IT department for a mid-sized company. There are no other full-time employees that I can delegate tasks to and that’s ok because I love what I do, but it also means that I have limited resources to be able to handle all that needs handling. This is one reason I’m very excited about SaaS as it allows me to offload some tasks like email server upkeep so that I can spend more time on projects like this one.

I’m certainly not a master programmer and probably not a master at any one thing, but what I am is confident that I can excel at being the IT department for ~160 employees with around 120 workstations with all the infrastructure in between. I love making IT easier for folks while at the same time giving them better tools to do their jobs better and from the place they feel most comfortable. I find it truly awesome that I can jump on my Mac at home or Starbucks and do probably 90% of what I can do in my physical office. The other 10% would be infrastructure failures which are rare due to the nature of such infrastructure and the redundancy built-in. Having automatic fail-overs for nearly all servers and a large percentage of user’s workstations (e.g. thin clients) makes this entirely possible.

Thank you technology for evolving at a fast enough pace to keep me on my feet and eager for the next day!

Company mobile project – launch day!

While it hasn’t been my only project, it has been a major side project of mine for most of 2012 thus far. The mobile project for my day job should be going live today in that more than a couple employees will start using these devices for order pulling and inventory management.

I’ve included a couple of screenshots in the past, but the project has evolved and thus some applications changed slightly (added features for example) so I’ve included a gallery of most applications below. Not all screens are shown, but it gives you an idea of why my application suite does. Not in the gallery is the order pulling application which has largely remained unchanged from the screenshot in my first post, though I have resolved several bugs that came up in rare occurrences and added a few minor enhancements.

I’m not really doing anything too fancy here. The only time I write back to our main database is for bin location changes, but that is immensely helpful for those who use it. To change the first bin location in our ERP system, for example, would require that the change be made in three different windows. This would also require the inventory manager (or whomever) to write down the part number and new bin location(s). With my device the change can be made on the spot (made even quicker if the user scans the barcode for the part) and have the bin changed in all needed parts of the database instantly. Doing this just a few times saves a drastic amount of time.

I’ve also coded a custom to-do list which is used for stock management. If the inventory manager sees that an item in our primary bin location is getting low, he/she can scan the item’s barcode and have it added to the list. It will let them know where to go to get additional stock (additional bin locations to pull from) and when the item is rescanned will mark it as ‘done’.

The device itself has been ruggedized with a heavily padded industrial case. We’re working on concrete here so I want to take every precaution that I can to ensure these devices stay in good condition. We also have holsters and laniards for those who work in a risky area or use the devices on forklifts for example.

I’m really pleased with everything overall. I’ve learned a great deal through this project and I’m proud to say that I was able to pull the hardware together to work with several software languages (PHP, Javascript and Objective-C). I’m far, FAR, from a ninja, but I’m certainly proud of my work. I’m a little sad that the bulk of this project is over. Sure, I will continue to enhance the applications and possibly write applications for other uses in our environment, but what I’ve written is the most needed for what we do and thus is the bulk of what is useful for employees.

A follow-up to my in-house ERP integrated web app…

I had mentioned in my previous post that I was using a third-party app from the iTunes App Store to interface between my web application and the Linea Pro 4 scanner. A couple of days ago IPCMobile gave me access to the SDK and yesterday I spent the day writing a simple iOS application using that SDK.

What I created was something much like the SwipeTrack application that was being used previously but I took out some features we didn’t need and even added a couple of my own that I thought would be useful. The application is basically a UIWebView with a UIToolbar. I have a Settings.Bundle that gives the Linea’s firmware version, serial number, etc. and a place to put the default URL that UIWebView uses when loading the app (obviously this would point to the aforementioned web app).

I added an activity monitor for web traffic so that the user knows that the server is still gathering the required info and displaying it on the screen. This is helpful for large orders where lots of SQL queries are made for example. I also streamlined things by only including what we needed and thus the application loads very fast.

The best part of this is that I no longer have to purchase the app for each device at $50/each. We’re still waiting on IPCMobile to get backorders of their industrial rubber case for the scanners so we still only have two Linea Pro 4’s…one actively used all day every day for pulling orders and the one I use for development.

I’ve definitely had a lot of fun making these apps and they have most certainly enriched my abilities and creativity. Even with having applications in the iTunes App Store and in an ad-hoc environment, I’m not sure that I could dub myself a real programmer. I still have to do research on things in all of the languages used (PHP, Javascript, Objective-C), but it’s a nice thought to know that something that was created from scratch is now being used to increase the productivity and accuracy of a medium-sized company.

In-house programming insights…

When I was 12 I began flipping through my first real programming book (BASIC doesn’t count): Teach Yourself C in 21 Days. Without a doubt I can say that I did not learn C in 21 days. I wouldn’t really pick up programming again until I was 28 and I couldn’t expect myself to remember anything that I had read regarding C when I was 12.

When I came back to programming at 28 I went for the platform that I love, iOS. I released two apps in the Apple app store in 2011. One a nightstand/weather app that I developed after a couple of years of not being able to find what I wanted from another developer (imagine that considering all of the apps in the app store these days!) and the other a tipping calculator that was really just something to keep my mind challenged.

I’ve sold several hundred copies of my nightstand app since it was introduced in mid-2011 and while that hasn’t made me rich or an acquisition target for Google, it does pay for my $99/annual Apple developer’s fee and part of my Starbucks habit.

Before I went on Christmas vacation for my day job I decided to start on a new project that took some of my newly found knowledge and apply it to something that could help the company be more efficient and ultimately help reduce errors and keep costs down.

I looked into doing this with Objective-C, but the database that we use doesn’t have a driver with any SDK available for iOS that I could find. It does support ODBC though, which made making this a web app the best solution.

The first step was giving me a connection to the database to work with of course. This ended up being a Windows 7 virtual machine (I converted our physical servers and lots of desktops over to vSphere in 2011) running IIS, PHP and the client for our database engine with the ODBC connections in place.

My place of work manufactures thousands of automotive parts and we just moved to a ODBC/SQL based ERP solution in 2011 to help increase our efficiency and enhance our processes going forward. In 2011 I deployed location-wide wifi by way of ~20 access points. That’s another feat in and of itself since you have to deal with channel interference between the AP’s and contend with other interferences around. This gives us the framework to move around between office/manufacturing/warehouse locations without so much as a hiccup in connectivity to our back-end resources.

I looked into a third-party solution with Motorola handheld computer/scanners that integrated directly with our ERP system, but because I’m so adamant about iOS and web based technologies driving the future of computing (even in the workplace), I decided that it would be a waste of money and resources to implement such a solution. The cost was astronomical and I set myself on a path to see if I could come up with a self-programmed alternative solution.

My first solution was the simpler of the two that I had on my plate. Inventory look-up. Since we have so many parts and locations, it becomes an issue in locating where a certain item is and more so checking the quantity that is on hand at any given time during the day. Traditionally we have workstations placed throughout to do these lookups which require a constant back and forth when looking up items. We could use laptops, but it adds heft and bulk that is over the top IMO.

Using a combination of PHP, HTML and Javascript, I was able to create a simple web app to do this lookup. It makes sure the part number entered or scanned exists in our system and if it does it displays the locations where that item exist and the total quantity on hand:

My next ask would bring a level of complexity that required me to really learn some more advanced PHP skills while I made the program. This is really my first real dealings with PHP to create a complete application, so my knowledge was fairly minimal when I started the project. To create my next application which would create an order picking system would test my endurance.

Several things were needed for order pullers using the application. For the above inventory lookup solution and this one I chose to go with the latest 4th generation iPod Touch. This would be coupled the Linea Pro 4 2D scanner from Infinite Peripherals. This is the same barcode scanning case that Apple uses in their stores. I also needed a way for my web app to talk to the Linea Pro 4. SwipeTrack Solutions has a program that does this so that’s what I ended up going with.

The application itself had a host of its own requirements. The order number, order date, salesperson, item # to be pulled, quantity to be pulled, quantity currently available in our warehouse(s) and where the item is located in our warehouse(s).

In addition, the puller would need to know that an item had already been pulled and have it automatically crossed off of the list so to speak. To do this part of the application I had to decide if I wanted to go with a flat file or a database. In the end I chose that a flat file was the easiest method to implement and has the easiest upkeep for what I’m using it for.

When a puller scans the barcode of the order’s picking ticket or enters it manually, the order is brought up on the device and lists all of the items and their quantity with location(s) in order of location to make their pulling as efficient as possible. The puller scans a barcode (2D Data Matrix type is what I went with) at the item’s location or on the item itself and physically pulls the item and places it into his or her cart. If the wrong item is scanned the puller is presented with an error and must manually continue the pulling process. This eliminates errors of the wrong item being pulled for the customer.

If the right item is scanned then the item number is appended to a file that has the order number as the file name. If the order doesn’t exist and the first item from the order was scanned, the order file is created and thus tells the program that the order is in the process of being pulled and places a notification at the top of the web app of that.

On each refresh of the page (happens each time an item is scanned, right or wrong) the application checks to see if an item in the order exists in the order file that was created. If it exists then that of course means that the item was pulled (because it was scanned) and I have it turn a different color and place a *P* (for pulled) beside of the item number. On each refresh the puller is taken back to the same spot on the order that he was at previously…this way he or she doesn’t have to scroll down the page to find where they left off. This is helpful on very large orders.

Here is what the end result looks like for an example order:

It’s simple on the surface, but I ran into several issues that I had to overcome. There are arrays within arrays which confused me to no end, but eventually I came up with a solution. I’m pulling from fairly complex SQL queries with joins, ordering, etc which also baffled me in the beginning. I’ve learned a lot about writing SQL queries over the last couple of weeks which I’ll no doubt reap benefits from for years to come.

We’re in the testing stages and have one puller using the app full-time now. It dazzles me to no end that something that I created, from scratch, has been worthwhile enough for the company to request a dozen or so of these things eventually after the test phase is over. At the same time, it is a scary thought to know that something that was created in spare time will end up being an essential part of doing business and will no doubt add another level of stress to my already growing levels.

Being the lone IT person for a medium sized company is daunting. I’m given freedom to do projects like this one, implement things like virtual machines and thin clients and other goodies, but in the end I have to support those technologies and it is forever becoming more challenging to keep up with.

I am always up for a good challenge however. Stuff like this makes me want to come back day after day, especially when others pronounce their appreciation for such hard work.