Calls May Be Recorded for Quality Assurance: Using MultiValue to Manage Multimedia

The Challenge

Sunrise Credit was facing a technical challenge. They had everything they needed to succeed — the data, the audio files, the technology, the people — but none of it was organized for ease of use. It was my task to take MVON Express and use it to bring order to the various parts.

The goal was simple: produce a two page website for a select number of internal experts. The first page would allow them to login and trigger a query. The second page would display the results. Sounds easy, right? The devil, as you know, is in the details.

The data for this project was in CSV (Comma-separated value) format. There are tools for treating CSV as a database. There are tools for populating virtually any database from a CSV source. Half a day to load and test… we should be ready for our next task by lunchtime. Except that the CSV source is thousands and thousands of separate CSVs. And, by the way, each one is embedded in its own ZIP file. Oh, and there will be at least a dozen new CSVs every business day.

Of course, since this is a website, we are looking for a fairly fast response. The database needs to be fast to update and fast to query. Over time, the queries will need to get more sophisticated. Looking down the road, reporting, not just interactive use, may be in the cards. We need a built-to-last approach. And we need to develop quickly.

As luck would have it, Dawn Wolthius called me as I was starting the project and asked if I'd like to be part of the first tour of the MVON Express product. The timing was too good to ignore. Here are the details on how we built a mature system for selectively monitoring calls for quality assurance in just a few days.

Fig. 1

Fig. 2

The Approach

John Ocampo, the head of IT at Sunrise, gave me permission to use whatever tech made sense. This is a rare and wonderful gift. He and Anthony Vidas found me a server, rebuilt it with a reasonably current version of Windows and let me loose.

We installed Apache and PHP using XAMPP. We added the current build of MVON Express. We added MS SQL Server Express. We installed multiple web browsers for testing. We had the tools.

The server had access to two network shares: "Archive" and "Available." Archive would contain the entire library of recordings in pristine form. Available would be the copies I could unzip, rezip, rip apart, extract CSVs from, and organize how I needed them to make the site work at a reasonable speed.

The Unexpected Ally

Perry Stauffer came into the picture at this point. Because we were the very first site for MVON Express, he took a direct interest in watching our progress. It didn't take long for him to trade watching for working. Once his sleeves were rolled up, things moved forward rapidly.

What We Did — All the Tech We Used on the Back End

Windows BAT files are a very efficient way to get a lot of simple work done. We built a BAT unzip, extract the CSV, and rezip each ZIP on the Available share. That moved us from a flat layout (every ZIP in the same directory) to a functional layout of calltype>yyyymm. Now, to find a call from February for call type ABC, you'd look in S:\ABC\201602\. Reducing the size of each directory by splitting them in a way which matched the query logic (call type is the most important break) made Windows happier — fewer files per folder — and made the storage match the retrieval.

I then wrote a small Delphi program — it could have been batch, but I liked the extra speed of an executable — which grabbed all the CSVs from each directory. Since the CSVs were now outside of the ZIPs, it was a matter of copying a text file to a new directory. This is where MVON enters the picture. The destination directory C:\Ongroup\MVON\RAWINDEX, is visible to MVON as a file called RAWINDEX in the SOUNDWAVE.OS account. We wrote a quick mvBASIC program to collect the CSVs and add them to a single MV database file called CALL_REVIEW. Because this is MVON, we had the option of making CALL_REVIEW an MS SQL Express table. That was the fastest option in terms of building the file and retrieving it.

Perry provided an example of how to make all of that work (See sidebar at http://www.intl-spectrum.com/resource/685/default.aspx ).

For those keeping score, we now had a single place to go when we wanted to query the contents of any of the CSVs. Because of how we did it, we had the option of pulling the data back using MV or SQL technologies. And both of those options would be quick. That left us with the work of building the web pages. There are a lot of ways to connect web pages to MV data. There are even more ways to do it with SQL. We chose none of them.

Kevin King taught me a trick during one of his International Spectrum classes. It allows the web developer who is ignorant of MV to still be very productive. We used Kevin's two-bucket solution for this project. Bucket one is a Windows directory called REQUEST which MVON Express sees as an MV file called REQUEST. Unsurprisingly, you put your requests from the web in that one. The other is called RESPONSE — in Windows and in MVON Express — and gets the responses back from the database. While I can't give you the code we used for this example, I can give you code which is fairly close. The example below is fully functional. We just stripped out the project-specific bits [Figure 3].

execute 'SELECT REQUEST'
TODO.rec = ''
done = 0
loop
readnext REQUEST.id else done = 1
until done do
read REQUEST.rec from REQUEST.file, REQUEST.id then
TODO.rec<-1> = REQUEST.id : @VM : REQUEST.rec<1>
end
repeat
*
loop
until TODO = '' do
RESPONSE.id = TODO<1,1>
CRITERIA = TODO<1,2>
TODO.rec = delete(TODO.rec,1)
gosub respond
end
*
STOP;* Logical end of program

Fig.3

What We Did — All the Tech We Used on the Front End

The site was done in HTML, CSS, Javascript, and PHP. Since the PHP bits handled all of the interaction with the data, let's cover that in a bit more depth [Figure 4].

$nugget .= "|" . $YYYYMMDD . '|' . $skill . '|' . $secondary . "|" . $secvalue . "|";
file_put_contents($TheRequestName,$nugget);
sleep(1);
$guardian = 120; // 120 attempts
while (!file_exists($TheResponseName) && $guardian > 0) {sleep(1); $guardian -= 1;}
$TheCSVData = explode('^',file_get_contents($TheResponseName));

Fig. 4

As you can see, the code isn't very complicated and it is unaware of the underlying database technology.

Final Thoughts

To accomplish this project, we used MVON Express databases, an MS SQL Express database, HTML, CSS, Javascript, PHP, Delphi, batch files, and mvBASIC. That's not counting the O/S, multiple web browsers, 7-Zip, and other assumed tech. We did it with one developer (plus some of Perry's time) and some active support in setting things up. Tech is less and less about using one skill set. Adding MVON Express to this project improved the speed of completion and added to the ease of maintenance.

Featured:

Mar/Apr 2016