Location: PHPKode > projects > Vehicle Service Tracker > VST/showhistory.php
<?php
//==========================================================================
// showhistory.php
//
// Show a vehicles complete service history
//
// Copyright (c) 2006 Kenneth J. Snyder
// Licensed under the GNU GPL. For full terms see the file LICENSE
// -------------------------------------------------------------------------
//
// Created: 10Mar2006 Snyder, Kenneth J. hide@address.com 
//
// Revised: 24Mar2006 Snyder, Kenneth J. hide@address.com v2.0.4
//          Added Miles Per Year calculation and insert into DB
//
//==========================================================================
?>


<html>
<link rel='stylesheet' type='text/css' href='vst.css'>
<head>
   <script language="javascript" type="text/javascript" src="popup.js"></script>
</head>

<?php
 $mtime = microtime();
 $mtime = explode(" ",$mtime);
 $mtime = $mtime[1] + $mtime[0];
 $starttime = $mtime;

function db2ts2int( $ts )
{
   $y = substr( $ts, 0, 4 );
   $m = substr( $ts, 5, 2 );
   $d = substr( $ts, 8, 2 );
   $h = substr( $ts, 11, 2 );
   $mi = substr( $ts, 14, 2 );
   $s = substr( $ts, 17, 2 );
   return mktime($h,$mi,$s,$m,$d,$y);
}

