Array of discounts to be applied based on the weeks booked all at different rate

[joesaddigh]

Hi,

 

Sorry about the title, it will hopefully become clearer when I explain my problem. OK the problem is as follows:

 

BACKGROUND INFORMATION

Admin - add a course and apply some discounts. There can be multiple discounts per course.

The format of a discount is:

(NAME-WEEKS-DISCOUNT)

NAME = The name of the discount - pointless mentioning

WEEKS = The discount will be applied for subsequent weeks after this value ( if WEEKS = 2 and user books for 3 then the DISCOUNT will be applied for weeks after this value ).

DISCOUNT = a decimal format for a discount rate which should be applied e.g. 10 OR 20 OR 50.6 etc NO MORE THAN 100 and more than 0.

 

The above is all implemented. I have a course calculator which will use this information quoted above:

For example: User picks a course and a duration ( 10 weeks )

 

When this is submitted I need to give them a price. 

 

As the weeks booked is variable and the amount of discounts for the chosen course is variable I would like to know the best way of calculating this. I currently have some code to do this and it seems to work but without a debugger I can't truely check it is accurate ( I think it might be doing something suttely wrong and if not the code is messy and hard to understand)

 

Can anybody give me a good solution mathmatically which I can implement where by I can work out how much the total will be:

 

Scenario in pseudo sort of code:

 

$weeksbooked = 30

 

// Get the discounts from db

// There are 3 so store these in array?

// Example of array contents

// Discount rate - qualifier(after how many weeks) - course price

array[ 0 ] = 10%-5-£100

array[ 1 ] = 20%-10-£100

array[ 2 ] = 30%-15-£100

 

So I would need to do the following maths:

5 weeks at normal price £100

5 weeks at £100 - 10%

5 weeks at £100 - 20%

THE REMAINING weeks at £100 - 30%

 

( £500 + £450 + £400 ) + REMAINING WEEKS at 30% discount ( £1350 + 1050 )

£2400

 

Please any ideas would be greatly received:

To prove that I am not trying to get free code here is my 'working' code for the whole thing!

 

