Mysql Buchungssumme für Buchung ermitteln mit Saisonzeiten

bubu9 · 25. November 2017

Moin,

ich arbeite gerade an einem kleinen Buchungsportal für Ferienwohnungen. Die Wohnungen haben Saisonpreise, die ich in einer Tabelle abgelegt habe. Aufbau der Tabelle:

id(int)|anreise(date)|abreise(date)|Preis(int)|wohnung(int)

Für eine Funktion preis_berechnen verzweifel ich an einer SQL-Anfrage, die mir optimaler weise direkt die Summe in Euro für eine Buchungsanfrage für einen bestimmten Zeitraum liefert oder zumindest alle betreffenden Saisonpreise innerhalb des Zeitraums liefert. Ich habe das bisher nur mit zwei SQL-queries lösen können:

SELECT *
FROM preise
WHERE CAST( '$anreise' AS DATE )
BETWEEN CAST(anreise AS DATE )
AND CAST(abreise AS DATE )
AND wohnung =$wohnung
OR CAST( '$abreise' AS DATE )
BETWEEN CAST(anreise AS DATE )
AND CAST(abreise AS DATE )
AND wohnung =$wohnung

Damit bekomme ich den ersten und den letzten relevanten Saisonpreise-Zeitraum geliefert.
Ich mache dann eine Fallunterscheidung in PHP, ob die Buchung nicht vielleicht sogar in einem Zeitraum liegt und kann für diesen Fall den Preis direkt berechnen.

Für den Fall, dass die Buchung sich über mehr als einen Saisonzeitraum erstreckt, brauche ich eine weitere SQL-Anfrage:

SELECT *
FROM preise
WHERE anreise
BETWEEN CAST( '$eins' AS DATE )
AND CAST( '$zwei' AS DATE )
AND wohnung =$wohnung

$eins ist das erste relevante Anreisedatum einer Saisonzeit, die in den Buchungszeitraum fällt,
$zwei das letzte relevante Anreisedatum einer Saisonzeit, die in den Buchungszeitraum fällt.

Damit bekomme ich alle relevanten Saisonzeiten für die Buchung und kann den Preis mehr oder weniger umständlich in PHP berechnen.

Ich bin mir absolut sicher, dass man das auch irgendwie in SQL lösen könnte, ist mir aber zu komplex. Vielleicht gibt es hier ja einen SQL-Guru der mir helfen kann.

NPC · 25. November 2017

Code:

// String $anreise := "2017-12-31 23:59:59"
select sum(preis)
from preise
where wohung = $wohnung
and anreise >= $anreise
and abreise <= $abreise

so?

#Seneca · 25. November 2017

Nope das funktioniert nicht (ich knoble auch grad dran).
Ich gehe davon aus, dass preis ein Tagespreis ist, sprich wenn es zB über 3 Preiszeiträume geht, muss xTage*p1+xTage*p2+xTage*p3 berechnet werden.

edit:
Also, ich hab als erstes mal angenommen, dass bei

id(int)|anreise(date)|abreise(date)|Preis(int)|wohnung(int)

Anreise und Abreise eigentlich Start und Ende des Preiszeitraums bedeutet, es also nicht klug benannt ist.
(Desweiteren könnte man sich das id-Feld sparen. Wenn zB man nicht drauf joinen will, unique kann man auch als kombinierten Schlüssel aus WohnungsId+an(+ab) erreichen, wenn man es braucht. Aber das ist eher nebensächlich) Außerdem die richtigen Datentypen nutzen, int ist als WohnungsID wohl etwas groß^^

Man kann das mit einem (komplexen) Query lösen, die Frage ist: ist es sinnvoll? Ggf. kann es übersichtlicher sein (möglicher Weise sogar performanter), das Ergebniss im php zu errechnen.

Code:

SELECT datediff(abreise,anreise)*preis as p
FROM `seasonpreise` 
WHERE (anreise>='$anreise' and abreise<='$abreise')
UNION
SELECT datediff(abreise,'$anreise')*preis as p
FROM `seasonpreise` 
WHERE (anreise<='$anreise' and abreise>='$anreise')
UNION
SELECT datediff('$abreise',anreise)*preis as p
FROM `seasonpreise` 
WHERE (anreise<='$abreise' and abreise>='$abreise')

