trying to filter input to @array

[bazz]

Hi,

Gradually, I am moving forwards with this.

The following select query brings in room prices by date. Each date may have a priority of 'a' but, it always has a priority of b.

If priority eq 'a' put it into the array
if priority eq 'b' and there was no 'a' put it into the array.

priority 'a' means that there is a date-specific price.
the selection for the array should revert to the high season default price (priority 'b') if there is no 'a'.

Can anyone please help me to achieve this. Currently, it still puts all prices into the array irrespective of priority code.

I tried without success to make the select do it. Then I tried fiddling with the output/processing of the array and now I have tried to filter what is put into the array.

bazz

here is the code


my $count = 0;

sub get_average_price {

my @prices=();
$count++;



foreach my $timestamp (sort @daylist) # run the loop for each date of the visit
{
my $iso = convert_to_iso($timestamp); # converts to iso format to match with db values

my ( $year, $month, $date ) = split "-", $iso, 3; # no longer needed (check)
my $day_number = get_day_number($timestamp);

#print qq( dn=$day_number<br /> ); # outputs correctly

my $sth = $bookings_db_connect->prepare ("SELECT
RR.price,
RR.priority
FROM tbl_room_rates RR
WHERE RR.room_type_id = ?
AND RR.business_id = ?
AND RR.period_start <= ?
AND RR.period_end >= ?
AND RR.day_start <= ?
AND RR.day_end >= ?

") or die "prepare statement failed: $DBI::errstr\n";


$sth->execute($room_type_id, $business_id, $iso, $iso, $day_number, $day_number );



while (my @fields = $sth->fetchrow_array )
{

if ($fields[1] eq 'a')
{
push (@prices, @fields[0]);
}
elsif ($fields[1] eq 'b')
{
push (@prices, $fields[0]);
}

}

}


foreach my $price (sort @prices)
{

if ($count > $count_after_running) zero the total between loop runs
{
$price_total = 0;
}


print qq(price = $price <br /> );
$price_total = $price_total + $price;


}


$average_price = $price_total / $number_of_nights;

$count_after_running = $count;

my $rounded_average_price = sprintf("%.2f", $average_price);
return $rounded_average_price;

}

[FishMonger]

Each date may have a priority of 'a' but, it always has a priority of b.

priority 'a' means that there is a date-specific price.
the selection for the array should revert to the high season default price (priority 'b') if there is no 'a'.That would indicate to me that there should be 2 rate fields that get selected, but your select statement only has 1.

Do you realize that this: while (my @fields = $sth->fetchrow_array )
{

if ($fields[1] eq 'a')
{
push (@prices, @fields[0]);
}
elsif ($fields[1] eq 'b')
{
push (@prices, $fields[0]);
}

}
reduces to this:
while (my @fields = $sth->fetchrow_array )
{

push (@prices, $fields[0]);

}
Why is $count declared outside of the sub and where is $count_after_running being declared?

You should be passing @daylist to the sub (either by value or by reference) instead of directly accessing it.

To me, it seams a little redundant to have RR.day_start and RR.day_end in the where clause given that it already has RR.period_start and RR.period_end.

I'm not sure how to help correct your sub because I'm unclear about what you're doing with the priorities 'a' and 'b'.

[bazz]

Thanks FishMonger,

OK let's see if I can explain....

There is only one rate field because it seemed to me to be the more concise way of normalising the table structure.

the tables are like this



tbl_room_details
| room_id | room_type_id |
| 0001 | 00000000001 |

tbl_room_types
| room_type_id | room_type |
| 0001 | Double_Ensuite |

tbl_rooms_rates
|room_rate_id | business_id | room_type_id | period_start | period_end | day_start | day_end | priority |
| 0000001 | 0015 | 00000000001 | 2008-04-01 | 2008-09-30 | 0 | 6 | b |
| 0000002 | 0015 | 00000000001 | 2008-04-01 | 2008-09-30 | 1 | 1 | a |


To explain this...

1. There are two overlapping timeframes, where one price or another, applies. Both are applicable to a time of year - a high season, from April to Sept (incl) but the the difference comes in. The hotel may have a special price for a Monday during that period (or another period [it's flexible]), and so if there is a special day rate, it should be put into the array. Else, the 'default' price should be stored in it. So Fumigator's suggestion (and I see how it could work in theory), was that I capture the appropriate proce, as determined by prioritising the 'priority' value.

2. $count is used (perhaps temporarily), because I found it necessary to empty the array, between loops of 'the date of visit'.

3. Not sure I understand why to pass the var @daylist to the sub rather than by calling it in. The value is being passed to the sub.

4. I hope I have explained the dates issue above.

if you still have the link I sent you a few days ago, then looking for availability on 15th May for 4 nights, should show you more than four prices for the visit. That is because until I process the priority, it is returning both the day price and the default price for the 6 month period.

Maybe I have got so tangled up with this that I can't think straight and it wouldn't surprise me.

So whatever suggestion you might have; I am 'all ears'.

bazz

[FishMonger]

I'm not totally convinced that your current structure for tbl_rooms_rates is the best, but off hand I'm not sure what the best structure would be.

As I see it, if the day_number is in both priorities, you want to select the highest priority (i.e. price). If so, I think all that's needed is an ODER BY and LIMIT clause.
my $sth = $bookings_db_connect->prepare ("SELECT
RR.price,
RR.priority
FROM tbl_room_rates RR
WHERE RR.room_type_id = ?
AND RR.business_id = ?
AND RR.period_start <= ?
AND RR.period_end >= ?
AND RR.day_start <= ?
AND RR.day_end >= ?
ORDER BY RR.priority ASC
LIMIT 1
") or die "prepare statement failed: $DBI::errstr\n";The prepare statement should be prior to the foreach loop but the execute statement would remain in the loop.

[FishMonger]

3. Not sure I understand why to pass the var @daylist to the sub rather than by calling it in.Using a var directly like that is considered poor practice. It makes it unclear where the var came from and can create scoping and/or other problems. For example, lets assume that you decided to move your subs to a separate module that you called in via a use statement. In that case, the @daylist in the sub no longer refers to the @daylist that was defined in the main script.

The value is being passed to the sub.From that I assume you mean that you're calling the sub like this:get_average_price(@daylist);If so then your sub doesn't reflect it. The sub would work exactly the same and give the exact same results if you call it like this:get_average_price('apple pie');

The sub needs to create a local var that is either a copy of the original array (called pass by value) or a scalar that is a reference to the array.
get_average_price(@daylist);orget_average_price(\@daylist);
The sub would receive it like this:
sub get_average_price {

my @list_of_days = @_; # receive a copy
or
my $list_of_days = shift; # receive a ref that points to the original

[bazz]

Ah, I see what you mean now. :rolleyes::eek:

I need to read through that a few more times because I think there are improvements that I can make elsewhere too now.

Thanks again,

bazz

[bazz]

FishMonger your a star!!!

I had tried order by before but I had completely forgotten about the LIMIT clause. And I hadn't known of the ASC or DESC restriction either.

Many many thanks.

bazz