Miles Flown on Pilot List

[Taran]

How would I go about getting each pilot's total miles flown (all time) on the roster? Like they have the flights and hours, how would I add the miles?

[servetas]

Open your core/common/StatsData.class.php file and before the last closing bracket (}) add this:

public static function TotalPilotMiles($pilotid)
{
 $key = 'total_miles';
 $key .= '_'.$pilotid;

 $total = CodonCache::read($key);

 if($total === false)
 {
  $total = 0;
  $sql = "SELECT * FROM ".TABLE_PREFIX."pireps WHERE pilotid='$pilotid' AND accepted=1";
  $results = DB::get_results($sql);
  if($results) { foreach($results as $result) { $total += $result->distance; } }

  CodonCache::write($key, $total, '15minute');
 }
 return $total;
}

and after that, you can echo the pilot's miles using the part of code below:

<?php echo StatsData::TotalPilotMiles($pilot->pilotid); ?>

PS: Pay special attention to the $pilot->pilotid, you will have to replace $pilot if the page is using a different variable name. Just to let you know that this script counts the total miles flown by a pilot based on his accepted pireps.

[Parkho]

I'm using this:

<?php
$dbm="SELECT t1.pilotid,t1.distance,t2.firstname,t2.lastname FROM
 (SELECT
 pilotid, sum(distance) as distance
 FROM phpvms_pireps
 WHERE date_format(submitdate, '%Y-%m') = date_format(now(), '%Y-%m') AND accepted = 1
	 GROUP BY pilotid) t1
	 LEFT JOIN phpvms_pilots t2 ON t1.pilotid = t2.pilotid
	 ORDER BY distance DESC LIMIT 10";
	 $bstm = DB::get_results($dbm);

foreach ($bstm as $btm)
{
 if($bstm == '')
 {
?>
 <tr><td align="center" colspan="2">No Records yet!</td></tr>
<?php
 }
 else
 {
?>
 <tr><td><?php echo $btm->lastname ;?></td><td align="center"><?php echo $btm->distance ;?> NM</td></tr>
<?php
 }
}
?>

This will give u top miles in current month.

If you need top miles for all time use this:

<?php
$dbm="SELECT t1.pilotid,t1.distance,t2.firstname,t2.lastname FROM
 (SELECT
 pilotid, sum(distance) as distance
 FROM phpvms_pireps
	 WHERE accepted = 1
	 GROUP BY pilotid) t1
	 LEFT JOIN phpvms_pilots t2 ON t1.pilotid = t2.pilotid
	 ORDER BY distance DESC LIMIT 10";
	 $bstm = DB::get_results($dbm);

foreach ($bstm as $btm)
{

?>
 <tr><td><?php echo $btm->lastname ;?></td><td align="center"><?php echo $btm->distance ;?> NM</td></tr>
<?php
}
?>

[ercio]

Sevetas, how would be the code if is cargo per pilot and pax per pilot?

[servetas]

For the pax carried per pilot add this before the ending bracket of the code/common/StasData.class.php file:

public static function TotalPilotPax($pilotid)
{
 $key = 'total_pax';
 $key .= '_'.$pilotid;

 $total = CodonCache::read($key);

 if($total === false)
 {
  $total = 0;
  $sql = "SELECT * FROM ".TABLE_PREFIX."pireps WHERE pilotid='$pilotid' AND accepted=1";
  $results = DB::get_results($sql);
  if($results) { foreach($results as $result) { $total += $result->load; } }

  CodonCache::write($key, $total, '15minute');
 }
 return $total;
}

And you can use the code below in order to echo the passengers carried by a pilot.

<?php echo StatsData::TotalPilotPax($pilotid); ?>

Once again, do not forget to take care of the $pilotid variable on the second part of code. You will have to "pass" the correct variable according to the page you want to include it.

As far as i know, phpVMS does not have a cargo value on each pilot's pireps. Are you using any custom pirep field for this?

[ercio]

for cargo per pax I believe we have to add flight type C to the variable but I don't know how. I managed it for the company statistic , I get both pax and cargo. I miss just per pilot

$sql = "SELECT * FROM ".TABLE_PREFIX."pireps WHERE pilotid='$pilotid' AND accepted=1"; how d I add in here ad flight type C?

[Parkho]

Try this:

$sql = "SELECT * FROM ".TABLE_PREFIX."pireps WHERE pilotid='$pilotid' AND flighttype = 'H' AND accepted=1";

'C' is for charter flights. 'H' is for cargo.

[ercio]

YES!!