| @@ -19,9 +19,9 @@ | ||
| 19 | 19 | { |
| 20 | 20 | /** |
| 21 | 21 | * Table name without prefix |
| 22 | 22 | */ |
| 23 | - // private const TABLE_NAME = 'yatra_new_booking_payments'; | |
| 23 | + // private const TABLE_NAME = 'yatra_booking_payments'; | |
| 24 | 24 | |
| 25 | 25 | /** @var bool|null */ |
| 26 | 26 | private ?bool $customerColumnExists = null; |
| 27 | 27 | |
| @@ -130,8 +130,28 @@ | ||
| 130 | 130 | $count_query = $this->wpdb->prepare($count_query, ...$where_values); |
| 131 | 131 | } |
| 132 | 132 | $total = (int) $this->wpdb->get_var($count_query); |
| 133 | 133 | |
| 134 | + // Resolve the sort column from a strict whitelist. ORDER BY cannot be | |
| 135 | + // parameterised with $wpdb->prepare (that's for values), so the column | |
| 136 | + // MUST come from this map of known-safe expressions and the direction is | |
| 137 | + // constrained to ASC/DESC — user input never reaches the SQL directly. | |
| 138 | + $sortColumns = [ | |
| 139 | + 'payment' => 'p.id', | |
| 140 | + 'customer' => 'b.contact_first_name', | |
| 141 | + 'amount' => 'p.amount', | |
| 142 | + 'method' => 'p.gateway', | |
| 143 | + 'status' => 'p.status', | |
| 144 | + 'date' => 'p.created_at', | |
| 145 | + 'payment_date' => 'p.created_at', | |
| 146 | + 'created_at' => 'p.created_at', | |
| 147 | + 'transaction_id' => 'p.transaction_id', | |
| 148 | + ]; | |
| 149 | + $orderColumn = $sortColumns[(string) ($filters['orderby'] ?? '')] ?? 'p.created_at'; | |
| 150 | + $orderDir = strtoupper((string) ($filters['order'] ?? '')) === 'ASC' ? 'ASC' : 'DESC'; | |
| 151 | + // Stable tie-breaker so equal values keep a deterministic order across pages. | |
| 152 | + $order_sql = $orderColumn . ' ' . $orderDir . ', p.id DESC'; | |
| 153 | + | |
| 134 | 154 | // Get payments with booking and trip info |
| 135 | 155 | $query = "SELECT p.*, |
| 136 | 156 | b.reference as booking_reference, |
| 137 | 157 | b.contact_email, |
| @@ -141,9 +161,9 @@ | ||
| 141 | 161 | FROM {$table} p |
| 142 | 162 | LEFT JOIN {$bookings_table} b ON p.booking_id = b.id |
| 143 | 163 | LEFT JOIN {$trips_table} t ON b.trip_id = t.id |
| 144 | 164 | WHERE {$where_sql} |
| 145 | - ORDER BY p.created_at DESC | |
| 165 | + ORDER BY {$order_sql} | |
| 146 | 166 | LIMIT %d OFFSET %d"; |
| 147 | 167 | |
| 148 | 168 | $query_values = array_merge($where_values, [$per_page, $offset]); |
| 149 | 169 | $payments = $this->wpdb->get_results($this->wpdb->prepare($query, ...$query_values)); |
| @@ -175,13 +195,21 @@ | ||
| 175 | 195 | b.user_id as booking_user_id, |
| 176 | 196 | b.contact_email, |
| 177 | 197 | b.contact_first_name, |
| 178 | 198 | b.contact_last_name, |
| 199 | + b.contact_data, | |
| 200 | + b.contact_country, | |
| 201 | + b.customer_id, | |
| 179 | 202 | b.total_amount as booking_total_amount, |
| 180 | 203 | b.amount_paid as booking_amount_paid, |
| 181 | 204 | b.amount_due as booking_amount_due, |
| 182 | 205 | b.travel_date as travel_date, |
| 183 | - t.title as trip_title | |
| 206 | + b.end_date as booking_end_date, | |
| 207 | + b.trip_id as trip_id, | |
| 208 | + b.payment_method as payment_method, | |
| 209 | + t.title as trip_title, | |
| 210 | + t.duration_days as trip_duration_days, | |
| 211 | + t.duration_nights as trip_duration_nights | |
| 184 | 212 | FROM {$table} p |
| 185 | 213 | LEFT JOIN {$bookings_table} b ON p.booking_id = b.id |
| 186 | 214 | LEFT JOIN {$trips_table} t ON b.trip_id = t.id |
| 187 | 215 | WHERE p.id = %d", |