(das ganze CAST-Zeugs hab ich weg gelassen, besser ist es die Variablen schon vom php korrekt formatiert zu übergeben, das erste Select sucht alle Zeiträume die voll im Buchungszeitraum liegen, das 2. alles wo nur das Ende eine Zeitraums getroffen wird, also wenn die Anreise in der Mitte des (Seasonpreis-)Zeitraums liegt, das 3. entsprechend das Gegenteil vom 2.)

Hier müsste nur noch die Summe der Ergebnisse im php gebildet werden, müsste auch im mysql gehen, hab ich grad aber nicht die Zeit das noch auszuknobeln. Und die WohnungsID hab ich in meinem Testszenario nicht benutzt, aber das noch zu ergänzen sollte kein Problem sein.

bubu9 · 25. November 2017

Boah! Ich liebe euch. Hätte nie gedacht, überhaupt eine Antwort darauf zu bekommen. Muss jetzt deine Lösung auch erstmal eruieren, weil ich den SQL-query so nicht verstehe. Bin leider nicht so fit in SQL. Trotzdem danke.

Ich gehe davon aus, dass preis ein Tagespreis ist, sprich wenn es zB über 3 Preiszeiträume geht, muss xTage*p1+xTage*p2+xTage*p3 berechnet werden.

Richtig.

das ganze CAST-Zeugs hab ich weg gelassen, besser ist es die Variablen schon vom php korrekt formatiert zu übergeben

Habe irgendwo gelesen, dass es wirklich nur mit CAST funktioniert, selbst wenn es richtig formatiert wurde. Lasse mich aber gerne eines Besseren belehren.

Desweiteren könnte man sich das id-Feld sparen...

Ich brauche das ID-Feld für andere Berechnungen (Kurtaxe).

Andreas_ · 25. November 2017

#Seneca schrieb:
Man kann das mit einem (komplexen) Query lösen, die Frage ist: ist es sinnvoll? Ggf. kann es übersichtlicher sein (möglicher Weise sogar performanter), das Ergebniss im php zu errechnen.

Ich denke eher, dass es ein Fall für eine stored procedure ist. Flaschenhals bei der Performance sind in der Regel Datenbankzugriffe ...

Code:

DELIMITER //

CREATE PROCEDURE getPreis( IN startDate DATE, IN endeDate DATE, IN wohnung_id INT ) 

BEGIN 
  DECLARE start_ID INT;
  DECLARE ende_ID INT;

  -- Testen ob startDate und endeDate im gleichen Zeitraum liegen
  SELECT id INTO start_ID FROM preise
  WHERE startDate BETWEEN anreise AND abreise
  AND wohnung=wohnung_id;

  SELECT id INTO ende_ID FROM preise
  WHERE endeDate BETWEEN anreise AND abreise
  AND wohnung=wohnung_id;

  IF start_ID = ende_ID THEN
    -- gleicher Zeitraum - einfaches Select
    SELECT DATEDIFF(endeDate, startDate) AS Tage,
      preis*DATEDIFF(endeDate, startDate) AS Gesamtpreis
    FROM preise
    WHERE wohnung=wohnung_id
	AND id=start_ID;
  ELSE
    -- verschiedene Zeitraeume - Union
    -- Zeitraum 1 = Zeitraum der Ankunft
    -- Zeitraum 2 = Zeitraum der Abfahrt
    -- Zeitraum 3 = alle daszwischen liegenden Zeitraeume
    SELECT SUM(iTage) as Tage, SUM(iGesamtpreis) AS Gesamtpreis FROM (
	SELECT DATEDIFF(abreise, startDate) AS iTage,
	  preis*DATEDIFF(abreise, startDate) AS iGesamtpreis
	FROM preise
	WHERE wohnung=wohnung_id
	  AND id=start_ID
	UNION 	
	SELECT DATEDIFF(endeDate, anreise) AS iTage,
	  preis*DATEDIFF(endeDate, anreise) AS iGesamtpreis
	FROM preise
	WHERE wohnung=wohnung_id
	  AND id=ende_ID
	UNION
	SELECT DATEDIFF(abreise, anreise) AS iTage,
	  preis*DATEDIFF(abreise, anreise) AS iGesamtpreis
	FROM preise
	WHERE wohnung=wohnung_id
	  AND anreise BETWEEN startDate AND endeDate
	  AND abreise BETWEEN startDate AND endeDate) AS temp;	  
  END IF;

