#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Location
    Marbella, Spain
    Posts
    381
    Rep Power
    0

    Calculator script - add easter


    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)
    Code:
    // 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 special a 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
    Last edited by helenp; December 8th, 2011 at 08:37 AM.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Location
    Marbella, Spain
    Posts
    381
    Rep Power
    0
    As expected no answer, it is not easy though.

    I suppose the correct way of doing a calculator script where you can have diferents dates for easter, and for christmas etc.
    I have the easiests, the end and start day of every month with every property as a row with a price for each month.

    However as there are always persons that want things in a diferent way, I cant personalize with this script.

    So I suppose the correct would be a table for each property, however that I dont like as I often calculate price for all properties together to get the one the client want.

    I suppose another way would be to have a column for every day of a year, and insert the daily price for that column for every property....
    however dont know if is a good idea having 365 columns....and having to change the year and date if feb have 29 days.
    And how to do the script....
    any ideas?
    Last edited by helenp; December 12th, 2011 at 08:54 AM.
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    script? properties? tables?

    seems to me like what you need is a calendar table that you can pre-load with a few years of data, that will flag easter for you

    as for that other stuff you're doing, with the CASE expressions, i have no idea what that's all about, but my gut says it can be greatly simplified

    for instance, why are you storing the start day of the month? isn't it always the 1st?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Location
    Marbella, Spain
    Posts
    381
    Rep Power
    0
    Originally Posted by r937
    script? properties? tables?

    seems to me like what you need is a calendar table that you can pre-load with a few years of data, that will flag easter for you

    as for that other stuff you're doing, with the CASE expressions, i have no idea what that's all about, but my gut says it can be greatly simplified

    for instance, why are you storing the start day of the month? isn't it always the 1st?
    Hm, If I dont remember bad, you were the one that helped me with the script, it was really complicated, if it was not you it was some other expert in mysql in this forum.

    Let me explain, I have another table where the bookings are inserted, and when I insert a booking for exampel I choose the arrival and departur dates, and the cases calculate the prices for the diferent months as some are low, medium and high season, and often a booking can be part of medium and high season etc.

    A calendar table sounds interesting, however no clue of how to do, is there some script to download? Even if I can do a calendar table, isnt the problem the same to calculate the prices, the script that get the result must change every year,?
    That is what I dont want to do, as complicated scripts, mostly I just forgot how I did and prefer not to touch.
    Last edited by helenp; December 12th, 2011 at 03:44 PM.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    a calendar table would have one row for each date in each year

    each date would have some kind of indicator describing what season it is

    2012-02-28 high
    2012-02-29 high
    2012-03-01 low
    2012-03-02 low

    (you can also have another column that indicates holidays like easter, if you want)

    then to calculate prices, you simply pull out all the dates that fall between the arrival and departure, and calculate a price for each date, and sum them all up

    you don't need all those CASEs because each date knows what season it is
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Location
    Marbella, Spain
    Posts
    381
    Rep Power
    0
    Originally Posted by r937
    a calendar table would have one row for each date in each year

    each date would have some kind of indicator describing what season it is

    2012-02-28 high
    2012-02-29 high
    2012-03-01 low
    2012-03-02 low

    (you can also have another column that indicates holidays like easter, if you want)

    then to calculate prices, you simply pull out all the dates that fall between the arrival and departure, and calculate a price for each date, and sum them all up

    you don't need all those CASEs because each date knows what season it is
    Thanks, Sounds good, however the problem is that everybody dont have the same high and low dates and price....so I suppose the dates would be the columns? and I would have to add each property as a new row, and its price for each day....
    How many columns is ok for a mysql table?
    And how can I add easily the columns with the dates? I imagine there is a php function to do this.
    But more complicated, how do I add the price for each date and property on more than 300 columns???
    Maybe I can insert where xx is between date and date?

    Edited, upps, I see now you said the dates will be in rows.....
    So I would have to insert the properties as columns.....not sure how that would work, as it is the contrary as to what I have know
    Last edited by helenp; December 12th, 2011 at 05:28 PM.
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by helenp
    Thanks, Sounds good, however the problem is that everybody dont have the same high and low dates....
    so you would have a separate calendar for each one


    Originally Posted by helenp
    So I would have to insert the properties as columns...
    properties???
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Location
    Marbella, Spain
    Posts
    381
    Rep Power
    0
    Originally Posted by r937
    so you would have a separate calendar for each one


    properties???
    Yes properties, we rent out properties.
    However separate calendar I think will be a problem as we take care of the properties, its not a portal,
    When I insert a booking I would have to choose table to calculate the prices (owner and client price) to insert in the database.
    Also I have a very practical calculator that checks availability and prices for a given date on all properties, so to do that I would have to calculate using many tables, .....
    Complicated, at least for me lol
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    each property would have its own calendar

    primary key would be { property_id, cal_date }

    all these rows would be in a single table

    hey, look, i don't want to tell you what to do, i'm only describing how i would handle different seasons for different properties, and including easter, as you originally wanted

    there's no way i'd use a complicated query with all those CASE expressions

    but i don't want to redesign your entire application...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Location
    Marbella, Spain
    Posts
    381
    Rep Power
    0
    Originally Posted by r937
    each property would have its own calendar

    primary key would be { property_id, cal_date }

    all these rows would be in a single table

    hey, look, i don't want to tell you what to do, i'm only describing how i would handle different seasons for different properties, and including easter, as you originally wanted

    there's no way i'd use a complicated query with all those CASE expressions

    but i don't want to redesign your entire application...
    Thanks,
    Yes I know you dont want to tell me what to do, and I apreciate your advices, however I am just trying to figure out if I can apply those to my application or not.
    Thats what I want handle each proeprty separetely but at the same time all together....ie been able to do a query on them all.

    How many tables can be involved in a query? I guess not to many.

    I dont understand this:
    primary key would be { property_id, cal_date }

    all these rows would be in a single table
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Location
    Marbella, Spain
    Posts
    381
    Rep Power
    0
    Originally Posted by r937
    each property would have its own calendar

    primary key would be { property_id, cal_date }

    all these rows would be in a single table
    I have been playing around a bit, trying to understand what you said, If I understood correctly I would have one table with the dates in rows, and one table for each property and to calculate the price I would join the tables.

    Playing I did a table with several years dates in rows, then I added a property as column, just to test, the name of the column = name_of_property and then I inserted the prices for high season for 2012 like this.
    UPDATE calendar_table_owner
    SET Casa_Blanca_4 = 514.285 where dt between '2012-07-01' and '2012-08-31'

    Then to calculate the price for a week in July I just did this:
    SELECT SUM(Casa_Blanca_4) AS price_total
    FROM calendar_table_owner where dt between ' 2012-07-01' and '2012-07-08' however it gave me the price of 8 days instead of 7 nights, but that is a minor problem I think.

    Is it posible to do it this way? that way I would have all the properties in 2 tables, one with client price and one with owner price.
    What do you think?
    Last edited by helenp; December 13th, 2011 at 04:57 AM.
  22. #12
  23. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by helenp
    What do you think?
    i think naming a column for a property is totally wrong
    Code:
    SELECT SUM(price * dailyrate) AS price_total 
      FROM calendar
     WHERE property = 'Casa_Blanca_4'
       AND cal_date BETWEEN '2012-07-01' AND '2012-07-08'
    Last edited by r937; December 13th, 2011 at 06:14 AM.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Location
    Marbella, Spain
    Posts
    381
    Rep Power
    0
    Originally Posted by r937
    i think naming a column for a property is totally wrong
    Code:
    SELECT SUM(price * dailyrate) AS price_total 
      FROM calendar
     WHERE property = 'Casa_Blanca_4'
       AND cal_date BETWEEN '2012-07-01' AND '2012-07-08'
    Why is naming a column for a property wrong? not correct by the rules, or can give problems?

    Anyway your solution looks better, however cant make it work,
    I did a new table using your query to make sure I do the right thing (as I dont understand how the structure of the table should be) it looks to me only one table and not several:
    Code:
    SELECT SUM(price * dailyrate) AS price_total 
      FROM calendar
     WHERE property = 'Casa_Blanca_4'
       AND cal_date BETWEEN '2012-07-01' AND '2012-07-08'
    And I got as error, price, dailyrate, property and cal_date are unknown columns, so I added these columns, also I added the dates to cal_date.
    When I do the query again I dont get error but I do get Null as a result, and it does not look correct to me at all,
    these are the last rows where I inserted the properties and prices.
    Full texts property cal_date price dailyrate
    Edit Edit Edit Inline Edit Copy Copy Delete Delete 0 2013-12-30 0.000 0.000
    Edit Edit Edit Inline Edit Copy Copy Delete Delete 0 2013-12-31 0.000 0.000
    Edit Edit Edit Inline Edit Copy Copy Delete Delete Casa_Blanca_4 0000-00-00 514.285 300.142
    Edit Edit Edit Inline Edit Copy Copy Delete Delete Costa_Nagueles_2 0000-00-00 478.689 125.456
    Last edited by helenp; December 13th, 2011 at 08:26 AM.

IMN logo majestic logo threadwatch logo seochat tools logo