Finance Data

[Guest lorathon]

I believe there is a problem with the finance data.

Right now the finance data has our pilot pay total as of 65 million dollars. Even if every pilot made our top pay of $180 * 1100 total hours = Total Pilot Pay should be $198,000.

I started looking through how it is calculated and found this in the PIREData.class.php (getIntervalData)

$sql = "SELECT DATE_FORMAT(p.submitdate, '{$format}') AS ym,
				UNIX_TIMESTAMP(p.submitdate) AS timestamp,
				COUNT(p.pirepid) AS total,
				SUM(p.revenue) as revenue,
				SUM(p.gross) as gross,
				SUM(p.fuelprice) as fuelprice,
				SUM(p.price) as price,
				SUM(p.expenses) as expenses,
				(SUM(p.pilotpay) * SUM(p.flighttime)) as pilotpay
			FROM ".TABLE_PREFIX."pireps p";

If I understand this correctly the calculation to get total pilot pay is not going to work. With this calculation it adds up all of the flighttimes and all of the hourly pay rates and then multiplies them. This would work for 1 pirep but once you add a second and then third the difference between what is real and this answer grows and grows.

Example.

1 PIREP

5.0 hours at $10 per hour = $50

Total Pilotpay should be $50

Using the calculation as above you would get (5.0) * ($10) = $50 Total Pilotpay

2 PIREP's

5.0 hours at $10 per hour = $50

3.0 hours at $10 per hour = $30

Total Pilotpay should be $80

Using the calculation as above you would get (5.0 + 3.0) * ($10 + $10) = $160 Total Pilotpay

3 PIREP's

5.0 hours at $10 per hour = $50

3.0 hours at $10 per hour = $30

2.0 hours at $10 per hour = $20

Total Pilotpay should be $100

Using the calculation as above you would get (5.0 + 3.0 + 2.0) * ($10 + $10 + $10) = $300 Total Pilotpay

As you can see the difference will continue to expand at an alarming rate. Pretty soon you end up with a $65,000,000 pilot pay tab. I cant cover that bill

Am I mistaken? If so then something else is wrong since I have the big bill. Please let me know.

[Cub'X]

Hello

I think I have the same problem. (see attached screenshots)

In our VA, we've only one rank and only on rate : 200 euros / hour from the beginning (March 2010 ... a very your VA)

As of today, the total hours flown is 98.35 so that we shouldnt have more than : (98 x 200) + ((35 x 200) / 60) = 19 716.67 eur.

For March 2010, we've a total of 74 208 eur

For April 2010, we've a total of 604 984 eur ... so that on this trend, we'll be very close to the virtual bankrupcy in a couple of month. I didn't went in deep to the code but I think it's a small bug somewhere. See a hardcopy as attached document

Friendly yours

Jm²

www.cubx-va.eu

[Nabeel]

http://bugs.phpvms.net/browse/VMS-284

[Nabeel]

Crap.

I guess it should be:

SUM(p.pilotpay * p.flighttime) as pilotpay

[Guest lorathon]

LOL....

No biggie. But man did we go in the hole fast.

[Guest lorathon]

Crap.

I guess it should be:

SUM(p.pilotpay * p.flighttime) as pilotpay

That works dandy

[Nabeel]

Give 931 a shot..

[Cub'X]

Ouch, I implemented your correction. Something doesn't work as well somewhere.

Pilot pay is 200 € / hr

Total Hours Flown: 98.35

normally I should have (98 x 200) + ((35 x 200) / 60) = 19 716.67 €.

But I have for

Month - - - - - - Pilot Pay

2010-03 - - - - - € 4,638.00

2010-04 - - - - - € 12,872.00

Sum of pilot pay is equal to 4638 + 12 872 = 17 710 € ... it's now closer to the reality but something doesn't work properly. I went to phpvms_pirep and checked. I don't see any change here (pilotpay is 200 everywhere)

Additional : pilotpay in financial data should be presented like that (€ 4,638.00) as it's not a profit in YEaly and Summary view.

[Nabeel]

Ouch, I implemented your correction. Something doesn't work as well somewhere.

Pilot pay is 200 € / hr

Total Hours Flown: 98.35

normally I should have (98 x 200) + ((35 x 200) / 60) = 19 716.67 €.

But I have for

Month - - - - - - Pilot Pay

2010-03 - - - - - € 4,638.00

2010-04 - - - - - € 12,872.00

Sum of pilot pay is equal to 4638 + 12 872 = 17 710 € ... it's now closer to the reality but something doesn't work properly. I went to phpvms_pirep and checked. I don't see any change here (pilotpay is 200 everywhere)

That pilotpay is the per-hour rate of the pilo, so that's fine.

I think the difference might be that it's doing the flighttime as a decimal, and not as minutes. Let me try something

[Nabeel]

Ok, perhaps this is the right calculation:

SUM((TIME_TO_SEC(flighttime_stamp)/60) * (pilotpay/60)) as pilotpay

What it does is takes it all down to the minute - so hourly pay to the minute, and then the flight time in minutes, I think that's probably the most accurate then. What do you think? (Sorry, I can't personally run down a complete itemized list ATM)

[Guest lorathon]

Ok, perhaps this is the right calculation:

SUM((TIME_TO_SEC(flighttime_stamp)/60) * (pilotpay/60)) as pilotpay

What it does is takes it all down to the minute - so hourly pay to the minute, and then the flight time in minutes, I think that's probably the most accurate then. What do you think? (Sorry, I can't personally run down a complete itemized list ATM)