END//

DELIMITER ;

Die stored procedure kann dann einfach mit

Code:

call getPreis('$anreise', '$abreise', '$wohnung')

aufgerufen werden.

Ist der Preis wirklich ein INT? Wird der Abreisetag mitberechnet oder nicht?

bubu9 · 25. November 2017

Ist der Preis wirklich ein INT? Wird der Abreisetag mitberechnet oder nicht?

Abreisetag wird nicht mitberechnet. Preis muss nicht int sein. Es ist aber immer eine Summe ohne Nachkommerstellen. Ich verleihe Andreas_ den Namen SQL-Gott und versuche nach und nach die stored procedure zu verstehen. Tausend Dank!

Andreas_ · 25. November 2017

Ich würde mich nicht auf ganzzahlige Preise festlegen wollen, dann ist man nicht flexibel (Rabattaktionen). Wenn der Abreisetag nicht mitgerechnet wird, sollte die stored procedure die korrekten Ergebnisse liefern.

Ergänzung (26. November 2017)

Mir sind 2 Fehler in meiner stored procedure aufgefallen:

Für alle Aufenthalte, die über das Ende eines Zeitraums hinausgehen, muss ich um alle Tage zu haben DATEDIFF(abreise, anreise) + 1 bzw. DATEDIFF(abreise, startDate)+1 rechnen, sonst fehlt ein Tag.
Wenn Startdatum auf anreise oder Endedatum auf abreise fällt, dann wird wegen Between bei mehreren Zeiträumen der betreffende Zeitraum doppelt erfasst. Deswegen muss ich dort < und > an Stelle von Between nutzen

Hier die Korrektur:

Code:

DELIMITER //

DROP PROCEDURE IF EXISTS getPreis//

CREATE PROCEDURE getPreis( IN startDate DATE, IN endeDate DATE, IN wohnung_id INT ) 
BEGIN 
  DECLARE start_ID INT;
  DECLARE ende_ID INT;

  -- Testen ob startDate und endeDate im gleichen Zeitraum liegen
  SELECT id INTO start_ID FROM preise
  WHERE startDate BETWEEN anreise AND abreise
  AND wohnung=wohnung_id;

  SELECT id INTO ende_ID FROM preise
  WHERE endeDate BETWEEN anreise AND abreise
  AND wohnung=wohnung_id;

  IF start_ID = ende_ID THEN
	-- gleicher Zeitraum - einfaches Select
	SELECT DATEDIFF(endeDate, startDate) AS Tage,
	  preis*DATEDIFF(endeDate, startDate) AS Gesamtpreis
	FROM preise
	WHERE wohnung=wohnung_id
	AND id=start_ID;
  ELSE
	-- verschiedene Zeitraeume - Union
	-- Zeitraum 1 = Zeitraum der Ankunft
	-- Zeitraum 2 = Zeitraum der Abfahrt
	-- Zeitraum 3 = alle daszwischen liegenden Zeitraeume
	SELECT SUM(iTage) as Tage, SUM(iGesamtpreis) AS Gesamtpreis FROM (
		SELECT start_ID AS iId, startDate AS iBeginn, abreise AS iEnde, 
			DATEDIFF(abreise, startDate)+1 AS iTage,
			preis*(DATEDIFF(abreise, startDate)+1) AS iGesamtpreis
		FROM preise
		WHERE wohnung=wohnung_id
		AND id=start_ID
		UNION 	
		SELECT id AS iId, anreise AS iBeginn, endeDate AS iEnde, 
			DATEDIFF(endeDate, anreise) AS iTage,
			preis*DATEDIFF(endeDate, anreise) AS iGesamtpreis
		FROM preise
		WHERE wohnung=wohnung_id
		AND id=ende_ID
		UNION
		SELECT id AS iId, anreise AS iBeginn, abreise AS iEnde, 
			DATEDIFF(abreise, anreise)+1 AS iTage,
			preis*(DATEDIFF(abreise, anreise)+1) AS iGesamtpreis
		FROM preise
		WHERE wohnung=wohnung_id
		AND anreise > startDate AND anreise < endeDate
		AND abreise > startDate AND abreise < endeDate) AS temp;
  END IF;

