calculator script - add easter

[helenp]

Hi I have a calculator script.
In my sql I have a table with the start day and the end day of each month, and every month I have to change the year on the start day and end day of the month, so I have a year in the database. And in

Then to calculate the prices I use a script like this (only past 3 months as is very long and the rest is equal)

// I calculate the prices using 2 diferent types of cases to get the price for the dates the customer choosed in a form:

(CASE WHEN ('$arrival' BETWEEN feb_start AND feb_fin and '$departure' BETWEEN feb_start AND feb_fin) 
THEN sum(febbr) * (TO_DAYS('$departure') - TO_DAYS('$arrival'))ELSE NULL END) AS price12,
(CASE WHEN ('$arrival' BETWEEN feb_start AND feb_fin and '$departure' > feb_fin) 
THEN sum(febbr) * (TO_DAYS(march_start) - TO_DAYS('$arrival'))ELSE NULL END) AS price13,
(CASE WHEN ('$arrival' < feb_start and '$departure' BETWEEN feb_start AND feb_fin) 
THEN sum(febbr) * (TO_DAYS('$departure') - TO_DAYS(feb_start))ELSE NULL END ) AS price14,

(CASE WHEN ('$arrival' BETWEEN march_start AND march_fin and '$departure' BETWEEN march_start AND march_fin) 
THEN sum(marzobr) * (TO_DAYS('$departure') - TO_DAYS('$arrival'))ELSE NULL END) AS price15,
(CASE WHEN ('$arrival' BETWEEN march_start AND march_fin and '$departure' > march_fin) 
THEN sum(marzobr) * (TO_DAYS(april_start) - TO_DAYS('$arrival'))ELSE NULL END ) AS price16,
(CASE WHEN ('$arrival' < march_start and '$departure' BETWEEN march_start AND march_fin) 
THEN sum(marzobr) * (TO_DAYS('$departure') - TO_DAYS(march_start))ELSE NULL END ) AS price17,

(CASE WHEN ('$arrival' BETWEEN april_start AND april_fin and '$departure' BETWEEN april_start AND april_fin) 
THEN sum(abrilbr) * (TO_DAYS('$departure') - TO_DAYS('$arrival'))ELSE NULL END) AS price21,
(CASE WHEN ('$arrival' BETWEEN april_start AND april_fin and '$departure' > april_fin) 
THEN sum(abrilbr) * (TO_DAYS(mayo_inicio) - TO_DAYS('$arrival'))ELSE NULL END) AS price22,
(CASE WHEN ('$arrival' < april_start and '$departure' BETWEEN april_start AND april_fin) 
THEN sum(abrilbr) * (TO_DAYS('$departure') - TO_DAYS(april_start) )ELSE NULL END) AS price23,

etc.

(CASE WHEN ('$arrival' < feb_start and '$departure' > feb_fin) 
THEN sum(febbr) * (TO_DAYS(march_start) - TO_DAYS(feb_inicio))ELSE NULL END) AS price46,

(CASE WHEN ('$arrival' < march_start and '$departure' > march_fin) 
THEN sum(marzobr) * (TO_DAYS(april_start) - TO_DAYS(march_start))ELSE NULL END) AS price47,

(CASE WHEN ('$arrival' < april_start and '$departure' > april_fin) 
THEN sum(abrilbr) * (TO_DAYS(may_start) - TO_DAYS(april_start))ELSE NULL END) AS price49,

etc.

//then I pass the value of the queries rows into a normal php var:

$price23=$row["price23"]; 
$price24=$row["price24"]; 
$price25=$row["price25"];

etc.

//then using php I sum the php vars to get the total price

$total=$price+$price1+$price2+$price3+$price4+$price5+$price6+$price7

This works like a charm, however every now and then I have clients that wants speacial price for easter, and as easter changes dates every year this is complicated to do. I dont want to change the script every year, as one forget and I think once a script is done and working it should not be touched.

