data retrieved but looking for best way to output it.

[bazz]

Hello,

I have retrieved (from MySQL db), the data I need but I am not sure whether I have the pricing part correct.

query code:

my $sth = $bookings_db_connect->prepare ("SELECT
RR.room_id,
RD.room_type,
RD.room_number,
RD.room_name,
RD.description_short,
RD.max_occupancy,
RD.image_url,
RR.room_rate
FROM tbl_rooms_details RD, tbl_room_rates RR
WHERE RR.room_id = RD.room_id
AND RR.date_from <= ?
AND RR.date_to >= ?
AND RD.room_id NOT IN ( SELECT
booking_id
FROM tbl_rooms_booked
WHERE check_in_date <= ?
AND check_out_date >= ? )
") or die "prepare statement failed: $DBI::errstr\n";

$sth->execute($departure_date_requested, $arrival_date_requested, $arrival_date_requested, $departure_date_requested );




dumper output:

$VAR1 = { '0000000002' =>
{ 'Suite' =>
{ '11' =>
{ 'The Conal Suite' =>
{ 'A large double suite with side views over Bangor Bay' =>
{ '2' =>
{ '\'image' =>
{ '65.32' => 1, '10.32' => 1, '125' => 1, '80.32' => 1, '40.32' => 1 }
}
}
}
}
}
}
, '0000000001' =>
{ 'Standard Double' =>
{ '12' =>
{ 'Standard Double' =>
{ 'A beautiful double room' =>
{ '2' =>
{ '\'image' =>
{ '90' => 1
}
}
}
}
}
}
}
};



Now I have been outputting it with a series of foreach loops but that stumps me on the prices section - highlighted.

As I output the data, I want to calculate the average cost of each room per night and show it in the output. Currently the output amasses all room rates, irrespective of room.

I ask for help please on whether I should change my query or if not, what way should I output it all.

The final output needs to be like :

image_url
room_name or room_number
type
occupancy
description
average rate (for chosen stay)

bazz

[FishMonger]

Please show us your loop that builds the hash and your series of foreach loops.

For each of those 2 rooms in your example, what are the expected average rates and what is the significants of the one room with 5 widely differing rates?

[bazz]

Hi FishMonger,

The differing rates shown are just for testing purposes.

The finished booking engines will need to allow the business owner/manager, to set their own prices where they will in various cases use these methods:

fixed price per room_type per period of time
weekend prices per room_type
prices per specific night.

The average rate is detemrined by the visitors chosen dates where the query takes their $first_night (date) and $number_of_nights (say 4) and gets the rate for each night. The I need to find the average of those rates to display.

here is the set of foreach loops you asked for



my $total=0;

foreach my $room_id ( sort keys %rooms )
{

foreach my $room_type (sort keys % { $rooms{$room_id}} )
{

foreach my $room_number (sort keys % { $rooms{$room_id}{$room_type}} )
{

foreach my $room_name (sort keys % { $rooms{$room_id}{$room_type}{$room_number}} )
{

foreach my $description_short (sort keys % { $rooms{$room_id}{$room_type}{$room_number}{$room_name}} )
{

foreach my $max_occupancy (sort keys % { $rooms{$room_id}{$room_type}{$room_number}{$room_name}{$description_short}} )
{

foreach my $image_url (sort keys % { $rooms{$room_id}{$room_type}{$room_number}{$room_name}{$description_short}{$max_occupancy}} )
{

foreach my $rate (sort keys % { $rooms{$room_id}{$room_type}{$room_number}{$room_name}{$description_short}{$max_occupancy}{$image_ur l}} )
{

$total=$total+$rate;

print qq(
<table>
<tr>
<td class="bookings_image">
$image_url
<p><a href="/cgi-bin/rooms_individual_details.pl/$room_id"> More Details >></a></p>
</td>
<td>
<table>
<tr>
<td><b>Room Name or Number :</b> $room_name . $total. </td>
</tr>
<tr>
<td><b>Room Type :</b> $room_type </td>
</tr>
<tr>
<td><b>Max Occ :</b> $max_occupancy</td>
</tr>
<tr>
<td class='price'><b>Price :</b> $rate GBP</td>
</tr>
<tr>
<td class='body'><b>Description :</b> $description_short </td>
</tr>
</table>
</td>
</tr>
</table>
);
}
}
}
}
}
}
}
}


I had moved the var declarations outside the loops because I was going to try to output the data for each room after these loops had run - but that seems wrong.

bazz

[FishMonger]

Your hash structure and looping is more complex than it needs to be and should be simplified. Do you want to fix that, or just patch what you've got? If you want to fix it, I'll need to see the prior section where you're building the %rooms hash.

[bazz]

Thanks FishMonger, I was getting around to thinking that might be the case and so, have built a second hash. I'm not yet sure whether two is the way to go but I do need all the values from the db, either for output on this page or for passing to the next page/session.


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

$rooms{$cols[0]}{$cols[1]}{$cols[3]}{$cols[4]}{$cols[5]}{$cols[6]}++;
$rates{$cols[2]}{$cols[7]}++;
}


