The Finalized Travel Expense Report

The Finalized Travel Expense Report

A few hours of work, and you've got what you see in Figure 22-9.

Figure 22-9

It's a first pass, so start with how to feed the Smarty template in file travel-expenses.php, which is the user's landing point if coming in from the main menu:

   <?php    require_once ("lib/common.php");    require_once ("lib/expense.phpm");    // is the user logged in?    if (!$session->isLoggedIn()) {        rect ("index.php");    }    $user = $session->getUserObject();    $week = new TravelExpenseWeek (        array ('emp_id'           => $user->id,               'week_start'       => getCurrentStartWeek(),               'territory_worked' => $_REQUEST["territory_worked"],               'comments'         => $_REQUEST["comments"],               'cash_advance'     => $_REQUEST["cash_advance"],               'mileage_rate'     => $GLOBALS["expense-mileage-travelrate"]),        $session->getDatabaseHandle());    // display    if ($_REQUEST["action"] != "persist_expense") {        $week->readWeek();        $smarty->assign_by_ref ("user",       $user);        $smarty->assign_by_ref ("week",       $week);        $smarty->assign('start_weeks',        getStartWeeks());        $smarty->assign('current_start_week', getCurrentStartWeek());        $smarty->assign_by_ref ('expenses',   $week->getExpensesMetaArray());        $smarty->assign('travelrate',        $GLOBALS["expense-mileage-travelrate"]);        $smarty->display('travel-expenses.tpl');        exit();    }    // gather and persist week    $week->parse($_REQUEST);    $week->persist();    print "saved, thanks";    ?> 

Being the user-accessible landing-spot, this script has two modes of operation: one is displaying the travel expenses by reading them from the database (readWeek); the other is saving the form data (persist) based on whether the action is set to persist_expense.

As usual, your very interesting getExpensesMetaArray() is the cornerstone of the screen and is passed in as the Smarty variable expenses. Also note that all heavy-weight objects are passed to Smarty by reference, so copies aren't automatically created.

