PHP Calculating total depending on rates

[phorce]

I'm having the following difficultly:

I have a table rates which contains the follow:

-> id 
-> rate_from
-> rate_to 
-> price 

A user can have many rates, and for an example:

between 0 - 10 hours the user charges 10.00 an hour
between 10 - 100 hours the user charges 20.00 an hour

What I want to do is calculate, let's say that the person has worked 40 hours then it would be the following (10 * 10 + 30 * 20)

But the issue is, how can I achieve this using PHP? They could potentially have worked 48.10 for example.

[jkon]

Hello phorce ,
I can see three issues with your table

1. Naming . From your example its obvious that you didn't meant rate_from , rate_to but hour_from , hour_to or even better fromHour , toHour . Its important to name our fields for what they represent in order for the code to make sense.

2. Closed and open sets. When you wrote:
   between 0 - 10 hours the user charges 10.00 an hour
   between 10 - 100 hours the user charges 20.00 an hour
   its not clear what happens if the user have worked exactly 10 hour.
   I guess what you meant is [rate_from,rate_to) that means:
   rate_from <= hours < rateto
   @see https://en.wikipedia.org/wiki/Interval(mathematics)#Including_or_excluding_endpoints

3. Its easier when we have sequential sets to define only the start point. There are many reasons for it but here are three of them:

a. In your case if the the table has [0,10) and [12,15) when the input value (hours in your case) is 11 you have problem.
b. In the way that you have described your data nothing stop having [0,10) and [7,15) and when the input value (hours in your case) is 11 you have problem.
c. If the user has worked 120 hours in your example you have problem also.
You could “bypass” those two problems introducing a “business rule” but this is a rather ugly fix that is not in the data layer (case NoMatch). The most logical one would be if the hours don't match any set to be used the exactly previous set.

Here is a solution using your table (that I really think needs rewriting) and the data provided in the example (this is a simple solution, I am sure that there can be a most efficient one) . Also our code must take in consideration what will happen if there aren't any data in the table (case NoData).

<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');

$dbSchema = "test_db";
$dbUser = "testDbUser";
$dbPassword = "testPassword";
$pdoOptions = array(PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false
        ,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);

$db = new PDO("mysql:dbname=".$dbSchema.";host=localhost"
    ,$dbUser,$dbPassword);

$statement = $db->prepare("SELECT * FROM rates ORDER BY rate_from ASC");
$statement->execute();
$rateRows = $statement->fetchAll();

function calcPrice($rateRows,$hours)
{
    $totalPrice = 0;
    $hoursLeft = $hours; // How many hours haven't we priced yet 
    if(count($rateRows) > 0) // Because case NoData (see above) 
    {
        foreach($rateRows as $row)
        {
            $rateHours = calcHoursInsideSet($row["rate_from"]
                , $row["rate_to"], $hours);
            if($rateHours > 0)
            {
                $totalPrice += $rateHours * $row["price"];
                $hoursLeft -= $rateHours;
            }
        }

        // Case NoMatch (see above) 
        if($hoursLeft > 0)
        {
            $lastRow = $rateRows[count($rateRows) - 1];
            $totalPrice += $hoursLeft * $lastRow["price"];
        }
    }
    return $totalPrice;
}

function calcHoursInsideSet($from,$to,$hours)
{
    if($hours > $to)
    {
        return $to - $from;
    }
    // Because we said that our set is closed to the start end
    else if($hours == $from) 
    {
        return $hours;
    }
    else if($hours > $from && $hours < $to)
    {
        return $hours - $from;
    }
    else 
    {
        return 0;
    }
}
?>

Examples:<br/>
48.10 : <?=calcPrice($rateRows, 48.1)?><br/>
10 : <?=calcPrice($rateRows, 10)?><br/>
140 : <?=calcPrice($rateRows, 140)?><br/>
5 : <?=calcPrice($rateRows, 5)?><br/>

[diafol]

As we're in a generous mood, thought I'd give my version:

class Salary
{
    private $hours;
    private $rates;
    private $keys = [0,1,2];
    private $total = 0;
    private $breakdown = [];

    //Accept data in [from_hours, to_hours, hourly_pay] - default numerically-indexed array
    //If using associated array - supply a $keys array e.g. ['from','to','price']
    //Or if numeric array is out of order of ideal, supply $keys array for this e.g. [1,2,0]

    public function __construct( array $rateArray, array $keys = NULL )
    {
        $this->setRates( $rateArray, $keys );
    }

    public function setRates( array $rateArray, array $keys = NULL )
    {
        if(!is_null($keys)) $this->keys = $keys;
        $this->rates = $rateArray;
        return $this;
    }

    public function hoursWorked( float $hours )
    {
        $this->hours = $hours;
        $this->calc();
        return $this;
    }

    public function startOver()
    {
        $this->total = 0;
        $this->breakdown = [];
        return $this;
    }

    public function total()
    {
        return $this->total;
    }

    public function breakdown()
    {
        return $this->breakdown;
    }

    public function output()
    {
        return ['hours'=>$this->hours, 'total' => $this->total, 'breakdown' => $this->breakdown];
    }