Am I on the right track yet?

bazz

[bazz]

sending you a pm Fish, with a link to the live page.

bazz

[FishMonger]

The solution below may need a slight tweak because it's a little unclear to me as to how the "rate" field is being returned. Is the query returning a single rate field that holds a comma separated list of rates, or is returning multiple rows that you concatenate?

while ( my $room = $sth->fetchrow_hashref) {

$rooms{ $room->{room_id} }{'type'} = $room->{room_type};
$rooms{ $room->{room_id} }{'number'} = $room->{room_number};
$rooms{ $room->{room_id} }{'name'} = $room->{room_name};
$rooms{ $room->{room_id} }{'description'} = $room->{description_short};
$rooms{ $room->{room_id} }{'max_occupancy'} = $room->{max_occupancy};
$rooms{ $room->{room_id} }{'image'} = $room->{image_url};

push @{ $rooms{ $room->{room_id} }{rate} }, $room->{room_rate} if $room->{room_rate};
}

foreach my $room_id ( sort keys %rooms ) {
my $total;
$total += $_ for @{ $rooms{ $room->{room_id} }{rate} };
my $rate = $total / @{$rooms{ $room->{room_id} }{rate} };

print qq(
<table>
<tr>
<td class="bookings_image">
$image_url
<p><a href="/cgi-bin/rooms_individual_details.pl/$room_id"> More Details >></a></p>
</td>
<td>
<table>
<tr>
<td><b>Room Name or Number :</b> $rooms{ $room_id}{'name'} . $total. </td>
</tr>
<tr>
<td><b>Room Type :</b> $rooms{$room_id}{'type'} </td>
</tr>
<tr>
<td><b>Max Occ :</b> $rooms{$room_id}{'max_occupancy'}</td>
</tr>
<tr>
<td class='price'><b>Price :</b> $rate GBP</td>
</tr>
<tr>
<td class='body'><b>Description :</b> $rooms{$room_id}{'description'} </td>
</tr>
</table>
</td>
</tr>
</table>
);
}

[FishMonger]

When dumping a hash or array via Data::Dumper, it's better to pass the var as a reference.

print Dumper \%rooms;

But I guess you already knew that.

[bazz]

Blimey Fish;

I could not have worked that out in a million years. :(

I'll study it and may have some questions later.

I haven't finally worked out the pricing structure (db structure), yet because I find it complicated.

some rooms are a set price from April to Sept(incl), and from Oct to Mar (incl).
Others are priced per day especially (mon tues wed).
Other are priced for weekend rates - might be different on sat and sun but there might also be a package price like sat & sun (as one booking), for a set price.

currently my table structure is:



| room_rate_id | room_id | room_type | room_rate | date_from | date_to | day_of_week |
| 00000000001 | 0000001| double | 65.50 |2008-04-01|2008-09-30| Mon |
| 00000000001 | 0000001| suite |125.30 |2008-04-01|2008-09-30| ALL |



I am trying to give flexibility. Some smaller clients may choose to enter individual room prices, per period of time (whether for one day of week, or all), and others may choose to enter prices for room types generally, for a period. Except that those who put in prices by room type generally have a different price on a monday from a tuesday etc.

I already reckon that my table structure is in need of simplification and I would appreicate a pointer from you (if you are willing), before I post in the MySQL forum.


[QUOTE=FishMonger;689459]When dumping a hash or array via Data::Dumper, it's better to pass the var as a reference.

print Dumper \%rooms;

I have been doing it like this:

print Dumper \%rooms; or
print Dumper \@rooms;
depending on whether it was an array or a hash.


bazz

[FishMonger]

Given your current DB structure, we could simplify it even more.
while ( my $room = $sth->fetchrow_hashref) {

print qq(
<table>
<tr>
<td class="bookings_image">
$image_url
<p><a href="/cgi-bin/rooms_individual_details.pl/$room->{room_id}"> More Details >></a></p>
</td>
<td>
<table>
<tr>
<td><b>Room Name or Number :</b> $room->{room_name} . $total. </td>
</tr>
<tr>
<td><b>Room Type :</b> $room->{room_type} </td>
</tr>
<tr>
<td><b>Max Occ :</b> $room->{max_occupancy}</td>
</tr>
<tr>
<td class='price'><b>Price :</b> $room->{room_rate} GBP</td>
</tr>
<tr>
<td class='body'><b>Description :</b> $room->{description_short} </td>
</tr>
</table>
</td>
</tr>
</table>
);
}
However, until you work out how you're going to store the rates (which I'm not sure either), working up the method of looping/outputting the data would seem to be pointless IMO. Else, wouldn't that fall under "putting the cart before the horse"?

[bazz]

However, until you work out how you're going to store the rates (which I'm not sure either), working up the method of looping/outputting the data would seem to be pointless IMO. Else, wouldn't that fall under "putting the cart before the horse"?

