database/search_sql.php at master · allenshih1/database · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
<?php
  $sql_txt =
"
SELECT
	TIMEDIFF(final_time, f_departure_date) AS total_time,
	CASE type
		WHEN 0 THEN f_flight_time
		WHEN 1 THEN ADDTIME(f_flight_time, s_flight_time)
		WHEN 2 THEN ADDTIME(ADDTIME(f_flight_time, s_flight_time), t_flight_time)
		ELSE             null
	END
	AS flight_time,
  c.*
FROM
(
SELECT
	CASE type
		WHEN 0 THEN f_arrival_date
		WHEN 1 THEN s_arrival_date
		WHEN 2 THEN t_arrival_date
		ELSE             null
	END
	AS final_time,
	CASE type
		WHEN 0 THEN  0
		WHEN 1 THEN TIMEDIFF(s_departure_date, f_arrival_date)
		WHEN 2 THEN ADDTIME( TIMEDIFF(s_departure_date, f_arrival_date), TIMEDIFF(t_departure_date, s_arrival_date))
		ELSE             null
	END
	AS transfer_time,
		TIMEDIFF(CONVERT_TZ(f_arrival_date, (SELECT timezone FROM Airport WHERE abbr = f_destination), (SELECT timezone FROM Airport WHERE abbr = f_departure)), f_departure_date)
	AS f_flight_time,
	CASE type
		WHEN 1 THEN TIMEDIFF(CONVERT_TZ(s_arrival_date, (SELECT timezone FROM Airport WHERE abbr = s_destination), (SELECT timezone FROM Airport WHERE abbr = s_departure)), s_departure_date)
		WHEN 2 THEN TIMEDIFF(CONVERT_TZ(s_arrival_date, (SELECT timezone FROM Airport WHERE abbr = s_destination), (SELECT timezone FROM Airport WHERE abbr = s_departure)), s_departure_date)
		ELSE             null
	END
	AS s_flight_time,
	CASE type
		WHEN 2 THEN TIMEDIFF(CONVERT_TZ(t_arrival_date, (SELECT timezone FROM Airport WHERE abbr = t_destination), (SELECT timezone FROM Airport WHERE abbr = t_departure)), t_departure_date)
		ELSE             null
	END
	AS t_flight_time,
	CASE type
		WHEN 0 THEN  f_ticket_price
		WHEN 1 THEN (f_ticket_price + s_ticket_price) * 0.9
		WHEN 2 THEN (f_ticket_price + s_ticket_price + t_ticket_price) * 0.8
		ELSE             null
	END
	AS price,
	a.*
FROM
(
	SELECT
		CASE
			WHEN s_id is null THEN 0
			WHEN t.id is null THEN 1
			ELSE 2
		END
		AS type,
		b.*,
		t.id AS t_id,
		t.flight_number AS t_flight_number,
		t.departure AS t_departure,
		t.destination AS t_destination,
		t.departure_date AS t_departure_date,
		t.arrival_date AS t_arrival_date,
		t.ticket_price AS t_ticket_price
	FROM
		(
			SELECT
				f.id AS f_id,
				f.flight_number AS f_flight_number,
				f.departure AS f_departure,
				f.destination AS f_destination,
				f.departure_date AS f_departure_date,
				f.arrival_date AS f_arrival_date,
				f.ticket_price AS f_ticket_price,
				s.id AS s_id,
				s.flight_number AS s_flight_number,
				s.departure AS s_departure,
				s.destination AS s_destination,
				s.departure_date AS s_departure_date,
				s.arrival_date AS s_arrival_date,
				s.ticket_price AS s_ticket_price
			FROM `Flight` AS f JOIN
			(
				SELECT * FROM `Flight` UNION
				SELECT
					null AS id,
					null AS flight_number,
					null AS departure,
					null AS destination,
					null AS departure_date,
					null AS arrival_date,
					null AS ticket_price
			) AS s
			ON
				(f.destination = s.departure AND f.arrival_date + interval 2 hour <= s.departure_date AND f.departure != s.destination)
				OR s.id is null
		) AS b
		JOIN
		(
			SELECT * FROM `Flight` UNION
			SELECT
				null AS id,
				null AS flight_number,
				null AS departure,
				null AS destination,
				null AS departure_date,
				null AS arrival_date,
				null AS ticket_price
		) AS t
	ON
		(s_destination = t.departure AND s_arrival_date + interval 2 hour <= t.departure_date)
		OR t.id is null
	WHERE
		f_departure = '".$departure."'
		AND
		CASE
			WHEN s_id is null THEN f_destination
			WHEN t.id is null THEN s_destination
			ELSE t.destination
		END = '".$destination."'
) AS a
) AS c
WHERE type <= ? ".$overnightsql.$order;

echo "<pre>".$sql_txt."</pre>";
?>