[PHP] variabel tarief berekenen adhv datumbereik

[QBiT]

Het probleem:

Voor de verhuur van een accomodatie moet variabel de prijs berekend kunnen worden. Een bezoeker wil bijvoorbeeld een accomodatie boeken van 25-04-2007 tot 05-05-2007. Voor iedere accomodatie zijn er verschillende periode die allemaal een andere dagprijs hebben. Bijvoorbeeld van 01-01-2007 tot 30-04-2007 is de dagprijs 45 euro, en van 01-05-2007 tot 30-06-2007 is de dagprijs 50 euro.

De periodes met dagprijzen staan dus in de database (Mysql). Een begindatum, een einddatum, en de prijs voor die periode.

Vragen:

1. Is het mogelijk om met behulp van een, of misschien twee queries alle periodes op te halen die van toepassing zijn op een bepaalde reserveringsperiode? Dus als ik zeg: Ik wil alle prijsperiodes die van toepassing zijn op 25-04-2007 tot 05-05-2007 dat ik dan 01-01-2007 tot 30-04-2007 en 01-05-2007 tot 30-06-2007 terugkrijg?
2. Wat is jou inziens de meest efficiente/snelste manier om dit probleem te tackelen?

De oplossing die ik nu voor ogen heb, maar me totaal inefficient lijkt:

- Haal alle prijsperiodes op.
- Controleer of de begindatum en de einddatum van een reservering binnen een van de prijsperiodes valt.
- Zo ja, bereken het aantal dagen, doet dat keer, etc.... Klaar.
- Zo nee, bepaal de beginperiode waarin de reservering valt.
- Bepaal eventueel nog een tussenliggende periode.
- Bepaal de laatste periode.
- Reken de boel uit.

Vragen staan hierboven al. Als ik zo kijk naar de mogelijke Mysql functies is date_diff nog een van de weinige functies die me misschien een beetje kan helpen.

[Y0ur1]

Heb je misschien de tabelstructuur van de betreffende tabellen?

Als ik het goed begrijp heb je een accomodatie die bijvoorbeeld 4 prijscategorien heeft, voor bijvoorbeeld de 4 seizoenen, met daarbij behorend een dagprijs.

dus ik neem aan zoiets:
tbl_prijscategorien:

id | begindatum | einddatum | dagprijs | accomodatie_FK

Dus als ik zeg: Ik wil alle prijsperiodes die van toepassing zijn op 25-04-2007 tot 05-05-2007 dat ik dan 01-01-2007 tot 30-04-2007 en 01-05-2007 tot 30-06-2007 terugkrijg?

Hier zie ik zo snel niet waarom je ook de periodes van vóór 25-04-2007 en ná 05-05-2007 wil terugkrijgen, kun je je in deze nog wat nader verklaren?

En klopt het dan een 'bepaalde reserveringsperiode' over meerdere prijsperiodes kan vallen?

[ Voor 93% gewijzigd door Y0ur1 op 19-12-2006 21:04 ]

[_js_]

Een periode is van toepassing wanneer de begindatum van de periode kleiner is dan het eind van het bereik en de einddatum van de periode groter of gelijk is aan het begin van het bereik.

Dat kun je in de WHERE voorwaarde zetten van een query.

[Janoz]

Inderdaad, de eerste twee stappen van je algo kun je combineren in 1 query. Als je er vervolgens ook nog voor zorgt dat je de resultaten gesorteerd terug geeft (op bv begindatum) dan heb je in je resultset mooi de complete reserverings periode op volgorde staan en kun je er gewoon lineair doorheen lussen.

[Verwijderd]

Gewoon om weer een keer te spelen met MySQL heb ik eens gekeken naar dit probleem. Gebruik makend van de tabelstructuur van Y0ur1 kom ik dan op zoiets uit. Ik weet niet of je hier iets aan hebt, maar ik heb me in ieder geval toch weer eventjes vermaakt .

SQL:

SELECT 
  id AS periode,
  begindatum,
  einddatum,
  dagprijs,
  ( DATEDIFF(einddatum, begindatum) - 
    ( GREATEST(0, DATEDIFF('2006-12-23', begindatum) ) + 
      GREATEST(0, DATEDIFF(einddatum, '2006-12-31') ) 
    ) + 1 
  )
  AS dagen_in_periode
FROM prijzen_accomodaties
WHERE 
  ( accomodatie_FK = 1 AND 
    ( ('2006-12-23' <= einddatum) AND 
      ('2006-12-31' >= begindatum) 
    )
  )
ORDER BY begindatum;