:o :confused: :(

My cart is full of carrots so I thought, that by putting it in front, I might make some more progress. :D

I am getting a clearer idea of what I need to do so, I shall have the pricing thing worked out in a day or so.

However, it would be good to know if the following field structure is wise in MySQL:



| room_rate_id | room_rate | date_from | date_to | DoW |
| 0001 | 65.50 | 2008_04_01 | 2008_09_30 | Mon,Tues,Wed,Thurs |




bazz

[FishMonger]

Having a single field contain multiple values (csv), such as your DoW, is generally considered poor design, i.e. the db is not normalized.

This is an incomplete thought, but how about using a numbering system for the DoW.

Something similar to this:
1-7 = Mon-Sun
So, Mon,Tues,Wed,Thurs would be represented in the db as: 1234

On the other hand, I don't see the need for the DoW field because that data is already in the db via the 2 date fields and the DoW names are easily extracted with the use of one of Perl's date modules.

I don't know enough about your data or your project to say for sure, but I'd look into the possibility of putting the room rates into a separate room_rate table.

[bazz]

Thanks FishMonger,

I shall ponder your suggestion some more.


On the other hand, I don't see the need for the DoW field because that data is already in the db via the 2 date fields and the DoW names are easily extracted with the use of one of Perl's date modules.


The dates you refer to represent a period of time, say high season/low season. But I know of a few potential clients who have certain rooms which on every Monday (for example), during a specific season are at a unique price compared with the other days of the same week.

[FishMonger]

Here's another incomplete thought. Create 2 rate tables. The first for the normal (high season) rates and a second "discount" or "adjustment" table that applies discounts or premiums to the "normal" rate.

Is that last table structure you posted a "rate" table or a "season" table? I think you need both. The season table would hold the date ranges and classifications (on season, off season, etc). The rate table would have the "normal" rates associated with the (room and season) classifications. And, you may want an adjustment rate table that applies discounts or premiums for weekends, holidays, etc.

Years ago (about 20) I sent 2 years as a front desk clerk at a Marriott (Courtyard) hotel. I'll try to dust off the cobb webs off of my memories on how our system was setup.

[bazz]

Thanks FishMonger,

One of my thoughts has been to have a structure like this (below). However, I have shyed (shied?), away from it because, I think it might be a chore for each admin.


tbl_rack_rate
| rack_rate_id | rack_rate |

tbl_seasons
| season_id | season_name | date_from | date_to |

tbl_daily_rate
| rate_id | rate | date_from | date_to |


tbl_rooms
| room_id | room_name_or_number | max_occ |

tbl_products
|product_id | season_id | room_id | rate_id | product_name | description |

what do you think?

bazz

[FishMonger]

Yes, that's a much better structure and once you start to fill in the details and run a few tests, you'll probably find that it will need a few tweaks.

Depending on which admins you're referring to, I wouldn't worry too much about them. If the front end administration is setup correctly, most admins won't know anything about the db structure. You'd only need to consider the main DB admin that would need direct to access the DB.

[bazz]

OK, thanks for that.

I am still woreking on the idea of enabling a room rate to be available only on (say), Mondays between the two dates.

I don't seem to be able to find a way to use an identifier for mondays (dow).

Do you know of a method wherein a person can choose say 2005-05-15 for 3 nights and I process that to see what days it includes. Then to perform a match with the dow fields so that the corresponding date is outputted?

If there is a way, I can get the rest of the code sorted and then, try to work it into the whole thing.

I don't yet have date::calc (just Date::Manip), but if I need it for this, I shall install it.


tbl_daily_rate
| rate_id | rate | date_from | date_to | dow |




I think I have found a way to achieve it thanks to a post by Fumigator. So I shall look into how to do the necessary comparisons from iso date to find out which day it is.


bazz

[bazz]

Following on and ...

1. I just have to get the pricing data.
2. Is this the best way?

using the earlier hashref and running a sub-routine within it (for each room type)


while ( my $room = $sth->fetchrow_hashref)
{
$type = $room->{room_type};
my @array = get_average_price($type,$business_id );

print qq(



and here's the sub so far


sub get_average_price {

print qq(
business_id = $business_id<br />
r=$type <br />
); # confirms I have the vars passed.

foreach my $timestamp (sort @daylist) #an array of the timestamp for the dates
{
my $iso = convert_to_iso($timestamp); # converts to iso format to match with db values
push (@iso_date, $iso); # array iso dates
my $iso_day = get_day_number($timestamp);
push (@dow_number, $iso_day); # arrray of dow_numbers
}

print qq( iso_date=@iso_date <br /> ); # successfully outputs all dates of the visit in 2008-05-04 format

print qq( day_number = @dow_number ); # successfully outputs all days of the visit - Monday Tuesday etc

my ($year, $month, $date);

foreach my $date_number (sort @iso_date)
{
($year, $month, $date) = split "-", $date_number, 3;
print qq( y=$year :: m=$month :: d=$date <br />); # splits iso date format into its constituent parts
}



return;

}



I don't seem to be able to work it out any other way.

Basically, in this sub, I shall be querying the tbl_room_rates using the variables that I have (enough, I think) and then putting the price for each night into an array to calculate the average.

How does that sound to any of you guys?

bazz