php - 预订检查空房情况
问题描述
我正在创建一个预订房间的系统。我需要找出日期范围内的可用房间数量。
该房间应在结帐日期可用。
到目前为止,我只能找到日期范围内的订单,但即使是那些也不能正常工作。
这项工作 2020-06-27 - 2020-07-05
这不起作用 2020-06-28 - 2020-07-05
目标是找出日期范围内的免费房间数量,然后提供订购服务。
我在这里阅读了很多主题,但我没有提出解决方案。
我使用 PHP 和 MySQL
-- phpMyAdmin SQL Dump
-- version 4.7.4
-- https://www.phpmyadmin.net/
--
-- Počítač: 127.0.0.1
-- Vytvořeno: Sob 27. čen 2020, 10:24
-- Verze serveru: 10.1.28-MariaDB
-- Verze PHP: 7.2.1
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Databáze: `rezervace`
--
-- --------------------------------------------------------
--
-- Struktura tabulky `booking_orders`
--
CREATE TABLE `booking_orders` (
`id` int(11) NOT NULL,
`number` int(11) NOT NULL,
`checkin` date NOT NULL,
`checkout` date NOT NULL,
`first_name` varchar(255) COLLATE utf8_czech_ci NOT NULL,
`last_name` varchar(255) COLLATE utf8_czech_ci NOT NULL,
`email` varchar(255) COLLATE utf8_czech_ci NOT NULL,
`phone` varchar(20) COLLATE utf8_czech_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;
--
-- Vypisuji data pro tabulku `booking_orders`
--
INSERT INTO `booking_orders` (`id`, `number`, `checkin`, `checkout`, `first_name`, `last_name`, `email`, `phone`) VALUES
(74, 0, '2020-06-27', '2020-06-28', '', '', '', ''),
(75, 0, '2020-06-27', '2020-06-29', '', '', '', ''),
(76, 0, '2020-06-27', '2020-07-01', '', '', '', ''),
(77, 0, '2020-06-28', '2020-06-29', '', '', '', '');
-- --------------------------------------------------------
--
-- Struktura tabulky `booking_order_room`
--
CREATE TABLE `booking_order_room` (
`id` int(4) NOT NULL,
`order_id` int(4) NOT NULL,
`room_id` int(4) NOT NULL,
`product_name` varchar(255) COLLATE utf8_czech_ci NOT NULL,
`quantity` int(3) NOT NULL,
`person` int(2) NOT NULL,
`price` varchar(10) COLLATE utf8_czech_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;
--
-- Vypisuji data pro tabulku `booking_order_room`
--
INSERT INTO `booking_order_room` (`id`, `order_id`, `room_id`, `product_name`, `quantity`, `person`, `price`) VALUES
(81, 74, 7, 'Standard', 1, 1, '50'),
(82, 75, 7, 'Standard', 1, 1, '50'),
(83, 76, 7, 'Standard', 1, 1, '50'),
(84, 77, 7, 'Standard', 1, 1, '50');
-- --------------------------------------------------------
--
-- Struktura tabulky `booking_rooms`
--
CREATE TABLE `booking_rooms` (
`id` int(11) NOT NULL,
`name` varchar(255) COLLATE utf8_czech_ci NOT NULL,
`quantity` int(3) NOT NULL,
`input` varchar(255) COLLATE utf8_czech_ci NOT NULL,
`price` int(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;
--
-- Vypisuji data pro tabulku `booking_rooms`
--
INSERT INTO `booking_rooms` (`id`, `name`, `quantity`, `input`, `price`) VALUES
(7, 'Standard', 2, 'standard1', 50),
(8, 'Deluxe', 4, 'deluxe2', 100);
--
-- Klíče pro exportované tabulky
--
--
-- Klíče pro tabulku `booking_orders`
--
ALTER TABLE `booking_orders`
ADD PRIMARY KEY (`id`);
--
-- Klíče pro tabulku `booking_order_room`
--
ALTER TABLE `booking_order_room`
ADD PRIMARY KEY (`id`);
--
-- Klíče pro tabulku `booking_rooms`
--
ALTER TABLE `booking_rooms`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT pro tabulky
--
--
-- AUTO_INCREMENT pro tabulku `booking_orders`
--
ALTER TABLE `booking_orders`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=79;
--
-- AUTO_INCREMENT pro tabulku `booking_order_room`
--
ALTER TABLE `booking_order_room`
MODIFY `id` int(4) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=85;
--
-- AUTO_INCREMENT pro tabulku `booking_rooms`
--
ALTER TABLE `booking_rooms`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;
COMMIT;
http://sqlfiddle.com/#!9/bfbabe/1
SELECT checkin, checkout, r.name as name, SUM(i.quantity) as qty
FROM booking_orders o
LEFT JOIN booking_order_room i on o.id = i.order_id
LEFT JOIN booking_rooms r on i.room_id = r.id
WHERE (checkin >= '2020-06-28' and checkout <= '2020-07-05')
OR (checkin <= '2020-06-28' and checkout >= '2020-07-05')
GROUP BY i.room_id, o.id
解决方案
我附上了我最终使用的解决方案。它可能会以不同的方式完成。
DROP TABLE IF EXISTS t1;
CREATE TEMPORARY TABLE t1 as (
SELECT r.name as name, r.id, sum(i.quantity) as qty
FROM booking_orders o
LEFT JOIN booking_order_room i on o.id = i.order_id
LEFT JOIN booking_rooms r on i.room_id = r.id
where
(checkin<'2020-07-17' and checkout>='2020-07-17') -- overlap at the end
OR (checkin<='2020-07-15' and checkout>'2020-07-15') -- overlap at the start
OR (checkin>='2020-07-15' and checkout<='2020-07-17') -- complete overlap
GROUP BY r.id
);
SELECT br.name as name, br.quantity as quantity, br.input as input, br. price as price, t1.qty
FROM booking_rooms br
LEFT JOIN t1 ON t1.id = br.room_id
ORDER BY br.name DESC
推荐阅读
- javascript - 调用函数时的角度5奇怪的行为
- vba - Excel VBA - 为打开文件创建循环
- ssis - 表达式生成器日期分配
- swift - Swift:tableView.reloadData() 表中没有数据
- fixed - Tableau 子集百分比计算
- javascript - 升级的 AngularJS 组件需要另一个升级的组件在使用 Angular 内容投影时找不到父控制器
- c# - 将 iagedList 匿名类型转换为 iagedList 模型
- java - Java Concurrency volatile 用于读取 同步用于写入
- python - Python-根据日期或时间间隔将 csv 文件拆分为不同的数据帧
- node.js - node Request-promis-native 永远不会返回 then 或 catch 类型脚本中的部分