END//

DELIMITER ;

Ich habe noch eine zweite stored procedure abgeleitet, damit Du Dir die Übersicht über die Zeiträume anzeigen lassen kannst:

Code:

DELIMITER //

DROP PROCEDURE IF EXISTS getDetails//

CREATE PROCEDURE getDetails( IN startDate DATE, IN endeDate DATE, IN wohnung_id INT ) 
BEGIN 
  DECLARE start_ID INT;
  DECLARE ende_ID INT;

  -- Testen ob startDate und endeDate im gleichen Zeitraum liegen
  SELECT id INTO start_ID FROM preise
  WHERE startDate BETWEEN anreise AND abreise
  AND wohnung=wohnung_id;

  SELECT id INTO ende_ID FROM preise
  WHERE endeDate BETWEEN anreise AND abreise
  AND wohnung=wohnung_id;

  IF start_ID = ende_ID THEN
	-- gleicher Zeitraum - einfaches Select
	SELECT start_ID AS iId, startDate AS iBeginn, endeDate AS iEnde, 
	  DATEDIFF(endeDate, startDate) AS iTage,
	  preis*DATEDIFF(endeDate, startDate) AS iGesamtpreis
	FROM preise
	WHERE wohnung=wohnung_id
	AND id=start_ID;
  ELSE
	-- verschiedene Zeitraeume - Union
	-- Zeitraum 1 = Zeitraum der Ankunft
	-- Zeitraum 2 = Zeitraum der Abfahrt
	-- Zeitraum 3 = alle daszwischen liegenden Zeitraeume
	SELECT start_ID AS iId, startDate AS iBeginn, abreise AS iEnde, 
		DATEDIFF(abreise, startDate)+1 AS iTage,
		preis*(DATEDIFF(abreise, startDate)+1) AS iGesamtpreis
	FROM preise
	WHERE wohnung=wohnung_id
	AND id=start_ID
	UNION 	
	SELECT ende_ID AS iId, anreise AS iBeginn, endeDate AS iEnde, 
		DATEDIFF(endeDate, anreise) AS iTage,
		preis*DATEDIFF(endeDate, anreise) AS iGesamtpreis
	FROM preise
	WHERE wohnung=wohnung_id
	AND id=ende_ID
	UNION
	SELECT id AS iId, anreise AS iBeginn, abreise AS iEnde, 
		DATEDIFF(abreise, anreise)+1 AS iTage,
		preis*(DATEDIFF(abreise, anreise)+1) AS iGesamtpreis
	FROM preise
	WHERE wohnung=wohnung_id
	AND anreise > startDate AND anreise < endeDate
	AND abreise > startDate AND abreise < endeDate
	ORDER BY  iBeginn;	  
  END IF;

END//

DELIMITER ;

bubu9 · 1. Dezember 2017

Heute erst gelesen. Ich hatte diese Woche keine Zeit mich mit dem Projekt zu beschäftigen. Tausend tausend Dank! Mein Angebot steht übrigens, falls Du es dir anders überlegen solltest

Kann aber verstehen, dass gute Leute wie du sehr beschäftigt sind.

Suche

Mysql Buchungssumme für Buchung ermitteln mit Saisonzeiten

bubu9

Lt. Junior Grade

NPC

Ensign

#Seneca

Banned

bubu9

Lt. Junior Grade

Andreas_

Commander

bubu9

Lt. Junior Grade

Andreas_

Commander

bubu9

Lt. Junior Grade