setlocale(LC_MONETARY, 'en_US');
if (isset($_REQUEST['SID'])) { $SID=$_REQUEST['SID']; } else { $SID=""; }
if (isset($_REQUEST['USERNAME'])) { $USERNAME=$_REQUEST['USERNAME'];} else { $USERNAME=""; }
include_once("includes.php");
$dbconn = odbc_connect("$dbname","$dbuid","$dbpasswd");
if ($dbconn==0) {
   $a = odbc_errormsg("DB2 Connect Failed. DB2 might not be running");
   echo($a);
 } else {
   authuser($dbconn,$USERNAME,$SID);
   function ShowAddSRButton ($SID,$USERNAME,$VIN,$MAKE,$MODEL,$COLOR,$YEAR,
                     $MORK,$GORD,$IMAGE,$GASMILE) { 
      echo "<FORM METHOD='POST' ACTION='add.php'>";
      echo "<INPUT name='SID' type='hidden' VALUE='$SID'>";
      echo "<INPUT name='USERNAME' type='hidden' VALUE='$USERNAME'>";
      echo "<INPUT name='VIN' type='hidden' VALUE='$VIN'>";
      echo "<INPUT name='MAKE' type='hidden' VALUE='$MAKE'>";
      echo "<INPUT name='MODEL' type='hidden' VALUE='$MODEL'>";
      echo "<INPUT name='COLOR' type='hidden' VALUE='$COLOR'>";
      echo "<INPUT name='YEAR' type='hidden' VALUE='$YEAR'>";
      echo "<INPUT name='MORK' type='hidden' VALUE='$MORK'>";
      echo "<INPUT name='GORD' type='hidden' VALUE='$GORD'>";
      echo "<INPUT name='IMAGE' type='hidden' VALUE='$IMAGE'>";
      echo "<INPUT name='GASMILE' type='hidden' VALUE='$GASMILE'>";
      echo "<INPUT name='add' id='butt' type='Submit' value='Add New Service Record'>";
      echo "</FORM>";
    } // ShowAddSRButton

// if (isset($show) && $show=="Show Service History") {

    $EditVehicleURL="newvehicle.php?SID=$SID&USERNAME=$USERNAME&";
    $EditVehicleURL.="EDIT=EDIT&VIN=$VIN&YEAR=$YEAR&MAKE=$MAKE&";
    $EditVehicleURL.="MODEL=$MODEL&COLOR=$COLOR&GAS=$GASMILE&";
    $EditVehicleURL.="IMAGE=$IMAGE&GORD=$GORD&MORK=$MORK";
    $EditVehicleURL=htmlentities($EditVehicleURL);

    // Show the Service History
    // --------------------------
    echo "<head>\n";
    echo "<title>Vehicle Service Tracker</title>\n";
    echo "</head>\n";
    echo "<center><p class='Header1'><B>Vehicle Service Tracker - ";
    echo "<small>Service History Inquiry</small></B></p></center>\n";
    echo "<CENTER><B>";
    echo "<a href='main.php?SID=$SID&USERNAME=$USERNAME'>Back to Main</a>";
    echo "</B></CENTER>";
    echo "<p class='Header2'>Vehicle: $YEAR $MAKE $MODEL &nbsp; &nbsp; &nbsp; ";
    echo "Color: $COLOR &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VIN: $VIN ";
    echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
    echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
    echo "<a href='".$EditVehicleURL."'>";
    echo "<small> [ edit vehicle ] </small></A>";
    echo "<CENTER>";
    ShowAddSRButton ($SID,$USERNAME,$VIN,$MAKE,$MODEL,$COLOR,$YEAR,
                     $MORK,$GORD,$IMAGE,$GASMILE);
    echo "</CENTER>";

    // Get this repair order's details
    // ------------------------------------------------------
    $ROSelect="select REPAIR_ORDER,date(SERVICE_DATE),MILEAGE,SERVICE_SHORT FROM ";
    $ROSelect.="vst.SERVICEHIST where VEHICLE='$VIN' ORDER BY MILEAGE DESC ";
    $ROSelect.="FOR FETCH ONLY";
    $ROResult=odbc_exec($dbconn,$ROSelect);
    $numROS=0;
    while (odbc_fetch_row($ROResult)) {
      $numROS++;
      $ROS[$numROS]     =odbc_result($ROResult,1);
      $RODATES[$numROS] =odbc_result($ROResult,2);
      $ODOS[$numROS]    =odbc_result($ROResult,3);
      $DESCS[$numROS]   =odbc_result($ROResult,4);
      // Set the last odo reading for cost/mile calculations
      if ($numROS==1) { 
        $LastODO=$ODOS[$numROS];
        $LastDate=db2ts2int($RODATES[$numROS]);
      }
    } 
    if ($numROS!=0)  {
       // Set the first odo reading for cost/mile calculations
       $FirstODO=$ODOS[$numROS];
       $FirstDate=db2ts2int($RODATES[$numROS]);
      
       $nYears =  (($LastDate - $FirstDate) / 31536000);
        
       //echo "Num Years [$nYears] ";
       //echo "FirstODO [$FirstODO] FirstDate [$FirstDate]";
       //echo "Total Repair Records [$numROS]<BR><BR>";
       $TotalCost=0;
       $TotalHours=0;
       for ($key=1; $key <= $numROS; $key++) {
         if ($MORK=="K") {$MorkSuffix=" Kilometers";}else{$MorkSuffix=" Miles";}
         // Get this repair orders line items and display them
         // -------------------------------------------------------
         //echo "Repair Order: $ROS[$key] key[$key]";
         echo "<CENTER><TABLE width=95%>";
         echo "<TR><TD align=center colspan=2><p class='header8'>$DESCS[$key]";
         if ($key==1) {
           // Show the Add line items for the first repair order
           echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
           echo "<A href='addli.php?SID=$SID&USERNAME=$USERNAME";
           echo "&VIN=$VIN&ODO=$ODOS[$key]&DESC=$DESCS[$key]&EditRO=1";
           echo "&YEAR=$YEAR&MAKE=$MAKE&MODEL=$MODEL&COLOR=$COLOR&IMAGE=$IMAGE";
           echo "&GASMILE=$GASMILE&GORD=$GORD&MORK=$MORK&RO=$ROS[$key]'";
           echo ">[ Add Line Items To Repair Order ]</A>";
         } // if first row
         echo "</TD></TR>";
         echo "<TR><TD><P class='header8'>Date: ";
         echo "$RODATES[$key]</TD>";
         echo "<TD><p class='header8'>Odometer: ";
         echo number_format($ODOS[$key]);
         echo "$MorkSuffix</TD></TR>";
         echo "</TABLE>";
         $SROSelect="select REPAIR_ORDER_INDEX,DATE(LINE_DATE),OPERATION,";
         $SROSelect.="SOURCE,PART_NUMBER,COST,HOURS,NOTES,LINE_INDEX ";
         $SROSelect.="FROM vst.SERVICELINE where REPAIR_ORDER=";
         $SROSelect.="'$ROS[$key]' ORDER BY REPAIR_ORDER_INDEX ASC  FOR FETCH ONLY";
         $SROResult=odbc_exec($dbconn,$SROSelect);
         echo "<TABLE WIDTH='95%'>";
         echo "<TR>";
         echo "<TD class='header1'>Item</TD>";
         echo "<TD class='header1'>Date</TD>";
         echo "<TD class='header1'>Labor Operation</TD>";
         echo "<TD class='header1'>Source</TD>";
         echo "<TD class='header1'>Part Number</TD>";
         echo "<TD class='header1'>Cost</TD>";
         echo "<TD class='header1'>Hours</TD>";
         echo "</TR>";
         $TotalROCost=0;
         $TotalROHours=0;
   
         $numLines=0;
         while (odbc_fetch_row($SROResult)) {
           $numLines++;
           if ($numLines %2 == 0) { echo "<TR class='band'>"; } else { echo "<TR>"; }
           echo "<TD>";
           echo odbc_result($SROResult,1);
           $NOTES=odbc_result($SROResult,8);
           if ($NOTES!="") {
               $NOTES.="ANEWLINEANEWLINE";
               echo "<script>document.write(\"<a href='#' onclick=openPopup('";
               echo $NOTES;
               echo "');>[n]</a>\");";
               echo "</script>\n";

              echo "<noscript>";
              $TMPURL="notes.php?SID=$SID&USERNAME=$USERNAME&INFO=";
              $TMPURL.=nl2br(odbc_result($SROResult,8));
              echo "&nbsp;";
              echo "<a href='$TMPURL'>[n]</a>";
              echo "</noscript>";
           }
           echo "</TD><TD>";
           echo odbc_result($SROResult,2);
           echo "</TD><TD>";
           echo odbc_result($SROResult,3);
           echo "</TD><TD>";
           echo odbc_result($SROResult,4);
           echo "</TD><TD>";
           echo odbc_result($SROResult,5);
           echo "</TD><TD>";
           echo money_format('%n',odbc_result($SROResult,6));
           echo "</TD><TD>";
           echo odbc_result($SROResult,7);
           echo "</TD></TR>";
           $TotalROCost=$TotalROCost+odbc_result($SROResult,6);
           $TotalROHours=$TotalROHours+odbc_result($SROResult,7);
         } // while (odbc_fetch_row($SROResult)) (for each line item)
         echo "</TABLE>";
         echo "<TABLE WIDTH='95%'><TR>";
         echo "<TD class=header1>Total Cost: ";
         echo money_format('%n',$TotalROCost);
         echo "</TD>";
         echo "<TD class=header1>Total Hours: $TotalROHours</TD>";
         echo "</TR></TABLE></CENTER>";
         echo "<BR><BR>";
         $TotalCost=$TotalCost+$TotalROCost;
         $TotalHours=$TotalHours+$TotalROHours;
       } //foreach repair order
       $TotalCost=round($TotalCost,2);
       $TotalHours=round($TotalHours,2);
       if ($numROS==1) {
          // There's only 1 Repair order so some calculations
          // cannot be beformed unless at least 2 exists
          // --------------------------------------------- 
          $MilesDriven=0;
          $CPM=0;
          $AMPY=0;
        } else {
          // There's more than 1 repair order so we
          // can calculate everything...
          // -----------------------------------------
          $AMPY=round(($LastODO-$FirstODO)/$nYears);
          if ($MORK=="K") {
             $MilesDriven=(($LastODO-$FirstODO)*0.621371192);
           } else { 
             $MilesDriven=$LastODO-$FirstODO;
          }
          $CPM=round((($TotalCost/$MilesDriven)*100),1);
       } // if only 1 repair order
       echo "<CENTER>";
       echo "<TABLE BORDER='0' WIDTH='70%' CELLSPACING='1' CELLPADDING='2'>";
       echo "<TR class='header4'><TD colspan='3' align='center'>";
       echo "Totals for $YEAR $MAKE $MODEL &nbsp; &nbsp &nbsp; VIN: $VIN</TD></TR>";
       echo "<TR class='header3'><TD rowspan='5'><img src='$IMAGE'></TD>";
       echo "<TD>Total Hours Spent Maintaining Vehicle</TD>";
       echo "<TD>$TotalHours</TD></TR>";
       echo "<TR class='header3'><TD>Total $ Spent For Maintaince</TD>";
       echo "<TD>";
       echo money_format('%n',$TotalCost);
       echo "</TD></TR>";
       echo "<TR class='header3'><TD>Total Miles driven</TD>";
       echo "<TD>";
       if ($numROS==1) {
          echo "N/A";
         } else {
          echo number_format($MilesDriven);
       }
       echo "</TD></TR>";
       echo "<TR class='header3'><TD>";
       echo "Average Miles Per year Driven";
       if ($nYears<1) echo " (Extrapolated)";
       echo "</TD><TD>";
       echo number_format($AMPY);
       echo "</TD></TR>";
       echo "<TR class='header3'><TD>";
       echo "Cost Per mile to maintain this vehicle (cents/mile)</TD>";
       if ($numROS==1) {
          echo "<TD>N/A</TD></TR>";
         } else { 
          echo "<TD>$CPM</TD></TR>";
       }
       echo "</TABLE>";
       echo "</CENTER>";
       if ($MilesDriven==0) {
          echo "<BR>";
          echo "<B>There is only 1 repair order.<BR>";
          echo "Cost per mile & Miles Driven can only be ";
          echo "calculated if there are at least 2 repair orders.</B><BR>";
       }
   
       // Insert the new calculated values into this vehicles history
       // we do it here so the main page doesn't have to go selecting
       // every line item for every vehicle when it loads
       // -------------------------------------------------------------
       $UpdateVehicleStats="update vst.VEHICLES set ";
       $UpdateVehicleStats.="MILESDRIVEN=".round($MilesDriven).",";
       $UpdateVehicleStats.="COSTPERMILE=$CPM,";
       $UpdateVehicleStats.="TOTALSPENT=$TotalCost,";
       $UpdateVehicleStats.="TOTALHOURS=$TotalHours,";
       $UpdateVehicleStats.="AVG_MILES_PER_YEAR=$AMPY ";
       if ($nYears<1) $UpdateVehicleStats.= ",EXAVG='E' ";
       $UpdateVehicleStats.="where VIN='$VIN'";
       //echo "Update stats statment [$UpdateVehicleStats]";
       odbc_exec($dbconn,$UpdateVehicleStats);
       odbc_commit($dbconn);
    } else {
       echo "There are no service records for this vehicle yet. Add some!<BR>";
   } // if there are any repair orders
   echo "<BR><BR><CENTER><B>";
   echo "<a href='main.php?SID=$SID&USERNAME=$USERNAME'>Back to Main</a>";
   echo "</B></CENTER>";
// } // show or add
odbc_close_all();
} // if dbconn

footer($PHP_SELF,$adminemail);

  $mtime = microtime();
  $mtime = explode(" ",$mtime);
  $mtime = $mtime[1] + $mtime[0];
  $endtime = $mtime;
  $totaltime = ($endtime - $starttime);
  echo "<br><br>This page was created in ".$totaltime." seconds";

?>
</html>
Return current item: Vehicle Service Tracker