<?php	
//Function used to generate a random password
function performcalculation()
{	
    // Connect to the database 
	require ("includes/connection.php");

	@$coursedetails = $_GET['coursedetails'];

	//Split the string getting the price and the type of accomodation
	$allcoursedetails = explode(',', $coursedetails);
	$courseid = $allcoursedetails[0];
	// Store the amount of weeks booked
	$weeksBooked = $allcoursedetails[1];

	// Now we have the relevant details, perform the calculation
    $query = "select discount_course.course_id, discount_course.discount_id, course.course_id, course.name AS coursename, course.level, course.price, discount.discount_id, discount.discount_name, discount.discount_qualifier_weeks, discount.discount_percentage
    FROM discount_course
    JOIN course
    ON discount_course.course_id = course.course_id
    LEFT JOIN discount
    ON discount_course.discount_id = discount.discount_id
    WHERE discount_course.course_id = '$courseid'
    ORDER BY discount.discount_id";

    //Use this query below 		  
    $result = mysql_query($query, $conn)
	    or die ("Unable to perform course query this is where we are trying to work out the discount");
	$numRows = mysql_num_rows( $result );

	// Initialise these variables
	$discountRate = 0;
	$qualifierWeeks = 0;
	$pricePerW = 0;
	$rate = 0;
	$arrayCounter = 0;
	$firstDiscountQualifier = 0;
	$firstDiscountRate = 0;

	// Create an array so that we can hold all of the discount rates
	$discountRates = array(); 

    while( $row = mysql_fetch_array( $result ) )
	{		    	
		// Get the first qualifier - we do not want to store anymore
		if ( $arrayCounter == 0 )
		{
			$firstDiscountQualifier = $row['discount_qualifier_weeks'];
			$firstDiscountRate = $row['discount_percentage'] / 100;
		}

		// Store the discount rate
		// Store the qualifier 
		// Store the course price per week
		$discountRate = $row['discount_percentage'];
		$rate = 1.0 - ( $discountRate / 100 );
		$qualifierWeeks = $row['discount_qualifier_weeks'];
		$pricePerW = $row['price'];

		// Add Details:
		// Rate
		// Qualifier
		// Course Price Per Week
		$arrayInfo = $discountRate . '-' . $rate . '-' . $qualifierWeeks . '-' . $pricePerW;
		$discountRates[ $arrayCounter ] = $arrayInfo;
		// Increment counter 
		$arrayCounter++;
	}

	// We have no discounts so just get the price otherwise we wont have it 
	// from the above loop
	if ( $numRows <= 0 )
	{
		$queryNoDiscounts = "select * FROM course
    			  WHERE course_id = '$courseid'";

    	//Use this query below 		  
    	$resultNoDiscounts = mysql_query($queryNoDiscounts, $conn)
	    	or die ("Unable to perform course query");
		$rowNoDiscounts = mysql_fetch_array( $resultNoDiscounts );
		$pricePerW = $rowNoDiscounts['price'];
	}

	// Return message if price is not greater than zero
	if ( $pricePerW <=0 )
	{
		return "0 - THE PRICE IS SET TO ZERO";
	}


	// Now work out the total price based on the discount and the discount if they qualify for it
	if( $weeksBooked > $firstDiscountQualifier &&
		$firstDiscountQualifier > 0 )
	{
		// Work out the price of the duration that does not apply for the relative discounts
		$nonDiscountPrice = $firstDiscountQualifier * $pricePerW;
		$remainingWeeks = $weeksBooked - $firstDiscountQualifier;

		// As we are in here we are entitile to some discount rates.
		// There might be many so we need to loop through the array which we have just created and 
		// do the calculations 	
		$rate = 0;
		$qualifier = 0;
		$price = 0;
		$counterForDiscountRates = 0;
		$totalDiscounted = 0;
		$difference = 0;

		foreach ( $discountRates as &$rate ) 
		{

			$discountSafetyGuard = explode( '-', $rate );
			$qualifierSafetyGuard = $discountSafetyGuard[ 2 ];

			// This means that the calculation will not be affected unless it should.
			// 
			if ( $weeksBooked >  $qualifierSafetyGuard )
			{
				// If we have been in here more than once then lets subtract the extra that we must have added last time
				// Do this before we get the new data
				// Subtract the extra that we added on. We made an assumption that it would be that rate for the entire 
				// duration but as we are in here more than once there is a new rate to be applied for the rest of the weeks
				if ( $counterForDiscountRates > 0 )
				{
					// Get the new qualifier so that we know how many weeks we needed to discount with the previous rate
					//Split the array getting the data that we need
					$discountCopy = explode( '-', $rate );
					$discountrateNew = $discountCopy[ 1 ];
					$qualifierNew = $discountCopy[ 2 ];

					// Calculate how many weeks that we should have applied for the previous rate
					$difference += $qualifierNew - $qualifier;
					// Now how many weeks we went over by.
					$durationToSubtract = ( $remainingWeeks - $difference );


					// Now subtract this figure from the total so that we can add on the new rate
					//echo '( ' . $durationToSubtract . ' * ' . $pricePerW . ' ) *' . $discountrate;
					$subtract = ( $durationToSubtract * $pricePerW ) * $discountrate;
					$totalDiscounted -= $subtract;						

					// Work out how many weeks this new rate should be applied for the remainder
					// of the weeks left
					// This is a sum of the remaining weeks variable minus the amount of weeks
					// that the first calculation accounted for 
					$newRateAmountOfweeks = $weeksBooked - $qualifierNew;

					// Do the actual calculation by adding on the new total after taking into consideration
					// the old and new rates.
					//echo $totalDiscounted . '+=( ' . $newRateAmountOfweeks . ' * ' . $pricePerW . ' ) *' . $discountrateNew;
					$totalDiscounted += ( $newRateAmountOfweeks * $pricePerW ) * $discountrateNew;						
				}

				//Split the array getting the data that we need
				$discount = explode( '-', $rate );
				$discountPercent = $discount[ 0 ];
				$discountrate = $discount[ 1 ];
				$qualifier = $discount[ 2 ];
				$price = $discount[ 3 ];

				if ( $counterForDiscountRates == 0 )
				{
					$totalDiscounted += ( $remainingWeeks * $pricePerW ) * $discountrate;
				}

				$counterForDiscountRates++;
			}
		}	

		// Return the price calculation
		$total = $nonDiscountPrice + $totalDiscounted;
		return round( $total );
	}
	// They have selected the amount of weeks that will start to apply for the discounted rate.
	// Set a message to say that any subsequent weeks will be charged at a discounted rate.
	elseif( $weeksBooked == $firstDiscountQualifier )
	{
		/*
		echo $message = "You have qualified for the discount, any subsequent weeks will be charged at a 
					discounted rate. Each week will be " . $firstDiscountRate * 100 . "% cheaper";
		*/

		$total = $weeksBooked * $pricePerW;
		return round( $total );
	}
	// They have selected the weeks less that the discounted rate
	else
	{
		/*
		echo $message = "You have nearly qualified for the discount. If you book for " . $firstDiscountQualifier . " weeks you will get a " . $firstDiscountRate * 100 . "% discount on any subsequent weeks";
		*/			

		$total = $weeksBooked * $pricePerW;
		return round( $total );
	}  
}
?>	

 

 

 

 

 

 

 

 

 