Now for the basic Smarty template that displays the travel expense page, which is in templates/travel-expenses.tpl:

   {include file="header.tpl" title="Widget World - Travel Expenses"}    {literal}    <SCRIPT TYPE="text/javascript">    <!--    functio reloadCalc () {        window.document.forms[0].week_start.value =    window.document.forms[1].week_start.value // hidden form         window.document.forms[0].submit(); // hidden form    }    // -->    </SCRIPT>    {/literal}    <h3>Travel Expense Report</h3>    <form method="post">    <input type="hidden" name="action" value="reload_expense">    <input type="hidden" name="week_start" value="">    </form>    <form  name="calc" action="travel-expenses.php" method="post">    <table border="0" width="100%">     <tr>    <td><b>Employee Name:</b></td>    <td>{$user->first_name} {$user->last_name}</td>    <td><b>Department:</b></td><td>{$user->department}</td>    </tr>    <tr>    <td><b>Number:</b></td>    <td>{$user->id}</td>    <td><b>Start Week:</b></td>    <td><SELECT NAME="week_start" onchange="reloadCalc()">{html_options    values=$start_weeks output=$start_weeks selected=$current_start_week}    </SELECT></td>    </tr>    <tr>    <td><b>Territory Worked:</b></td>    <td colspan=3><input name="territory_worked" size=20 maxsize=60    value="{$week->territory_worked}"></td>    </tr>    </table>    <br><br> 

The first form on the page is similar to that of the Customer Contact Report, in which you utilized a special "hidden'' form that was populated (by JavaScript) with the value for your starting week. The form was then automatically submitted when a new week was selected.

The start of the second form simply contains some of the basic information that you are collecting, including the territory_worked.

The next section begins the spreadsheet part of the display. Code is simply replicated between Sunday and Saturday. Remember that this screen is your first attempt and you may have to change items, thus the neglect of a final "week" loop is understandable. For now it's just a basic input form without error-checking or spreadsheet-like activity:

   ble border="0">    <tr><td></td><td>Sun</td><td>Mon</td><td>Tues</td><td>Wed</td><td>Thur</td>    <td>Fri</td><td>Sat</td><td>Total</td></tr>    {section name=idx loop=$expenses}{strip}    <tr><td><b>{$expenses[idx].name}</b></td><td></td><td></td><td></td><td></td>    <td></td><td></td><td></td><td></td></tr>       {section name=idx2 loop=$expenses[idx].data}{strip}       {assign var="p" value=$expenses[idx].persist[idx2]}       <tr bgcolor="{cycle values="#eeeee,#dddddd"}">       <td>{$expenses[idx].data[idx2]}</td>    <td><input name="{$expenses[idx].code}_sun_{$smarty.section.idx2.index}"    type="text" size="7" maxsize="17" value="{$week->getExpenseAmount(0, $p)}"></td> 

Remember that you're building up HTML output that looks like:

   <input name="lodging_sun_0" type="text" size="7" maxsize="17" value="1.00"> 

The two main loops here iterate through your expense array:

   return array(        array('name' => 'Lodging',              'code' => 'lodging',              'data' => array('Lodging &amp; Hotel','Other','Tips'),              'persist' => array('lodging_and_hotel', 'lodging_laundry',                                 'lodging_tips')) 

The Smarty variable $p is dynamically created as a convenience in order that the Smarty code not get too unwieldy: getExpenseAmount(0, $p) is more readable than getExpenseAmount(0, $expenses[idx].persist[idx2]). Finally, the last two lines representing Sunday are copied six more times to represent each day of the week; they are, thankfully, not displayed.

   <td><input readonly    name="{$expenses[idx].code}_week_sub_{$smarty.section.idx2.index}"    type="text" size="7" maxsize="17"></td>       </tr>       {/strip}{/section} 

This works well, but there is always an exception. Your clients have requested that transportation subtotal before accepting the "number of miles traveled," which then totals that section.

Smarty can handle its own conditionals, so put that in next. But the number of miles traveled is not in your expenses meta-array, so it must be accounted for:

      {if $expenses[idx].code == 'trans'}           <tr><td>{$expenses[idx].name} Subtotal</td>    <td><input readonly name="{$expenses[idx].code}_sun_sub" type="text" size="7"    maxsize="17"></td> 

Again, the code is duplicated six more times (not displayed) before the subtotal:

   <td><input readonly name="{$expenses[idx].code}_week_sub" type="text" size="7"    maxsize="17"></td></tr>           <tr><td>Nbr of miles traveled</td>               <td><input name="mitr_sun" type="text" size="7" maxsize="17" value="{$week->getExpenseAmount(0, 'trans_miles_traveled')}"></td> 

The rest of the week is not shown, for brevity.

          <td><input readonly name="mitr_tot" type="text" size="7"    maxsize="17"></td></tr>           <tr><td>Miles at {$travelrate} / mile </td><td><input readonly    name="mitot_sun" type="text" size="7" maxsize="17"></td> are the duplicated mileage (mitrs).

   <td><input readonly name="mitot_tot" type="text" size="7"    maxsize="17"></td></tr>           <tr><td>{$expenses[idx].name} Total</td><td><input readonly    name="{$expenses[idx].code}_sun_sub2" type="text" size="7" maxsize="17"></td>       {else}           <tr><td>{$expenses[idx].name} Total</td><td><input readonly    name="{$expenses[idx].code}_sun_sub" type="text" size="7" maxsize="17"></td> 

This completes the transportation exception and continues with the standard processing. Essentially it's avoiding adding an extra line to the bottom of transportation for mileage.

   <td><input readonly name="{$expenses[idx].code}_week_sub" type="text" size="7"    maxsize="17"></td></tr>       {/if}    {/strip}{/section} 

The rest of the screen is the subtotals, amounts due, and weekly comments:

   <tr><td></td><td></td><td></td><td></td><td></td><td    colspan="3">Subtotal</td><td><input readonly name="subtotal"    type="text" size="7" maxsize="17"></td></tr>    <tr><td></td><td></td><td></td><td></td><td></td><td colspan="3">Less Cash    Advance</td><td><input name="cash_advance" type="text" size="7" maxsize="17"    value="{$week->cash_advance}"></td></tr>    <tr><td></td><td></td><td></td><td></td><td></td><td colspan="3">Due    Employee</td><td><input readonly name="totaldueemployee" type="text" size="7"    maxsize="17"></td></tr>    <tr><td></td><td></td><td></td><td></td><td></td><td colspan="3">Due    Company</td><td><input readonly name="totalduecompany" type="text" size="7"    maxsize="17"></td></tr>    </table>    <br><br>    Comments:<br><TEXTAREA NAME="comments" COLS=80 ROWS=6>{$week->comments}    </TEXTAREA>    <br><br><center>    <input type="submit" name="submit" value=" Submit Report " >    </center>    <input type="hidden" name="action" value="persist_expense">    </form>    {include file="footer.tpl"} 

Even though the screen lacks even basic error-checking or any spreadsheet-like features, you're still at a very pivotal point in the project. This screen, as is, could technically be used in production.

Unadvisable, yes, but this is the point where you are delivering business value! Get this into the hands of your clients and start getting their feedback.

This big gulp has just taken care of stories 2, 3, and 10.

Travel Expense Report as Spreadsheet

Your clients are enjoying the travel expense report; they're using it and giving you valuable feedback regarding how, what, and why.

Fortunately for you, all the additional functionality they want is to get it to act like a spreadsheet. So, what's that going to take?

JavaScript offers one event that you're interested in, onkeyup; it can be applied to your input boxes in order to recalculate on the fly when new information is entered.

But it turns out that simulating a spread-sheet is not as easy as it sounds. Here is the new template/travel-expenses.tpl:

   {include file="header.tpl" title="Widget World - Travel Expenses"}    {literal}    <SCRIPT TYPE="text/javascript">    <!--    function subtotal(thisForm, totalcell, cellArray) {        var subtot = 0;        for (var i=0; i < cellArray.length; i++) {            if(isNaN(thisForm[cellArray[i]].value))                thisForm[totalcell].value = 0;            else                subtot = Math.round(subtot*100 +                                    thisForm[cellArray[i]].value*100)/100;        }        thisForm[totalcell].value = subtot;        return subtot;    } 

Starting from the top, the JavaScript function subtotal() takes a form, which is a place to put the answer and an array of numbers to add.

Although JavaScript supports decimals, it doesn't support arbitrary precision. So because we'd like dollars down to the penny (as in $9.99), we need to multiply everything by 100. Do the math, round the result, and then divide by 100.

So by adding 0.01 to 9.98, multiply everything by 100: 1.0 and 998.0; do the addition: 999.0; round the result: 999; and divide by 100: 9.99. Yes, it looks as though it'll be a pain.

The next few functions deal with totaling up columns in each section (Lodging, Meals, Transportation, and so on), vertically totaling each amount per day. Finally, daycalc() is called by each of the "cells":

   function subday (thisForm, totalcell, prefix, maxindex) {        var cellArray = new Array (maxindex);        for (var i=0; i < maxindex; i++) {             cellArray[i] = thisForm[prefix+i].name;        }        return subtotal(thisForm, totalcell, cellArray);    }    function subweek (thisForm, totalcell, prefix, postfix) {        return subtotal (thisForm, totalcell,                         new Array (prefix+'sun'+postfix, prefix+'mon'+postfix,                                    prefix+'tue'+postfix, prefix+'wed'+postfix,                                    prefix+'thr'+postfix, prefix+'fri'+postfix,                                    prefix+'sat'+postfix));    }    function daycalc (thisForm, day, code, thisindex, maxindex) {        subday (thisForm, code+"_"+day+"_sub", code+"_"+day+"_", maxindex);        subweek(thisForm, code+"_week_sub_"+thisindex, code+"_", '_'+thisindex);        subday (thisForm, code+"_week_sub", code+"_week_sub_", maxindex);        totalcalc (thisForm);        return true;    } 

The function daycalc() is called from each cell that accepts user-input in the HTML form in such a manner:

   onkeyup="return daycalc(this.form, 'sun', 'lodging', '0', 3)" 

Remember that the screen is generated from your expense meta-array:

   array('meals_breakfast', 'meals_lunch',                             'meals_dinner', 'meals_tips',                             'meals_entertainment') 

But JavaScript has no knowledge of PHP or Smarty or whatever else you're using in the backend. So you need to be explicit and tell JavaScript which cell you are working with and how many cells need to be subtotaled. These, respectively, are the last two arguments in the daycalc() function call: '0' is the current row and '3' signifies the maximum number of rows that need to be vertically subtotaled in the subday function.

The number of miles, an exception, needs to be calculated separately. The cells for the mileage input are named mitr_sun, mitr_mon, and so on. Their subtotal, which is the number of miles multiplied by the mileage rate, is represented by mitot_sun, mitot_mon, and so on. After the horizontal weekly totals are calculated with subweek, the total mileage is updated based on the current vertical day. Finally, the grand total of the transportation section is updated.

   function micalc (thisForm, day, travelrate) {        var totalcell = 'mitot_'+day;        var sourcecell = 'mitr_'+day;        // mileage input and mileage total        thisForm[totalcell].value = Math.round(            thisForm[sourcecell].value * 100 * travelrate)/100;        subweek (thisForm, 'mitr_tot', 'mitr_', '');        subweek (thisForm, 'mitot_tot', 'mitot_', '');        // trans total by day        thisForm["trans_"+day+"_sub2"].value = Math.round(           (thisForm[totalcell].value * 100) +           (thisForm["trans_"+day+"_sub"].value * 100))/100;        // grand total of week        subweek (thisForm, "trans_week_sub2", "trans_", "_sub2");        totalcalc (thisForm);    } 

Next, some basic error-checking:

   function checkTransInput (thisForm, day) {        if (thisForm["trans_"+day+"_sub"].value>0&&            thisForm["mitr_"+day].value == "") {            alert("Please enter your mileage for "+day);            return false;        }        return true;    }    function checkInputs(thisForm) {        if ( checkTransInput (thisForm, "sun") == false) { return false; }        if ( checkTransInput (thisForm, "mon") == false) { return false; }        if ( checkTransInput (thisForm, "tue") == false) { return false; }        if ( checkTransInput (thisForm, "wed") == false) { return false; }        if ( checkTransInput (thisForm, "thr") == false) { return false; }        if ( checkTransInput (thisForm, "fri") == false) { return false; }        if ( checkTransInput (thisForm, "sat") == false) { return false; }        if (  thisForm["subtotal"].value == 0) {            alert ("Please enter data.");             return false;         }        if (thisForm["territory_worked"].value == "") {            alert ("Please enter your territory worked.");            return false;        }        return true;    } 

Now comes the monster function totalcalc(), which makes sure that the entire form is recalculated:

   //    // No easy way to pass a php array to JS unless we generate it by hand.    //    function totalcalc (thisForm) {        var sectionArray = new Array ('lodging', 'meals', 'trans', 'misc');        var subtotal = 0;        for (var i=0; i < sectionArray.length; i++) {            subtotal = subtotal +                Math.round(thisForm[sectionArray[i]+"_week_sub"].value*100)/100;        }        subtotal = subtotal + Math.round(thisForm["mitot_tot"].value*100)/100;        thisForm["subtotal"].value = subtotal;        var total = subtotal - Math.round(thisForm["cash_advance"].value*100)/100;        total = Math.round(total*100)/100;        if (total >= 0) {            thisForm["totaldueemployee"].value = total;            thisForm["totalduecompany"].value = "";        }  else {             thisForm["totaldueemployee"].value = "";             thisForm["totalduecompany"].value = Math.round(total * 100)/100 *(-1);        }    } 

In the event of an error, the recalculate() function will be attached to a "recalculate'' button. This function "touches'' every row and every column in order to cause the screen to be initially calculated. Remember that columns are represented by days, and rows are the first layer of your expense meta-array. Although the recalculate function is not the epitome of good programming, it is easier to read when it is laid out by brute force rather than by relying on another layer of looping.

   // don't knock it; looping is less readable    function recalculate (thisForm, mileage) {        daycalc(thisForm, 'sun', 'lodging', '0', 3); 

Monday through Friday are removed for brevity.

       daycalc(thisForm, 'sat', 'lodging', '0', 3);        daycalc(thisForm, 'sat', 'lodging', '1', 3);        daycalc(thisForm, 'sat', 'lodging', '2', 3);        daycalc(thisForm, 'sun', 'meals', '0', 5); 

Monday through Friday are removed for brevity.

       daycalc(thisForm, 'sat', 'meals', '0', 5);        daycalc(thisForm, 'sat', 'meals', '1', 5);        daycalc(thisForm, 'sat', 'meals', '2', 5);        daycalc(thisForm, 'sat', 'meals', '3', 5);        daycalc(thisForm, 'sat', 'meals', '4', 5);        daycalc(thisForm, 'sun', 'trans', '0', 5); 

Monday through Friday are removed for brevity.

       daycalc(thisForm, 'sat', 'trans', '0', 5);        daycalc(thisForm, 'sat', 'trans', '1', 5);        daycalc(thisForm, 'sat', 'trans', '2', 5);        daycalc(thisForm, 'sat', 'trans', '3', 5);        daycalc(thisForm, 'sat', 'trans', '4', 5);        daycalc(thisForm, 'sun', 'misc', '0', 5); 

Monday through Friday are removed for brevity.

       daycalc(thisForm, 'sat', 'misc', '0', 5);        daycalc(thisForm, 'sat', 'misc', '1', 5);        daycalc(thisForm, 'sat', 'misc', '2', 5);        daycalc(thisForm, 'sat', 'misc', '3', 5);        daycalc(thisForm, 'sat', 'misc', '4', 5);        micalc(thisForm, 'sun', mileage); 

Monday through Friday are removed for brevity.

       micalc(thisForm, 'sat', mileage);        return (totalcalc(thisForm));    }    function reloadCalc () {        window.document.forms[0].week_start.value =    window.document.forms[1].week_start.value // hidden form        window.document.forms[0].submit(); // hidden form    }    // -->    </SCRIPT>    {/literal} 

Now that there is code to calculate the row and column totals, every cell needs to responds to the onkeyup event by calling daycalc(). Modify the seven daily inputs in your templates/travel-expenses.tpl:

   <input name="{$expenses[idx].code}_sun_{$smarty.section.idx2.index}"    onkeyup="return daycalc(this.form, 'sun', '{$expenses[idx].code}',    '{$smarty.section.idx2.index}', {$})"    type="text" size="7" maxsize="17" value="{$week->getExpenseAmount(0, $p)}"> 

The mileage input functions also need to have their keyup function:

   <input name="mitr_sun" onkeyup="return micalc(this.form, 'sun',    {$travel_rate})" ... 

A recalculate button should be added; it will aid in debugging the JavaScript in the event that everything doesn't work perfectly.

   <br><br>    <center>    <input type="button" value=" Recalculate " onclick="return recalculate    (this.form, {$travelrate})">    </center>    <br><br>    Comments:<br><TEXTAREA NAME="comments" COLS=80 ROWS=6>{$week->comments}    </TEXTAREA>    <br><br><center> 

The main Submit button now checks for basic input errors by running checkInputs() from the onclick event:

   <input type="submit" name="submit" value=" Submit Report " onclick="return    checkInputs(this.form);"> 

Almost done.

There are just a few outstanding issues. For instance, if the drop-down start week is changed, the form will reload and recalculate itself, but unfortunately that event won't be fired when the form initially loads itself.

You need to do make sure that the form runs your JavaScript recalculate() function when the form is loaded. This is accomplished from the onload event of the <body> tag. But the <body> tag is defined in your header.tpl, so here is what you do: In your travel-expenses.php file, modify the display section:

   // display    if ($_REQUEST["action"] != "persist_expense") {        $week->readWeek();        $smarty->assign_by_ref ("user",       $user);        $smarty->assign_by_ref ("week",       $week);        $smarty->assign('start_weeks',        getStartWeeks());        $smarty->assign('current_start_week', getCurrentStartWeek());        $smarty->assign_by_ref ('expenses',   $week->getExpensesMetaArray());        $smarty->assign('travelrate',         $GLOBALS["expense-mileage-                                              travelrate"]);        $smarty->assign('formfunc',            "recalculate(window.document.forms[1],".            $GLOBALS["expense-mileage-travelrate"].")");        $smarty->display('travel-expenses.tpl');        exit();    } 

You're assigning a function to the variable formfunc. Now change the header.tpl so that it reads accordingly:

   <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">    <html>    <head>    <meta HTTP-EQUIV="content-type" CONTENT="text/html; charset=ISO-8859-1">    <title>{$title|default:"no title"}</title>    </head>    <body onload="{$formfunc|default:""}">    <h1>Widget World</h1>    <hr><p> 

The $formfunc Smarty variable holds the name of a JavaScript function that is executed when the HTML form is loaded, $formfunc. The onload form property is set to $formfunc or empty string if $formfunc is not defined. This allows you to selectively recalculate your entire spreadsheet during the loading process. By your doing so, daycalc() has to be concerned only with its column, row, and the totals rather than with recalculating every cell during every keystroke.

Anything Else

Feedback from your clients has told you that the "Number of Miles" doesn't appear to be persisted correctly from the screen. Being the exception to your meta-array, it's not taken care of in the parse() function.

By now you should know the drill. Whip up a test:

       function testParsingNbrMiles() {            $this->_session->getDatabaseHandle()->query("delete FROM    travel_expense_item WHERE emp_id = 1 and expense_date >= '1980-01-06' and    expense_date <= '2001-09-15'");            $response = array ('mitr_sun' => "1.1",                               'mitr_mon' => "2.2");            $week = new TravelExpenseWeek (                array ('emp_id'             => "1",                       'week_start'         => "1980-01-06",                       'territory_worked'   => "Midwest",                       'comments'           => "comment",                       'cash_advance'       => "0",                       'mileage_rate'       => "0.31"),                $this->_session->getDatabaseHandle());            $week->parse($response);            $this->assertEquals(true, $week->persist());            $week = new TravelExpenseWeek (                array ('emp_id'             => "1",                       'week_start'         => "1980-01-06",                       'territory_worked'   => "Midwest",                       'comments'           => "comment",                       'cash_advance'       => "0",                       'mileage_rate'       => "0.31"),                $this->_session->getDatabaseHandle());            $week->readWeek();            $this->assertEquals(1.1, (float) $week->getExpenseAmount(0,    'trans_miles_traveled'));            $this->assertEquals(2.2, (float) $week->getExpenseAmount(1,    'trans_miles_traveled'));        } 

Make it succeed accordingly with changes to TravelExpenseWeek lib/expense.phpm:

       private function createByRequest ($description, $daynum,                                          $index, &$request) {            if (array_key_exists($index, $request) and                $request[$index] <> null and                $request[$index] <> "") {                array_push (                    $this->items,                    new TravelExpenseItem (                        array ('emp_id' => $this->emp_id,                               'expense_date' =>                               $this->addDays($this->week_start, $daynum),                               'description' => $description,                               'amount' => (float) $request[$index]),                        $this->dbh));            }        }        /**         * This function bridges the gap between the day-based DB and the         * week-based view         */         public function parse(&$request) {             foreach ($this->getExpensesMetaArray() as $sectionlist) {                 for ($i=0; $i < count ($sectionlist['persist']); $i++) {                     $daynum = 0;                     foreach ($this->getWeekArray() as $day) {                         $index = $sectionlist['code']."_".$day."_".$i;                         $this->createByRequest (                             $sectionlist['persist'][$i], $daynum,                             $index, $request);                         $daynum++;                     }                 }             }             // arg, mitr is an exception             $daynum = 0;             foreach ($this->getWeekArray() as $day) {                 $this->createByRequest ("trans_miles_traveled", $daynum,                                         "mitr_".$day, $request);                 $daynum++;            }       } 

A new function, TravelExpenseWeek->createByRequest(), rids you of code duplication because it's called during normal processing and for the mileage.

All the tests pass, your clients are happy, the screen works. Now you're done with Story 4: "Expense Report as Spreadsheet," as shown in Figure 22-10.

Figure 22-10

So, other than "simply working,'' you've got a well-tested system and the beginning of a framework which is capable of evolution.

You've seen how PHP, JavaScript, and browsers do (and do not) interact, how each one is responsible for its layers, and how to move information among all of them. Creating complex spreadsheet-like behavior, although not easy, can be done with relatively little code.

So, where do you go from here? Just plod through the rest of the stories? Keep on reading, because there is another important lesson in the final section.

Professional PHP5 (Programmer to Programmer Series)
Professional PHP5 (Programmer to Programmer Series)
Year: 2003
Pages: 182
BUY ON AMAZON © 2008-2017.
If you may any questions please contact us: