|
Problem:
Client receives nightly inventory uploads from several dozen vendors. Each
vendor has their own field order and data format (Excel, dBase, CSV,
tab-delimited, etc.) Manually importing each file is tedious and
time-consuming.
Solution:
First, design a database to hold the data, then:
Ask each vendor to e-mail their inventory to a special mailbox which forwards
the data off to a Perl script for processing. The script parses the e-mail,
locating any attachments or inline data. If valid inventory data is found, we
archive yesterdays inventory for this vendor, then validate and insert current
data.
Problem:
We have all this great data, how do we use it?
Solution:
An instant messenger robot, of course! The 'bot is an automated AIM agent (using the Net::AIM Perl module)
which allows customers to interactively query current inventory and prices as well as retrieve information about
the various vendors. This client has a sense of humour, so the 'bot was programmed with a few extra's, as well
as a somewhat wise-cracking persona.
Upon request, I can arrange for you to "speak" with our robot.
This industry happens to utilize instant messaging to communicate, but we also built out a traditional web
interface to query the data, along with custom reports to:
- Find "hot" items.
- Find over and underpriced items.
- Various other automated reports.
I wrote a couple of Perl modules and some PHP classes to facilitate faster rollout
of new reports.
The project also included a web interface to add, remove and edit vendors.
Technologies Used:
Perl, SQL, MySQL, PHP, CSS, JavaScript
I used many Perl modules in this project including:
- DBI
- Net::AIM (modified)
- Text::ParseWords
- Text::CSV
- MIME::Parser
- Spreadsheet::ParseExcel
- Spreadsheet::WriteExcel
- File::Basename
- XBase
- Date::Calc
All of these modules are available at the Comprehensive Perl Archive Network (CPAN)
Thanks to the developers of those modules for doing the hard part!
|