Any idea of how I can add a special case for a week in march or april depending of year into this script without changing it every year? Or maybe I should do another script that is more flexibel.
Thanks in advance.
Helena

[penders]

Have a look at the PHP function easter_date() [uk3.php.net] - pass it the year and it returns the date (unix timestamp) of easter Sunday.

[helenp]

Thanks, see it gives the end day date of easter.
However I cant see what I can do with it, as the start dates and end dates of a month or period such as christmas are in the database, and using the database I get the price for each property, I only sum the result from the rows in the mysql query with php.
Suppose I am to short to see what I can do with easter_date :)

[Jstanfield]

What if you can return the date of Easter for the current year in your query?

In the past, when I've needed to figure out Easter, I've used the example in the link below, which is easily adapted to different languages.

[usno.navy.mil...]

select @y:=year(now()) y,
@c:= @y DIV 100 c,
@n:= @y - 19 * ( @y DIV 19 ) n,
@k:= ( @c - 17 ) DIV 25 k,
@i:= @c - @c DIV 4 - ( @c - @k ) DIV 3 + 19 * @n + 15 i1,
@i:= @i - 30 * ( @i DIV 30 ) i2,
@i:= @i - ( @i DIV 28 ) * (1 - ( @i DIV 28 ) * ( 29 DIV ( @i + 1 ) ) * ( ( 21 - @n ) DIV 11 ) ) i3,
@j:= @y + @y DIV 4 + @i + 2 - @c + @c DIV 4 j1,
@j:= @j - 7 * ( @j DIV 7 ) j2,
@l:= @i - @j l,
@m:= 3 + ( @l + 40 ) DIV 44 m,
@d:= @l + 28 - 31 * ( @m DIV 4 ) d,
cast(concat(@y,'-',@m,'-',@d) as date) easter

Just wrap it in parentheses and add it to your FROM clause, and your CASE statements can refer to easter.easter as if it was a field named easter on a table named easter. Caveat: it uses year(now()) so it assumes you're always expecting to use the current year.

select some_field,easter.easter
from some_table,
 (select @y:=year(now()) y,
@c:= @y DIV 100 c,
@n:= @y - 19 * ( @y DIV 19 ) n,
@k:= ( @c - 17 ) DIV 25 k,
@i:= @c - @c DIV 4 - ( @c - @k ) DIV 3 + 19 * @n + 15 i1,
@i:= @i - 30 * ( @i DIV 30 ) i2,
@i:= @i - ( @i DIV 28 ) * (1 - ( @i DIV 28 ) * ( 29 DIV ( @i + 1 ) ) * ( ( 21 - @n ) DIV 11 ) ) i3,
@j:= @y + @y DIV 4 + @i + 2 - @c + @c DIV 4 j1,
@j:= @j - 7 * ( @j DIV 7 ) j2,
@l:= @i - @j l,
@m:= 3 + ( @l + 40 ) DIV 44 m,
@d:= @l + 28 - 31 * ( @m DIV 4 ) d,
cast(concat(@y,'-',@m,'-',@d) as date)) easter
where some_table.some_date=easter.easter;
+------------+------------+
| some_field | easter |
+------------+------------+
| 1 | 2011-04-24 |
| 2 | 2011-04-24 |
| 3 | 2011-04-24 |
| 4 | 2011-04-24 |
| 5 | 2011-04-24 |
| 6 | 2011-04-24 |
| 7 | 2011-04-24 |
| 8 | 2011-04-24 |
+------------+------------+

[helenp]

Thanks,
Not sure how you mean, anyway I cant use currect year, as for exampel at this moment I have one year in the web, ie, in the calculator you can calutate prices for march, april etc, and that is next year.

And the problem is if I have a booking in april, for exampel 3 nights can be easter price and 4 nights april price.

Not sure if you mean to add a column for easter with the prices into the table where I have the dates and prices....if not where do I get the prices from?