[mikecampbell]

I think it would be best if you stored your discount parameters each in a separate field in the DB, as opposed to mashing them together in a string. But that is neither here nor there.

 

First break the discount parameters apart (discarding symbols like % and £) and place them into three arrays, $rates, qualifiers, $prices so that you can sort.

 

Then something like this would work.

 

array_multisort($qualifiers, $rates, $prices);
$remaining_weeks = $weeksbooked;
$total = 0;
$last_pcnt = 0;
$last_price = 100; //how do you know what price to charge when there is no discount?
$last_qualifier = 0;
for ($i=0;$i<count($qualifiers);$i++) {
  if ($remaining_weeks>($qualifiers[$i]-$last_qualifier))
    $total += (100-$last_pcnt)/100*$last_price*($qualifiers[$i]-$last_qualifier);
  else
    $total += (100-$last_pcnt)/100*$last_price*$remaining_weeks;
  $weeks = $weeks-($qualifiers[$i]-$last_qualifier);
  $last_pcnt = $rates[$i];
  $last_price = $prices[$i];
  $last_qualifier = $qualifiers[$i];
  if ($weeks<=0) break;
}

if ($remaining_weeks>0)
  $total += (100-$last_pcnt)/100*$last_price*$remaining_weeks;

[joesaddigh]

First of all thank you so much for your reply, I didnt expect anybody to take the time to read it as it was quite long winded.

 

I will try this out later.. Have had a little read through and I can follow it which is a good start. It also looks really simple compared to reading the crap that I wrote!!

 

I will be in touch, Thanks again.

 

Joe

 

P.s.

 

$last_price = 100; //how do you know what price to charge when there is no discount?

 

I get the price from the DB as the course details are passed through after a simple query.

[joesaddigh]

Hi,

 

I have been struggling with this....

Can you see any reason why this wouldnt work?

The problem is that the price never gets any higher once it meets the first qualifier discount.

 

 

<?php
//Function used to generate a random password
function performcalculation()
{	
	// Connect to the database 
	require ("includes/connection.php");

	@$coursedetails = $_GET['coursedetails'];

	//Split the string getting the price and the type of accomodation
	$allcoursedetails = explode(',', $coursedetails);
	$courseid = $allcoursedetails[0];
	// Store the amount of weeks booked
	$weeksBooked = $allcoursedetails[1];

	// Now we have the relevant details, perform the calculation
	$query = "select discount_course.course_id, discount_course.discount_id, course.course_id, course.name AS coursename, course.level, course.price, discount.discount_id, discount.discount_name, discount.discount_qualifier_weeks, discount.discount_percentage
	FROM discount_course
	JOIN course
	ON discount_course.course_id = course.course_id
	LEFT JOIN discount
	ON discount_course.discount_id = discount.discount_id
	WHERE discount_course.course_id = '$courseid'
	ORDER BY discount.discount_id";

	//Use this query below 		  
	$result = mysql_query($query, $conn)
		or die ("Unable to perform course query this is where we are trying to work out the discount");
	$numRows = mysql_num_rows( $result );

	// Create seperate arrays to hold values
	$qualifiers = array();
	$rates = array();
	$price = 0.0;
	$iCounter = 0;

	// Populate arrays
	while( $row = mysql_fetch_array( $result ) )
	{		    	
		$price = $row['price'];
		$rates[ $iCounter ] = $row['discount_percentage'];
		$qualifiers[ $iCounter ] = $row['discount_qualifier_weeks'];
		$iCounter++;
	}

	array_multisort( $qualifiers, $rates );
	$remaining_weeks = $weeksBooked;
	$total = 0.0;
	$last_pcnt = 0.0;
	$last_qualifier = 0.0;

	for ( $i = 0 ;$i < count( $qualifiers ); $i++ ) 
	{
		//echo "The remaining weeks " . $remaining_weeks;
		//echo "Qualifier in array " . $qualifiers[$i];
		//echo "Last Qualifier " . $last_qualifier;


		if ( $remaining_weeks > ( $qualifiers[$i] - $last_qualifier ) )
		{
			echo $addValue = ( ( 100 - $last_pcnt ) / 100 * $price * ( $qualifiers[$i] - $last_qualifier ) );
			$total += $addValue;
			echo "<br/>";

			/*
			$r = ( $qualifiers[$i] - $last_qualifier );
			echo "<br/>";
			echo "The calculation is: " .$total. "+ ( 100 - " .$last_pcnt. " ) /100 * " .$price. "*" .$r; 
			echo "<br/>";
			*/
		}
		else
		{
			$addValue = ( ( 100 - $last_pcnt ) / 100 * $price * $remaining_weeks );
			$total += $addValue;
			echo "<br/>";
		}		

		$weeksBooked = ( $weeksBooked -( $qualifiers[$i] - $last_qualifier ) );
		$last_pcnt = $rates[$i];
		$price = $prices[$i];
		$last_qualifier = $qualifiers[$i];
		if ( $weeksBooked <=0 ) 
		{
			break;
		}
	}

	if ( $remaining_weeks >0 )
	{
	     $addValue = ( ( 100 - $last_pcnt ) / 100 * $price * $remaining_weeks ); 
		 $total += $addValue;
	}

	return round( $total );
}
?>

