===== sin-search-todo / list ===== SELECT STRAIGHT_JOIN `r`.`Id_Room`, `r`.`Room_Date`, `r`.`Room_RequestDateStart`, `r`.`Room_RequestDateEnd`, `r`.`Room_RequestLastDateStart`, `r`.`Room_RequestLastDateEnd`, `r`.`Room_ReservationDateAlarm`, `r`.`Room_ReservationDateFinal`, `r`.`Room_Observation`, `r`.`Room_BuyPrice`, `r`.`Room_Paid`, `r`.`Room_BuyDateStart`, `r`.`Room_BuyDateEnd`, `r`.`Room_BuyDatePayment`, `r`.`Room_BuyCode`, `r`.`Room_Lock`, `r`.`Room_SoftLock`, `r`.`Room_PassengersAmount`, `r`.`Room_NoValidAmount`, `r`.`Room_DateChanged`, `r`.`Room_Status`, `r`.`Id_Booking`, `r`.`Id_TypeRoom`, `r`.`Id_BookingExtra`, `r`.`Id_Provider`, `r`.`Id_Provider_Buy`, `r`.`Id_Admin`, `r`.`Id_Admin_Validated`, `r`.`Id_PaxInformationRoom`, `r`.`Id_TypePayment`, `tr`.`Id_TypeRoom`, `tr`.`TypeRoom_Name`, `tr`.`TypeRoom_Abrv`, `tr`.`TypeRoom_Capacity`, `tr`.`TypeRoom_Status`, `be`.`Id_BookingExtra`, `be`.`BookingExtra_Quantity`, `be`.`BookingExtra_Price`, `be`.`BookingExtra_Total`, `be`.`BookingExtra_Feature`, `be`.`BookingExtra_Included`, `be`.`BookingExtra_Mode`, `e`.`Id_Extra`, `e`.`Extra_Name`, `bt`.`Id_BookingTour`, `bt`.`BookingTour_DateStart`, `bt`.`BookingTour_PickUp`, `bt`.`BookingTour_Type`, `bt`.`BookingTour_NoPax`, `bt`.`BookingTour_Name`, `bt`.`BookingTour_TourName`, `bt`.`BookingTour_FeeType`, `bt`.`BookingTour_Full`, `bt`.`BookingTour_MinimumFull`, `bt`.`BookingTour_Group`, `bt`.`BookingTour_TourDuration`, `bt`.`BookingTour_DateBriefing`, `bt`.`BookingTour_PlaceBriefing`, `bt`.`BookingTour_UseBookingPlaceBriefing`, `bt`.`BookingTour_Status`, `bt`.`Id_Booking`, `bt`.`BookingTour_Date`, `bt`.`Id_Package`, `bt`.`Id_Tour`, `bt`.`Id_LodgingPlace_PickUp`, `bt`.`Id_LodgingPlace_DropOff`, `t`.`Id_Tour`, `t`.`Tour_Name`, `t`.`Tour_Code`, `t`.`Tour_Color`, `pk`.`Id_Package`, `pk`.`Package_Name`, `pk`.`Package_Code`, `r`.`Id_Provider`, `pr`.`Provider_BusinessName`, `pr`.`Provider_NickName`, `pr`.`Provider_Phone`, `pr`.`Provider_ImgProfile_Thumb`, `b`.`Id_Booking`, `b`.`Booking_Date`, `b`.`Booking_DateConfirm`, `b`.`Booking_DateStart`, `b`.`Booking_DateBriefing`, `b`.`Booking_PlaceBriefing`, `b`.`Booking_Code`, `b`.`Booking_HistoryChanges`, `b`.`Booking_Hotel`, `b`.`Booking_Observation`, `b`.`Booking_InternalRemark`, `b`.`Booking_Receipt`, `b`.`Booking_Type`, `b`.`Booking_LastMinute`, `b`.`Booking_Assigned`, `b`.`Booking_Status`, `b`.`Booking_ManuallyConfirmed`, `b`.`Booking_InPerson`, `b`.`Booking_PassengerAmount`, `b`.`Booking_CheckedInPassengers`, `b`.`Booking_LastPassenger`, `b`.`Booking_Token`, `b`.`Id_Booking_Replace`, `b`.`Id_Admin`, `b`.`Id_GuideLanguage`, `b`.`Id_Language`, `b`.`Id_User`, `b`.`Id_User_Additional`, `b`.`Id_BookingRelationship`, `b`.`Id_BookingReferredFrom`, CONCAT('[', IFNULL(GROUP_CONCAT(DISTINCT IF(`rp`.`Id_RoomPassenger` IS NULL, NULL, JSON_OBJECT( 'Id_RoomPassenger', `rp`.`Id_RoomPassenger`, 'RoomPassenger_IsNotValid', `rp`.`RoomPassenger_IsNotValid`, 'RoomPassenger_Status', `rp`.`RoomPassenger_Status`, 'Id_BookingExtraPassenger', `bep`.`Id_BookingExtraPassenger`, 'BookingExtraPassenger_DateEntryStart', `bep`.`BookingExtraPassenger_DateEntryStart`, 'BookingExtraPassenger_DateEntryEnd', `bep`.`BookingExtraPassenger_DateEntryEnd`, 'BookingExtraPassenger_DatePurchase', `bep`.`BookingExtraPassenger_DatePurchase`, 'BookingExtraPassenger_Remark', `bep`.`BookingExtraPassenger_Remark`, 'BookingExtraPassenger_Status', `bep`.`BookingExtraPassenger_Status`, 'Id_BookingTourPassenger', `btp`.`Id_BookingTourPassenger`, 'BookingTourPassenger_Remark', `btp`.`BookingTourPassenger_Remark`, 'BookingTourPassenger_Price', `btp`.`BookingTourPassenger_Price`, 'BookingTourPassenger_Full', `btp`.`BookingTourPassenger_Full`, 'BookingTourPassenger_ValidGroups', `btp`.`BookingTourPassenger_ValidGroups`, 'BookingTourPassenger_NoValidGroups', `btp`.`BookingTourPassenger_NoValidGroups`, 'BookingTourPassenger_Status', `btp`.`BookingTourPassenger_Status`, 'BookingTourPassenger_PaymentStatus', `btp`.`BookingTourPassenger_PaymentStatus`, 'Id_Passenger', `p`.`Id_Passenger`, 'Passenger_Name', `p`.`Passenger_Name`, 'Passenger_LastName', `p`.`Passenger_LastName`, 'Passenger_Gender', `p`.`Passenger_Gender`, 'Passenger_DOB', `p`.`Passenger_DOB`, 'Passenger_NoDocument', `p`.`Passenger_NoDocument`, 'Passenger_Status', `p`.`Passenger_Status`, 'Passenger_PaymentStatus', `p`.`Passenger_PaymentStatus`, 'Passenger_Age', YEAR(CURDATE())-YEAR(`p`.`Passenger_DOB`) + IF(DATE_FORMAT(CURDATE(),'%m-%d') > DATE_FORMAT(`p`.`Passenger_DOB`,'%m-%d'), 0 , -1 ), 'Id_UserCountry', `uc`.`Id_UserCountry`, 'UserCountry_Name', `uc`.`UserCountry_Name`, 'UserCountry_Code', `uc`.`UserCountry_Code`, 'UserCountry_Img', `uc`.`UserCountry_Img`, 'Id_TypeDocument', `td`.`Id_TypeDocument`, 'TypeDocument_Name', `td`.`TypeDocument_Name`, 'TypeDocument_Abrv', `td`.`TypeDocument_Abrv`, 'Invoice_Total', `i`.`Invoice_Total`, 'Invoice_TotalPay', `i`.`Invoice_TotalPay`, 'Invoice_TotalPending', `i`.`Invoice_TotalPending` )) SEPARATOR ','), ''), ']' ) AS `PassengerList`, (COUNT(`rp`.`Id_RoomPassenger`)) AS `PassengerCount` FROM `t_room` `r` FORCE INDEX FOR ORDER BY (`INDEX_Room_RequestDateStart`) INNER JOIN `t_type_room` `tr` ON `tr`.`Id_TypeRoom` = `r`.`Id_TypeRoom` INNER JOIN `t_booking_extra` `be` ON `be`.`Id_BookingExtra` = `r`.`Id_BookingExtra` INNER JOIN `t_extra` `e` ON `e`.`Id_Extra` = `be`.`Id_Extra` INNER JOIN `t_booking_tour` `bt` ON `bt`.`Id_BookingTour` = `be`.`Id_BookingTour` INNER JOIN `t_booking` `b` ON `b`.`Id_Booking` = `bt`.`Id_Booking` INNER JOIN `t_tour` `t` ON `t`.`Id_Tour` = `bt`.`Id_Tour` INNER JOIN `t_package` `pk` ON `pk`.`Id_Package` = `bt`.`Id_Package` LEFT JOIN `t_provider` `pr` ON `pr`.`Id_Provider` = `r`.`Id_Provider` LEFT JOIN `t_room_passenger` `rp` ON `rp`.`Id_Room` = `r`.`Id_Room` AND `rp`.`RoomPassenger_Status` <> 0 LEFT JOIN `t_booking_extra_passenger` `bep` ON `bep`.`Id_BookingExtraPassenger` = `rp`.`Id_BookingExtraPassenger` LEFT JOIN `t_booking_tour_passenger` `btp` ON `btp`.`Id_BookingTourPassenger` = `bep`.`Id_BookingTourPassenger` LEFT JOIN `t_passenger` `p` ON `p`.`Id_Passenger` = `btp`.`Id_Passenger` LEFT JOIN `t_invoice` `i` ON `i`.`Id_Passenger` = `p`.`Id_Passenger` LEFT JOIN `t_user_country` `uc` ON `uc`.`Id_UserCountry` = `p`.`Id_UserCountry` LEFT JOIN `t_type_document` `td` ON `td`.`Id_TypeDocument` = `p`.`Id_TypeDocument` WHERE `e`.`Id_TypeExtra` = 4 AND `r`.`Room_Status` <> 0 AND ( `rp`.`Id_RoomPassenger` IS NULL OR `bep`.`BookingExtraPassenger_Status` = 2 OR ( `bep`.`BookingExtraPassenger_Status` = 1 AND `btp`.`BookingTourPassenger_Status` IN (1, 2, 3) AND `b`.`Booking_Status` IN (1, 2) AND ( `b`.`Booking_ManuallyConfirmed` = 2 OR `btp`.`BookingTourPassenger_PaymentStatus` = 2 ) ) ) AND ((1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1 = ?) AND (1 = ?) AND (1 = ?) AND (`r`.`Room_RequestDateStart` BETWEEN ? AND ?)) GROUP BY `r`.`Id_Room` ORDER BY `r`.`Room_RequestDateStart` desc, `t`.`Id_Tour` ASC, `b`.`Id_Booking` ASC, `r`.`Id_Room` ASC LIMIT 50 OFFSET 0 -- binds: ["1","1","1","2024-01-01 00:00:00","2030-12-31 23:59:59"] ===== sin-search-todo / count ===== SELECT STRAIGHT_JOIN COUNT(DISTINCT `r`.`Id_Room`) as `total` FROM `t_room` `r` INNER JOIN `t_type_room` `tr` ON `tr`.`Id_TypeRoom` = `r`.`Id_TypeRoom` INNER JOIN `t_booking_extra` `be` ON `be`.`Id_BookingExtra` = `r`.`Id_BookingExtra` INNER JOIN `t_extra` `e` ON `e`.`Id_Extra` = `be`.`Id_Extra` INNER JOIN `t_booking_tour` `bt` ON `bt`.`Id_BookingTour` = `be`.`Id_BookingTour` INNER JOIN `t_booking` `b` ON `b`.`Id_Booking` = `bt`.`Id_Booking` INNER JOIN `t_tour` `t` ON `t`.`Id_Tour` = `bt`.`Id_Tour` INNER JOIN `t_package` `pk` ON `pk`.`Id_Package` = `bt`.`Id_Package` LEFT JOIN `t_provider` `pr` ON `pr`.`Id_Provider` = `r`.`Id_Provider` LEFT JOIN `t_room_passenger` `rp` ON `rp`.`Id_Room` = `r`.`Id_Room` AND `rp`.`RoomPassenger_Status` <> 0 LEFT JOIN `t_booking_extra_passenger` `bep` ON `bep`.`Id_BookingExtraPassenger` = `rp`.`Id_BookingExtraPassenger` LEFT JOIN `t_booking_tour_passenger` `btp` ON `btp`.`Id_BookingTourPassenger` = `bep`.`Id_BookingTourPassenger` WHERE `e`.`Id_TypeExtra` = 4 AND `r`.`Room_Status` <> 0 AND ( `rp`.`Id_RoomPassenger` IS NULL OR `bep`.`BookingExtraPassenger_Status` = 2 OR ( `bep`.`BookingExtraPassenger_Status` = 1 AND `btp`.`BookingTourPassenger_Status` IN (1, 2, 3) AND `b`.`Booking_Status` IN (1, 2) AND ( `b`.`Booking_ManuallyConfirmed` = 2 OR `btp`.`BookingTourPassenger_PaymentStatus` = 2 ) ) ) AND ((1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1 = ?) AND (1 = ?) AND (1 = ?) AND (`r`.`Room_RequestDateStart` BETWEEN ? AND ?)) -- binds: ["1","1","1","2024-01-01 00:00:00","2030-12-31 23:59:59"] ===== pagina-3 / list ===== SELECT STRAIGHT_JOIN `r`.`Id_Room`, `r`.`Room_Date`, `r`.`Room_RequestDateStart`, `r`.`Room_RequestDateEnd`, `r`.`Room_RequestLastDateStart`, `r`.`Room_RequestLastDateEnd`, `r`.`Room_ReservationDateAlarm`, `r`.`Room_ReservationDateFinal`, `r`.`Room_Observation`, `r`.`Room_BuyPrice`, `r`.`Room_Paid`, `r`.`Room_BuyDateStart`, `r`.`Room_BuyDateEnd`, `r`.`Room_BuyDatePayment`, `r`.`Room_BuyCode`, `r`.`Room_Lock`, `r`.`Room_SoftLock`, `r`.`Room_PassengersAmount`, `r`.`Room_NoValidAmount`, `r`.`Room_DateChanged`, `r`.`Room_Status`, `r`.`Id_Booking`, `r`.`Id_TypeRoom`, `r`.`Id_BookingExtra`, `r`.`Id_Provider`, `r`.`Id_Provider_Buy`, `r`.`Id_Admin`, `r`.`Id_Admin_Validated`, `r`.`Id_PaxInformationRoom`, `r`.`Id_TypePayment`, `tr`.`Id_TypeRoom`, `tr`.`TypeRoom_Name`, `tr`.`TypeRoom_Abrv`, `tr`.`TypeRoom_Capacity`, `tr`.`TypeRoom_Status`, `be`.`Id_BookingExtra`, `be`.`BookingExtra_Quantity`, `be`.`BookingExtra_Price`, `be`.`BookingExtra_Total`, `be`.`BookingExtra_Feature`, `be`.`BookingExtra_Included`, `be`.`BookingExtra_Mode`, `e`.`Id_Extra`, `e`.`Extra_Name`, `bt`.`Id_BookingTour`, `bt`.`BookingTour_DateStart`, `bt`.`BookingTour_PickUp`, `bt`.`BookingTour_Type`, `bt`.`BookingTour_NoPax`, `bt`.`BookingTour_Name`, `bt`.`BookingTour_TourName`, `bt`.`BookingTour_FeeType`, `bt`.`BookingTour_Full`, `bt`.`BookingTour_MinimumFull`, `bt`.`BookingTour_Group`, `bt`.`BookingTour_TourDuration`, `bt`.`BookingTour_DateBriefing`, `bt`.`BookingTour_PlaceBriefing`, `bt`.`BookingTour_UseBookingPlaceBriefing`, `bt`.`BookingTour_Status`, `bt`.`Id_Booking`, `bt`.`BookingTour_Date`, `bt`.`Id_Package`, `bt`.`Id_Tour`, `bt`.`Id_LodgingPlace_PickUp`, `bt`.`Id_LodgingPlace_DropOff`, `t`.`Id_Tour`, `t`.`Tour_Name`, `t`.`Tour_Code`, `t`.`Tour_Color`, `pk`.`Id_Package`, `pk`.`Package_Name`, `pk`.`Package_Code`, `r`.`Id_Provider`, `pr`.`Provider_BusinessName`, `pr`.`Provider_NickName`, `pr`.`Provider_Phone`, `pr`.`Provider_ImgProfile_Thumb`, `b`.`Id_Booking`, `b`.`Booking_Date`, `b`.`Booking_DateConfirm`, `b`.`Booking_DateStart`, `b`.`Booking_DateBriefing`, `b`.`Booking_PlaceBriefing`, `b`.`Booking_Code`, `b`.`Booking_HistoryChanges`, `b`.`Booking_Hotel`, `b`.`Booking_Observation`, `b`.`Booking_InternalRemark`, `b`.`Booking_Receipt`, `b`.`Booking_Type`, `b`.`Booking_LastMinute`, `b`.`Booking_Assigned`, `b`.`Booking_Status`, `b`.`Booking_ManuallyConfirmed`, `b`.`Booking_InPerson`, `b`.`Booking_PassengerAmount`, `b`.`Booking_CheckedInPassengers`, `b`.`Booking_LastPassenger`, `b`.`Booking_Token`, `b`.`Id_Booking_Replace`, `b`.`Id_Admin`, `b`.`Id_GuideLanguage`, `b`.`Id_Language`, `b`.`Id_User`, `b`.`Id_User_Additional`, `b`.`Id_BookingRelationship`, `b`.`Id_BookingReferredFrom`, CONCAT('[', IFNULL(GROUP_CONCAT(DISTINCT IF(`rp`.`Id_RoomPassenger` IS NULL, NULL, JSON_OBJECT( 'Id_RoomPassenger', `rp`.`Id_RoomPassenger`, 'RoomPassenger_IsNotValid', `rp`.`RoomPassenger_IsNotValid`, 'RoomPassenger_Status', `rp`.`RoomPassenger_Status`, 'Id_BookingExtraPassenger', `bep`.`Id_BookingExtraPassenger`, 'BookingExtraPassenger_DateEntryStart', `bep`.`BookingExtraPassenger_DateEntryStart`, 'BookingExtraPassenger_DateEntryEnd', `bep`.`BookingExtraPassenger_DateEntryEnd`, 'BookingExtraPassenger_DatePurchase', `bep`.`BookingExtraPassenger_DatePurchase`, 'BookingExtraPassenger_Remark', `bep`.`BookingExtraPassenger_Remark`, 'BookingExtraPassenger_Status', `bep`.`BookingExtraPassenger_Status`, 'Id_BookingTourPassenger', `btp`.`Id_BookingTourPassenger`, 'BookingTourPassenger_Remark', `btp`.`BookingTourPassenger_Remark`, 'BookingTourPassenger_Price', `btp`.`BookingTourPassenger_Price`, 'BookingTourPassenger_Full', `btp`.`BookingTourPassenger_Full`, 'BookingTourPassenger_ValidGroups', `btp`.`BookingTourPassenger_ValidGroups`, 'BookingTourPassenger_NoValidGroups', `btp`.`BookingTourPassenger_NoValidGroups`, 'BookingTourPassenger_Status', `btp`.`BookingTourPassenger_Status`, 'BookingTourPassenger_PaymentStatus', `btp`.`BookingTourPassenger_PaymentStatus`, 'Id_Passenger', `p`.`Id_Passenger`, 'Passenger_Name', `p`.`Passenger_Name`, 'Passenger_LastName', `p`.`Passenger_LastName`, 'Passenger_Gender', `p`.`Passenger_Gender`, 'Passenger_DOB', `p`.`Passenger_DOB`, 'Passenger_NoDocument', `p`.`Passenger_NoDocument`, 'Passenger_Status', `p`.`Passenger_Status`, 'Passenger_PaymentStatus', `p`.`Passenger_PaymentStatus`, 'Passenger_Age', YEAR(CURDATE())-YEAR(`p`.`Passenger_DOB`) + IF(DATE_FORMAT(CURDATE(),'%m-%d') > DATE_FORMAT(`p`.`Passenger_DOB`,'%m-%d'), 0 , -1 ), 'Id_UserCountry', `uc`.`Id_UserCountry`, 'UserCountry_Name', `uc`.`UserCountry_Name`, 'UserCountry_Code', `uc`.`UserCountry_Code`, 'UserCountry_Img', `uc`.`UserCountry_Img`, 'Id_TypeDocument', `td`.`Id_TypeDocument`, 'TypeDocument_Name', `td`.`TypeDocument_Name`, 'TypeDocument_Abrv', `td`.`TypeDocument_Abrv`, 'Invoice_Total', `i`.`Invoice_Total`, 'Invoice_TotalPay', `i`.`Invoice_TotalPay`, 'Invoice_TotalPending', `i`.`Invoice_TotalPending` )) SEPARATOR ','), ''), ']' ) AS `PassengerList`, (COUNT(`rp`.`Id_RoomPassenger`)) AS `PassengerCount` FROM `t_room` `r` FORCE INDEX FOR ORDER BY (`INDEX_Room_RequestDateStart`) INNER JOIN `t_type_room` `tr` ON `tr`.`Id_TypeRoom` = `r`.`Id_TypeRoom` INNER JOIN `t_booking_extra` `be` ON `be`.`Id_BookingExtra` = `r`.`Id_BookingExtra` INNER JOIN `t_extra` `e` ON `e`.`Id_Extra` = `be`.`Id_Extra` INNER JOIN `t_booking_tour` `bt` ON `bt`.`Id_BookingTour` = `be`.`Id_BookingTour` INNER JOIN `t_booking` `b` ON `b`.`Id_Booking` = `bt`.`Id_Booking` INNER JOIN `t_tour` `t` ON `t`.`Id_Tour` = `bt`.`Id_Tour` INNER JOIN `t_package` `pk` ON `pk`.`Id_Package` = `bt`.`Id_Package` LEFT JOIN `t_provider` `pr` ON `pr`.`Id_Provider` = `r`.`Id_Provider` LEFT JOIN `t_room_passenger` `rp` ON `rp`.`Id_Room` = `r`.`Id_Room` AND `rp`.`RoomPassenger_Status` <> 0 LEFT JOIN `t_booking_extra_passenger` `bep` ON `bep`.`Id_BookingExtraPassenger` = `rp`.`Id_BookingExtraPassenger` LEFT JOIN `t_booking_tour_passenger` `btp` ON `btp`.`Id_BookingTourPassenger` = `bep`.`Id_BookingTourPassenger` LEFT JOIN `t_passenger` `p` ON `p`.`Id_Passenger` = `btp`.`Id_Passenger` LEFT JOIN `t_invoice` `i` ON `i`.`Id_Passenger` = `p`.`Id_Passenger` LEFT JOIN `t_user_country` `uc` ON `uc`.`Id_UserCountry` = `p`.`Id_UserCountry` LEFT JOIN `t_type_document` `td` ON `td`.`Id_TypeDocument` = `p`.`Id_TypeDocument` WHERE `e`.`Id_TypeExtra` = 4 AND `r`.`Room_Status` <> 0 AND ( `rp`.`Id_RoomPassenger` IS NULL OR `bep`.`BookingExtraPassenger_Status` = 2 OR ( `bep`.`BookingExtraPassenger_Status` = 1 AND `btp`.`BookingTourPassenger_Status` IN (1, 2, 3) AND `b`.`Booking_Status` IN (1, 2) AND ( `b`.`Booking_ManuallyConfirmed` = 2 OR `btp`.`BookingTourPassenger_PaymentStatus` = 2 ) ) ) AND ((1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1 = ?) AND (1 = ?) AND (1 = ?) AND (`r`.`Room_RequestDateStart` BETWEEN ? AND ?)) GROUP BY `r`.`Id_Room` ORDER BY `r`.`Room_RequestDateStart` desc, `t`.`Id_Tour` ASC, `b`.`Id_Booking` ASC, `r`.`Id_Room` ASC LIMIT 50 OFFSET 100 -- binds: ["1","1","1","2024-01-01 00:00:00","2030-12-31 23:59:59"] ===== pagina-3 / count ===== SELECT STRAIGHT_JOIN COUNT(DISTINCT `r`.`Id_Room`) as `total` FROM `t_room` `r` INNER JOIN `t_type_room` `tr` ON `tr`.`Id_TypeRoom` = `r`.`Id_TypeRoom` INNER JOIN `t_booking_extra` `be` ON `be`.`Id_BookingExtra` = `r`.`Id_BookingExtra` INNER JOIN `t_extra` `e` ON `e`.`Id_Extra` = `be`.`Id_Extra` INNER JOIN `t_booking_tour` `bt` ON `bt`.`Id_BookingTour` = `be`.`Id_BookingTour` INNER JOIN `t_booking` `b` ON `b`.`Id_Booking` = `bt`.`Id_Booking` INNER JOIN `t_tour` `t` ON `t`.`Id_Tour` = `bt`.`Id_Tour` INNER JOIN `t_package` `pk` ON `pk`.`Id_Package` = `bt`.`Id_Package` LEFT JOIN `t_provider` `pr` ON `pr`.`Id_Provider` = `r`.`Id_Provider` LEFT JOIN `t_room_passenger` `rp` ON `rp`.`Id_Room` = `r`.`Id_Room` AND `rp`.`RoomPassenger_Status` <> 0 LEFT JOIN `t_booking_extra_passenger` `bep` ON `bep`.`Id_BookingExtraPassenger` = `rp`.`Id_BookingExtraPassenger` LEFT JOIN `t_booking_tour_passenger` `btp` ON `btp`.`Id_BookingTourPassenger` = `bep`.`Id_BookingTourPassenger` WHERE `e`.`Id_TypeExtra` = 4 AND `r`.`Room_Status` <> 0 AND ( `rp`.`Id_RoomPassenger` IS NULL OR `bep`.`BookingExtraPassenger_Status` = 2 OR ( `bep`.`BookingExtraPassenger_Status` = 1 AND `btp`.`BookingTourPassenger_Status` IN (1, 2, 3) AND `b`.`Booking_Status` IN (1, 2) AND ( `b`.`Booking_ManuallyConfirmed` = 2 OR `btp`.`BookingTourPassenger_PaymentStatus` = 2 ) ) ) AND ((1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1 = ?) AND (1 = ?) AND (1 = ?) AND (`r`.`Room_RequestDateStart` BETWEEN ? AND ?)) -- binds: ["1","1","1","2024-01-01 00:00:00","2030-12-31 23:59:59"] ===== orden-tour / list ===== SELECT STRAIGHT_JOIN `r`.`Id_Room`, `r`.`Room_Date`, `r`.`Room_RequestDateStart`, `r`.`Room_RequestDateEnd`, `r`.`Room_RequestLastDateStart`, `r`.`Room_RequestLastDateEnd`, `r`.`Room_ReservationDateAlarm`, `r`.`Room_ReservationDateFinal`, `r`.`Room_Observation`, `r`.`Room_BuyPrice`, `r`.`Room_Paid`, `r`.`Room_BuyDateStart`, `r`.`Room_BuyDateEnd`, `r`.`Room_BuyDatePayment`, `r`.`Room_BuyCode`, `r`.`Room_Lock`, `r`.`Room_SoftLock`, `r`.`Room_PassengersAmount`, `r`.`Room_NoValidAmount`, `r`.`Room_DateChanged`, `r`.`Room_Status`, `r`.`Id_Booking`, `r`.`Id_TypeRoom`, `r`.`Id_BookingExtra`, `r`.`Id_Provider`, `r`.`Id_Provider_Buy`, `r`.`Id_Admin`, `r`.`Id_Admin_Validated`, `r`.`Id_PaxInformationRoom`, `r`.`Id_TypePayment`, `tr`.`Id_TypeRoom`, `tr`.`TypeRoom_Name`, `tr`.`TypeRoom_Abrv`, `tr`.`TypeRoom_Capacity`, `tr`.`TypeRoom_Status`, `be`.`Id_BookingExtra`, `be`.`BookingExtra_Quantity`, `be`.`BookingExtra_Price`, `be`.`BookingExtra_Total`, `be`.`BookingExtra_Feature`, `be`.`BookingExtra_Included`, `be`.`BookingExtra_Mode`, `e`.`Id_Extra`, `e`.`Extra_Name`, `bt`.`Id_BookingTour`, `bt`.`BookingTour_DateStart`, `bt`.`BookingTour_PickUp`, `bt`.`BookingTour_Type`, `bt`.`BookingTour_NoPax`, `bt`.`BookingTour_Name`, `bt`.`BookingTour_TourName`, `bt`.`BookingTour_FeeType`, `bt`.`BookingTour_Full`, `bt`.`BookingTour_MinimumFull`, `bt`.`BookingTour_Group`, `bt`.`BookingTour_TourDuration`, `bt`.`BookingTour_DateBriefing`, `bt`.`BookingTour_PlaceBriefing`, `bt`.`BookingTour_UseBookingPlaceBriefing`, `bt`.`BookingTour_Status`, `bt`.`Id_Booking`, `bt`.`BookingTour_Date`, `bt`.`Id_Package`, `bt`.`Id_Tour`, `bt`.`Id_LodgingPlace_PickUp`, `bt`.`Id_LodgingPlace_DropOff`, `t`.`Id_Tour`, `t`.`Tour_Name`, `t`.`Tour_Code`, `t`.`Tour_Color`, `pk`.`Id_Package`, `pk`.`Package_Name`, `pk`.`Package_Code`, `r`.`Id_Provider`, `pr`.`Provider_BusinessName`, `pr`.`Provider_NickName`, `pr`.`Provider_Phone`, `pr`.`Provider_ImgProfile_Thumb`, `b`.`Id_Booking`, `b`.`Booking_Date`, `b`.`Booking_DateConfirm`, `b`.`Booking_DateStart`, `b`.`Booking_DateBriefing`, `b`.`Booking_PlaceBriefing`, `b`.`Booking_Code`, `b`.`Booking_HistoryChanges`, `b`.`Booking_Hotel`, `b`.`Booking_Observation`, `b`.`Booking_InternalRemark`, `b`.`Booking_Receipt`, `b`.`Booking_Type`, `b`.`Booking_LastMinute`, `b`.`Booking_Assigned`, `b`.`Booking_Status`, `b`.`Booking_ManuallyConfirmed`, `b`.`Booking_InPerson`, `b`.`Booking_PassengerAmount`, `b`.`Booking_CheckedInPassengers`, `b`.`Booking_LastPassenger`, `b`.`Booking_Token`, `b`.`Id_Booking_Replace`, `b`.`Id_Admin`, `b`.`Id_GuideLanguage`, `b`.`Id_Language`, `b`.`Id_User`, `b`.`Id_User_Additional`, `b`.`Id_BookingRelationship`, `b`.`Id_BookingReferredFrom`, CONCAT('[', IFNULL(GROUP_CONCAT(DISTINCT IF(`rp`.`Id_RoomPassenger` IS NULL, NULL, JSON_OBJECT( 'Id_RoomPassenger', `rp`.`Id_RoomPassenger`, 'RoomPassenger_IsNotValid', `rp`.`RoomPassenger_IsNotValid`, 'RoomPassenger_Status', `rp`.`RoomPassenger_Status`, 'Id_BookingExtraPassenger', `bep`.`Id_BookingExtraPassenger`, 'BookingExtraPassenger_DateEntryStart', `bep`.`BookingExtraPassenger_DateEntryStart`, 'BookingExtraPassenger_DateEntryEnd', `bep`.`BookingExtraPassenger_DateEntryEnd`, 'BookingExtraPassenger_DatePurchase', `bep`.`BookingExtraPassenger_DatePurchase`, 'BookingExtraPassenger_Remark', `bep`.`BookingExtraPassenger_Remark`, 'BookingExtraPassenger_Status', `bep`.`BookingExtraPassenger_Status`, 'Id_BookingTourPassenger', `btp`.`Id_BookingTourPassenger`, 'BookingTourPassenger_Remark', `btp`.`BookingTourPassenger_Remark`, 'BookingTourPassenger_Price', `btp`.`BookingTourPassenger_Price`, 'BookingTourPassenger_Full', `btp`.`BookingTourPassenger_Full`, 'BookingTourPassenger_ValidGroups', `btp`.`BookingTourPassenger_ValidGroups`, 'BookingTourPassenger_NoValidGroups', `btp`.`BookingTourPassenger_NoValidGroups`, 'BookingTourPassenger_Status', `btp`.`BookingTourPassenger_Status`, 'BookingTourPassenger_PaymentStatus', `btp`.`BookingTourPassenger_PaymentStatus`, 'Id_Passenger', `p`.`Id_Passenger`, 'Passenger_Name', `p`.`Passenger_Name`, 'Passenger_LastName', `p`.`Passenger_LastName`, 'Passenger_Gender', `p`.`Passenger_Gender`, 'Passenger_DOB', `p`.`Passenger_DOB`, 'Passenger_NoDocument', `p`.`Passenger_NoDocument`, 'Passenger_Status', `p`.`Passenger_Status`, 'Passenger_PaymentStatus', `p`.`Passenger_PaymentStatus`, 'Passenger_Age', YEAR(CURDATE())-YEAR(`p`.`Passenger_DOB`) + IF(DATE_FORMAT(CURDATE(),'%m-%d') > DATE_FORMAT(`p`.`Passenger_DOB`,'%m-%d'), 0 , -1 ), 'Id_UserCountry', `uc`.`Id_UserCountry`, 'UserCountry_Name', `uc`.`UserCountry_Name`, 'UserCountry_Code', `uc`.`UserCountry_Code`, 'UserCountry_Img', `uc`.`UserCountry_Img`, 'Id_TypeDocument', `td`.`Id_TypeDocument`, 'TypeDocument_Name', `td`.`TypeDocument_Name`, 'TypeDocument_Abrv', `td`.`TypeDocument_Abrv`, 'Invoice_Total', `i`.`Invoice_Total`, 'Invoice_TotalPay', `i`.`Invoice_TotalPay`, 'Invoice_TotalPending', `i`.`Invoice_TotalPending` )) SEPARATOR ','), ''), ']' ) AS `PassengerList`, (COUNT(`rp`.`Id_RoomPassenger`)) AS `PassengerCount` FROM `t_room` `r` INNER JOIN `t_type_room` `tr` ON `tr`.`Id_TypeRoom` = `r`.`Id_TypeRoom` INNER JOIN `t_booking_extra` `be` ON `be`.`Id_BookingExtra` = `r`.`Id_BookingExtra` INNER JOIN `t_extra` `e` ON `e`.`Id_Extra` = `be`.`Id_Extra` INNER JOIN `t_booking_tour` `bt` ON `bt`.`Id_BookingTour` = `be`.`Id_BookingTour` INNER JOIN `t_booking` `b` ON `b`.`Id_Booking` = `bt`.`Id_Booking` INNER JOIN `t_tour` `t` ON `t`.`Id_Tour` = `bt`.`Id_Tour` INNER JOIN `t_package` `pk` ON `pk`.`Id_Package` = `bt`.`Id_Package` LEFT JOIN `t_provider` `pr` ON `pr`.`Id_Provider` = `r`.`Id_Provider` LEFT JOIN `t_room_passenger` `rp` ON `rp`.`Id_Room` = `r`.`Id_Room` AND `rp`.`RoomPassenger_Status` <> 0 LEFT JOIN `t_booking_extra_passenger` `bep` ON `bep`.`Id_BookingExtraPassenger` = `rp`.`Id_BookingExtraPassenger` LEFT JOIN `t_booking_tour_passenger` `btp` ON `btp`.`Id_BookingTourPassenger` = `bep`.`Id_BookingTourPassenger` LEFT JOIN `t_passenger` `p` ON `p`.`Id_Passenger` = `btp`.`Id_Passenger` LEFT JOIN `t_invoice` `i` ON `i`.`Id_Passenger` = `p`.`Id_Passenger` LEFT JOIN `t_user_country` `uc` ON `uc`.`Id_UserCountry` = `p`.`Id_UserCountry` LEFT JOIN `t_type_document` `td` ON `td`.`Id_TypeDocument` = `p`.`Id_TypeDocument` WHERE `e`.`Id_TypeExtra` = 4 AND `r`.`Room_Status` <> 0 AND ( `rp`.`Id_RoomPassenger` IS NULL OR `bep`.`BookingExtraPassenger_Status` = 2 OR ( `bep`.`BookingExtraPassenger_Status` = 1 AND `btp`.`BookingTourPassenger_Status` IN (1, 2, 3) AND `b`.`Booking_Status` IN (1, 2) AND ( `b`.`Booking_ManuallyConfirmed` = 2 OR `btp`.`BookingTourPassenger_PaymentStatus` = 2 ) ) ) AND ((1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1 = ?) AND (1 = ?) AND (1 = ?) AND (`bt`.`BookingTour_DateStart` BETWEEN ? AND ?)) GROUP BY `r`.`Id_Room` ORDER BY `bt`.`BookingTour_DateStart` desc, `t`.`Id_Tour` ASC, `b`.`Id_Booking` ASC, `r`.`Id_Room` ASC LIMIT 50 OFFSET 50 -- binds: ["1","1","1","2024-01-01 00:00:00","2030-12-31 23:59:59"] ===== orden-tour / count ===== SELECT STRAIGHT_JOIN COUNT(DISTINCT `r`.`Id_Room`) as `total` FROM `t_room` `r` INNER JOIN `t_type_room` `tr` ON `tr`.`Id_TypeRoom` = `r`.`Id_TypeRoom` INNER JOIN `t_booking_extra` `be` ON `be`.`Id_BookingExtra` = `r`.`Id_BookingExtra` INNER JOIN `t_extra` `e` ON `e`.`Id_Extra` = `be`.`Id_Extra` INNER JOIN `t_booking_tour` `bt` ON `bt`.`Id_BookingTour` = `be`.`Id_BookingTour` INNER JOIN `t_booking` `b` ON `b`.`Id_Booking` = `bt`.`Id_Booking` INNER JOIN `t_tour` `t` ON `t`.`Id_Tour` = `bt`.`Id_Tour` INNER JOIN `t_package` `pk` ON `pk`.`Id_Package` = `bt`.`Id_Package` LEFT JOIN `t_provider` `pr` ON `pr`.`Id_Provider` = `r`.`Id_Provider` LEFT JOIN `t_room_passenger` `rp` ON `rp`.`Id_Room` = `r`.`Id_Room` AND `rp`.`RoomPassenger_Status` <> 0 LEFT JOIN `t_booking_extra_passenger` `bep` ON `bep`.`Id_BookingExtraPassenger` = `rp`.`Id_BookingExtraPassenger` LEFT JOIN `t_booking_tour_passenger` `btp` ON `btp`.`Id_BookingTourPassenger` = `bep`.`Id_BookingTourPassenger` WHERE `e`.`Id_TypeExtra` = 4 AND `r`.`Room_Status` <> 0 AND ( `rp`.`Id_RoomPassenger` IS NULL OR `bep`.`BookingExtraPassenger_Status` = 2 OR ( `bep`.`BookingExtraPassenger_Status` = 1 AND `btp`.`BookingTourPassenger_Status` IN (1, 2, 3) AND `b`.`Booking_Status` IN (1, 2) AND ( `b`.`Booking_ManuallyConfirmed` = 2 OR `btp`.`BookingTourPassenger_PaymentStatus` = 2 ) ) ) AND ((1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1 = ?) AND (1 = ?) AND (1 = ?) AND (`bt`.`BookingTour_DateStart` BETWEEN ? AND ?)) -- binds: ["1","1","1","2024-01-01 00:00:00","2030-12-31 23:59:59"] ===== orden-final-asc / list ===== SELECT STRAIGHT_JOIN `r`.`Id_Room`, `r`.`Room_Date`, `r`.`Room_RequestDateStart`, `r`.`Room_RequestDateEnd`, `r`.`Room_RequestLastDateStart`, `r`.`Room_RequestLastDateEnd`, `r`.`Room_ReservationDateAlarm`, `r`.`Room_ReservationDateFinal`, `r`.`Room_Observation`, `r`.`Room_BuyPrice`, `r`.`Room_Paid`, `r`.`Room_BuyDateStart`, `r`.`Room_BuyDateEnd`, `r`.`Room_BuyDatePayment`, `r`.`Room_BuyCode`, `r`.`Room_Lock`, `r`.`Room_SoftLock`, `r`.`Room_PassengersAmount`, `r`.`Room_NoValidAmount`, `r`.`Room_DateChanged`, `r`.`Room_Status`, `r`.`Id_Booking`, `r`.`Id_TypeRoom`, `r`.`Id_BookingExtra`, `r`.`Id_Provider`, `r`.`Id_Provider_Buy`, `r`.`Id_Admin`, `r`.`Id_Admin_Validated`, `r`.`Id_PaxInformationRoom`, `r`.`Id_TypePayment`, `tr`.`Id_TypeRoom`, `tr`.`TypeRoom_Name`, `tr`.`TypeRoom_Abrv`, `tr`.`TypeRoom_Capacity`, `tr`.`TypeRoom_Status`, `be`.`Id_BookingExtra`, `be`.`BookingExtra_Quantity`, `be`.`BookingExtra_Price`, `be`.`BookingExtra_Total`, `be`.`BookingExtra_Feature`, `be`.`BookingExtra_Included`, `be`.`BookingExtra_Mode`, `e`.`Id_Extra`, `e`.`Extra_Name`, `bt`.`Id_BookingTour`, `bt`.`BookingTour_DateStart`, `bt`.`BookingTour_PickUp`, `bt`.`BookingTour_Type`, `bt`.`BookingTour_NoPax`, `bt`.`BookingTour_Name`, `bt`.`BookingTour_TourName`, `bt`.`BookingTour_FeeType`, `bt`.`BookingTour_Full`, `bt`.`BookingTour_MinimumFull`, `bt`.`BookingTour_Group`, `bt`.`BookingTour_TourDuration`, `bt`.`BookingTour_DateBriefing`, `bt`.`BookingTour_PlaceBriefing`, `bt`.`BookingTour_UseBookingPlaceBriefing`, `bt`.`BookingTour_Status`, `bt`.`Id_Booking`, `bt`.`BookingTour_Date`, `bt`.`Id_Package`, `bt`.`Id_Tour`, `bt`.`Id_LodgingPlace_PickUp`, `bt`.`Id_LodgingPlace_DropOff`, `t`.`Id_Tour`, `t`.`Tour_Name`, `t`.`Tour_Code`, `t`.`Tour_Color`, `pk`.`Id_Package`, `pk`.`Package_Name`, `pk`.`Package_Code`, `r`.`Id_Provider`, `pr`.`Provider_BusinessName`, `pr`.`Provider_NickName`, `pr`.`Provider_Phone`, `pr`.`Provider_ImgProfile_Thumb`, `b`.`Id_Booking`, `b`.`Booking_Date`, `b`.`Booking_DateConfirm`, `b`.`Booking_DateStart`, `b`.`Booking_DateBriefing`, `b`.`Booking_PlaceBriefing`, `b`.`Booking_Code`, `b`.`Booking_HistoryChanges`, `b`.`Booking_Hotel`, `b`.`Booking_Observation`, `b`.`Booking_InternalRemark`, `b`.`Booking_Receipt`, `b`.`Booking_Type`, `b`.`Booking_LastMinute`, `b`.`Booking_Assigned`, `b`.`Booking_Status`, `b`.`Booking_ManuallyConfirmed`, `b`.`Booking_InPerson`, `b`.`Booking_PassengerAmount`, `b`.`Booking_CheckedInPassengers`, `b`.`Booking_LastPassenger`, `b`.`Booking_Token`, `b`.`Id_Booking_Replace`, `b`.`Id_Admin`, `b`.`Id_GuideLanguage`, `b`.`Id_Language`, `b`.`Id_User`, `b`.`Id_User_Additional`, `b`.`Id_BookingRelationship`, `b`.`Id_BookingReferredFrom`, CONCAT('[', IFNULL(GROUP_CONCAT(DISTINCT IF(`rp`.`Id_RoomPassenger` IS NULL, NULL, JSON_OBJECT( 'Id_RoomPassenger', `rp`.`Id_RoomPassenger`, 'RoomPassenger_IsNotValid', `rp`.`RoomPassenger_IsNotValid`, 'RoomPassenger_Status', `rp`.`RoomPassenger_Status`, 'Id_BookingExtraPassenger', `bep`.`Id_BookingExtraPassenger`, 'BookingExtraPassenger_DateEntryStart', `bep`.`BookingExtraPassenger_DateEntryStart`, 'BookingExtraPassenger_DateEntryEnd', `bep`.`BookingExtraPassenger_DateEntryEnd`, 'BookingExtraPassenger_DatePurchase', `bep`.`BookingExtraPassenger_DatePurchase`, 'BookingExtraPassenger_Remark', `bep`.`BookingExtraPassenger_Remark`, 'BookingExtraPassenger_Status', `bep`.`BookingExtraPassenger_Status`, 'Id_BookingTourPassenger', `btp`.`Id_BookingTourPassenger`, 'BookingTourPassenger_Remark', `btp`.`BookingTourPassenger_Remark`, 'BookingTourPassenger_Price', `btp`.`BookingTourPassenger_Price`, 'BookingTourPassenger_Full', `btp`.`BookingTourPassenger_Full`, 'BookingTourPassenger_ValidGroups', `btp`.`BookingTourPassenger_ValidGroups`, 'BookingTourPassenger_NoValidGroups', `btp`.`BookingTourPassenger_NoValidGroups`, 'BookingTourPassenger_Status', `btp`.`BookingTourPassenger_Status`, 'BookingTourPassenger_PaymentStatus', `btp`.`BookingTourPassenger_PaymentStatus`, 'Id_Passenger', `p`.`Id_Passenger`, 'Passenger_Name', `p`.`Passenger_Name`, 'Passenger_LastName', `p`.`Passenger_LastName`, 'Passenger_Gender', `p`.`Passenger_Gender`, 'Passenger_DOB', `p`.`Passenger_DOB`, 'Passenger_NoDocument', `p`.`Passenger_NoDocument`, 'Passenger_Status', `p`.`Passenger_Status`, 'Passenger_PaymentStatus', `p`.`Passenger_PaymentStatus`, 'Passenger_Age', YEAR(CURDATE())-YEAR(`p`.`Passenger_DOB`) + IF(DATE_FORMAT(CURDATE(),'%m-%d') > DATE_FORMAT(`p`.`Passenger_DOB`,'%m-%d'), 0 , -1 ), 'Id_UserCountry', `uc`.`Id_UserCountry`, 'UserCountry_Name', `uc`.`UserCountry_Name`, 'UserCountry_Code', `uc`.`UserCountry_Code`, 'UserCountry_Img', `uc`.`UserCountry_Img`, 'Id_TypeDocument', `td`.`Id_TypeDocument`, 'TypeDocument_Name', `td`.`TypeDocument_Name`, 'TypeDocument_Abrv', `td`.`TypeDocument_Abrv`, 'Invoice_Total', `i`.`Invoice_Total`, 'Invoice_TotalPay', `i`.`Invoice_TotalPay`, 'Invoice_TotalPending', `i`.`Invoice_TotalPending` )) SEPARATOR ','), ''), ']' ) AS `PassengerList`, (COUNT(`rp`.`Id_RoomPassenger`)) AS `PassengerCount` FROM `t_room` `r` INNER JOIN `t_type_room` `tr` ON `tr`.`Id_TypeRoom` = `r`.`Id_TypeRoom` INNER JOIN `t_booking_extra` `be` ON `be`.`Id_BookingExtra` = `r`.`Id_BookingExtra` INNER JOIN `t_extra` `e` ON `e`.`Id_Extra` = `be`.`Id_Extra` INNER JOIN `t_booking_tour` `bt` ON `bt`.`Id_BookingTour` = `be`.`Id_BookingTour` INNER JOIN `t_booking` `b` ON `b`.`Id_Booking` = `bt`.`Id_Booking` INNER JOIN `t_tour` `t` ON `t`.`Id_Tour` = `bt`.`Id_Tour` INNER JOIN `t_package` `pk` ON `pk`.`Id_Package` = `bt`.`Id_Package` LEFT JOIN `t_provider` `pr` ON `pr`.`Id_Provider` = `r`.`Id_Provider` LEFT JOIN `t_room_passenger` `rp` ON `rp`.`Id_Room` = `r`.`Id_Room` AND `rp`.`RoomPassenger_Status` <> 0 LEFT JOIN `t_booking_extra_passenger` `bep` ON `bep`.`Id_BookingExtraPassenger` = `rp`.`Id_BookingExtraPassenger` LEFT JOIN `t_booking_tour_passenger` `btp` ON `btp`.`Id_BookingTourPassenger` = `bep`.`Id_BookingTourPassenger` LEFT JOIN `t_passenger` `p` ON `p`.`Id_Passenger` = `btp`.`Id_Passenger` LEFT JOIN `t_invoice` `i` ON `i`.`Id_Passenger` = `p`.`Id_Passenger` LEFT JOIN `t_user_country` `uc` ON `uc`.`Id_UserCountry` = `p`.`Id_UserCountry` LEFT JOIN `t_type_document` `td` ON `td`.`Id_TypeDocument` = `p`.`Id_TypeDocument` WHERE `e`.`Id_TypeExtra` = 4 AND `r`.`Room_Status` <> 0 AND ( `rp`.`Id_RoomPassenger` IS NULL OR `bep`.`BookingExtraPassenger_Status` = 2 OR ( `bep`.`BookingExtraPassenger_Status` = 1 AND `btp`.`BookingTourPassenger_Status` IN (1, 2, 3) AND `b`.`Booking_Status` IN (1, 2) AND ( `b`.`Booking_ManuallyConfirmed` = 2 OR `btp`.`BookingTourPassenger_PaymentStatus` = 2 ) ) ) AND ((1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1 = ?) AND (1 = ?) AND (1 = ?) AND (`r`.`Room_ReservationDateFinal` BETWEEN ? AND ?)) GROUP BY `r`.`Id_Room` ORDER BY `r`.`Room_ReservationDateFinal` asc, `t`.`Id_Tour` ASC, `b`.`Id_Booking` ASC, `r`.`Id_Room` ASC LIMIT 50 OFFSET 0 -- binds: ["1","1","1","2024-01-01 00:00:00","2030-12-31 23:59:59"] ===== orden-final-asc / count ===== SELECT STRAIGHT_JOIN COUNT(DISTINCT `r`.`Id_Room`) as `total` FROM `t_room` `r` INNER JOIN `t_type_room` `tr` ON `tr`.`Id_TypeRoom` = `r`.`Id_TypeRoom` INNER JOIN `t_booking_extra` `be` ON `be`.`Id_BookingExtra` = `r`.`Id_BookingExtra` INNER JOIN `t_extra` `e` ON `e`.`Id_Extra` = `be`.`Id_Extra` INNER JOIN `t_booking_tour` `bt` ON `bt`.`Id_BookingTour` = `be`.`Id_BookingTour` INNER JOIN `t_booking` `b` ON `b`.`Id_Booking` = `bt`.`Id_Booking` INNER JOIN `t_tour` `t` ON `t`.`Id_Tour` = `bt`.`Id_Tour` INNER JOIN `t_package` `pk` ON `pk`.`Id_Package` = `bt`.`Id_Package` LEFT JOIN `t_provider` `pr` ON `pr`.`Id_Provider` = `r`.`Id_Provider` LEFT JOIN `t_room_passenger` `rp` ON `rp`.`Id_Room` = `r`.`Id_Room` AND `rp`.`RoomPassenger_Status` <> 0 LEFT JOIN `t_booking_extra_passenger` `bep` ON `bep`.`Id_BookingExtraPassenger` = `rp`.`Id_BookingExtraPassenger` LEFT JOIN `t_booking_tour_passenger` `btp` ON `btp`.`Id_BookingTourPassenger` = `bep`.`Id_BookingTourPassenger` WHERE `e`.`Id_TypeExtra` = 4 AND `r`.`Room_Status` <> 0 AND ( `rp`.`Id_RoomPassenger` IS NULL OR `bep`.`BookingExtraPassenger_Status` = 2 OR ( `bep`.`BookingExtraPassenger_Status` = 1 AND `btp`.`BookingTourPassenger_Status` IN (1, 2, 3) AND `b`.`Booking_Status` IN (1, 2) AND ( `b`.`Booking_ManuallyConfirmed` = 2 OR `btp`.`BookingTourPassenger_PaymentStatus` = 2 ) ) ) AND ((1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1 = ?) AND (1 = ?) AND (1 = ?) AND (`r`.`Room_ReservationDateFinal` BETWEEN ? AND ?)) -- binds: ["1","1","1","2024-01-01 00:00:00","2030-12-31 23:59:59"] ===== search-apellido / list ===== SELECT STRAIGHT_JOIN `r`.`Id_Room`, `r`.`Room_Date`, `r`.`Room_RequestDateStart`, `r`.`Room_RequestDateEnd`, `r`.`Room_RequestLastDateStart`, `r`.`Room_RequestLastDateEnd`, `r`.`Room_ReservationDateAlarm`, `r`.`Room_ReservationDateFinal`, `r`.`Room_Observation`, `r`.`Room_BuyPrice`, `r`.`Room_Paid`, `r`.`Room_BuyDateStart`, `r`.`Room_BuyDateEnd`, `r`.`Room_BuyDatePayment`, `r`.`Room_BuyCode`, `r`.`Room_Lock`, `r`.`Room_SoftLock`, `r`.`Room_PassengersAmount`, `r`.`Room_NoValidAmount`, `r`.`Room_DateChanged`, `r`.`Room_Status`, `r`.`Id_Booking`, `r`.`Id_TypeRoom`, `r`.`Id_BookingExtra`, `r`.`Id_Provider`, `r`.`Id_Provider_Buy`, `r`.`Id_Admin`, `r`.`Id_Admin_Validated`, `r`.`Id_PaxInformationRoom`, `r`.`Id_TypePayment`, `tr`.`Id_TypeRoom`, `tr`.`TypeRoom_Name`, `tr`.`TypeRoom_Abrv`, `tr`.`TypeRoom_Capacity`, `tr`.`TypeRoom_Status`, `be`.`Id_BookingExtra`, `be`.`BookingExtra_Quantity`, `be`.`BookingExtra_Price`, `be`.`BookingExtra_Total`, `be`.`BookingExtra_Feature`, `be`.`BookingExtra_Included`, `be`.`BookingExtra_Mode`, `e`.`Id_Extra`, `e`.`Extra_Name`, `bt`.`Id_BookingTour`, `bt`.`BookingTour_DateStart`, `bt`.`BookingTour_PickUp`, `bt`.`BookingTour_Type`, `bt`.`BookingTour_NoPax`, `bt`.`BookingTour_Name`, `bt`.`BookingTour_TourName`, `bt`.`BookingTour_FeeType`, `bt`.`BookingTour_Full`, `bt`.`BookingTour_MinimumFull`, `bt`.`BookingTour_Group`, `bt`.`BookingTour_TourDuration`, `bt`.`BookingTour_DateBriefing`, `bt`.`BookingTour_PlaceBriefing`, `bt`.`BookingTour_UseBookingPlaceBriefing`, `bt`.`BookingTour_Status`, `bt`.`Id_Booking`, `bt`.`BookingTour_Date`, `bt`.`Id_Package`, `bt`.`Id_Tour`, `bt`.`Id_LodgingPlace_PickUp`, `bt`.`Id_LodgingPlace_DropOff`, `t`.`Id_Tour`, `t`.`Tour_Name`, `t`.`Tour_Code`, `t`.`Tour_Color`, `pk`.`Id_Package`, `pk`.`Package_Name`, `pk`.`Package_Code`, `r`.`Id_Provider`, `pr`.`Provider_BusinessName`, `pr`.`Provider_NickName`, `pr`.`Provider_Phone`, `pr`.`Provider_ImgProfile_Thumb`, `b`.`Id_Booking`, `b`.`Booking_Date`, `b`.`Booking_DateConfirm`, `b`.`Booking_DateStart`, `b`.`Booking_DateBriefing`, `b`.`Booking_PlaceBriefing`, `b`.`Booking_Code`, `b`.`Booking_HistoryChanges`, `b`.`Booking_Hotel`, `b`.`Booking_Observation`, `b`.`Booking_InternalRemark`, `b`.`Booking_Receipt`, `b`.`Booking_Type`, `b`.`Booking_LastMinute`, `b`.`Booking_Assigned`, `b`.`Booking_Status`, `b`.`Booking_ManuallyConfirmed`, `b`.`Booking_InPerson`, `b`.`Booking_PassengerAmount`, `b`.`Booking_CheckedInPassengers`, `b`.`Booking_LastPassenger`, `b`.`Booking_Token`, `b`.`Id_Booking_Replace`, `b`.`Id_Admin`, `b`.`Id_GuideLanguage`, `b`.`Id_Language`, `b`.`Id_User`, `b`.`Id_User_Additional`, `b`.`Id_BookingRelationship`, `b`.`Id_BookingReferredFrom`, CONCAT('[', IFNULL(GROUP_CONCAT(DISTINCT IF(`rp`.`Id_RoomPassenger` IS NULL, NULL, JSON_OBJECT( 'Id_RoomPassenger', `rp`.`Id_RoomPassenger`, 'RoomPassenger_IsNotValid', `rp`.`RoomPassenger_IsNotValid`, 'RoomPassenger_Status', `rp`.`RoomPassenger_Status`, 'Id_BookingExtraPassenger', `bep`.`Id_BookingExtraPassenger`, 'BookingExtraPassenger_DateEntryStart', `bep`.`BookingExtraPassenger_DateEntryStart`, 'BookingExtraPassenger_DateEntryEnd', `bep`.`BookingExtraPassenger_DateEntryEnd`, 'BookingExtraPassenger_DatePurchase', `bep`.`BookingExtraPassenger_DatePurchase`, 'BookingExtraPassenger_Remark', `bep`.`BookingExtraPassenger_Remark`, 'BookingExtraPassenger_Status', `bep`.`BookingExtraPassenger_Status`, 'Id_BookingTourPassenger', `btp`.`Id_BookingTourPassenger`, 'BookingTourPassenger_Remark', `btp`.`BookingTourPassenger_Remark`, 'BookingTourPassenger_Price', `btp`.`BookingTourPassenger_Price`, 'BookingTourPassenger_Full', `btp`.`BookingTourPassenger_Full`, 'BookingTourPassenger_ValidGroups', `btp`.`BookingTourPassenger_ValidGroups`, 'BookingTourPassenger_NoValidGroups', `btp`.`BookingTourPassenger_NoValidGroups`, 'BookingTourPassenger_Status', `btp`.`BookingTourPassenger_Status`, 'BookingTourPassenger_PaymentStatus', `btp`.`BookingTourPassenger_PaymentStatus`, 'Id_Passenger', `p`.`Id_Passenger`, 'Passenger_Name', `p`.`Passenger_Name`, 'Passenger_LastName', `p`.`Passenger_LastName`, 'Passenger_Gender', `p`.`Passenger_Gender`, 'Passenger_DOB', `p`.`Passenger_DOB`, 'Passenger_NoDocument', `p`.`Passenger_NoDocument`, 'Passenger_Status', `p`.`Passenger_Status`, 'Passenger_PaymentStatus', `p`.`Passenger_PaymentStatus`, 'Passenger_Age', YEAR(CURDATE())-YEAR(`p`.`Passenger_DOB`) + IF(DATE_FORMAT(CURDATE(),'%m-%d') > DATE_FORMAT(`p`.`Passenger_DOB`,'%m-%d'), 0 , -1 ), 'Id_UserCountry', `uc`.`Id_UserCountry`, 'UserCountry_Name', `uc`.`UserCountry_Name`, 'UserCountry_Code', `uc`.`UserCountry_Code`, 'UserCountry_Img', `uc`.`UserCountry_Img`, 'Id_TypeDocument', `td`.`Id_TypeDocument`, 'TypeDocument_Name', `td`.`TypeDocument_Name`, 'TypeDocument_Abrv', `td`.`TypeDocument_Abrv`, 'Invoice_Total', `i`.`Invoice_Total`, 'Invoice_TotalPay', `i`.`Invoice_TotalPay`, 'Invoice_TotalPending', `i`.`Invoice_TotalPending` )) SEPARATOR ','), ''), ']' ) AS `PassengerList`, (COUNT(`rp`.`Id_RoomPassenger`)) AS `PassengerCount` FROM `t_room` `r` FORCE INDEX FOR ORDER BY (`INDEX_Room_RequestDateStart`) INNER JOIN `t_type_room` `tr` ON `tr`.`Id_TypeRoom` = `r`.`Id_TypeRoom` INNER JOIN `t_booking_extra` `be` ON `be`.`Id_BookingExtra` = `r`.`Id_BookingExtra` INNER JOIN `t_extra` `e` ON `e`.`Id_Extra` = `be`.`Id_Extra` INNER JOIN `t_booking_tour` `bt` ON `bt`.`Id_BookingTour` = `be`.`Id_BookingTour` INNER JOIN `t_booking` `b` ON `b`.`Id_Booking` = `bt`.`Id_Booking` INNER JOIN `t_tour` `t` ON `t`.`Id_Tour` = `bt`.`Id_Tour` INNER JOIN `t_package` `pk` ON `pk`.`Id_Package` = `bt`.`Id_Package` LEFT JOIN `t_provider` `pr` ON `pr`.`Id_Provider` = `r`.`Id_Provider` LEFT JOIN `t_room_passenger` `rp` ON `rp`.`Id_Room` = `r`.`Id_Room` AND `rp`.`RoomPassenger_Status` <> 0 LEFT JOIN `t_booking_extra_passenger` `bep` ON `bep`.`Id_BookingExtraPassenger` = `rp`.`Id_BookingExtraPassenger` LEFT JOIN `t_booking_tour_passenger` `btp` ON `btp`.`Id_BookingTourPassenger` = `bep`.`Id_BookingTourPassenger` LEFT JOIN `t_passenger` `p` ON `p`.`Id_Passenger` = `btp`.`Id_Passenger` LEFT JOIN `t_invoice` `i` ON `i`.`Id_Passenger` = `p`.`Id_Passenger` LEFT JOIN `t_user_country` `uc` ON `uc`.`Id_UserCountry` = `p`.`Id_UserCountry` LEFT JOIN `t_type_document` `td` ON `td`.`Id_TypeDocument` = `p`.`Id_TypeDocument` WHERE `e`.`Id_TypeExtra` = 4 AND `r`.`Room_Status` <> 0 AND ( `rp`.`Id_RoomPassenger` IS NULL OR `bep`.`BookingExtraPassenger_Status` = 2 OR ( `bep`.`BookingExtraPassenger_Status` = 1 AND `btp`.`BookingTourPassenger_Status` IN (1, 2, 3) AND `b`.`Booking_Status` IN (1, 2) AND ( `b`.`Booking_ManuallyConfirmed` = 2 OR `btp`.`BookingTourPassenger_PaymentStatus` = 2 ) ) ) AND ((1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1 = ?) AND (((`b`.`Booking_Code` LIKE ?) OR (`pr`.`Provider_BusinessName` LIKE ?) OR (`pr`.`Provider_NickName` LIKE ?) OR (`t`.`Tour_Code` LIKE ?) OR (`pk`.`Package_Code` LIKE ?) OR (`b`.`Booking_Observation` LIKE ?) OR (EXISTS ( SELECT 1 FROM `t_room_passenger` `srp` INNER JOIN `t_booking_extra_passenger` `sbep` ON `sbep`.`Id_BookingExtraPassenger` = `srp`.`Id_BookingExtraPassenger` INNER JOIN `t_booking_tour_passenger` `sbtp` ON `sbtp`.`Id_BookingTourPassenger` = `sbep`.`Id_BookingTourPassenger` INNER JOIN `t_passenger` `sp` ON `sp`.`Id_Passenger` = `sbtp`.`Id_Passenger` WHERE `srp`.`Id_Room` = `r`.`Id_Room` AND `srp`.`RoomPassenger_Status` <> 0 AND ( `sp`.`Passenger_Name` LIKE ? OR `sp`.`Passenger_LastName` LIKE ? OR `sp`.`Passenger_NoDocument` LIKE ? ) )) OR (EXISTS ( SELECT 1 FROM `t_room_passenger` `grp` INNER JOIN `t_booking_extra_passenger` `gbep` ON `gbep`.`Id_BookingExtraPassenger` = `grp`.`Id_BookingExtraPassenger` INNER JOIN `t_group_passenger` `ggp` ON `ggp`.`Id` = `gbep`.`Id_BookingTourPassenger` AND `ggp`.`GroupPassenger_Type` = 1 INNER JOIN `t_group` `gg` ON `gg`.`Id_Group` = `ggp`.`Id_Group` INNER JOIN `t_group_provider` `ggpv` ON `ggpv`.`Id_Group` = `gg`.`Id_Group` AND `ggpv`.`Id_TypeProvider` = 1 INNER JOIN `t_provider` `gpv` ON `gpv`.`Id_Provider` = `ggpv`.`Id_Provider` WHERE `grp`.`Id_Room` = `r`.`Id_Room` AND `grp`.`RoomPassenger_Status` <> 0 AND `gpv`.`Provider_NickName` LIKE ? )))) AND (1 = ?)) GROUP BY `r`.`Id_Room` ORDER BY `r`.`Room_RequestDateStart` desc, `t`.`Id_Tour` ASC, `b`.`Id_Booking` ASC, `r`.`Id_Room` ASC LIMIT 50 OFFSET 0 -- binds: ["1","%Asdasd%","%Asdasd%","%Asdasd%","%Asdasd%","%Asdasd%","%Asdasd%","%Asdasd%","%Asdasd%","%Asdasd%","%Asdasd%","1"] ===== search-apellido / count ===== SELECT STRAIGHT_JOIN COUNT(DISTINCT `r`.`Id_Room`) as `total` FROM `t_room` `r` INNER JOIN `t_type_room` `tr` ON `tr`.`Id_TypeRoom` = `r`.`Id_TypeRoom` INNER JOIN `t_booking_extra` `be` ON `be`.`Id_BookingExtra` = `r`.`Id_BookingExtra` INNER JOIN `t_extra` `e` ON `e`.`Id_Extra` = `be`.`Id_Extra` INNER JOIN `t_booking_tour` `bt` ON `bt`.`Id_BookingTour` = `be`.`Id_BookingTour` INNER JOIN `t_booking` `b` ON `b`.`Id_Booking` = `bt`.`Id_Booking` INNER JOIN `t_tour` `t` ON `t`.`Id_Tour` = `bt`.`Id_Tour` INNER JOIN `t_package` `pk` ON `pk`.`Id_Package` = `bt`.`Id_Package` LEFT JOIN `t_provider` `pr` ON `pr`.`Id_Provider` = `r`.`Id_Provider` LEFT JOIN `t_room_passenger` `rp` ON `rp`.`Id_Room` = `r`.`Id_Room` AND `rp`.`RoomPassenger_Status` <> 0 LEFT JOIN `t_booking_extra_passenger` `bep` ON `bep`.`Id_BookingExtraPassenger` = `rp`.`Id_BookingExtraPassenger` LEFT JOIN `t_booking_tour_passenger` `btp` ON `btp`.`Id_BookingTourPassenger` = `bep`.`Id_BookingTourPassenger` WHERE `e`.`Id_TypeExtra` = 4 AND `r`.`Room_Status` <> 0 AND ( `rp`.`Id_RoomPassenger` IS NULL OR `bep`.`BookingExtraPassenger_Status` = 2 OR ( `bep`.`BookingExtraPassenger_Status` = 1 AND `btp`.`BookingTourPassenger_Status` IN (1, 2, 3) AND `b`.`Booking_Status` IN (1, 2) AND ( `b`.`Booking_ManuallyConfirmed` = 2 OR `btp`.`BookingTourPassenger_PaymentStatus` = 2 ) ) ) AND ((1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1 = ?) AND (((`b`.`Booking_Code` LIKE ?) OR (`pr`.`Provider_BusinessName` LIKE ?) OR (`pr`.`Provider_NickName` LIKE ?) OR (`t`.`Tour_Code` LIKE ?) OR (`pk`.`Package_Code` LIKE ?) OR (`b`.`Booking_Observation` LIKE ?) OR (EXISTS ( SELECT 1 FROM `t_room_passenger` `srp` INNER JOIN `t_booking_extra_passenger` `sbep` ON `sbep`.`Id_BookingExtraPassenger` = `srp`.`Id_BookingExtraPassenger` INNER JOIN `t_booking_tour_passenger` `sbtp` ON `sbtp`.`Id_BookingTourPassenger` = `sbep`.`Id_BookingTourPassenger` INNER JOIN `t_passenger` `sp` ON `sp`.`Id_Passenger` = `sbtp`.`Id_Passenger` WHERE `srp`.`Id_Room` = `r`.`Id_Room` AND `srp`.`RoomPassenger_Status` <> 0 AND ( `sp`.`Passenger_Name` LIKE ? OR `sp`.`Passenger_LastName` LIKE ? OR `sp`.`Passenger_NoDocument` LIKE ? ) )) OR (EXISTS ( SELECT 1 FROM `t_room_passenger` `grp` INNER JOIN `t_booking_extra_passenger` `gbep` ON `gbep`.`Id_BookingExtraPassenger` = `grp`.`Id_BookingExtraPassenger` INNER JOIN `t_group_passenger` `ggp` ON `ggp`.`Id` = `gbep`.`Id_BookingTourPassenger` AND `ggp`.`GroupPassenger_Type` = 1 INNER JOIN `t_group` `gg` ON `gg`.`Id_Group` = `ggp`.`Id_Group` INNER JOIN `t_group_provider` `ggpv` ON `ggpv`.`Id_Group` = `gg`.`Id_Group` AND `ggpv`.`Id_TypeProvider` = 1 INNER JOIN `t_provider` `gpv` ON `gpv`.`Id_Provider` = `ggpv`.`Id_Provider` WHERE `grp`.`Id_Room` = `r`.`Id_Room` AND `grp`.`RoomPassenger_Status` <> 0 AND `gpv`.`Provider_NickName` LIKE ? )))) AND (1 = ?)) -- binds: ["1","%Asdasd%","%Asdasd%","%Asdasd%","%Asdasd%","%Asdasd%","%Asdasd%","%Asdasd%","%Asdasd%","%Asdasd%","%Asdasd%","1"] ===== search-generico / list ===== SELECT STRAIGHT_JOIN `r`.`Id_Room`, `r`.`Room_Date`, `r`.`Room_RequestDateStart`, `r`.`Room_RequestDateEnd`, `r`.`Room_RequestLastDateStart`, `r`.`Room_RequestLastDateEnd`, `r`.`Room_ReservationDateAlarm`, `r`.`Room_ReservationDateFinal`, `r`.`Room_Observation`, `r`.`Room_BuyPrice`, `r`.`Room_Paid`, `r`.`Room_BuyDateStart`, `r`.`Room_BuyDateEnd`, `r`.`Room_BuyDatePayment`, `r`.`Room_BuyCode`, `r`.`Room_Lock`, `r`.`Room_SoftLock`, `r`.`Room_PassengersAmount`, `r`.`Room_NoValidAmount`, `r`.`Room_DateChanged`, `r`.`Room_Status`, `r`.`Id_Booking`, `r`.`Id_TypeRoom`, `r`.`Id_BookingExtra`, `r`.`Id_Provider`, `r`.`Id_Provider_Buy`, `r`.`Id_Admin`, `r`.`Id_Admin_Validated`, `r`.`Id_PaxInformationRoom`, `r`.`Id_TypePayment`, `tr`.`Id_TypeRoom`, `tr`.`TypeRoom_Name`, `tr`.`TypeRoom_Abrv`, `tr`.`TypeRoom_Capacity`, `tr`.`TypeRoom_Status`, `be`.`Id_BookingExtra`, `be`.`BookingExtra_Quantity`, `be`.`BookingExtra_Price`, `be`.`BookingExtra_Total`, `be`.`BookingExtra_Feature`, `be`.`BookingExtra_Included`, `be`.`BookingExtra_Mode`, `e`.`Id_Extra`, `e`.`Extra_Name`, `bt`.`Id_BookingTour`, `bt`.`BookingTour_DateStart`, `bt`.`BookingTour_PickUp`, `bt`.`BookingTour_Type`, `bt`.`BookingTour_NoPax`, `bt`.`BookingTour_Name`, `bt`.`BookingTour_TourName`, `bt`.`BookingTour_FeeType`, `bt`.`BookingTour_Full`, `bt`.`BookingTour_MinimumFull`, `bt`.`BookingTour_Group`, `bt`.`BookingTour_TourDuration`, `bt`.`BookingTour_DateBriefing`, `bt`.`BookingTour_PlaceBriefing`, `bt`.`BookingTour_UseBookingPlaceBriefing`, `bt`.`BookingTour_Status`, `bt`.`Id_Booking`, `bt`.`BookingTour_Date`, `bt`.`Id_Package`, `bt`.`Id_Tour`, `bt`.`Id_LodgingPlace_PickUp`, `bt`.`Id_LodgingPlace_DropOff`, `t`.`Id_Tour`, `t`.`Tour_Name`, `t`.`Tour_Code`, `t`.`Tour_Color`, `pk`.`Id_Package`, `pk`.`Package_Name`, `pk`.`Package_Code`, `r`.`Id_Provider`, `pr`.`Provider_BusinessName`, `pr`.`Provider_NickName`, `pr`.`Provider_Phone`, `pr`.`Provider_ImgProfile_Thumb`, `b`.`Id_Booking`, `b`.`Booking_Date`, `b`.`Booking_DateConfirm`, `b`.`Booking_DateStart`, `b`.`Booking_DateBriefing`, `b`.`Booking_PlaceBriefing`, `b`.`Booking_Code`, `b`.`Booking_HistoryChanges`, `b`.`Booking_Hotel`, `b`.`Booking_Observation`, `b`.`Booking_InternalRemark`, `b`.`Booking_Receipt`, `b`.`Booking_Type`, `b`.`Booking_LastMinute`, `b`.`Booking_Assigned`, `b`.`Booking_Status`, `b`.`Booking_ManuallyConfirmed`, `b`.`Booking_InPerson`, `b`.`Booking_PassengerAmount`, `b`.`Booking_CheckedInPassengers`, `b`.`Booking_LastPassenger`, `b`.`Booking_Token`, `b`.`Id_Booking_Replace`, `b`.`Id_Admin`, `b`.`Id_GuideLanguage`, `b`.`Id_Language`, `b`.`Id_User`, `b`.`Id_User_Additional`, `b`.`Id_BookingRelationship`, `b`.`Id_BookingReferredFrom`, CONCAT('[', IFNULL(GROUP_CONCAT(DISTINCT IF(`rp`.`Id_RoomPassenger` IS NULL, NULL, JSON_OBJECT( 'Id_RoomPassenger', `rp`.`Id_RoomPassenger`, 'RoomPassenger_IsNotValid', `rp`.`RoomPassenger_IsNotValid`, 'RoomPassenger_Status', `rp`.`RoomPassenger_Status`, 'Id_BookingExtraPassenger', `bep`.`Id_BookingExtraPassenger`, 'BookingExtraPassenger_DateEntryStart', `bep`.`BookingExtraPassenger_DateEntryStart`, 'BookingExtraPassenger_DateEntryEnd', `bep`.`BookingExtraPassenger_DateEntryEnd`, 'BookingExtraPassenger_DatePurchase', `bep`.`BookingExtraPassenger_DatePurchase`, 'BookingExtraPassenger_Remark', `bep`.`BookingExtraPassenger_Remark`, 'BookingExtraPassenger_Status', `bep`.`BookingExtraPassenger_Status`, 'Id_BookingTourPassenger', `btp`.`Id_BookingTourPassenger`, 'BookingTourPassenger_Remark', `btp`.`BookingTourPassenger_Remark`, 'BookingTourPassenger_Price', `btp`.`BookingTourPassenger_Price`, 'BookingTourPassenger_Full', `btp`.`BookingTourPassenger_Full`, 'BookingTourPassenger_ValidGroups', `btp`.`BookingTourPassenger_ValidGroups`, 'BookingTourPassenger_NoValidGroups', `btp`.`BookingTourPassenger_NoValidGroups`, 'BookingTourPassenger_Status', `btp`.`BookingTourPassenger_Status`, 'BookingTourPassenger_PaymentStatus', `btp`.`BookingTourPassenger_PaymentStatus`, 'Id_Passenger', `p`.`Id_Passenger`, 'Passenger_Name', `p`.`Passenger_Name`, 'Passenger_LastName', `p`.`Passenger_LastName`, 'Passenger_Gender', `p`.`Passenger_Gender`, 'Passenger_DOB', `p`.`Passenger_DOB`, 'Passenger_NoDocument', `p`.`Passenger_NoDocument`, 'Passenger_Status', `p`.`Passenger_Status`, 'Passenger_PaymentStatus', `p`.`Passenger_PaymentStatus`, 'Passenger_Age', YEAR(CURDATE())-YEAR(`p`.`Passenger_DOB`) + IF(DATE_FORMAT(CURDATE(),'%m-%d') > DATE_FORMAT(`p`.`Passenger_DOB`,'%m-%d'), 0 , -1 ), 'Id_UserCountry', `uc`.`Id_UserCountry`, 'UserCountry_Name', `uc`.`UserCountry_Name`, 'UserCountry_Code', `uc`.`UserCountry_Code`, 'UserCountry_Img', `uc`.`UserCountry_Img`, 'Id_TypeDocument', `td`.`Id_TypeDocument`, 'TypeDocument_Name', `td`.`TypeDocument_Name`, 'TypeDocument_Abrv', `td`.`TypeDocument_Abrv`, 'Invoice_Total', `i`.`Invoice_Total`, 'Invoice_TotalPay', `i`.`Invoice_TotalPay`, 'Invoice_TotalPending', `i`.`Invoice_TotalPending` )) SEPARATOR ','), ''), ']' ) AS `PassengerList`, (COUNT(`rp`.`Id_RoomPassenger`)) AS `PassengerCount` FROM `t_room` `r` FORCE INDEX FOR ORDER BY (`INDEX_Room_RequestDateStart`) INNER JOIN `t_type_room` `tr` ON `tr`.`Id_TypeRoom` = `r`.`Id_TypeRoom` INNER JOIN `t_booking_extra` `be` ON `be`.`Id_BookingExtra` = `r`.`Id_BookingExtra` INNER JOIN `t_extra` `e` ON `e`.`Id_Extra` = `be`.`Id_Extra` INNER JOIN `t_booking_tour` `bt` ON `bt`.`Id_BookingTour` = `be`.`Id_BookingTour` INNER JOIN `t_booking` `b` ON `b`.`Id_Booking` = `bt`.`Id_Booking` INNER JOIN `t_tour` `t` ON `t`.`Id_Tour` = `bt`.`Id_Tour` INNER JOIN `t_package` `pk` ON `pk`.`Id_Package` = `bt`.`Id_Package` LEFT JOIN `t_provider` `pr` ON `pr`.`Id_Provider` = `r`.`Id_Provider` LEFT JOIN `t_room_passenger` `rp` ON `rp`.`Id_Room` = `r`.`Id_Room` AND `rp`.`RoomPassenger_Status` <> 0 LEFT JOIN `t_booking_extra_passenger` `bep` ON `bep`.`Id_BookingExtraPassenger` = `rp`.`Id_BookingExtraPassenger` LEFT JOIN `t_booking_tour_passenger` `btp` ON `btp`.`Id_BookingTourPassenger` = `bep`.`Id_BookingTourPassenger` LEFT JOIN `t_passenger` `p` ON `p`.`Id_Passenger` = `btp`.`Id_Passenger` LEFT JOIN `t_invoice` `i` ON `i`.`Id_Passenger` = `p`.`Id_Passenger` LEFT JOIN `t_user_country` `uc` ON `uc`.`Id_UserCountry` = `p`.`Id_UserCountry` LEFT JOIN `t_type_document` `td` ON `td`.`Id_TypeDocument` = `p`.`Id_TypeDocument` WHERE `e`.`Id_TypeExtra` = 4 AND `r`.`Room_Status` <> 0 AND ( `rp`.`Id_RoomPassenger` IS NULL OR `bep`.`BookingExtraPassenger_Status` = 2 OR ( `bep`.`BookingExtraPassenger_Status` = 1 AND `btp`.`BookingTourPassenger_Status` IN (1, 2, 3) AND `b`.`Booking_Status` IN (1, 2) AND ( `b`.`Booking_ManuallyConfirmed` = 2 OR `btp`.`BookingTourPassenger_PaymentStatus` = 2 ) ) ) AND ((1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1 = ?) AND (((`b`.`Booking_Code` LIKE ?) OR (`pr`.`Provider_BusinessName` LIKE ?) OR (`pr`.`Provider_NickName` LIKE ?) OR (`t`.`Tour_Code` LIKE ?) OR (`pk`.`Package_Code` LIKE ?) OR (`b`.`Booking_Observation` LIKE ?) OR (EXISTS ( SELECT 1 FROM `t_room_passenger` `srp` INNER JOIN `t_booking_extra_passenger` `sbep` ON `sbep`.`Id_BookingExtraPassenger` = `srp`.`Id_BookingExtraPassenger` INNER JOIN `t_booking_tour_passenger` `sbtp` ON `sbtp`.`Id_BookingTourPassenger` = `sbep`.`Id_BookingTourPassenger` INNER JOIN `t_passenger` `sp` ON `sp`.`Id_Passenger` = `sbtp`.`Id_Passenger` WHERE `srp`.`Id_Room` = `r`.`Id_Room` AND `srp`.`RoomPassenger_Status` <> 0 AND ( `sp`.`Passenger_Name` LIKE ? OR `sp`.`Passenger_LastName` LIKE ? OR `sp`.`Passenger_NoDocument` LIKE ? ) )) OR (EXISTS ( SELECT 1 FROM `t_room_passenger` `grp` INNER JOIN `t_booking_extra_passenger` `gbep` ON `gbep`.`Id_BookingExtraPassenger` = `grp`.`Id_BookingExtraPassenger` INNER JOIN `t_group_passenger` `ggp` ON `ggp`.`Id` = `gbep`.`Id_BookingTourPassenger` AND `ggp`.`GroupPassenger_Type` = 1 INNER JOIN `t_group` `gg` ON `gg`.`Id_Group` = `ggp`.`Id_Group` INNER JOIN `t_group_provider` `ggpv` ON `ggpv`.`Id_Group` = `gg`.`Id_Group` AND `ggpv`.`Id_TypeProvider` = 1 INNER JOIN `t_provider` `gpv` ON `gpv`.`Id_Provider` = `ggpv`.`Id_Provider` WHERE `grp`.`Id_Room` = `r`.`Id_Room` AND `grp`.`RoomPassenger_Status` <> 0 AND `gpv`.`Provider_NickName` LIKE ? )))) AND (1 = ?)) GROUP BY `r`.`Id_Room` ORDER BY `r`.`Room_RequestDateStart` desc, `t`.`Id_Tour` ASC, `b`.`Id_Booking` ASC, `r`.`Id_Room` ASC LIMIT 50 OFFSET 50 -- binds: ["1","%a%","%a%","%a%","%a%","%a%","%a%","%a%","%a%","%a%","%a%","1"] ===== search-generico / count ===== SELECT STRAIGHT_JOIN COUNT(DISTINCT `r`.`Id_Room`) as `total` FROM `t_room` `r` INNER JOIN `t_type_room` `tr` ON `tr`.`Id_TypeRoom` = `r`.`Id_TypeRoom` INNER JOIN `t_booking_extra` `be` ON `be`.`Id_BookingExtra` = `r`.`Id_BookingExtra` INNER JOIN `t_extra` `e` ON `e`.`Id_Extra` = `be`.`Id_Extra` INNER JOIN `t_booking_tour` `bt` ON `bt`.`Id_BookingTour` = `be`.`Id_BookingTour` INNER JOIN `t_booking` `b` ON `b`.`Id_Booking` = `bt`.`Id_Booking` INNER JOIN `t_tour` `t` ON `t`.`Id_Tour` = `bt`.`Id_Tour` INNER JOIN `t_package` `pk` ON `pk`.`Id_Package` = `bt`.`Id_Package` LEFT JOIN `t_provider` `pr` ON `pr`.`Id_Provider` = `r`.`Id_Provider` LEFT JOIN `t_room_passenger` `rp` ON `rp`.`Id_Room` = `r`.`Id_Room` AND `rp`.`RoomPassenger_Status` <> 0 LEFT JOIN `t_booking_extra_passenger` `bep` ON `bep`.`Id_BookingExtraPassenger` = `rp`.`Id_BookingExtraPassenger` LEFT JOIN `t_booking_tour_passenger` `btp` ON `btp`.`Id_BookingTourPassenger` = `bep`.`Id_BookingTourPassenger` WHERE `e`.`Id_TypeExtra` = 4 AND `r`.`Room_Status` <> 0 AND ( `rp`.`Id_RoomPassenger` IS NULL OR `bep`.`BookingExtraPassenger_Status` = 2 OR ( `bep`.`BookingExtraPassenger_Status` = 1 AND `btp`.`BookingTourPassenger_Status` IN (1, 2, 3) AND `b`.`Booking_Status` IN (1, 2) AND ( `b`.`Booking_ManuallyConfirmed` = 2 OR `btp`.`BookingTourPassenger_PaymentStatus` = 2 ) ) ) AND ((1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1 = ?) AND (((`b`.`Booking_Code` LIKE ?) OR (`pr`.`Provider_BusinessName` LIKE ?) OR (`pr`.`Provider_NickName` LIKE ?) OR (`t`.`Tour_Code` LIKE ?) OR (`pk`.`Package_Code` LIKE ?) OR (`b`.`Booking_Observation` LIKE ?) OR (EXISTS ( SELECT 1 FROM `t_room_passenger` `srp` INNER JOIN `t_booking_extra_passenger` `sbep` ON `sbep`.`Id_BookingExtraPassenger` = `srp`.`Id_BookingExtraPassenger` INNER JOIN `t_booking_tour_passenger` `sbtp` ON `sbtp`.`Id_BookingTourPassenger` = `sbep`.`Id_BookingTourPassenger` INNER JOIN `t_passenger` `sp` ON `sp`.`Id_Passenger` = `sbtp`.`Id_Passenger` WHERE `srp`.`Id_Room` = `r`.`Id_Room` AND `srp`.`RoomPassenger_Status` <> 0 AND ( `sp`.`Passenger_Name` LIKE ? OR `sp`.`Passenger_LastName` LIKE ? OR `sp`.`Passenger_NoDocument` LIKE ? ) )) OR (EXISTS ( SELECT 1 FROM `t_room_passenger` `grp` INNER JOIN `t_booking_extra_passenger` `gbep` ON `gbep`.`Id_BookingExtraPassenger` = `grp`.`Id_BookingExtraPassenger` INNER JOIN `t_group_passenger` `ggp` ON `ggp`.`Id` = `gbep`.`Id_BookingTourPassenger` AND `ggp`.`GroupPassenger_Type` = 1 INNER JOIN `t_group` `gg` ON `gg`.`Id_Group` = `ggp`.`Id_Group` INNER JOIN `t_group_provider` `ggpv` ON `ggpv`.`Id_Group` = `gg`.`Id_Group` AND `ggpv`.`Id_TypeProvider` = 1 INNER JOIN `t_provider` `gpv` ON `gpv`.`Id_Provider` = `ggpv`.`Id_Provider` WHERE `grp`.`Id_Room` = `r`.`Id_Room` AND `grp`.`RoomPassenger_Status` <> 0 AND `gpv`.`Provider_NickName` LIKE ? )))) AND (1 = ?)) -- binds: ["1","%a%","%a%","%a%","%a%","%a%","%a%","%a%","%a%","%a%","%a%","1"] ===== filtros-reales / list ===== SELECT STRAIGHT_JOIN `r`.`Id_Room`, `r`.`Room_Date`, `r`.`Room_RequestDateStart`, `r`.`Room_RequestDateEnd`, `r`.`Room_RequestLastDateStart`, `r`.`Room_RequestLastDateEnd`, `r`.`Room_ReservationDateAlarm`, `r`.`Room_ReservationDateFinal`, `r`.`Room_Observation`, `r`.`Room_BuyPrice`, `r`.`Room_Paid`, `r`.`Room_BuyDateStart`, `r`.`Room_BuyDateEnd`, `r`.`Room_BuyDatePayment`, `r`.`Room_BuyCode`, `r`.`Room_Lock`, `r`.`Room_SoftLock`, `r`.`Room_PassengersAmount`, `r`.`Room_NoValidAmount`, `r`.`Room_DateChanged`, `r`.`Room_Status`, `r`.`Id_Booking`, `r`.`Id_TypeRoom`, `r`.`Id_BookingExtra`, `r`.`Id_Provider`, `r`.`Id_Provider_Buy`, `r`.`Id_Admin`, `r`.`Id_Admin_Validated`, `r`.`Id_PaxInformationRoom`, `r`.`Id_TypePayment`, `tr`.`Id_TypeRoom`, `tr`.`TypeRoom_Name`, `tr`.`TypeRoom_Abrv`, `tr`.`TypeRoom_Capacity`, `tr`.`TypeRoom_Status`, `be`.`Id_BookingExtra`, `be`.`BookingExtra_Quantity`, `be`.`BookingExtra_Price`, `be`.`BookingExtra_Total`, `be`.`BookingExtra_Feature`, `be`.`BookingExtra_Included`, `be`.`BookingExtra_Mode`, `e`.`Id_Extra`, `e`.`Extra_Name`, `bt`.`Id_BookingTour`, `bt`.`BookingTour_DateStart`, `bt`.`BookingTour_PickUp`, `bt`.`BookingTour_Type`, `bt`.`BookingTour_NoPax`, `bt`.`BookingTour_Name`, `bt`.`BookingTour_TourName`, `bt`.`BookingTour_FeeType`, `bt`.`BookingTour_Full`, `bt`.`BookingTour_MinimumFull`, `bt`.`BookingTour_Group`, `bt`.`BookingTour_TourDuration`, `bt`.`BookingTour_DateBriefing`, `bt`.`BookingTour_PlaceBriefing`, `bt`.`BookingTour_UseBookingPlaceBriefing`, `bt`.`BookingTour_Status`, `bt`.`Id_Booking`, `bt`.`BookingTour_Date`, `bt`.`Id_Package`, `bt`.`Id_Tour`, `bt`.`Id_LodgingPlace_PickUp`, `bt`.`Id_LodgingPlace_DropOff`, `t`.`Id_Tour`, `t`.`Tour_Name`, `t`.`Tour_Code`, `t`.`Tour_Color`, `pk`.`Id_Package`, `pk`.`Package_Name`, `pk`.`Package_Code`, `r`.`Id_Provider`, `pr`.`Provider_BusinessName`, `pr`.`Provider_NickName`, `pr`.`Provider_Phone`, `pr`.`Provider_ImgProfile_Thumb`, `b`.`Id_Booking`, `b`.`Booking_Date`, `b`.`Booking_DateConfirm`, `b`.`Booking_DateStart`, `b`.`Booking_DateBriefing`, `b`.`Booking_PlaceBriefing`, `b`.`Booking_Code`, `b`.`Booking_HistoryChanges`, `b`.`Booking_Hotel`, `b`.`Booking_Observation`, `b`.`Booking_InternalRemark`, `b`.`Booking_Receipt`, `b`.`Booking_Type`, `b`.`Booking_LastMinute`, `b`.`Booking_Assigned`, `b`.`Booking_Status`, `b`.`Booking_ManuallyConfirmed`, `b`.`Booking_InPerson`, `b`.`Booking_PassengerAmount`, `b`.`Booking_CheckedInPassengers`, `b`.`Booking_LastPassenger`, `b`.`Booking_Token`, `b`.`Id_Booking_Replace`, `b`.`Id_Admin`, `b`.`Id_GuideLanguage`, `b`.`Id_Language`, `b`.`Id_User`, `b`.`Id_User_Additional`, `b`.`Id_BookingRelationship`, `b`.`Id_BookingReferredFrom`, CONCAT('[', IFNULL(GROUP_CONCAT(DISTINCT IF(`rp`.`Id_RoomPassenger` IS NULL, NULL, JSON_OBJECT( 'Id_RoomPassenger', `rp`.`Id_RoomPassenger`, 'RoomPassenger_IsNotValid', `rp`.`RoomPassenger_IsNotValid`, 'RoomPassenger_Status', `rp`.`RoomPassenger_Status`, 'Id_BookingExtraPassenger', `bep`.`Id_BookingExtraPassenger`, 'BookingExtraPassenger_DateEntryStart', `bep`.`BookingExtraPassenger_DateEntryStart`, 'BookingExtraPassenger_DateEntryEnd', `bep`.`BookingExtraPassenger_DateEntryEnd`, 'BookingExtraPassenger_DatePurchase', `bep`.`BookingExtraPassenger_DatePurchase`, 'BookingExtraPassenger_Remark', `bep`.`BookingExtraPassenger_Remark`, 'BookingExtraPassenger_Status', `bep`.`BookingExtraPassenger_Status`, 'Id_BookingTourPassenger', `btp`.`Id_BookingTourPassenger`, 'BookingTourPassenger_Remark', `btp`.`BookingTourPassenger_Remark`, 'BookingTourPassenger_Price', `btp`.`BookingTourPassenger_Price`, 'BookingTourPassenger_Full', `btp`.`BookingTourPassenger_Full`, 'BookingTourPassenger_ValidGroups', `btp`.`BookingTourPassenger_ValidGroups`, 'BookingTourPassenger_NoValidGroups', `btp`.`BookingTourPassenger_NoValidGroups`, 'BookingTourPassenger_Status', `btp`.`BookingTourPassenger_Status`, 'BookingTourPassenger_PaymentStatus', `btp`.`BookingTourPassenger_PaymentStatus`, 'Id_Passenger', `p`.`Id_Passenger`, 'Passenger_Name', `p`.`Passenger_Name`, 'Passenger_LastName', `p`.`Passenger_LastName`, 'Passenger_Gender', `p`.`Passenger_Gender`, 'Passenger_DOB', `p`.`Passenger_DOB`, 'Passenger_NoDocument', `p`.`Passenger_NoDocument`, 'Passenger_Status', `p`.`Passenger_Status`, 'Passenger_PaymentStatus', `p`.`Passenger_PaymentStatus`, 'Passenger_Age', YEAR(CURDATE())-YEAR(`p`.`Passenger_DOB`) + IF(DATE_FORMAT(CURDATE(),'%m-%d') > DATE_FORMAT(`p`.`Passenger_DOB`,'%m-%d'), 0 , -1 ), 'Id_UserCountry', `uc`.`Id_UserCountry`, 'UserCountry_Name', `uc`.`UserCountry_Name`, 'UserCountry_Code', `uc`.`UserCountry_Code`, 'UserCountry_Img', `uc`.`UserCountry_Img`, 'Id_TypeDocument', `td`.`Id_TypeDocument`, 'TypeDocument_Name', `td`.`TypeDocument_Name`, 'TypeDocument_Abrv', `td`.`TypeDocument_Abrv`, 'Invoice_Total', `i`.`Invoice_Total`, 'Invoice_TotalPay', `i`.`Invoice_TotalPay`, 'Invoice_TotalPending', `i`.`Invoice_TotalPending` )) SEPARATOR ','), ''), ']' ) AS `PassengerList`, (COUNT(`rp`.`Id_RoomPassenger`)) AS `PassengerCount` FROM `t_room` `r` FORCE INDEX FOR ORDER BY (`INDEX_Room_RequestDateStart`) INNER JOIN `t_type_room` `tr` ON `tr`.`Id_TypeRoom` = `r`.`Id_TypeRoom` INNER JOIN `t_booking_extra` `be` ON `be`.`Id_BookingExtra` = `r`.`Id_BookingExtra` INNER JOIN `t_extra` `e` ON `e`.`Id_Extra` = `be`.`Id_Extra` INNER JOIN `t_booking_tour` `bt` ON `bt`.`Id_BookingTour` = `be`.`Id_BookingTour` INNER JOIN `t_booking` `b` ON `b`.`Id_Booking` = `bt`.`Id_Booking` INNER JOIN `t_tour` `t` ON `t`.`Id_Tour` = `bt`.`Id_Tour` INNER JOIN `t_package` `pk` ON `pk`.`Id_Package` = `bt`.`Id_Package` LEFT JOIN `t_provider` `pr` ON `pr`.`Id_Provider` = `r`.`Id_Provider` LEFT JOIN `t_room_passenger` `rp` ON `rp`.`Id_Room` = `r`.`Id_Room` AND `rp`.`RoomPassenger_Status` <> 0 LEFT JOIN `t_booking_extra_passenger` `bep` ON `bep`.`Id_BookingExtraPassenger` = `rp`.`Id_BookingExtraPassenger` LEFT JOIN `t_booking_tour_passenger` `btp` ON `btp`.`Id_BookingTourPassenger` = `bep`.`Id_BookingTourPassenger` LEFT JOIN `t_passenger` `p` ON `p`.`Id_Passenger` = `btp`.`Id_Passenger` LEFT JOIN `t_invoice` `i` ON `i`.`Id_Passenger` = `p`.`Id_Passenger` LEFT JOIN `t_user_country` `uc` ON `uc`.`Id_UserCountry` = `p`.`Id_UserCountry` LEFT JOIN `t_type_document` `td` ON `td`.`Id_TypeDocument` = `p`.`Id_TypeDocument` WHERE `e`.`Id_TypeExtra` = 4 AND `r`.`Room_Status` <> 0 AND ( `rp`.`Id_RoomPassenger` IS NULL OR `bep`.`BookingExtraPassenger_Status` = 2 OR ( `bep`.`BookingExtraPassenger_Status` = 1 AND `btp`.`BookingTourPassenger_Status` IN (1, 2, 3) AND `b`.`Booking_Status` IN (1, 2) AND ( `b`.`Booking_ManuallyConfirmed` = 2 OR `btp`.`BookingTourPassenger_PaymentStatus` = 2 ) ) ) AND ((`r`.`Room_Status` IN (?,?,?,?)) AND (`e`.`Id_Extra` IN (?,?,?,?,?)) AND (`r`.`Id_Provider` IN (?,?,?,?)) AND (`r`.`Room_Lock` IN (?,?)) AND (`bt`.`Id_Tour` IN (?,?,?,?,?,?,?,?)) AND (`r`.`Id_TypeRoom` IN (?,?,?)) AND (`be`.`BookingExtra_Mode` IN (?,?)) AND (`e`.`Id_Location` = ?) AND (1 = ?) AND (1 = ?) AND (`r`.`Room_RequestDateStart` BETWEEN ? AND ?)) GROUP BY `r`.`Id_Room` ORDER BY `r`.`Room_RequestDateStart` desc, `t`.`Id_Tour` ASC, `b`.`Id_Booking` ASC, `r`.`Id_Room` ASC LIMIT 50 OFFSET 0 -- binds: [2,3,4,5,17,18,19,21,22,0,247,249,250,1,2,1,2,3,4,5,6,7,8,1,2,3,1,2,1,"1","1","2024-01-01 00:00:00","2030-12-31 23:59:59"] ===== filtros-reales / count ===== SELECT STRAIGHT_JOIN COUNT(DISTINCT `r`.`Id_Room`) as `total` FROM `t_room` `r` INNER JOIN `t_type_room` `tr` ON `tr`.`Id_TypeRoom` = `r`.`Id_TypeRoom` INNER JOIN `t_booking_extra` `be` ON `be`.`Id_BookingExtra` = `r`.`Id_BookingExtra` INNER JOIN `t_extra` `e` ON `e`.`Id_Extra` = `be`.`Id_Extra` INNER JOIN `t_booking_tour` `bt` ON `bt`.`Id_BookingTour` = `be`.`Id_BookingTour` INNER JOIN `t_booking` `b` ON `b`.`Id_Booking` = `bt`.`Id_Booking` INNER JOIN `t_tour` `t` ON `t`.`Id_Tour` = `bt`.`Id_Tour` INNER JOIN `t_package` `pk` ON `pk`.`Id_Package` = `bt`.`Id_Package` LEFT JOIN `t_provider` `pr` ON `pr`.`Id_Provider` = `r`.`Id_Provider` LEFT JOIN `t_room_passenger` `rp` ON `rp`.`Id_Room` = `r`.`Id_Room` AND `rp`.`RoomPassenger_Status` <> 0 LEFT JOIN `t_booking_extra_passenger` `bep` ON `bep`.`Id_BookingExtraPassenger` = `rp`.`Id_BookingExtraPassenger` LEFT JOIN `t_booking_tour_passenger` `btp` ON `btp`.`Id_BookingTourPassenger` = `bep`.`Id_BookingTourPassenger` WHERE `e`.`Id_TypeExtra` = 4 AND `r`.`Room_Status` <> 0 AND ( `rp`.`Id_RoomPassenger` IS NULL OR `bep`.`BookingExtraPassenger_Status` = 2 OR ( `bep`.`BookingExtraPassenger_Status` = 1 AND `btp`.`BookingTourPassenger_Status` IN (1, 2, 3) AND `b`.`Booking_Status` IN (1, 2) AND ( `b`.`Booking_ManuallyConfirmed` = 2 OR `btp`.`BookingTourPassenger_PaymentStatus` = 2 ) ) ) AND ((`r`.`Room_Status` IN (?,?,?,?)) AND (`e`.`Id_Extra` IN (?,?,?,?,?)) AND (`r`.`Id_Provider` IN (?,?,?,?)) AND (`r`.`Room_Lock` IN (?,?)) AND (`bt`.`Id_Tour` IN (?,?,?,?,?,?,?,?)) AND (`r`.`Id_TypeRoom` IN (?,?,?)) AND (`be`.`BookingExtra_Mode` IN (?,?)) AND (`e`.`Id_Location` = ?) AND (1 = ?) AND (1 = ?) AND (`r`.`Room_RequestDateStart` BETWEEN ? AND ?)) -- binds: [2,3,4,5,17,18,19,21,22,0,247,249,250,1,2,1,2,3,4,5,6,7,8,1,2,3,1,2,1,"1","1","2024-01-01 00:00:00","2030-12-31 23:59:59"] ===== filtros-y-search / list ===== SELECT STRAIGHT_JOIN `r`.`Id_Room`, `r`.`Room_Date`, `r`.`Room_RequestDateStart`, `r`.`Room_RequestDateEnd`, `r`.`Room_RequestLastDateStart`, `r`.`Room_RequestLastDateEnd`, `r`.`Room_ReservationDateAlarm`, `r`.`Room_ReservationDateFinal`, `r`.`Room_Observation`, `r`.`Room_BuyPrice`, `r`.`Room_Paid`, `r`.`Room_BuyDateStart`, `r`.`Room_BuyDateEnd`, `r`.`Room_BuyDatePayment`, `r`.`Room_BuyCode`, `r`.`Room_Lock`, `r`.`Room_SoftLock`, `r`.`Room_PassengersAmount`, `r`.`Room_NoValidAmount`, `r`.`Room_DateChanged`, `r`.`Room_Status`, `r`.`Id_Booking`, `r`.`Id_TypeRoom`, `r`.`Id_BookingExtra`, `r`.`Id_Provider`, `r`.`Id_Provider_Buy`, `r`.`Id_Admin`, `r`.`Id_Admin_Validated`, `r`.`Id_PaxInformationRoom`, `r`.`Id_TypePayment`, `tr`.`Id_TypeRoom`, `tr`.`TypeRoom_Name`, `tr`.`TypeRoom_Abrv`, `tr`.`TypeRoom_Capacity`, `tr`.`TypeRoom_Status`, `be`.`Id_BookingExtra`, `be`.`BookingExtra_Quantity`, `be`.`BookingExtra_Price`, `be`.`BookingExtra_Total`, `be`.`BookingExtra_Feature`, `be`.`BookingExtra_Included`, `be`.`BookingExtra_Mode`, `e`.`Id_Extra`, `e`.`Extra_Name`, `bt`.`Id_BookingTour`, `bt`.`BookingTour_DateStart`, `bt`.`BookingTour_PickUp`, `bt`.`BookingTour_Type`, `bt`.`BookingTour_NoPax`, `bt`.`BookingTour_Name`, `bt`.`BookingTour_TourName`, `bt`.`BookingTour_FeeType`, `bt`.`BookingTour_Full`, `bt`.`BookingTour_MinimumFull`, `bt`.`BookingTour_Group`, `bt`.`BookingTour_TourDuration`, `bt`.`BookingTour_DateBriefing`, `bt`.`BookingTour_PlaceBriefing`, `bt`.`BookingTour_UseBookingPlaceBriefing`, `bt`.`BookingTour_Status`, `bt`.`Id_Booking`, `bt`.`BookingTour_Date`, `bt`.`Id_Package`, `bt`.`Id_Tour`, `bt`.`Id_LodgingPlace_PickUp`, `bt`.`Id_LodgingPlace_DropOff`, `t`.`Id_Tour`, `t`.`Tour_Name`, `t`.`Tour_Code`, `t`.`Tour_Color`, `pk`.`Id_Package`, `pk`.`Package_Name`, `pk`.`Package_Code`, `r`.`Id_Provider`, `pr`.`Provider_BusinessName`, `pr`.`Provider_NickName`, `pr`.`Provider_Phone`, `pr`.`Provider_ImgProfile_Thumb`, `b`.`Id_Booking`, `b`.`Booking_Date`, `b`.`Booking_DateConfirm`, `b`.`Booking_DateStart`, `b`.`Booking_DateBriefing`, `b`.`Booking_PlaceBriefing`, `b`.`Booking_Code`, `b`.`Booking_HistoryChanges`, `b`.`Booking_Hotel`, `b`.`Booking_Observation`, `b`.`Booking_InternalRemark`, `b`.`Booking_Receipt`, `b`.`Booking_Type`, `b`.`Booking_LastMinute`, `b`.`Booking_Assigned`, `b`.`Booking_Status`, `b`.`Booking_ManuallyConfirmed`, `b`.`Booking_InPerson`, `b`.`Booking_PassengerAmount`, `b`.`Booking_CheckedInPassengers`, `b`.`Booking_LastPassenger`, `b`.`Booking_Token`, `b`.`Id_Booking_Replace`, `b`.`Id_Admin`, `b`.`Id_GuideLanguage`, `b`.`Id_Language`, `b`.`Id_User`, `b`.`Id_User_Additional`, `b`.`Id_BookingRelationship`, `b`.`Id_BookingReferredFrom`, CONCAT('[', IFNULL(GROUP_CONCAT(DISTINCT IF(`rp`.`Id_RoomPassenger` IS NULL, NULL, JSON_OBJECT( 'Id_RoomPassenger', `rp`.`Id_RoomPassenger`, 'RoomPassenger_IsNotValid', `rp`.`RoomPassenger_IsNotValid`, 'RoomPassenger_Status', `rp`.`RoomPassenger_Status`, 'Id_BookingExtraPassenger', `bep`.`Id_BookingExtraPassenger`, 'BookingExtraPassenger_DateEntryStart', `bep`.`BookingExtraPassenger_DateEntryStart`, 'BookingExtraPassenger_DateEntryEnd', `bep`.`BookingExtraPassenger_DateEntryEnd`, 'BookingExtraPassenger_DatePurchase', `bep`.`BookingExtraPassenger_DatePurchase`, 'BookingExtraPassenger_Remark', `bep`.`BookingExtraPassenger_Remark`, 'BookingExtraPassenger_Status', `bep`.`BookingExtraPassenger_Status`, 'Id_BookingTourPassenger', `btp`.`Id_BookingTourPassenger`, 'BookingTourPassenger_Remark', `btp`.`BookingTourPassenger_Remark`, 'BookingTourPassenger_Price', `btp`.`BookingTourPassenger_Price`, 'BookingTourPassenger_Full', `btp`.`BookingTourPassenger_Full`, 'BookingTourPassenger_ValidGroups', `btp`.`BookingTourPassenger_ValidGroups`, 'BookingTourPassenger_NoValidGroups', `btp`.`BookingTourPassenger_NoValidGroups`, 'BookingTourPassenger_Status', `btp`.`BookingTourPassenger_Status`, 'BookingTourPassenger_PaymentStatus', `btp`.`BookingTourPassenger_PaymentStatus`, 'Id_Passenger', `p`.`Id_Passenger`, 'Passenger_Name', `p`.`Passenger_Name`, 'Passenger_LastName', `p`.`Passenger_LastName`, 'Passenger_Gender', `p`.`Passenger_Gender`, 'Passenger_DOB', `p`.`Passenger_DOB`, 'Passenger_NoDocument', `p`.`Passenger_NoDocument`, 'Passenger_Status', `p`.`Passenger_Status`, 'Passenger_PaymentStatus', `p`.`Passenger_PaymentStatus`, 'Passenger_Age', YEAR(CURDATE())-YEAR(`p`.`Passenger_DOB`) + IF(DATE_FORMAT(CURDATE(),'%m-%d') > DATE_FORMAT(`p`.`Passenger_DOB`,'%m-%d'), 0 , -1 ), 'Id_UserCountry', `uc`.`Id_UserCountry`, 'UserCountry_Name', `uc`.`UserCountry_Name`, 'UserCountry_Code', `uc`.`UserCountry_Code`, 'UserCountry_Img', `uc`.`UserCountry_Img`, 'Id_TypeDocument', `td`.`Id_TypeDocument`, 'TypeDocument_Name', `td`.`TypeDocument_Name`, 'TypeDocument_Abrv', `td`.`TypeDocument_Abrv`, 'Invoice_Total', `i`.`Invoice_Total`, 'Invoice_TotalPay', `i`.`Invoice_TotalPay`, 'Invoice_TotalPending', `i`.`Invoice_TotalPending` )) SEPARATOR ','), ''), ']' ) AS `PassengerList`, (COUNT(`rp`.`Id_RoomPassenger`)) AS `PassengerCount` FROM `t_room` `r` FORCE INDEX FOR ORDER BY (`INDEX_Room_RequestDateStart`) INNER JOIN `t_type_room` `tr` ON `tr`.`Id_TypeRoom` = `r`.`Id_TypeRoom` INNER JOIN `t_booking_extra` `be` ON `be`.`Id_BookingExtra` = `r`.`Id_BookingExtra` INNER JOIN `t_extra` `e` ON `e`.`Id_Extra` = `be`.`Id_Extra` INNER JOIN `t_booking_tour` `bt` ON `bt`.`Id_BookingTour` = `be`.`Id_BookingTour` INNER JOIN `t_booking` `b` ON `b`.`Id_Booking` = `bt`.`Id_Booking` INNER JOIN `t_tour` `t` ON `t`.`Id_Tour` = `bt`.`Id_Tour` INNER JOIN `t_package` `pk` ON `pk`.`Id_Package` = `bt`.`Id_Package` LEFT JOIN `t_provider` `pr` ON `pr`.`Id_Provider` = `r`.`Id_Provider` LEFT JOIN `t_room_passenger` `rp` ON `rp`.`Id_Room` = `r`.`Id_Room` AND `rp`.`RoomPassenger_Status` <> 0 LEFT JOIN `t_booking_extra_passenger` `bep` ON `bep`.`Id_BookingExtraPassenger` = `rp`.`Id_BookingExtraPassenger` LEFT JOIN `t_booking_tour_passenger` `btp` ON `btp`.`Id_BookingTourPassenger` = `bep`.`Id_BookingTourPassenger` LEFT JOIN `t_passenger` `p` ON `p`.`Id_Passenger` = `btp`.`Id_Passenger` LEFT JOIN `t_invoice` `i` ON `i`.`Id_Passenger` = `p`.`Id_Passenger` LEFT JOIN `t_user_country` `uc` ON `uc`.`Id_UserCountry` = `p`.`Id_UserCountry` LEFT JOIN `t_type_document` `td` ON `td`.`Id_TypeDocument` = `p`.`Id_TypeDocument` WHERE `e`.`Id_TypeExtra` = 4 AND `r`.`Room_Status` <> 0 AND ( `rp`.`Id_RoomPassenger` IS NULL OR `bep`.`BookingExtraPassenger_Status` = 2 OR ( `bep`.`BookingExtraPassenger_Status` = 1 AND `btp`.`BookingTourPassenger_Status` IN (1, 2, 3) AND `b`.`Booking_Status` IN (1, 2) AND ( `b`.`Booking_ManuallyConfirmed` = 2 OR `btp`.`BookingTourPassenger_PaymentStatus` = 2 ) ) ) AND ((`r`.`Room_Status` IN (?,?,?,?)) AND (`e`.`Id_Extra` IN (?,?,?,?,?)) AND (`r`.`Id_Provider` IN (?,?,?,?)) AND (`r`.`Room_Lock` IN (?,?)) AND (`bt`.`Id_Tour` IN (?,?,?,?,?,?,?,?)) AND (`r`.`Id_TypeRoom` IN (?,?,?)) AND (`be`.`BookingExtra_Mode` IN (?,?)) AND (`e`.`Id_Location` = ?) AND (((`b`.`Booking_Code` LIKE ?) OR (`pr`.`Provider_BusinessName` LIKE ?) OR (`pr`.`Provider_NickName` LIKE ?) OR (`t`.`Tour_Code` LIKE ?) OR (`pk`.`Package_Code` LIKE ?) OR (`b`.`Booking_Observation` LIKE ?) OR (EXISTS ( SELECT 1 FROM `t_room_passenger` `srp` INNER JOIN `t_booking_extra_passenger` `sbep` ON `sbep`.`Id_BookingExtraPassenger` = `srp`.`Id_BookingExtraPassenger` INNER JOIN `t_booking_tour_passenger` `sbtp` ON `sbtp`.`Id_BookingTourPassenger` = `sbep`.`Id_BookingTourPassenger` INNER JOIN `t_passenger` `sp` ON `sp`.`Id_Passenger` = `sbtp`.`Id_Passenger` WHERE `srp`.`Id_Room` = `r`.`Id_Room` AND `srp`.`RoomPassenger_Status` <> 0 AND ( `sp`.`Passenger_Name` LIKE ? OR `sp`.`Passenger_LastName` LIKE ? OR `sp`.`Passenger_NoDocument` LIKE ? ) )) OR (EXISTS ( SELECT 1 FROM `t_room_passenger` `grp` INNER JOIN `t_booking_extra_passenger` `gbep` ON `gbep`.`Id_BookingExtraPassenger` = `grp`.`Id_BookingExtraPassenger` INNER JOIN `t_group_passenger` `ggp` ON `ggp`.`Id` = `gbep`.`Id_BookingTourPassenger` AND `ggp`.`GroupPassenger_Type` = 1 INNER JOIN `t_group` `gg` ON `gg`.`Id_Group` = `ggp`.`Id_Group` INNER JOIN `t_group_provider` `ggpv` ON `ggpv`.`Id_Group` = `gg`.`Id_Group` AND `ggpv`.`Id_TypeProvider` = 1 INNER JOIN `t_provider` `gpv` ON `gpv`.`Id_Provider` = `ggpv`.`Id_Provider` WHERE `grp`.`Id_Room` = `r`.`Id_Room` AND `grp`.`RoomPassenger_Status` <> 0 AND `gpv`.`Provider_NickName` LIKE ? )))) AND (1 = ?)) GROUP BY `r`.`Id_Room` ORDER BY `r`.`Room_RequestDateStart` desc, `t`.`Id_Tour` ASC, `b`.`Id_Booking` ASC, `r`.`Id_Room` ASC LIMIT 50 OFFSET 0 -- binds: [2,3,4,5,17,18,19,21,22,0,247,249,250,1,2,1,2,3,4,5,6,7,8,1,2,3,1,2,1,"%a%","%a%","%a%","%a%","%a%","%a%","%a%","%a%","%a%","%a%","1"] ===== filtros-y-search / count ===== SELECT STRAIGHT_JOIN COUNT(DISTINCT `r`.`Id_Room`) as `total` FROM `t_room` `r` INNER JOIN `t_type_room` `tr` ON `tr`.`Id_TypeRoom` = `r`.`Id_TypeRoom` INNER JOIN `t_booking_extra` `be` ON `be`.`Id_BookingExtra` = `r`.`Id_BookingExtra` INNER JOIN `t_extra` `e` ON `e`.`Id_Extra` = `be`.`Id_Extra` INNER JOIN `t_booking_tour` `bt` ON `bt`.`Id_BookingTour` = `be`.`Id_BookingTour` INNER JOIN `t_booking` `b` ON `b`.`Id_Booking` = `bt`.`Id_Booking` INNER JOIN `t_tour` `t` ON `t`.`Id_Tour` = `bt`.`Id_Tour` INNER JOIN `t_package` `pk` ON `pk`.`Id_Package` = `bt`.`Id_Package` LEFT JOIN `t_provider` `pr` ON `pr`.`Id_Provider` = `r`.`Id_Provider` LEFT JOIN `t_room_passenger` `rp` ON `rp`.`Id_Room` = `r`.`Id_Room` AND `rp`.`RoomPassenger_Status` <> 0 LEFT JOIN `t_booking_extra_passenger` `bep` ON `bep`.`Id_BookingExtraPassenger` = `rp`.`Id_BookingExtraPassenger` LEFT JOIN `t_booking_tour_passenger` `btp` ON `btp`.`Id_BookingTourPassenger` = `bep`.`Id_BookingTourPassenger` WHERE `e`.`Id_TypeExtra` = 4 AND `r`.`Room_Status` <> 0 AND ( `rp`.`Id_RoomPassenger` IS NULL OR `bep`.`BookingExtraPassenger_Status` = 2 OR ( `bep`.`BookingExtraPassenger_Status` = 1 AND `btp`.`BookingTourPassenger_Status` IN (1, 2, 3) AND `b`.`Booking_Status` IN (1, 2) AND ( `b`.`Booking_ManuallyConfirmed` = 2 OR `btp`.`BookingTourPassenger_PaymentStatus` = 2 ) ) ) AND ((`r`.`Room_Status` IN (?,?,?,?)) AND (`e`.`Id_Extra` IN (?,?,?,?,?)) AND (`r`.`Id_Provider` IN (?,?,?,?)) AND (`r`.`Room_Lock` IN (?,?)) AND (`bt`.`Id_Tour` IN (?,?,?,?,?,?,?,?)) AND (`r`.`Id_TypeRoom` IN (?,?,?)) AND (`be`.`BookingExtra_Mode` IN (?,?)) AND (`e`.`Id_Location` = ?) AND (((`b`.`Booking_Code` LIKE ?) OR (`pr`.`Provider_BusinessName` LIKE ?) OR (`pr`.`Provider_NickName` LIKE ?) OR (`t`.`Tour_Code` LIKE ?) OR (`pk`.`Package_Code` LIKE ?) OR (`b`.`Booking_Observation` LIKE ?) OR (EXISTS ( SELECT 1 FROM `t_room_passenger` `srp` INNER JOIN `t_booking_extra_passenger` `sbep` ON `sbep`.`Id_BookingExtraPassenger` = `srp`.`Id_BookingExtraPassenger` INNER JOIN `t_booking_tour_passenger` `sbtp` ON `sbtp`.`Id_BookingTourPassenger` = `sbep`.`Id_BookingTourPassenger` INNER JOIN `t_passenger` `sp` ON `sp`.`Id_Passenger` = `sbtp`.`Id_Passenger` WHERE `srp`.`Id_Room` = `r`.`Id_Room` AND `srp`.`RoomPassenger_Status` <> 0 AND ( `sp`.`Passenger_Name` LIKE ? OR `sp`.`Passenger_LastName` LIKE ? OR `sp`.`Passenger_NoDocument` LIKE ? ) )) OR (EXISTS ( SELECT 1 FROM `t_room_passenger` `grp` INNER JOIN `t_booking_extra_passenger` `gbep` ON `gbep`.`Id_BookingExtraPassenger` = `grp`.`Id_BookingExtraPassenger` INNER JOIN `t_group_passenger` `ggp` ON `ggp`.`Id` = `gbep`.`Id_BookingTourPassenger` AND `ggp`.`GroupPassenger_Type` = 1 INNER JOIN `t_group` `gg` ON `gg`.`Id_Group` = `ggp`.`Id_Group` INNER JOIN `t_group_provider` `ggpv` ON `ggpv`.`Id_Group` = `gg`.`Id_Group` AND `ggpv`.`Id_TypeProvider` = 1 INNER JOIN `t_provider` `gpv` ON `gpv`.`Id_Provider` = `ggpv`.`Id_Provider` WHERE `grp`.`Id_Room` = `r`.`Id_Room` AND `grp`.`RoomPassenger_Status` <> 0 AND `gpv`.`Provider_NickName` LIKE ? )))) AND (1 = ?)) -- binds: [2,3,4,5,17,18,19,21,22,0,247,249,250,1,2,1,2,3,4,5,6,7,8,1,2,3,1,2,1,"%a%","%a%","%a%","%a%","%a%","%a%","%a%","%a%","%a%","%a%","1"] ===== room-sin-pax / list ===== SELECT STRAIGHT_JOIN `r`.`Id_Room`, `r`.`Room_Date`, `r`.`Room_RequestDateStart`, `r`.`Room_RequestDateEnd`, `r`.`Room_RequestLastDateStart`, `r`.`Room_RequestLastDateEnd`, `r`.`Room_ReservationDateAlarm`, `r`.`Room_ReservationDateFinal`, `r`.`Room_Observation`, `r`.`Room_BuyPrice`, `r`.`Room_Paid`, `r`.`Room_BuyDateStart`, `r`.`Room_BuyDateEnd`, `r`.`Room_BuyDatePayment`, `r`.`Room_BuyCode`, `r`.`Room_Lock`, `r`.`Room_SoftLock`, `r`.`Room_PassengersAmount`, `r`.`Room_NoValidAmount`, `r`.`Room_DateChanged`, `r`.`Room_Status`, `r`.`Id_Booking`, `r`.`Id_TypeRoom`, `r`.`Id_BookingExtra`, `r`.`Id_Provider`, `r`.`Id_Provider_Buy`, `r`.`Id_Admin`, `r`.`Id_Admin_Validated`, `r`.`Id_PaxInformationRoom`, `r`.`Id_TypePayment`, `tr`.`Id_TypeRoom`, `tr`.`TypeRoom_Name`, `tr`.`TypeRoom_Abrv`, `tr`.`TypeRoom_Capacity`, `tr`.`TypeRoom_Status`, `be`.`Id_BookingExtra`, `be`.`BookingExtra_Quantity`, `be`.`BookingExtra_Price`, `be`.`BookingExtra_Total`, `be`.`BookingExtra_Feature`, `be`.`BookingExtra_Included`, `be`.`BookingExtra_Mode`, `e`.`Id_Extra`, `e`.`Extra_Name`, `bt`.`Id_BookingTour`, `bt`.`BookingTour_DateStart`, `bt`.`BookingTour_PickUp`, `bt`.`BookingTour_Type`, `bt`.`BookingTour_NoPax`, `bt`.`BookingTour_Name`, `bt`.`BookingTour_TourName`, `bt`.`BookingTour_FeeType`, `bt`.`BookingTour_Full`, `bt`.`BookingTour_MinimumFull`, `bt`.`BookingTour_Group`, `bt`.`BookingTour_TourDuration`, `bt`.`BookingTour_DateBriefing`, `bt`.`BookingTour_PlaceBriefing`, `bt`.`BookingTour_UseBookingPlaceBriefing`, `bt`.`BookingTour_Status`, `bt`.`Id_Booking`, `bt`.`BookingTour_Date`, `bt`.`Id_Package`, `bt`.`Id_Tour`, `bt`.`Id_LodgingPlace_PickUp`, `bt`.`Id_LodgingPlace_DropOff`, `t`.`Id_Tour`, `t`.`Tour_Name`, `t`.`Tour_Code`, `t`.`Tour_Color`, `pk`.`Id_Package`, `pk`.`Package_Name`, `pk`.`Package_Code`, `r`.`Id_Provider`, `pr`.`Provider_BusinessName`, `pr`.`Provider_NickName`, `pr`.`Provider_Phone`, `pr`.`Provider_ImgProfile_Thumb`, `b`.`Id_Booking`, `b`.`Booking_Date`, `b`.`Booking_DateConfirm`, `b`.`Booking_DateStart`, `b`.`Booking_DateBriefing`, `b`.`Booking_PlaceBriefing`, `b`.`Booking_Code`, `b`.`Booking_HistoryChanges`, `b`.`Booking_Hotel`, `b`.`Booking_Observation`, `b`.`Booking_InternalRemark`, `b`.`Booking_Receipt`, `b`.`Booking_Type`, `b`.`Booking_LastMinute`, `b`.`Booking_Assigned`, `b`.`Booking_Status`, `b`.`Booking_ManuallyConfirmed`, `b`.`Booking_InPerson`, `b`.`Booking_PassengerAmount`, `b`.`Booking_CheckedInPassengers`, `b`.`Booking_LastPassenger`, `b`.`Booking_Token`, `b`.`Id_Booking_Replace`, `b`.`Id_Admin`, `b`.`Id_GuideLanguage`, `b`.`Id_Language`, `b`.`Id_User`, `b`.`Id_User_Additional`, `b`.`Id_BookingRelationship`, `b`.`Id_BookingReferredFrom`, CONCAT('[', IFNULL(GROUP_CONCAT(DISTINCT IF(`rp`.`Id_RoomPassenger` IS NULL, NULL, JSON_OBJECT( 'Id_RoomPassenger', `rp`.`Id_RoomPassenger`, 'RoomPassenger_IsNotValid', `rp`.`RoomPassenger_IsNotValid`, 'RoomPassenger_Status', `rp`.`RoomPassenger_Status`, 'Id_BookingExtraPassenger', `bep`.`Id_BookingExtraPassenger`, 'BookingExtraPassenger_DateEntryStart', `bep`.`BookingExtraPassenger_DateEntryStart`, 'BookingExtraPassenger_DateEntryEnd', `bep`.`BookingExtraPassenger_DateEntryEnd`, 'BookingExtraPassenger_DatePurchase', `bep`.`BookingExtraPassenger_DatePurchase`, 'BookingExtraPassenger_Remark', `bep`.`BookingExtraPassenger_Remark`, 'BookingExtraPassenger_Status', `bep`.`BookingExtraPassenger_Status`, 'Id_BookingTourPassenger', `btp`.`Id_BookingTourPassenger`, 'BookingTourPassenger_Remark', `btp`.`BookingTourPassenger_Remark`, 'BookingTourPassenger_Price', `btp`.`BookingTourPassenger_Price`, 'BookingTourPassenger_Full', `btp`.`BookingTourPassenger_Full`, 'BookingTourPassenger_ValidGroups', `btp`.`BookingTourPassenger_ValidGroups`, 'BookingTourPassenger_NoValidGroups', `btp`.`BookingTourPassenger_NoValidGroups`, 'BookingTourPassenger_Status', `btp`.`BookingTourPassenger_Status`, 'BookingTourPassenger_PaymentStatus', `btp`.`BookingTourPassenger_PaymentStatus`, 'Id_Passenger', `p`.`Id_Passenger`, 'Passenger_Name', `p`.`Passenger_Name`, 'Passenger_LastName', `p`.`Passenger_LastName`, 'Passenger_Gender', `p`.`Passenger_Gender`, 'Passenger_DOB', `p`.`Passenger_DOB`, 'Passenger_NoDocument', `p`.`Passenger_NoDocument`, 'Passenger_Status', `p`.`Passenger_Status`, 'Passenger_PaymentStatus', `p`.`Passenger_PaymentStatus`, 'Passenger_Age', YEAR(CURDATE())-YEAR(`p`.`Passenger_DOB`) + IF(DATE_FORMAT(CURDATE(),'%m-%d') > DATE_FORMAT(`p`.`Passenger_DOB`,'%m-%d'), 0 , -1 ), 'Id_UserCountry', `uc`.`Id_UserCountry`, 'UserCountry_Name', `uc`.`UserCountry_Name`, 'UserCountry_Code', `uc`.`UserCountry_Code`, 'UserCountry_Img', `uc`.`UserCountry_Img`, 'Id_TypeDocument', `td`.`Id_TypeDocument`, 'TypeDocument_Name', `td`.`TypeDocument_Name`, 'TypeDocument_Abrv', `td`.`TypeDocument_Abrv`, 'Invoice_Total', `i`.`Invoice_Total`, 'Invoice_TotalPay', `i`.`Invoice_TotalPay`, 'Invoice_TotalPending', `i`.`Invoice_TotalPending` )) SEPARATOR ','), ''), ']' ) AS `PassengerList`, (COUNT(`rp`.`Id_RoomPassenger`)) AS `PassengerCount` FROM `t_room` `r` FORCE INDEX FOR ORDER BY (`INDEX_Room_RequestDateStart`) INNER JOIN `t_type_room` `tr` ON `tr`.`Id_TypeRoom` = `r`.`Id_TypeRoom` INNER JOIN `t_booking_extra` `be` ON `be`.`Id_BookingExtra` = `r`.`Id_BookingExtra` INNER JOIN `t_extra` `e` ON `e`.`Id_Extra` = `be`.`Id_Extra` INNER JOIN `t_booking_tour` `bt` ON `bt`.`Id_BookingTour` = `be`.`Id_BookingTour` INNER JOIN `t_booking` `b` ON `b`.`Id_Booking` = `bt`.`Id_Booking` INNER JOIN `t_tour` `t` ON `t`.`Id_Tour` = `bt`.`Id_Tour` INNER JOIN `t_package` `pk` ON `pk`.`Id_Package` = `bt`.`Id_Package` LEFT JOIN `t_provider` `pr` ON `pr`.`Id_Provider` = `r`.`Id_Provider` LEFT JOIN `t_room_passenger` `rp` ON `rp`.`Id_Room` = `r`.`Id_Room` AND `rp`.`RoomPassenger_Status` <> 0 LEFT JOIN `t_booking_extra_passenger` `bep` ON `bep`.`Id_BookingExtraPassenger` = `rp`.`Id_BookingExtraPassenger` LEFT JOIN `t_booking_tour_passenger` `btp` ON `btp`.`Id_BookingTourPassenger` = `bep`.`Id_BookingTourPassenger` LEFT JOIN `t_passenger` `p` ON `p`.`Id_Passenger` = `btp`.`Id_Passenger` LEFT JOIN `t_invoice` `i` ON `i`.`Id_Passenger` = `p`.`Id_Passenger` LEFT JOIN `t_user_country` `uc` ON `uc`.`Id_UserCountry` = `p`.`Id_UserCountry` LEFT JOIN `t_type_document` `td` ON `td`.`Id_TypeDocument` = `p`.`Id_TypeDocument` WHERE `e`.`Id_TypeExtra` = 4 AND `r`.`Room_Status` <> 0 AND ( `rp`.`Id_RoomPassenger` IS NULL OR `bep`.`BookingExtraPassenger_Status` = 2 OR ( `bep`.`BookingExtraPassenger_Status` = 1 AND `btp`.`BookingTourPassenger_Status` IN (1, 2, 3) AND `b`.`Booking_Status` IN (1, 2) AND ( `b`.`Booking_ManuallyConfirmed` = 2 OR `btp`.`BookingTourPassenger_PaymentStatus` = 2 ) ) ) AND ((1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1 = ?) AND (1 = ?) AND (`r`.`Id_Room` = ?) AND (`r`.`Room_RequestDateStart` BETWEEN ? AND ?)) GROUP BY `r`.`Id_Room` ORDER BY `r`.`Room_RequestDateStart` desc, `t`.`Id_Tour` ASC, `b`.`Id_Booking` ASC, `r`.`Id_Room` ASC LIMIT 50 OFFSET 0 -- binds: ["1","1",38,"2024-01-01 00:00:00","2030-12-31 23:59:59"] ===== room-sin-pax / count ===== SELECT STRAIGHT_JOIN COUNT(DISTINCT `r`.`Id_Room`) as `total` FROM `t_room` `r` INNER JOIN `t_type_room` `tr` ON `tr`.`Id_TypeRoom` = `r`.`Id_TypeRoom` INNER JOIN `t_booking_extra` `be` ON `be`.`Id_BookingExtra` = `r`.`Id_BookingExtra` INNER JOIN `t_extra` `e` ON `e`.`Id_Extra` = `be`.`Id_Extra` INNER JOIN `t_booking_tour` `bt` ON `bt`.`Id_BookingTour` = `be`.`Id_BookingTour` INNER JOIN `t_booking` `b` ON `b`.`Id_Booking` = `bt`.`Id_Booking` INNER JOIN `t_tour` `t` ON `t`.`Id_Tour` = `bt`.`Id_Tour` INNER JOIN `t_package` `pk` ON `pk`.`Id_Package` = `bt`.`Id_Package` LEFT JOIN `t_provider` `pr` ON `pr`.`Id_Provider` = `r`.`Id_Provider` LEFT JOIN `t_room_passenger` `rp` ON `rp`.`Id_Room` = `r`.`Id_Room` AND `rp`.`RoomPassenger_Status` <> 0 LEFT JOIN `t_booking_extra_passenger` `bep` ON `bep`.`Id_BookingExtraPassenger` = `rp`.`Id_BookingExtraPassenger` LEFT JOIN `t_booking_tour_passenger` `btp` ON `btp`.`Id_BookingTourPassenger` = `bep`.`Id_BookingTourPassenger` WHERE `e`.`Id_TypeExtra` = 4 AND `r`.`Room_Status` <> 0 AND ( `rp`.`Id_RoomPassenger` IS NULL OR `bep`.`BookingExtraPassenger_Status` = 2 OR ( `bep`.`BookingExtraPassenger_Status` = 1 AND `btp`.`BookingTourPassenger_Status` IN (1, 2, 3) AND `b`.`Booking_Status` IN (1, 2) AND ( `b`.`Booking_ManuallyConfirmed` = 2 OR `btp`.`BookingTourPassenger_PaymentStatus` = 2 ) ) ) AND ((1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1 = ?) AND (1 = ?) AND (`r`.`Id_Room` = ?) AND (`r`.`Room_RequestDateStart` BETWEEN ? AND ?)) -- binds: ["1","1",38,"2024-01-01 00:00:00","2030-12-31 23:59:59"] ===== room-con-pax / list ===== SELECT STRAIGHT_JOIN `r`.`Id_Room`, `r`.`Room_Date`, `r`.`Room_RequestDateStart`, `r`.`Room_RequestDateEnd`, `r`.`Room_RequestLastDateStart`, `r`.`Room_RequestLastDateEnd`, `r`.`Room_ReservationDateAlarm`, `r`.`Room_ReservationDateFinal`, `r`.`Room_Observation`, `r`.`Room_BuyPrice`, `r`.`Room_Paid`, `r`.`Room_BuyDateStart`, `r`.`Room_BuyDateEnd`, `r`.`Room_BuyDatePayment`, `r`.`Room_BuyCode`, `r`.`Room_Lock`, `r`.`Room_SoftLock`, `r`.`Room_PassengersAmount`, `r`.`Room_NoValidAmount`, `r`.`Room_DateChanged`, `r`.`Room_Status`, `r`.`Id_Booking`, `r`.`Id_TypeRoom`, `r`.`Id_BookingExtra`, `r`.`Id_Provider`, `r`.`Id_Provider_Buy`, `r`.`Id_Admin`, `r`.`Id_Admin_Validated`, `r`.`Id_PaxInformationRoom`, `r`.`Id_TypePayment`, `tr`.`Id_TypeRoom`, `tr`.`TypeRoom_Name`, `tr`.`TypeRoom_Abrv`, `tr`.`TypeRoom_Capacity`, `tr`.`TypeRoom_Status`, `be`.`Id_BookingExtra`, `be`.`BookingExtra_Quantity`, `be`.`BookingExtra_Price`, `be`.`BookingExtra_Total`, `be`.`BookingExtra_Feature`, `be`.`BookingExtra_Included`, `be`.`BookingExtra_Mode`, `e`.`Id_Extra`, `e`.`Extra_Name`, `bt`.`Id_BookingTour`, `bt`.`BookingTour_DateStart`, `bt`.`BookingTour_PickUp`, `bt`.`BookingTour_Type`, `bt`.`BookingTour_NoPax`, `bt`.`BookingTour_Name`, `bt`.`BookingTour_TourName`, `bt`.`BookingTour_FeeType`, `bt`.`BookingTour_Full`, `bt`.`BookingTour_MinimumFull`, `bt`.`BookingTour_Group`, `bt`.`BookingTour_TourDuration`, `bt`.`BookingTour_DateBriefing`, `bt`.`BookingTour_PlaceBriefing`, `bt`.`BookingTour_UseBookingPlaceBriefing`, `bt`.`BookingTour_Status`, `bt`.`Id_Booking`, `bt`.`BookingTour_Date`, `bt`.`Id_Package`, `bt`.`Id_Tour`, `bt`.`Id_LodgingPlace_PickUp`, `bt`.`Id_LodgingPlace_DropOff`, `t`.`Id_Tour`, `t`.`Tour_Name`, `t`.`Tour_Code`, `t`.`Tour_Color`, `pk`.`Id_Package`, `pk`.`Package_Name`, `pk`.`Package_Code`, `r`.`Id_Provider`, `pr`.`Provider_BusinessName`, `pr`.`Provider_NickName`, `pr`.`Provider_Phone`, `pr`.`Provider_ImgProfile_Thumb`, `b`.`Id_Booking`, `b`.`Booking_Date`, `b`.`Booking_DateConfirm`, `b`.`Booking_DateStart`, `b`.`Booking_DateBriefing`, `b`.`Booking_PlaceBriefing`, `b`.`Booking_Code`, `b`.`Booking_HistoryChanges`, `b`.`Booking_Hotel`, `b`.`Booking_Observation`, `b`.`Booking_InternalRemark`, `b`.`Booking_Receipt`, `b`.`Booking_Type`, `b`.`Booking_LastMinute`, `b`.`Booking_Assigned`, `b`.`Booking_Status`, `b`.`Booking_ManuallyConfirmed`, `b`.`Booking_InPerson`, `b`.`Booking_PassengerAmount`, `b`.`Booking_CheckedInPassengers`, `b`.`Booking_LastPassenger`, `b`.`Booking_Token`, `b`.`Id_Booking_Replace`, `b`.`Id_Admin`, `b`.`Id_GuideLanguage`, `b`.`Id_Language`, `b`.`Id_User`, `b`.`Id_User_Additional`, `b`.`Id_BookingRelationship`, `b`.`Id_BookingReferredFrom`, CONCAT('[', IFNULL(GROUP_CONCAT(DISTINCT IF(`rp`.`Id_RoomPassenger` IS NULL, NULL, JSON_OBJECT( 'Id_RoomPassenger', `rp`.`Id_RoomPassenger`, 'RoomPassenger_IsNotValid', `rp`.`RoomPassenger_IsNotValid`, 'RoomPassenger_Status', `rp`.`RoomPassenger_Status`, 'Id_BookingExtraPassenger', `bep`.`Id_BookingExtraPassenger`, 'BookingExtraPassenger_DateEntryStart', `bep`.`BookingExtraPassenger_DateEntryStart`, 'BookingExtraPassenger_DateEntryEnd', `bep`.`BookingExtraPassenger_DateEntryEnd`, 'BookingExtraPassenger_DatePurchase', `bep`.`BookingExtraPassenger_DatePurchase`, 'BookingExtraPassenger_Remark', `bep`.`BookingExtraPassenger_Remark`, 'BookingExtraPassenger_Status', `bep`.`BookingExtraPassenger_Status`, 'Id_BookingTourPassenger', `btp`.`Id_BookingTourPassenger`, 'BookingTourPassenger_Remark', `btp`.`BookingTourPassenger_Remark`, 'BookingTourPassenger_Price', `btp`.`BookingTourPassenger_Price`, 'BookingTourPassenger_Full', `btp`.`BookingTourPassenger_Full`, 'BookingTourPassenger_ValidGroups', `btp`.`BookingTourPassenger_ValidGroups`, 'BookingTourPassenger_NoValidGroups', `btp`.`BookingTourPassenger_NoValidGroups`, 'BookingTourPassenger_Status', `btp`.`BookingTourPassenger_Status`, 'BookingTourPassenger_PaymentStatus', `btp`.`BookingTourPassenger_PaymentStatus`, 'Id_Passenger', `p`.`Id_Passenger`, 'Passenger_Name', `p`.`Passenger_Name`, 'Passenger_LastName', `p`.`Passenger_LastName`, 'Passenger_Gender', `p`.`Passenger_Gender`, 'Passenger_DOB', `p`.`Passenger_DOB`, 'Passenger_NoDocument', `p`.`Passenger_NoDocument`, 'Passenger_Status', `p`.`Passenger_Status`, 'Passenger_PaymentStatus', `p`.`Passenger_PaymentStatus`, 'Passenger_Age', YEAR(CURDATE())-YEAR(`p`.`Passenger_DOB`) + IF(DATE_FORMAT(CURDATE(),'%m-%d') > DATE_FORMAT(`p`.`Passenger_DOB`,'%m-%d'), 0 , -1 ), 'Id_UserCountry', `uc`.`Id_UserCountry`, 'UserCountry_Name', `uc`.`UserCountry_Name`, 'UserCountry_Code', `uc`.`UserCountry_Code`, 'UserCountry_Img', `uc`.`UserCountry_Img`, 'Id_TypeDocument', `td`.`Id_TypeDocument`, 'TypeDocument_Name', `td`.`TypeDocument_Name`, 'TypeDocument_Abrv', `td`.`TypeDocument_Abrv`, 'Invoice_Total', `i`.`Invoice_Total`, 'Invoice_TotalPay', `i`.`Invoice_TotalPay`, 'Invoice_TotalPending', `i`.`Invoice_TotalPending` )) SEPARATOR ','), ''), ']' ) AS `PassengerList`, (COUNT(`rp`.`Id_RoomPassenger`)) AS `PassengerCount` FROM `t_room` `r` FORCE INDEX FOR ORDER BY (`INDEX_Room_RequestDateStart`) INNER JOIN `t_type_room` `tr` ON `tr`.`Id_TypeRoom` = `r`.`Id_TypeRoom` INNER JOIN `t_booking_extra` `be` ON `be`.`Id_BookingExtra` = `r`.`Id_BookingExtra` INNER JOIN `t_extra` `e` ON `e`.`Id_Extra` = `be`.`Id_Extra` INNER JOIN `t_booking_tour` `bt` ON `bt`.`Id_BookingTour` = `be`.`Id_BookingTour` INNER JOIN `t_booking` `b` ON `b`.`Id_Booking` = `bt`.`Id_Booking` INNER JOIN `t_tour` `t` ON `t`.`Id_Tour` = `bt`.`Id_Tour` INNER JOIN `t_package` `pk` ON `pk`.`Id_Package` = `bt`.`Id_Package` LEFT JOIN `t_provider` `pr` ON `pr`.`Id_Provider` = `r`.`Id_Provider` LEFT JOIN `t_room_passenger` `rp` ON `rp`.`Id_Room` = `r`.`Id_Room` AND `rp`.`RoomPassenger_Status` <> 0 LEFT JOIN `t_booking_extra_passenger` `bep` ON `bep`.`Id_BookingExtraPassenger` = `rp`.`Id_BookingExtraPassenger` LEFT JOIN `t_booking_tour_passenger` `btp` ON `btp`.`Id_BookingTourPassenger` = `bep`.`Id_BookingTourPassenger` LEFT JOIN `t_passenger` `p` ON `p`.`Id_Passenger` = `btp`.`Id_Passenger` LEFT JOIN `t_invoice` `i` ON `i`.`Id_Passenger` = `p`.`Id_Passenger` LEFT JOIN `t_user_country` `uc` ON `uc`.`Id_UserCountry` = `p`.`Id_UserCountry` LEFT JOIN `t_type_document` `td` ON `td`.`Id_TypeDocument` = `p`.`Id_TypeDocument` WHERE `e`.`Id_TypeExtra` = 4 AND `r`.`Room_Status` <> 0 AND ( `rp`.`Id_RoomPassenger` IS NULL OR `bep`.`BookingExtraPassenger_Status` = 2 OR ( `bep`.`BookingExtraPassenger_Status` = 1 AND `btp`.`BookingTourPassenger_Status` IN (1, 2, 3) AND `b`.`Booking_Status` IN (1, 2) AND ( `b`.`Booking_ManuallyConfirmed` = 2 OR `btp`.`BookingTourPassenger_PaymentStatus` = 2 ) ) ) AND ((1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1 = ?) AND (1 = ?) AND (`r`.`Id_Room` = ?) AND (`r`.`Room_RequestDateStart` BETWEEN ? AND ?)) GROUP BY `r`.`Id_Room` ORDER BY `r`.`Room_RequestDateStart` desc, `t`.`Id_Tour` ASC, `b`.`Id_Booking` ASC, `r`.`Id_Room` ASC LIMIT 50 OFFSET 0 -- binds: ["1","1",1,"2024-01-01 00:00:00","2030-12-31 23:59:59"] ===== room-con-pax / count ===== SELECT STRAIGHT_JOIN COUNT(DISTINCT `r`.`Id_Room`) as `total` FROM `t_room` `r` INNER JOIN `t_type_room` `tr` ON `tr`.`Id_TypeRoom` = `r`.`Id_TypeRoom` INNER JOIN `t_booking_extra` `be` ON `be`.`Id_BookingExtra` = `r`.`Id_BookingExtra` INNER JOIN `t_extra` `e` ON `e`.`Id_Extra` = `be`.`Id_Extra` INNER JOIN `t_booking_tour` `bt` ON `bt`.`Id_BookingTour` = `be`.`Id_BookingTour` INNER JOIN `t_booking` `b` ON `b`.`Id_Booking` = `bt`.`Id_Booking` INNER JOIN `t_tour` `t` ON `t`.`Id_Tour` = `bt`.`Id_Tour` INNER JOIN `t_package` `pk` ON `pk`.`Id_Package` = `bt`.`Id_Package` LEFT JOIN `t_provider` `pr` ON `pr`.`Id_Provider` = `r`.`Id_Provider` LEFT JOIN `t_room_passenger` `rp` ON `rp`.`Id_Room` = `r`.`Id_Room` AND `rp`.`RoomPassenger_Status` <> 0 LEFT JOIN `t_booking_extra_passenger` `bep` ON `bep`.`Id_BookingExtraPassenger` = `rp`.`Id_BookingExtraPassenger` LEFT JOIN `t_booking_tour_passenger` `btp` ON `btp`.`Id_BookingTourPassenger` = `bep`.`Id_BookingTourPassenger` WHERE `e`.`Id_TypeExtra` = 4 AND `r`.`Room_Status` <> 0 AND ( `rp`.`Id_RoomPassenger` IS NULL OR `bep`.`BookingExtraPassenger_Status` = 2 OR ( `bep`.`BookingExtraPassenger_Status` = 1 AND `btp`.`BookingTourPassenger_Status` IN (1, 2, 3) AND `b`.`Booking_Status` IN (1, 2) AND ( `b`.`Booking_ManuallyConfirmed` = 2 OR `btp`.`BookingTourPassenger_PaymentStatus` = 2 ) ) ) AND ((1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1 = ?) AND (1 = ?) AND (`r`.`Id_Room` = ?) AND (`r`.`Room_RequestDateStart` BETWEEN ? AND ?)) -- binds: ["1","1",1,"2024-01-01 00:00:00","2030-12-31 23:59:59"] ===== room-pax-cancelado / list ===== SELECT STRAIGHT_JOIN `r`.`Id_Room`, `r`.`Room_Date`, `r`.`Room_RequestDateStart`, `r`.`Room_RequestDateEnd`, `r`.`Room_RequestLastDateStart`, `r`.`Room_RequestLastDateEnd`, `r`.`Room_ReservationDateAlarm`, `r`.`Room_ReservationDateFinal`, `r`.`Room_Observation`, `r`.`Room_BuyPrice`, `r`.`Room_Paid`, `r`.`Room_BuyDateStart`, `r`.`Room_BuyDateEnd`, `r`.`Room_BuyDatePayment`, `r`.`Room_BuyCode`, `r`.`Room_Lock`, `r`.`Room_SoftLock`, `r`.`Room_PassengersAmount`, `r`.`Room_NoValidAmount`, `r`.`Room_DateChanged`, `r`.`Room_Status`, `r`.`Id_Booking`, `r`.`Id_TypeRoom`, `r`.`Id_BookingExtra`, `r`.`Id_Provider`, `r`.`Id_Provider_Buy`, `r`.`Id_Admin`, `r`.`Id_Admin_Validated`, `r`.`Id_PaxInformationRoom`, `r`.`Id_TypePayment`, `tr`.`Id_TypeRoom`, `tr`.`TypeRoom_Name`, `tr`.`TypeRoom_Abrv`, `tr`.`TypeRoom_Capacity`, `tr`.`TypeRoom_Status`, `be`.`Id_BookingExtra`, `be`.`BookingExtra_Quantity`, `be`.`BookingExtra_Price`, `be`.`BookingExtra_Total`, `be`.`BookingExtra_Feature`, `be`.`BookingExtra_Included`, `be`.`BookingExtra_Mode`, `e`.`Id_Extra`, `e`.`Extra_Name`, `bt`.`Id_BookingTour`, `bt`.`BookingTour_DateStart`, `bt`.`BookingTour_PickUp`, `bt`.`BookingTour_Type`, `bt`.`BookingTour_NoPax`, `bt`.`BookingTour_Name`, `bt`.`BookingTour_TourName`, `bt`.`BookingTour_FeeType`, `bt`.`BookingTour_Full`, `bt`.`BookingTour_MinimumFull`, `bt`.`BookingTour_Group`, `bt`.`BookingTour_TourDuration`, `bt`.`BookingTour_DateBriefing`, `bt`.`BookingTour_PlaceBriefing`, `bt`.`BookingTour_UseBookingPlaceBriefing`, `bt`.`BookingTour_Status`, `bt`.`Id_Booking`, `bt`.`BookingTour_Date`, `bt`.`Id_Package`, `bt`.`Id_Tour`, `bt`.`Id_LodgingPlace_PickUp`, `bt`.`Id_LodgingPlace_DropOff`, `t`.`Id_Tour`, `t`.`Tour_Name`, `t`.`Tour_Code`, `t`.`Tour_Color`, `pk`.`Id_Package`, `pk`.`Package_Name`, `pk`.`Package_Code`, `r`.`Id_Provider`, `pr`.`Provider_BusinessName`, `pr`.`Provider_NickName`, `pr`.`Provider_Phone`, `pr`.`Provider_ImgProfile_Thumb`, `b`.`Id_Booking`, `b`.`Booking_Date`, `b`.`Booking_DateConfirm`, `b`.`Booking_DateStart`, `b`.`Booking_DateBriefing`, `b`.`Booking_PlaceBriefing`, `b`.`Booking_Code`, `b`.`Booking_HistoryChanges`, `b`.`Booking_Hotel`, `b`.`Booking_Observation`, `b`.`Booking_InternalRemark`, `b`.`Booking_Receipt`, `b`.`Booking_Type`, `b`.`Booking_LastMinute`, `b`.`Booking_Assigned`, `b`.`Booking_Status`, `b`.`Booking_ManuallyConfirmed`, `b`.`Booking_InPerson`, `b`.`Booking_PassengerAmount`, `b`.`Booking_CheckedInPassengers`, `b`.`Booking_LastPassenger`, `b`.`Booking_Token`, `b`.`Id_Booking_Replace`, `b`.`Id_Admin`, `b`.`Id_GuideLanguage`, `b`.`Id_Language`, `b`.`Id_User`, `b`.`Id_User_Additional`, `b`.`Id_BookingRelationship`, `b`.`Id_BookingReferredFrom`, CONCAT('[', IFNULL(GROUP_CONCAT(DISTINCT IF(`rp`.`Id_RoomPassenger` IS NULL, NULL, JSON_OBJECT( 'Id_RoomPassenger', `rp`.`Id_RoomPassenger`, 'RoomPassenger_IsNotValid', `rp`.`RoomPassenger_IsNotValid`, 'RoomPassenger_Status', `rp`.`RoomPassenger_Status`, 'Id_BookingExtraPassenger', `bep`.`Id_BookingExtraPassenger`, 'BookingExtraPassenger_DateEntryStart', `bep`.`BookingExtraPassenger_DateEntryStart`, 'BookingExtraPassenger_DateEntryEnd', `bep`.`BookingExtraPassenger_DateEntryEnd`, 'BookingExtraPassenger_DatePurchase', `bep`.`BookingExtraPassenger_DatePurchase`, 'BookingExtraPassenger_Remark', `bep`.`BookingExtraPassenger_Remark`, 'BookingExtraPassenger_Status', `bep`.`BookingExtraPassenger_Status`, 'Id_BookingTourPassenger', `btp`.`Id_BookingTourPassenger`, 'BookingTourPassenger_Remark', `btp`.`BookingTourPassenger_Remark`, 'BookingTourPassenger_Price', `btp`.`BookingTourPassenger_Price`, 'BookingTourPassenger_Full', `btp`.`BookingTourPassenger_Full`, 'BookingTourPassenger_ValidGroups', `btp`.`BookingTourPassenger_ValidGroups`, 'BookingTourPassenger_NoValidGroups', `btp`.`BookingTourPassenger_NoValidGroups`, 'BookingTourPassenger_Status', `btp`.`BookingTourPassenger_Status`, 'BookingTourPassenger_PaymentStatus', `btp`.`BookingTourPassenger_PaymentStatus`, 'Id_Passenger', `p`.`Id_Passenger`, 'Passenger_Name', `p`.`Passenger_Name`, 'Passenger_LastName', `p`.`Passenger_LastName`, 'Passenger_Gender', `p`.`Passenger_Gender`, 'Passenger_DOB', `p`.`Passenger_DOB`, 'Passenger_NoDocument', `p`.`Passenger_NoDocument`, 'Passenger_Status', `p`.`Passenger_Status`, 'Passenger_PaymentStatus', `p`.`Passenger_PaymentStatus`, 'Passenger_Age', YEAR(CURDATE())-YEAR(`p`.`Passenger_DOB`) + IF(DATE_FORMAT(CURDATE(),'%m-%d') > DATE_FORMAT(`p`.`Passenger_DOB`,'%m-%d'), 0 , -1 ), 'Id_UserCountry', `uc`.`Id_UserCountry`, 'UserCountry_Name', `uc`.`UserCountry_Name`, 'UserCountry_Code', `uc`.`UserCountry_Code`, 'UserCountry_Img', `uc`.`UserCountry_Img`, 'Id_TypeDocument', `td`.`Id_TypeDocument`, 'TypeDocument_Name', `td`.`TypeDocument_Name`, 'TypeDocument_Abrv', `td`.`TypeDocument_Abrv`, 'Invoice_Total', `i`.`Invoice_Total`, 'Invoice_TotalPay', `i`.`Invoice_TotalPay`, 'Invoice_TotalPending', `i`.`Invoice_TotalPending` )) SEPARATOR ','), ''), ']' ) AS `PassengerList`, (COUNT(`rp`.`Id_RoomPassenger`)) AS `PassengerCount` FROM `t_room` `r` FORCE INDEX FOR ORDER BY (`INDEX_Room_RequestDateStart`) INNER JOIN `t_type_room` `tr` ON `tr`.`Id_TypeRoom` = `r`.`Id_TypeRoom` INNER JOIN `t_booking_extra` `be` ON `be`.`Id_BookingExtra` = `r`.`Id_BookingExtra` INNER JOIN `t_extra` `e` ON `e`.`Id_Extra` = `be`.`Id_Extra` INNER JOIN `t_booking_tour` `bt` ON `bt`.`Id_BookingTour` = `be`.`Id_BookingTour` INNER JOIN `t_booking` `b` ON `b`.`Id_Booking` = `bt`.`Id_Booking` INNER JOIN `t_tour` `t` ON `t`.`Id_Tour` = `bt`.`Id_Tour` INNER JOIN `t_package` `pk` ON `pk`.`Id_Package` = `bt`.`Id_Package` LEFT JOIN `t_provider` `pr` ON `pr`.`Id_Provider` = `r`.`Id_Provider` LEFT JOIN `t_room_passenger` `rp` ON `rp`.`Id_Room` = `r`.`Id_Room` AND `rp`.`RoomPassenger_Status` <> 0 LEFT JOIN `t_booking_extra_passenger` `bep` ON `bep`.`Id_BookingExtraPassenger` = `rp`.`Id_BookingExtraPassenger` LEFT JOIN `t_booking_tour_passenger` `btp` ON `btp`.`Id_BookingTourPassenger` = `bep`.`Id_BookingTourPassenger` LEFT JOIN `t_passenger` `p` ON `p`.`Id_Passenger` = `btp`.`Id_Passenger` LEFT JOIN `t_invoice` `i` ON `i`.`Id_Passenger` = `p`.`Id_Passenger` LEFT JOIN `t_user_country` `uc` ON `uc`.`Id_UserCountry` = `p`.`Id_UserCountry` LEFT JOIN `t_type_document` `td` ON `td`.`Id_TypeDocument` = `p`.`Id_TypeDocument` WHERE `e`.`Id_TypeExtra` = 4 AND `r`.`Room_Status` <> 0 AND ( `rp`.`Id_RoomPassenger` IS NULL OR `bep`.`BookingExtraPassenger_Status` = 2 OR ( `bep`.`BookingExtraPassenger_Status` = 1 AND `btp`.`BookingTourPassenger_Status` IN (1, 2, 3) AND `b`.`Booking_Status` IN (1, 2) AND ( `b`.`Booking_ManuallyConfirmed` = 2 OR `btp`.`BookingTourPassenger_PaymentStatus` = 2 ) ) ) AND ((1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1 = ?) AND (1 = ?) AND (`r`.`Id_Room` = ?) AND (`r`.`Room_RequestDateStart` BETWEEN ? AND ?)) GROUP BY `r`.`Id_Room` ORDER BY `r`.`Room_RequestDateStart` desc, `t`.`Id_Tour` ASC, `b`.`Id_Booking` ASC, `r`.`Id_Room` ASC LIMIT 50 OFFSET 0 -- binds: ["1","1",21379,"2024-01-01 00:00:00","2030-12-31 23:59:59"] ===== room-pax-cancelado / count ===== SELECT STRAIGHT_JOIN COUNT(DISTINCT `r`.`Id_Room`) as `total` FROM `t_room` `r` INNER JOIN `t_type_room` `tr` ON `tr`.`Id_TypeRoom` = `r`.`Id_TypeRoom` INNER JOIN `t_booking_extra` `be` ON `be`.`Id_BookingExtra` = `r`.`Id_BookingExtra` INNER JOIN `t_extra` `e` ON `e`.`Id_Extra` = `be`.`Id_Extra` INNER JOIN `t_booking_tour` `bt` ON `bt`.`Id_BookingTour` = `be`.`Id_BookingTour` INNER JOIN `t_booking` `b` ON `b`.`Id_Booking` = `bt`.`Id_Booking` INNER JOIN `t_tour` `t` ON `t`.`Id_Tour` = `bt`.`Id_Tour` INNER JOIN `t_package` `pk` ON `pk`.`Id_Package` = `bt`.`Id_Package` LEFT JOIN `t_provider` `pr` ON `pr`.`Id_Provider` = `r`.`Id_Provider` LEFT JOIN `t_room_passenger` `rp` ON `rp`.`Id_Room` = `r`.`Id_Room` AND `rp`.`RoomPassenger_Status` <> 0 LEFT JOIN `t_booking_extra_passenger` `bep` ON `bep`.`Id_BookingExtraPassenger` = `rp`.`Id_BookingExtraPassenger` LEFT JOIN `t_booking_tour_passenger` `btp` ON `btp`.`Id_BookingTourPassenger` = `bep`.`Id_BookingTourPassenger` WHERE `e`.`Id_TypeExtra` = 4 AND `r`.`Room_Status` <> 0 AND ( `rp`.`Id_RoomPassenger` IS NULL OR `bep`.`BookingExtraPassenger_Status` = 2 OR ( `bep`.`BookingExtraPassenger_Status` = 1 AND `btp`.`BookingTourPassenger_Status` IN (1, 2, 3) AND `b`.`Booking_Status` IN (1, 2) AND ( `b`.`Booking_ManuallyConfirmed` = 2 OR `btp`.`BookingTourPassenger_PaymentStatus` = 2 ) ) ) AND ((1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1) AND (1 = ?) AND (1 = ?) AND (`r`.`Id_Room` = ?) AND (`r`.`Room_RequestDateStart` BETWEEN ? AND ?)) -- binds: ["1","1",21379,"2024-01-01 00:00:00","2030-12-31 23:59:59"]