/// AVAILABLE SELECT DISTINCT PSA.id AS psa_id, FTP.ticket_date, FTP.flight_id, (SELECT COUNT(FT.plan_1) FROM flight_ticket_prices FT WHERE FT.ticket_date = FTP.ticket_date AND FT.flight_id = FTP.flight_id AND FT.booked_status = 0 AND FT.plan_1 > 0) AS ftp_seats, PSA.available_seats FROM planwise_seat_availabilities PSA LEFT JOIN flight_ticket_prices FTP ON FTP.ticket_date = PSA.flight_date AND FTP.flight_id = PSA.flight_id LEFT JOIN flights F ON F.id = PSA.flight_id WHERE (SELECT COUNT(FT.id) FROM flight_ticket_prices FT WHERE FT.ticket_date = FTP.ticket_date AND FT.flight_id = FTP.flight_id AND FT.booked_status = 0 AND FT.plan_1 > 0) - PSA.available_seats != 0 AND PSA.plan_no = 1 AND PSA.flight_date > NOW() AND PSA.date_blocked = 0 AND PSA.status != 2 AND FTP.ticket_date > NOW() AND F.user_id = 140 AND F.status != 2 /// SOLD SELECT DISTINCT PSA.id AS psa_id, FTP.ticket_date, FTP.flight_id, (SELECT COUNT(FT.plan_1) FROM flight_ticket_prices FT WHERE FT.ticket_date = FTP.ticket_date AND FT.flight_id = FTP.flight_id AND FT.booked_status = 1 AND FT.plan_1 > 0) AS ftp_seats, PSA.sold_seats FROM planwise_seat_availabilities PSA LEFT JOIN flight_ticket_prices FTP ON FTP.ticket_date = PSA.flight_date AND FTP.flight_id = PSA.flight_id LEFT JOIN flights F ON F.id = PSA.flight_id WHERE (SELECT COUNT(FT.id) FROM flight_ticket_prices FT WHERE FT.ticket_date = FTP.ticket_date AND FT.flight_id = FTP.flight_id AND FT.booked_status IN (1, 3) AND FT.plan_1 > 0) - PSA.sold_seats != 0 AND PSA.plan_no = 1 AND PSA.flight_date > NOW() AND PSA.date_blocked = 0 AND FTP.ticket_date > NOW() AND F.user_id != 140 AND F.status != 2 CREATE TABLE 277_accounts LIKE 4_accounts; CREATE TABLE 277_banners LIKE 4_banners; CREATE TABLE 277_booking_tokens LIKE 4_booking_tokens; CREATE TABLE 277_enquiries LIKE 4_enquiries; CREATE TABLE 277_enquiry_chats LIKE 4_enquiry_chats; CREATE TABLE 277_enquiry_status LIKE 4_enquiry_status; CREATE TABLE 277_flight_bookings LIKE 4_flight_bookings; CREATE TABLE 277_flight_booking_details LIKE 4_flight_booking_details; CREATE TABLE 277_partner_apis LIKE 4_partner_apis; CREATE TABLE 277_partner_searches LIKE 4_partner_searches; CREATE TABLE 277_roles LIKE 4_roles; CREATE TABLE 277_users LIKE 4_users; CREATE TABLE 277_user_activities LIKE 4_user_activities; CREATE TABLE 277_user_device_tokens LIKE 4_user_device_tokens; INSERT INTO `277_roles` (`id`, `name`, `status`, `createdAt`, `updatedAt`) VALUES (NULL, 'Vendor', '1', NULL, NULL), (NULL, 'Client', '1', NULL, NULL); /// CRED 1 & CRED 2 ALTER TABLE `94_users` ADD `cred1` VARCHAR(22) NULL AFTER `device_type`, ADD `cred2` VARCHAR(22) NULL AFTER `cred1`; /// GOOGLE LOGIN ALTER TABLE `4_users` ADD `google_id` VARCHAR(100) NULL AFTER `device_type`, ADD `google_id_token` LONGTEXT NULL AFTER `google_id`; ALTER TABLE `api_datas` ADD `type` TINYINT(2) NOT NULL DEFAULT '0' COMMENT '0: oneway; 1: return' AFTER `user_id`; /// FIT ALTER TABLE `users` ADD `fit_partner` TINYINT(2) NOT NULL DEFAULT '0' AFTER `api_user`; ALTER TABLE `64_users` ADD `fit_group_id` BIGINT NOT NULL DEFAULT '0' AFTER `search_count_history`; SELECT booking_date, SUM(CASE WHEN user_id NOT IN (3, 4) THEN total_book_seats ELSE 0 END) AS total, SUM(CASE WHEN user_id = 134 THEN total_book_seats ELSE 0 END) AS HAP, SUM(CASE WHEN user_id = 254 THEN total_book_seats ELSE 0 END) AS CNP, SUM(CASE WHEN user_id = 96 THEN total_book_seats ELSE 0 END) AS BNT, SUM(CASE WHEN user_id = 295 THEN total_book_seats ELSE 0 END) AS FMO, SUM(CASE WHEN user_id = 274 THEN total_book_seats ELSE 0 END) AS TMZ, SUM(CASE WHEN user_id = 253 THEN total_book_seats ELSE 0 END) AS BUD, SUM(CASE WHEN user_id = 311 THEN total_book_seats ELSE 0 END) AS TPF, SUM(CASE WHEN user_id = 298 THEN total_book_seats ELSE 0 END) AS AIQ, SUM(CASE WHEN user_id = 288 THEN total_book_seats ELSE 0 END) AS FLS, SUM(CASE WHEN flight_id = 0 THEN total_book_seats ELSE 0 END) AS OTH FROM `flight_bookings` WHERE seat_book_status = 1 AND booking_date >= '2025-02-01' AND booking_date < '2025-03-01' GROUP BY booking_date /// Billing for Sainik Udaan SELECT SUM(total_book_seats) AS qty FROM `flight_bookings` WHERE `user_id` = 252 AND direct_booking_user_id = 1125 AND createdAt >= "2024-05-01" AND createdAt < "2024-06-01" AND seat_book_status != 0 ORDER BY `id` ASC SELECT u.company_name, direct_booking_user_id, SUM(total_book_seats) AS qty, GROUP_CONCAT(fb.id) AS id FROM `flight_bookings` fb JOIN website.326_users u on u.id = fb.direct_booking_user_id WHERE `user_id` = 326 AND f_user_id != 326 AND fb.createdAt >= "2024-04-01" AND fb.createdAt < "2024-05-01" AND seat_book_status != 0 AND u.cred1 is not null group by direct_booking_user_id /// PNR MARKUP ALTER TABLE `pnr_markup` ADD `user_id` BIGINT NOT NULL AFTER `id`, ADD `partner_user_id` BIGINT NOT NULL AFTER `user_id`, ADD `pnr` VARCHAR(10) NOT NULL AFTER `partner_user_id`, ADD `markup_adult` INT(11) NOT NULL AFTER `pnr`, ADD `markup_infant` INT(11) NOT NULL AFTER `markup_adult`, ADD `status` TINYINT(2) NOT NULL DEFAULT '1' AFTER `markup_infant`, ADD `createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `status`; ALTER TABLE `pnr_markup` ADD `updatedAt` DATETIME on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `createdAt`; /// FLIGHT NUMBER MARKUP ALTER TABLE `flight_number_markup` ADD `user_id` BIGINT NOT NULL AFTER `id`, ADD `partner_user_id` BIGINT NOT NULL AFTER `user_id`, ADD `flight_number` INT(11) NOT NULL AFTER `partner_user_id`, ADD `markup_adult` INT(5) NOT NULL AFTER `flight_number`, ADD `markup_infant` INT(5) NOT NULL AFTER `markup_adult`, ADD `status` TINYINT(2) NOT NULL DEFAULT '1' AFTER `markup_infant`, ADD `createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `status`, ADD `updatedAt` DATETIME on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `createdAt`; /// MARGINS ADDED IN FLIGHT BOOKING DETAILS ALTER TABLE `flight_booking_details` ADD `m_supplier` INT(5) NOT NULL DEFAULT '0' AFTER `ticket_price`, ADD `m_inv_owner_partner` INT(5) NOT NULL DEFAULT '0' AFTER `m_supplier`, ADD `m_pnr` INT(5) NOT NULL DEFAULT '0' AFTER `m_inv_owner_partner`, ADD `m_flight_number` INT(5) NOT NULL DEFAULT '0' AFTER `m_pnr`, ADD `m_partner` INT(5) NOT NULL DEFAULT '0' COMMENT 'margin set by the website owner on the partner inventory' AFTER `m_flight_number`, ADD `m_website_global` INT(5) NOT NULL DEFAULT '0' COMMENT 'global margin set by the website owner' AFTER `m_partner`, ADD `m_user` INT(5) NOT NULL DEFAULT '0' AFTER `m_website_global`, ADD `m_other` INT(5) NOT NULL DEFAULT '0' AFTER `m_user`, ADD `m_supplier_global` INT(5) NOT NULL DEFAULT '0' AFTER `m_supplier`; /// FOT FIT IN WEBSITE ALTER TABLE `273_booking_tokens` ADD `fare_quote_req` LONGTEXT NULL AFTER `is_expired`, ADD `fare_quote_res` LONGTEXT NULL AFTER `fare_quote_req`, ADD `price_check_req_our` LONGTEXT NULL AFTER `fare_quote_res`, ADD `price_check_res_our` LONGTEXT NULL AFTER `price_check_req_our`; ALTER TABLE `12_users` ADD `cred1` VARCHAR(22) NULL AFTER `device_type`, ADD `cred2` VARCHAR(22) NULL AFTER `cred1`; /// CANCELLATION ALTER TABLE `cancellation` ADD `user_id` INT NOT NULL AFTER `id`, ADD `airline_code` VARCHAR(10) NOT NULL AFTER `user_id`, ADD `dom_p` JSON NULL AFTER `airline_code`, ADD `dom_b2b` JSON NULL AFTER `dom_p`, ADD `dom_b2c` JSON NULL AFTER `dom_b2b`, ADD `int_p` JSON NULL AFTER `dom_b2c`, ADD `int_b2b` JSON NULL AFTER `int_p`, ADD `int_b2c` JSON NULL AFTER `int_b2b`, ADD `status` TINYINT NOT NULL DEFAULT '1' AFTER `int_b2c`, ADD `createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `status`, ADD `updatedAt` DATETIME on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `createdAt`, ADD `old_data` JSON NULL AFTER `updatedAt`; ALTER TABLE `users` ADD `dom_b2b` INT NOT NULL DEFAULT '0' AFTER `is_visa_supplier`, ADD `dom_b2c` INT NOT NULL DEFAULT '0' AFTER `dom_b2b`, ADD `int_b2b` INT NOT NULL DEFAULT '0' AFTER `dom_b2c`, ADD `int_b2c` INT NOT NULL DEFAULT '0' AFTER `int_b2b`; /// vendor_accounts ALTER TABLE `vendor_accounts` ADD `user_id` INT(11) NOT NULL AFTER `id`, ADD `web_user_id` INT(11) NOT NULL AFTER `user_id`, ADD `booking_id` BIGINT NOT NULL AFTER `web_user_id`, ADD `account_type` VARCHAR(50) NOT NULL AFTER `booking_id`, ADD `amount` DOUBLE NOT NULL AFTER `account_type`, ADD `available_balance` DOUBLE NOT NULL AFTER `amount`, ADD `actual_amount` DOUBLE NOT NULL AFTER `available_balance`, ADD `credit` DOUBLE NOT NULL AFTER `actual_amount`, ADD `balance` DOUBLE NOT NULL AFTER `credit`, ADD `credit_reversal_date` DATE NULL AFTER `balance`, ADD `credit_reversal_status` TINYINT NOT NULL DEFAULT '0' AFTER `credit_reversal_date`, ADD `credit_reversal_for` BIGINT NULL AFTER `credit_reversal_status`, ADD `narration` VARCHAR(200) NULL AFTER `credit_reversal_for`, ADD `reference_id` VARCHAR(100) NULL AFTER `narration`, ADD `transaction_id` VARCHAR(200) NULL AFTER `reference_id`, ADD `payment_charges_fix_amount` DOUBLE NULL AFTER `transaction_id`, ADD `payment_charges_in_percentag` INT NULL AFTER `payment_charges_fix_amount`, ADD `payment_details` LONGTEXT NOT NULL AFTER `payment_charges_in_percentag`, ADD `payment_gateway_id` INT NULL AFTER `payment_details`, ADD `payment_signature` VARCHAR(510) NULL AFTER `payment_gateway_id`, ADD `paytm_txt_token` VARCHAR(510) NULL AFTER `payment_signature`, ADD `payment_reason` VARCHAR(510) NULL AFTER `paytm_txt_token`, ADD `payment_received_status` TINYINT(3) NOT NULL DEFAULT '0' AFTER `payment_reason`, ADD `payment_status` TINYINT(3) NOT NULL DEFAULT '0' AFTER `payment_received_status`, ADD `status` TINYINT(3) NOT NULL DEFAULT '1' AFTER `payment_status`, ADD `createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `status`, ADD `updatedAt` DATETIME on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `createdAt`; /// vendors ALTER TABLE `vendor` ADD `user_id` INT NOT NULL AFTER `id`, ADD `role_id` INT NOT NULL AFTER `user_id`, ADD `name` VARCHAR(120) NOT NULL AFTER `role_id`, ADD `company_name` VARCHAR(200) NULL AFTER `name`, ADD `email` VARCHAR(200) NOT NULL AFTER `company_name`, ADD `mobile` VARCHAR(25) NOT NULL AFTER `email`, ADD `address` VARCHAR(250) NULL AFTER `mobile`, ADD `address_2` VARCHAR(250) NULL AFTER `address`, ADD `city` VARCHAR(100) NULL AFTER `address_2`, ADD `state_id` INT NULL AFTER `city`, ADD `country_id` INT NULL AFTER `state_id`, ADD `zipcode` VARCHAR(10) NULL AFTER `country_id`, ADD `contact_person_name` VARCHAR(100) NULL AFTER `zipcode`, ADD `contact_person_no` VARCHAR(25) NULL AFTER `contact_person_name`, ADD `iata_code` VARCHAR(100) NULL AFTER `contact_person_no`, ADD `website` VARCHAR(100) NULL AFTER `iata_code`, ADD `gst_no` VARCHAR(20) NULL AFTER `website`, ADD `pan_no` VARCHAR(20) NULL AFTER `gst_no`, ADD `aadhaar_no` VARCHAR(20) NULL AFTER `pan_no`, ADD `gst_doc` VARCHAR(100) NULL AFTER `aadhaar_no`, ADD `pan_doc` VARCHAR(100) NULL AFTER `gst_doc`, ADD `aadhaar_front_doc` VARCHAR(100) NULL AFTER `pan_doc`, ADD `aadhaar_back_doc` VARCHAR(100) NULL AFTER `aadhaar_front_doc`, ADD `mail_otp` VARCHAR(20) NULL AFTER `aadhaar_back_doc`, ADD `password` VARCHAR(150) NOT NULL AFTER `mail_otp`, ADD `verified` TINYINT(2) NULL DEFAULT '0' AFTER `password`, ADD `my_adult` INT NULL DEFAULT '0' AFTER `verified`, ADD `my_infant` INT NULL DEFAULT '0' AFTER `my_adult`, ADD `partner_adult` INT NULL DEFAULT '0' AFTER `my_infant`, ADD `partner_infant` INT NULL DEFAULT '0' AFTER `partner_adult`, ADD `supplier_adult` INT NULL DEFAULT '0' AFTER `partner_infant`, ADD `supplier_infant` INT NULL DEFAULT '0' AFTER `supplier_adult`, ADD `status` TINYINT NULL DEFAULT '1' AFTER `supplier_infant`, ADD `device_token` VARCHAR(200) NULL AFTER `status`, ADD `device_type` VARCHAR(200) NULL AFTER `device_token`, ADD `google_id` VARCHAR(200) NULL AFTER `device_type`, ADD `google_id_token` VARCHAR(200) NULL AFTER `google_id`, ADD `cred` VARCHAR(200) NULL AFTER `google_id_token`, ADD `search_limit` INT NULL DEFAULT '0' AFTER `cred`, ADD `search_count` INT NULL DEFAULT '0' AFTER `search_limit`, ADD `search_count_history` LONGTEXT NULL AFTER `search_count`, ADD `nearest_airport` VARCHAR(20) NULL AFTER `search_count_history`, ADD `credit` INT NOT NULL DEFAULT '0' AFTER `nearest_airport`, ADD `balance` INT NOT NULL DEFAULT '0' AFTER `credit`, ADD `createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `balance`, ADD `updatedAt` DATETIME on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `createdAt`; ALTER TABLE `vendor` ADD `reference_id` VARCHAR(10) NOT NULL AFTER `role_id`; ALTER TABLE `vendors` ADD `image` VARCHAR(100) NULL AFTER `zipcode`; ALTER TABLE `vendor_accounts` CHANGE `booking_id` `booking_ref_id` VARCHAR(100) NULL; /// EXPIRE JWT TOKEN ALTER TABLE `4_users` ADD `last_visited` TIMESTAMP NULL AFTER `ggn_group_id`; ALTER TABLE `users` ADD `logout_time_hr` INT(5) NOT NULL DEFAULT '0' AFTER `master_key`; /// PAX_NAMES UPDATE `flight_bookings` fb SET pax_names = (SELECT GROUP_CONCAT(fbd.gender, ' ', fbd.first_name, ' ', IF(fbd.middle_name IS NOT NULL AND fbd.middle_name != '', CONCAT(fbd.middle_name, ' '), ''), fbd.last_name, IF(fbd.age < 2, ' (INF)', '') SEPARATOR ', ') FROM flight_booking_details fbd WHERE fbd.flight_booking_id = fb.id) WHERE fb.id = 4637 /// WEBSITE ACCOUNTS ALTER TABLE `3_accounts` CHANGE `account_type` `account_type` ENUM('booking','balance','credit','refund','other_charges','credit_reversal') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'balance' /// xTripJack - share_id & dont_share_id ALTER TABLE `326_users` ADD `share_ids` VARCHAR(250) NULL AFTER `cred2`, ADD `dont_share_ids` VARCHAR(250) NULL AFTER `share_ids`;