[mikecampbell]

You can get rid of the line that says

$price=$prices[$i];

[joesaddigh]

I cant believe I didnt see that!! Thank You.

 

The logic is still not quite right though as it is giving  a value way higher than expected! I will have another play with it and see if I can work it out!

 

Do you use a debugger? If so is it easy to setup as I am used to using a nice IDE which makes life a lot easier

 

Cheers.

Joe

[joesaddigh]

I think it might just be working!!

 

<?php
//Function used to generate a random password
function performcalculation()
{	
	// Connect to the database 
	require ("includes/connection.php");

	@$coursedetails = $_GET['coursedetails'];

	//Split the string getting the price and the type of accomodation
	$allcoursedetails = explode(',', $coursedetails);
	$courseid = $allcoursedetails[0];
	// Store the amount of weeks booked
	$weeksBooked = $allcoursedetails[1];

	// Now we have the relevant details, perform the calculation
	$query = "select discount_course.course_id, discount_course.discount_id, course.course_id, course.name AS coursename, course.level, course.price, discount.discount_id, discount.discount_name, discount.discount_qualifier_weeks, discount.discount_percentage
	FROM discount_course
	JOIN course
	ON discount_course.course_id = course.course_id
	LEFT JOIN discount
	ON discount_course.discount_id = discount.discount_id
	WHERE discount_course.course_id = '$courseid'
	ORDER BY discount.discount_id";

	//Use this query below 		  
	$result = mysql_query($query, $conn)
		or die ("Unable to perform course query this is where we are trying to work out the discount");
	$numRows = mysql_num_rows( $result );

	// Create seperate arrays to hold values
	$qualifiers = array();
	$rates = array();
	$price = 0.0;
	$iCounter = 0;

	// Populate arrays
	while( $row = mysql_fetch_array( $result ) )
	{		    	
		$price = $row['price'];
		$rates[ $iCounter ] = $row['discount_percentage'];
		$qualifiers[ $iCounter ] = $row['discount_qualifier_weeks'];
		$iCounter++;
	}

	array_multisort( $qualifiers, $rates );
	$remaining_weeks = $weeksBooked;
	$total = 0.0;
	$last_pcnt = 0.0;
	$last_qualifier = 0.0;

	for ( $i = 0 ;$i < count( $qualifiers ); $i++ ) 
	{			
		if ( $remaining_weeks > ( $qualifiers[$i] - $last_qualifier ) )
		{
			$total += (100-$last_pcnt)/100*$price*($qualifiers[$i]-$last_qualifier);
		}
		else
		{
			/*
			echo "In ELSE TOTAL:" . $total;
			echo "<br/>";
			echo "<br/>";
			echo "(100-".$last_pcnt.")/100*".$price."*".$remaining_weeks;  
			*/
			$total += (100-$last_pcnt)/100*$price*$remaining_weeks;  
		}		

		$remaining_weeks = ( $remaining_weeks -( $qualifiers[$i] - $last_qualifier ) );
		$last_pcnt = $rates[$i];
		$last_qualifier = $qualifiers[$i];
		if ( $remaining_weeks <=0 ) 
		{
			break;
		}
	}

	if ( $remaining_weeks >0 )
	{
	    $total += (100-$last_pcnt)/100*$price*$remaining_weeks;
	}

	return round( $total );
}
?>

 

Thank you very much this is a very neat solution!! I would have never thought of that.