help please, a tweak to my hashref code

[bazz]

Hi,

I think this just needs me to know of another bit of perl vocabulary. I don't know what I need to use, to make this last bit do as I need.

The below script outputs almost all perfectly. (see picture). (left shows data as returned by the query - un-processed b=booking_id, i=in o=out).

When I sort @check_in and @check_out to enable correct colouring it all displays fine even when one set of dates straddles two months. (see pic below again - 1st not yellow means it started in June). However, it's different with my @booking_id. I need it to remain relative to the booking. Oh, that's not clear :( coz your going to say use a hash and I don't see how it wil solve this.

the booking_id will always be higgeldy-piggeldy (jumbled/unsortable) even if the dates are ordered because they will have been made randonly ie with no correlation between when they were booked and when the booking was for.

The yello and orange 'dates' hyperlink to another script and I need to use the booking_id in the link. I don't seem to be able to keep them matching.

How can I / what do I need to do, to keep the booking_id relative to the dates.


while ( $room = $sth->fetchrow_hashref)
{
$room_number = $room->{room_number};
$room_name = $room->{room_name};
my $check_in_date = $room->{'GROUP_CONCAT( RB.check_in_date )'};
my $check_out_date = $room->{'GROUP_CONCAT( RB.check_out_date )'};

$room_status = $room->{room_status};
my $bookings_ids = $room->{'GROUP_CONCAT( RB.booking_id )'};

#print Dumper \$room;
my @check_in = (split /,/, $check_in_date);
my @check_out = (split /,/, $check_out_date);
my @booking_id = (split /,/, $bookings_ids);

my @check_in = sort(@check_in);
my @check_out = sort(@check_out);
# my @booking_id = sort(@booking_id); #oops, the sort won't work here, as is
I don't understand how a change to the query would fix it so I have posted in this perl forum.

More especially than ever, I would appreciate your help guys.

and here's the next part of perl script.


print qq(
<tr>
<td class='rooms_label'>$room_number $room_name b=$bookings_ids i=$check_in_date o=$check_out_date</td>
);


foreach my $day_number (1..$days)
{

if (length($day_number) < 2 )
{
$day_number = (join ( "" , ( '0', $day_number)));
}


if (length($month) < 2 )
{
$month = (join ( "" , ( '0', $month)));
}

my $full_iso_date = (join("-",($year, $month, $day_number)));


# compute state 'vacant' or 'busy'
if ($full_iso_date eq $check_out[0])
{
shift(@check_in);
shift(@check_out);
shift(@booking_id);
}

my $room_condition = get_day_status($check_in[0],
$check_out[0],
$full_iso_date);

I hope I have explained this legibly.
bazz

PS
July is the first thumb and june is the second. June gets it all in order and July gets knocked out because its first booking begins in the preceding month of June.

[oesxyl]

when I do the complicated way to pack and unpack data from mysql, this was what I have in mind.
in mysql there are triplets (booking_id, check_in_date, check_out_date) and this must be keeped until you use them.
any sort you want must sort the triplets not individual data

this two lines will mess things also:

my @check_in = sort(@check_in);
my @check_out = sort(@check_out);

imagine two records:

check_in_date check_out_date
2008-05-01 2008-05-10
2008-05-03 2008-05-08

after sort check_in_date will stay how it is but check_out_date will swich first record with second what is obvious wrong.
other problem here can be that will be sorted alphabetical not by date.

I will try to find a solution.

regards

[bazz]

Hmmm, I see what you are saying but the key thing (soory for the pun), is that your example of dates can never be real. The room can only be booked once at a time so the nth value in one array seems to me always to correspond with the nth value in the other array. What I mean is that the first check-in date will always relate to the first check-out date, even when sorted.

However, I do take what you say about triplets and so I shall look into GROUP_CONCAT again, for the three values. like GROUP_CONCAT (booking_id, check_in_date, check_out_date) and see how I get on. I'll also review your suggested code pattern to see how I can progress.

bazz

[FishMonger]

I've never use GROUP_CONCAT(), but shouldn't it be used in the select statement instead of the already fetched row?

This is a pretty klungy approach.
if (length($day_number) < 2 )
{
$day_number = (join ( "" , ( '0', $day_number)));
}Have you thought about using sprintf?$day_number = sprintf "%02d", $day_number;
Do the same for the month, but it should be prior to the foreach loop.

[oesxyl]

Hmmm, I see what you are saying but the key thing (soory for the pun), is that your example of dates can never be real. The room can only be booked once at a time so the nth value in one array seems to me always to correspond with the nth value in the other array. What I mean is that the first check-in date will always relate to the first check-out date, even when sorted.
I know is not possible, :) The intention was to explain why sorting each one don't work. :)

However, I do take what you say about triplets and so I shall look into GROUP_CONCAT again, for the three values. like GROUP_CONCAT (booking_id, check_in_date, check_out_date) and see how I get on. I'll also review your suggested code pattern to see how I can progress.

bazz

I think you don't need to sort them, just retrive one by one when you need. A sketch:


while(....){
# get data for each room
@check_in = ...
@check_out = ...
@business_id = ...

foreach my $day_number (1 ... $days){
# check if $day_number is in any check_in/out interval in arrays
# and use them or not depending on result
}
}


why are three arrays and not four, you don't need room_status information?

regards

[FishMonger]

my $full_iso_date = (join("-",($year, $month, $day_number)));A direct assignment would be cleaner and more efficient.my $full_iso_date = "$year-$month-$day_number";

[bazz]

Thank you FishMonger.

those changes hadn't entered my head even though I have used the first suggestion before. I think I have been so occupied with the main code that I have blanked out everything else. Bad move, I know.

bazz

[bazz]

why are three arrays and not four, you don't need room_status information?

regards

I shall reflect on the whole code before deciding anything else. I have it 'working' now but for the booking_id which I shall take as being an indicator that it is wrong. I shall plan it out more fully and tst, before asking for more help.

bazz

[oesxyl]

I shall reflect on the whole code before deciding anything else. I have it 'working' now but for the booking_id which I shall take as being an indicator that it is wrong. I shall plan it out more fully and tst, before asking for more help.

bazz
I was wrong, $room_state is not an array because is in tbl_rooms_details.
about $booking_id, you retrive from database a array, I guess that the solution I posted now will solve this.


while ( $room = $sth->fetchrow_hashref)
{
$room_number = $room->{room_number};
$room_name = $room->{room_name};
my $check_in_date = $room->{'GROUP_CONCAT( RB.check_in_date )'};
my $check_out_date = $room->{'GROUP_CONCAT( RB.check_out_date )'};

$room_status = $room->{room_status};
my $bookings_ids = $room->{'GROUP_CONCAT( RB.booking_id )'};

my @check_in = (split /,/, $check_in_date);
my @check_out = (split /,/, $check_out_date);
my @booking_id = (split /,/, $bookings_ids);


the solution I find is to wrapp get_day_status into another sub:


sub check_day_status {
my ($ck_in, $ck_out, $bk_id, $curr_day) = @_;
for(my $i = 0; $i < @$ck_in; $i++){
my $state = get_day_status($$ck_in[$i], $$ck_out[$i], $curr_day);
return ($bk_id[$i], $state) if $state ne 'available';
}
return (undef, 'available');
}


then you can replace:


# compute state 'vacant' or 'busy'
if ($full_iso_date eq $check_out[0])
{
shift(@check_in);
shift(@check_out);
shift(@booking_id);
}

my $room_condition = get_day_status($check_in[0],
$check_out[0],
$full_iso_date);


with:


my $room_condition;
($booking_id, $room_condition) = check_day_state(\@check_in, \@check_out, \@booking_id, $full_iso_date);


after that you must check if $booking_id is undef and $room_condition is 'available'.

regards

[bazz]

Blimey oesxyl, I was just getting to understand the last bit of code.

There need be four arrays, I think.

1. check_in
2. check_out
3. room_status,
4. booking_id.

btw, room_status is in tbl_rooms_booked, because it changes depending on the booking and so it is an array.

I shall get on with this today after a break yesterday.

bazz

[bazz]

YAY!!

I put all the values of booking_id, room_status, check_in and check_out into the same concat and that made it very easy to integrate to the existing code.

Thank you both - very much - for your help.

bazz