Wat bijvoorbeeld kan resulteren in:

periode	begindatum	einddatum	dagprijs	dagen_in_periode
1	2006-12-23	2006-12-23	20	1
5	2006-12-24	2006-12-30	32	7
3	2006-12-31	2007-01-06	23	1

[QBiT]

@Y0ur1: De tabelstructuur zoals jij die daar schetst klopt inderdaad. Een accomodatie zal inderdaad meerdere prijscategorien hebben. 4 is een goed voorbeeld. Mogelijk meer, mogelijk minder.

Y0ur1 schreef op dinsdag 19 december 2006 @ 20:35:

Hier zie ik zo snel niet waarom je ook de periodes van vóór 25-04-2007 en ná 05-05-2007 wil terugkrijgen, kun je je in deze nog wat nader verklaren?

De data 25-04-2007 tot 05-05-2007 zijn de data dat iemand een accomodatie wil reserveren. Dan wil ik dus iig alle prijsperiodes/categorien hebben die daar op van toepassing zijn. Waarschijnlijk waren de termen allemaal onduidelijk.

Nog een lastig geval is inderdaad dat een reservering in 3 of meer prijsperiodes valt.

@djingelz: mhmhm, ziet er ingewikkeld maar logisch uit. Ik ga het nog even op me in laten werken, en bekijken wat wat precies doet. Maar volgensmij kom ik hier een heel eind mee!

Bedankt voor jullie reacties!

[Joolee]

kun je niet gewoon doen:

SQL:

SELECT * FROM tabel WHERE (begin BETWEEN begindatum AND einddatum) OR (eind BETWEEN begindatum AND einddatum)

[Verwijderd]

Dat doe ik ook zo ongeveer. Alleen dan op de manier zoals _js_ al eerder aangaf. De oplossing die jij geeft werkt echter niet helemaal correct. Wat gebeurd er met een reservering die 1 of meerdere perioden helemaal afdekt (zoals het resultaat wat ik eerder gaf), deze perioden worden door jou query niet gedetecteerd. Een vereenvoudigde versie ziet (die dit probleem oplost) er als volgt uit:

SQL:

SELECT *
FROM prijzen_accomodaties
WHERE ('2006-12-23' <= einddatum) AND ('2006-12-31' >= begindatum) 

@QBit: Een beetje spelen met datediff levert al vlug wat inzicht op. Samen met de documentatie heb je het zo door (en anders horen we het wel weer ). Succes!

[QBiT]

De code van djingelz lijkt bijna perfect te werken. Als ik de onderstaande query uitvoer:

SQL:

SELECT id AS periode, begin, eind, prijs, 
( DATEDIFF(eind, begin) - ( GREATEST(0, DATEDIFF('2007-02-26', begin) ) + GREATEST(0, DATEDIFF(eind, '2007-03-02') ) ) + 1 ) AS dagen_in_periode 
FROM prijzen 
WHERE ( accomodatie = 2 AND ( ('2007-02-26' <= eind) AND ('2007-03-02' >= begin) ) ) 
ORDER BY begin;

Krijg ik het volgende resultaat:

1 2007-01-01 2007-02-30 59.00 5
2 2007-03-01 2007-05-15 69.00 2

Het komt er dus op neer dat hij zegt dat de gevraagde datumreeks 5 dagen in de eerste periode valt, en 2 dagen in de laatste. Dat klopt niet. Februari heeft namelijk 28 dagen. Geen 30.

in eerste instantie dacht ik dat het aan mysql lag. Maar dat is natuurlijk niet het geval:

SQL:

SELECT DATEDIFF( '2007-03-02', '2007-02-26' ) ;

Geeft 4 terug. Het is mij dus een mysterie hoezo dit niet goed functioneert. De kans lijkt dus groot dat het iets met de query te maken heeft? Alleen ik zie geen fout?

Suggesties?

[Verwijderd]

Ik heb de query verder niet meer verder bekeken, maar ik zie dat bij de eerste rij van het resultaat de einddatum '2007-02-30' is. Kan het kloppen dat de einddatum van deze rij niet helemaal correct is , waardoor datediff zijn werk niet goed kan doen

[QBiT]

Verwijderd schreef op donderdag 21 december 2006 @ 15:17:
Ik heb de query verder niet meer verder bekeken, maar ik zie dat bij de eerste rij van het resultaat de einddatum '2007-02-30' is. Kan het kloppen dat de einddatum van deze rij niet helemaal correct is , waardoor datediff zijn werk niet goed kan doen

ahum, i rest my case....

Het werkt! Super. Thnx!