File size: 6,981 Bytes
73cddce 3fa0e2d 73cddce 3fa0e2d 73cddce 3fa0e2d 73cddce 3fa0e2d 73cddce 3fa0e2d 73cddce 3fa0e2d |
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 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 |
import os
import psycopg2
from psycopg2 import pool
from psycopg2.extras import DictCursor
from contextlib import contextmanager
from .config import DB_USER, DB_PASSWORD, DB_HOST, DB_PORT, DB_NAME, DB_ENDPOINT_ID
conn_pool = None
try:
if DB_ENDPOINT_ID:
DATABASE_URL = (
f"postgresql://{DB_USER}:{DB_PASSWORD}"
f"@{DB_HOST}:{DB_PORT}/{DB_NAME}"
f"?sslmode=require"
)
else:
DATABASE_URL = f"postgresql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}"
conn_pool = pool.SimpleConnectionPool(
minconn=1,
maxconn=10,
dsn=DATABASE_URL
)
except (psycopg2.OperationalError, Exception) as e:
conn_pool = None
@contextmanager
def get_pooled_connection():
if conn_pool is None:
raise ConnectionError("Database connection pool is not initialized.")
conn = None
try:
conn = conn_pool.getconn()
yield conn
conn.commit()
except (Exception, psycopg2.Error) as e:
if conn:
try:
conn.rollback()
except psycopg2.Error as rb_err:
pass
raise
finally:
if conn:
try:
conn_pool.putconn(conn)
except Exception as pc_err:
pass
def execute_query(query: str, params: tuple = None, fetch_one: bool = False):
if conn_pool is None:
return None
results = None
try:
with get_pooled_connection() as conn:
with conn.cursor(cursor_factory=DictCursor) as cur:
cur.execute(query, params)
if fetch_one:
result_dict = cur.fetchone()
results = dict(result_dict) if result_dict else None
else:
results_list = cur.fetchall()
results = [dict(row) for row in results_list]
return results
except ConnectionError as e:
return None
except psycopg2.Error as e:
return None
except Exception as e:
return None
def get_available_locations():
query = """
SELECT DISTINCT unnest(destination) AS destination
FROM Tour
WHERE availability = true
ORDER BY destination;
"""
results = execute_query(query)
if results:
return [row['destination'] for row in results]
elif results == []:
return []
else:
return None
def get_tour_by_id(tour_id):
query = """
SELECT
t.tour_id,
t.title,
t.duration,
t.departure_location,
t.destination,
t.region,
t.itinerary,
t.max_participants,
d.departure_id,
d.start_date,
d.price_adult,
d.price_child_120_140,
d.price_child_100_120,
p.promotion_id,
p.name AS promotion_name,
p.type AS promotion_type,
p.discount AS promotion_discount,
p.start_date AS promotion_start_date,
p.end_date AS promotion_end_date
FROM Tour t
LEFT JOIN Departure d ON t.tour_id = d.tour_id AND d.availability = true
LEFT JOIN Tour_Promotion tp ON t.tour_id = tp.tour_id
LEFT JOIN Promotion p ON tp.promotion_id = p.promotion_id
AND CURRENT_DATE BETWEEN p.start_date AND p.end_date
AND p.status = 'active'
WHERE t.tour_id = %s AND t.availability = true
ORDER BY d.start_date
LIMIT 1;
"""
result = execute_query(query, (tour_id,), fetch_one=True)
return result
def search_tours_db(entities: dict):
base_query = """
SELECT
t.tour_id,
t.title,
t.duration,
t.departure_location,
t.destination,
t.region,
t.itinerary,
t.max_participants,
d.departure_id,
d.start_date,
d.price_adult,
d.price_child_120_140,
d.price_child_100_120,
p.promotion_id,
p.name AS promotion_name,
p.type AS promotion_type,
p.discount AS promotion_discount,
p.start_date AS promotion_start_date,
p.end_date AS promotion_end_date
FROM Departure d
JOIN Tour t ON d.tour_id = t.tour_id
LEFT JOIN Tour_Promotion tp ON t.tour_id = tp.tour_id
LEFT JOIN Promotion p ON tp.promotion_id = p.promotion_id
AND d.start_date BETWEEN p.start_date AND p.end_date
AND p.status = 'active'
WHERE t.availability = true AND d.availability = true
"""
filters = []
params = []
if entities.get('region'):
filters.append("t.region = %s")
params.append(entities['region'])
if entities.get('destination'):
dest_list = entities['destination'] if isinstance(entities['destination'], list) else [entities['destination']]
filters.append("t.destination && %s::text[]")
params.append(dest_list)
if entities.get('duration'):
filters.append("t.duration ILIKE %s")
params.append(f"%{entities['duration']}%")
if entities.get('time'):
time_filter_parts = []
time_info = entities['time']
if not isinstance(time_info, list): time_info = [time_info]
for time_obj in time_info:
if 'departure_date' in time_obj:
time_filter_parts.append("d.start_date = %s")
params.append(time_obj['departure_date'])
elif 'start_date' in time_obj and 'end_date' in time_obj:
time_filter_parts.append("d.start_date BETWEEN %s AND %s")
params.extend([time_obj['start_date'], time_obj['end_date']])
if time_filter_parts: filters.append(f"({' OR '.join(time_filter_parts)})")
if entities.get('budget'):
budget = str(entities['budget'])
try:
if '-' in budget:
min_price, max_price = map(float, budget.split('-'))
filters.append("d.price_adult BETWEEN %s AND %s")
params.extend([min_price, max_price])
else:
max_price = float(budget)
filters.append("d.price_adult <= %s")
params.append(max_price)
except ValueError:
pass
if entities.get('number_of_people'):
num_people = str(entities['number_of_people'])
min_required = 1
try:
if num_people.startswith('>'): min_required = int(num_people[1:]) + 1
elif '-' in num_people: min_req, _ = map(int, num_people.split('-')); min_required = max(min_required, min_req)
else: min_required = max(min_required, int(num_people))
except ValueError:
pass
if min_required > 1:
filters.append("t.max_participants >= %s")
params.append(min_required)
if filters:
base_query += " AND " + " AND ".join(filters)
base_query += " ORDER BY d.start_date, t.title;"
results = execute_query(base_query, tuple(params))
if results is None:
return []
return results |