Store Discount

[mattsc]

I am trying to figure out the best way to do this. So where I work, there is a manufacturer that has a "5/6" Discount, here it is explained:

For every 5 pieces of Wee Forest Folk you purchase, receive a 50% Discount on the 6th piece:

6th Piece's retail price must not be greater than one of the first 5 pieces. If so, then the discount will only be up to 50% of the highest price of one of the first 5 pieces.

The majority of this code was written by the previous developer and I am trying to fix it. I have everything working fine, except I need to exclude the most recent piece if the price of it was more than the previous ones. I am unsure of the best way to do this. Every purchase of this manufacturer is stored in it's own table and it grabs the highest price once they have ordered the right amount.

Here is what I have right now:

if ($summary[15] > 0) {
		$billing_phone=$FORM{billing_phone};
		$billing_phone=~s/\D//g;
		if ($FORM{email}) {
$sql = qq{
SELECT 
	SUM(weeforest.quantity) 
FROM 
	weeforest,orders 
WHERE 
	weeforest.ordernumber = orders.number 
	AND weeforest.email="$FORM{email}" 
	AND weeforest.email <> "noemail\@cherrylanekeepsakes.com" 
	AND weeforest.email <> "test\@test.com" 
	AND (
		orders.order_status="New" 
		OR orders.order_status="shipped" 
		OR orders.order_status="Pending"
		) 
	GROUP BY email 
};
		} 

	else {

$sql = qq{
SELECT 
	SUM(weeforest.quantity) 
FROM 
	weeforest,
	orders 
WHERE 
	weeforest.ordernumber = orders.number 
	AND weeforest.phone="$billing_phone" 
	AND (
		orders.order_status="New" 
		OR orders.order_status="shipped" 
		OR orders.order_status="Pending"
		) 
	GROUP BY phone 
};
		}

		$sth=$dbh->prepare ($sql);
		$sth->execute;
		($weequantity) = $sth->fetchrow_array ();

		$weetotal=$weequantity + $summary[15];

		if ($FORM{email}) {
			$sql=qq{
SELECT 
	price 
FROM 
	weeforest,
	orders 
WHERE 
	weeforest.ordernumber = orders.number 
	AND weeforest.email="$FORM{'email'}" 
	AND weeforest.email <> "noemail\@cherrylanekeepsakes.com" 
	AND weeforest.email <> "test\@test.com" 
	AND (
		orders.order_status="New" 
		OR orders.order_status="shipped" 
		OR orders.order_status="Pending"
		) ORDER BY price 
};
		} 
		else {
$sql=qq{
SELECT 
	price 
FROM 
	weeforest,
	orders 
WHERE 
	weeforest.ordernumber = orders.number 
	AND weeforest.phone="$billing_phone" 
	AND (
		orders.order_status="New" 
		OR orders.order_status="shipped" 
		OR orders.order_status="Pending"
		) 
	ORDER BY price 
};
		}

		$sth=$dbh->prepare ($sql);
		$sth->execute;

		while(($weeprice) = $sth->fetchrow_array ()) {
			$weeprice[$k]=$weeprice;
			$k++;
		}

$sql = qq[
SELECT 
	c.price 
FROM 
	cartdata c, 
	$config{database}{db_view} p 
WHERE 
	c.product = p.number 
AND 
	c.customer="$FORM{uid}" 
AND 
	p.manufacturer LIKE '%wee%'
];

		$sth=$dbh->prepare ($sql);
		$sth->execute;
		while(($weeprice) = $sth->fetchrow_array ()) {
			$weeprice[$k]=$weeprice;
			$k++;
		}

		@weeprice=sort numerically_desc (@weeprice);

		if ($weetotal >= 6) {

			if ($discount_display ne "") {$and=" and";} else {$and="";}
			$weediscount=$weeprice[0]/2;
			$discount=$discount+$weediscount;
			$discount_display="Wee Forest Folk Discount $and $discount_display";
			$FORM{'weeforest_discounts'}++;
		}
	}