This works also. I am going to go with this one.

Thanks Nabeel.

[TennShadow]

This works also. I am going to go with this one.

Thanks Nabeel.

Good catch. I was starting to wonder about this.

[Cub'X]

Implementing this formula gives me a total of 19 177.89, so it's close to 19 716.67 but it make a 9.2% difference. I'll export the table to Excel and make an analysis this afternoon. I'll keep you posted.

Jm²

[Nabeel]

Implementing this formula gives me a total of 19 177.89, so it's close to 19 716.67 but it make a 9.2% difference. I'll export the table to Excel and make an analysis this afternoon. I'll keep you posted.

Jm²

Thanks. It could be the conversion of the time to minutes or the price to per-minute.

[Cub'X]

Hi Nabeel,

as promised, I performed an analysis, extracting all the PIREP under Excel. So that, two news, one good, one ... let say ... different.

The good one : the formula given is the right one, whatever the methodology used (line by line calculation the sum or sum then calculation). The calculation made initially on my side were wrong as they were based on 'flightime' column ... but this highlighted why I had a discrepancy in my intial calculation .....

The other one : I've noticed that, if a pilot enter a manual PIREP with 1.2 for 1 hour and 20 minutes (forgetting the to enter it as 1.20), the system consider this as 1 hour and 2 minutes (normally entered as 1.02). I put an extract of the base as picture attached. I don't know whether in terms of development you consider this as normal behaviour or if I've identified a bug. That's why I'm reporting that here.

Friendly yours,

Jm²

[Nabeel]

It's not a 'bug' per-say, but it should be entered as hours:minutes, so 02 or just 2 will count as 2 minutes as you've discovered. For 20 minutes you do have to enter 1:20. Leading 0's (02) are stripped off by PHP. That also matches up with how all the ACARS report time

[Cub'X]

Ok, I'll put an additional notice on PIREP page.

Thx

Jm²

[Parkho]

Ok, perhaps this is the right calculation:

SUM((TIME_TO_SEC(flighttime_stamp)/60) * (pilotpay/60)) as pilotpay

What it does is takes it all down to the minute - so hourly pay to the minute, and then the flight time in minutes, I think that's probably the most accurate then. What do you think? (Sorry, I can't personally run down a complete itemized list ATM)

Hi

Is it possible to do a calculation based on a percentage of the revenue for pilotpay?

meaning the pilot gets paid for example 0.03 % of the total revenue instead of per hour.

Thanks