John Croson's Blog Home

Image via CrunchBase Reporting in OpenAir has presented our company with some challenges. As such, I was asked to take a look at how we might be able to create an ETL of this data , create our reports and automatically distribute them to our project managers. This is how I did it. First we needed to purchase OpenAir's Automated Backup service. This service will export data in text format or MySQL dumps of any or all tables in their database, zip them up and deposit them anywhere via FTP, FTPS , or SFTP . We set up an FTP server for our repository in the DMZ. Once this bit was done, I needed an installation of MySQL Server and a method to programmatically pull the data from the DMZ, deposit it on a Windows share, unzip and insert the data. Below is a batch file used for this purpose. @ECHO On SETLOCAL ENABLEDELAYEDEXPANSION @SET _LOGFILE="C:\Export\LOGS\OpenAirMysqlImports.txt" @SET _MYSQL="C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe...

Most healthcare professionals know at this point that all providers of health care, require NPI (National Provider Identifier) numbers. Without one, it will become increasingly difficult for claims to be paid by commercial payers, and impossible to collect medicare and medicaid payments. Since we are a coding/billing/collection/management agency, we have frequented the NPPES (National Plan & Provider Enumeration System) to lookup NPI information. Unfortunately, there have been brief periods of downtime of the site, causing us to implement our own solution: a backup of the registry. I have a daily cron job that downloads the NPI database, push it into MySQL, giving us an albeit slow, but accurate access to an off-line version of this data. The shell script below performs the retrieval: #!/bin/sh WORKDIR="/srv/htdocs/npi" LOG="$WORKDIR/npi.log" MONTH=`date +%b` LASTMONTH=`date +%b --date='1 month ago'` YEAR=`date +%Y` URL="http://nppesdata.cms.h...

Much has been going on in my life. My wife had foot surgery, which is good and bad. Good because when we go on walks with the pooch [ I've been screwing around with more PHP and MySQL and want to do more! I like it, Mikey, I really do! We have a public calendaring program written in PHP from Able Design . It can be seen in action here . We have [or should I say 'had'] about 5 or 6 pages of static text describing our exhibitions, and it was getting tedious to update. Also a little rediculous to do both the calendar and static pages...especially when the shows change so much...sooooo I wrote a little script on each exhibition page to pull that data from the calendar, and format it correctly on the page. All the folks that input it have to do is conform to some input criteria, and it works great. Maybe some day I'll get around to modifying the forms and db.... $today = getdate(); $year = $today[year]; $currdate = $today[mday]." "; ...