    private function calc()
    {
        foreach($this->rates as $rate){
            if($rate[$this->keys[0]] >= $this->hours) continue;
            $upper = ($rate[$this->keys[1]] > $this->hours) ? $this->hours : $rate[$this->keys[1]];
            $r = $rate[$this->keys[2]];
            $h = $upper - $rate[$this->keys[0]];
            $st = $r * $h;
            $this->total += $st;
            $this->breakdown[] = ['hours'=>$h,'rate'=>$r,'subtotal'=>$st];
        }
    }
}

Usage

$rates = [[0,10,10],[10,50,20],[50,80,40]]; //similar to PDO::NUM you could expect for a fetchAll() for those 3 fields 

$s = new Salary( $rates ); 

//Display
echo "<pre>";
print_r( $s->hoursWorked(10.5)->output() );
//Instead of creating a new Object for each employee (which you can), you can use it as a glorified calculator
print_r( $s->startOver()->hoursWorked(100)->output() );

Gives:

Array
(
    [hours] => 10.5
    [total] => 110
    [breakdown] => Array
        (
            [0] => Array
                (
                    [hours] => 10
                    [rate] => 10
                    [subtotal] => 100
                )

            [1] => Array
                (
                    [hours] => 0.5
                    [rate] => 20
                    [subtotal] => 10
                )
        )
)
Array
(
    [hours] => 100
    [total] => 2100
    [breakdown] => Array
        (
            [0] => Array
                (
                    [hours] => 10
                    [rate] => 10
                    [subtotal] => 100
                )

            [1] => Array
                (
                    [hours] => 40
                    [rate] => 20
                    [subtotal] => 800
                )

            [2] => Array
                (
                    [hours] => 30
                    [rate] => 40
                    [subtotal] => 1200
                )
        )
)

This does not address the issue outlined by jkon about common end/start points. However, I understand the way this has been designed to be human readable...

0 < x <= 10, 10 < x <= 20 etc.

I don't think it's a big issue unless you code it so that you make use of the common limit twice.
With regard to the "missing hours" - e.g. 0-10, 15-50 so 11-15 missing - take care - but you could write a function to check the ensuing array from the DB for "continuity".
The only proviso I'd add would be the upper limit. The number of hours in a week = 378 (unless you count daylight saving when it could be +/- 1). Anyhow, you'd place the upper limit in your last pay rate, whether this is the theoretical maximum (e.g. paying somebody for their time away from home) or the maximum number of hours you allow somebody to work in a week.

Note my last usage example: only 80 hours can be paid due to maximum set at 80 hours, even though 100 hours worked.

Sorry, starting to ramble...

[jkon]

Diafol apparently we wrote our answers almost in vain. The creator of this thread just created it and didn't came back to see the answers (@see user logs). I wrote “almost” because even in that context when you exchange code you always have something to gain if you are willing to.

Our different results produced by different assumptions that the creator of this thread didn't made clear.

I thought that it is [rate_from,rate_to) you thought that is (rate_from,rate_to] so in the data given in 10 hours the total price from my approach is 200 from yours is 100. 140 hours (with data given in the creator example) I price them 2700 you price them 1900 because I use a “fall back” in last known price record when there isn't one , and you didn't price them at all.

None approach is “correct” because we have no idea what the creator of this thread meant. Its great that your approach is OOP , I haven't wrote function in this way till my last thread here. Its also great that you break down the results , but is it needed ?

In my code I have a foreach with if($rateHours > 0) inside it , there could be also an else in that , with break; because the structure supposed to be sequential and if there isn't any pricing hour in that row it won't be in the next ones also.

The data structure is the problem. When we have sequential sets of numbers its really easier and cleaner to define only the start point , and in a RDBMS to make this point as a unique index.

[diafol]

I agree with "only the start point" being cleaner however that would still require a "0" entry.

I did come up with this method for checking continuous or contiguous:

public function checkRates()
    {
        $tos = [];
        $froms = [];
        foreach($this->rates as $r){
            $tos[] = $r[$this->keys[0]]; //this is for rate_to
            $froms[] = $r[$this->keys[1]]; // this is for rate_from
            //Create error routine of your choice...
            if($r[$this->keys[0]] <= $r[$this->keys[1]]){
                echo "BOO weird rate!!!"; //check "from" is not more or some to "to"
            }
        }
        $cntIS = count(array_intersect($tos, $froms)); //count common end/start
        $cntEXP = count($this->rates) - 1; //count entries-1
        //Create error routine of your choice...
        if($cntEXP !== $cntIS){
            echo "BOO not continuous";
            exit;
        }
    }

It's not complete, but is gettign there. I used the assumption that there would be common start/end for (records-1) entries.

[diafol]

Better one here after sorting:

private function checkRates()
    {
        $tos = [];
        $froms = [];
        foreach($this->rates as $k=>$r){
            $froms[$k] = $r[$this->keys[0]];
            $tos[$k] = $r[$this->keys[1]];
        }
        array_multisort($froms, SORT_ASC, $this->rates);

        $previousValue = 0;
        foreach($this->rates as $k=>$r){
            if($r[$this->keys[0]] != $previousValue) {
                echo "<pre>";
                echo "<b>Error:</b> Array Item [$k] - The 'from' value ({$r[$this->keys[0]]}) should actually be $previousValue<br/><br/>";
                print_r($this->rates);
                echo "</pre>";
                exit;
            }
            $previousValue = $r[$this->keys[1]];
        }
        return true;
    }

Just check the previous value to see that they're the same.