bookings, MySQL - rextester

Language: Layout:

drop table if exists ___SalesTaxes;
drop table if exists ___Invoices;
drop table if exists ___Inventory;
drop table if exists ___Bookings;
drop table if exists ___BillableDatas;

--
-- Structure de la table `___BillableDatas`
--

CREATE TABLE IF NOT EXISTS `___BillableDatas` (
  `BIL_Id` int(11) NOT NULL AUTO_INCREMENT,
  `BIL_HotelId` varchar(5) NOT NULL,
  `BIL_BookingId` int(5) NOT NULL,
  `BIL_Date` date NOT NULL,
  `BIL_Item` int(9) NOT NULL,
  `BIL_Rate` decimal(10,2) NOT NULL,
  `BIL_Type` enum('Night','Item') NOT NULL,
  `BIL_Quantity` tinyint(2) NOT NULL,
  `BIL_ApplicableTaxes` varchar(50) NOT NULL,
  `BIL_Creationdate` datetime NOT NULL,
  `BIL_By` int(10) NOT NULL,
  `BIL_Status` enum('NotCharged','Charged','Cancelled') NOT NULL,
  UNIQUE KEY `CHA_Id` (`BIL_Id`),
  KEY `id` (`BIL_Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=64 ;

--
-- Contenu de la table `___BillableDatas`
--

INSERT INTO `___BillableDatas` (`BIL_Id`, `BIL_HotelId`, `BIL_BookingId`, `BIL_Date`, `BIL_Item`, `BIL_Rate`, `BIL_Type`, `BIL_Quantity`, `BIL_ApplicableTaxes`, `BIL_Creationdate`, `BIL_By`, `BIL_Status`) VALUES
(33, 'AAA00', 15, '2017-03-17', 0, '130.00', 'Night', 1, '', '2017-03-12 22:42:14', 0, 'NotCharged'),
(31, 'AAA00', 15, '2017-03-15', 0, '130.00', 'Night', 1, '', '2017-03-12 22:42:14', 0, 'NotCharged'),
(32, 'AAA00', 15, '2017-03-16', 0, '130.00', 'Night', 1, '', '2017-03-12 22:42:14', 0, 'NotCharged'),
(45, 'AAA00', 15, '2017-11-01', 0, '100.00', 'Item', 5, '', '2017-03-12 22:43:06', 0, 'NotCharged'),
(50, 'AAA00', 15, '2017-03-16', 17, '12.00', 'Item', 2, '11', '2017-03-18 12:15:46', 1, 'NotCharged');

-- --------------------------------------------------------

--
-- Structure de la table `___Bookings`
--

CREATE TABLE IF NOT EXISTS `___Bookings` (
  `BOO_Id` int(11) NOT NULL AUTO_INCREMENT,
  `BOO_HotelId` varchar(5) NOT NULL,
  `BOO_RoomId` int(10) NOT NULL,
  `BOO_ClientId` int(10) NOT NULL,
  `BOO_CompanyId` int(10) NOT NULL,
  `BOO_BillingId` int(10) NOT NULL,
  `BOO_BillingInstruction` enum('None','Accommodation','AccommodationFood','Full') NOT NULL DEFAULT 'None',
  `BOO_DateCI` date NOT NULL,
  `BOO_DateCO` date NOT NULL,
  `BOO_EstimatedTimeOfArrival` enum('Unspecified','Morning','Noon','Afternoon','Evening','Night') NOT NULL DEFAULT 'Unspecified',
  `BOO_Movable` enum('Yes','No') NOT NULL,
  `BOO_CheckInDate` datetime NOT NULL,
  `BOO_CheckOutDate` datetime NOT NULL,
  `BOO_NumberAdults` tinyint(2) NOT NULL,
  `BOO_NumberChildren` tinyint(2) NOT NULL,
  `BOO_NumberBabies` tinyint(2) NOT NULL,
  `BOO_Source` int(5) NOT NULL,
  `BOO_Segmentation` int(5) NOT NULL,
  `BOO_CreationDate` datetime NOT NULL,
  `BOO_By` int(11) NOT NULL,
  `BOO_Status` enum('Confirmed','NotConfirmed','Option','Cancel','CheckIn','CheckOut') NOT NULL,
  UNIQUE KEY `BOO_Id` (`BOO_Id`),
  KEY `id` (`BOO_Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=22 ;

--
-- Contenu de la table `___Bookings`
--

INSERT INTO `___Bookings` (`BOO_Id`, `BOO_HotelId`, `BOO_RoomId`, `BOO_ClientId`, `BOO_CompanyId`, `BOO_BillingId`, `BOO_BillingInstruction`, `BOO_DateCI`, `BOO_DateCO`, `BOO_EstimatedTimeOfArrival`, `BOO_Movable`, `BOO_CheckInDate`, `BOO_CheckOutDate`, `BOO_NumberAdults`, `BOO_NumberChildren`, `BOO_NumberBabies`, `BOO_Source`, `BOO_Segmentation`, `BOO_CreationDate`, `BOO_By`, `BOO_Status`) VALUES
(15, 'AAA00', 1, 7, 107, 108, 'Accommodation', '2017-03-14', '2017-03-18', 'Noon', 'Yes', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 1, 0, 0, 12, 1, '2017-03-12 22:42:14', 0, 'NotConfirmed');

-- --------------------------------------------------------

--
-- Structure de la table `___Inventory`
--

CREATE TABLE IF NOT EXISTS `___Inventory` (
  `INV_Id` int(11) NOT NULL AUTO_INCREMENT,
  `INV_HotelId` varchar(5) NOT NULL,
  `INV_Name` varchar(50) NOT NULL,
  `INV_Rate` decimal(10,2) NOT NULL,
  `INV_RateType` enum('+','-','D') NOT NULL,
  `INV_Quantity` varchar(5) NOT NULL,
  `INV_ApplicableTaxes` varchar(100) NOT NULL,
  `INV_Creationdate` datetime NOT NULL,
  `INV_Status` enum('Active','Inactive') NOT NULL,
  UNIQUE KEY `INV_Id` (`INV_Id`),
  KEY `id` (`INV_Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=21 ;

--
-- Contenu de la table `___Inventory`
--

INSERT INTO `___Inventory` (`INV_Id`, `INV_HotelId`, `INV_Name`, `INV_Rate`, `INV_RateType`, `INV_Quantity`, `INV_ApplicableTaxes`, `INV_Creationdate`, `INV_Status`) VALUES
(17, 'AAA00', 'Petit-déjeuner', '12.00', '+', '', '11', '2014-06-11 15:37:38', 'Active'),
(20, 'AAA00', 'Fanta', '2.00', '+', '', '10,11,12', '2016-07-09 22:02:51', 'Active'),
(13, 'AAA00', 'Lit supplémentaire', '0.00', '+', '', '', '2014-06-11 13:43:55', 'Active'),
(18, 'AAA00', 'Coca', '3.00', '+', '', '', '2014-06-11 15:40:05', 'Active'),
(0, '', 'Debtor', '0.00', 'D', '', '', '2000-06-30 00:00:00', 'Active');

-- --------------------------------------------------------

--
-- Structure de la table `___Invoices`
--

CREATE TABLE IF NOT EXISTS `___Invoices` (
  `IVC_Id` int(11) NOT NULL AUTO_INCREMENT,
  `IVC_HotelId` varchar(5) NOT NULL,
  `IVC_BookingId` int(10) NOT NULL,
  `IVC_DateShown` date NOT NULL,
  `IVC_DueDate` date NOT NULL,
  `IVC_Elements` text NOT NULL,
  `IVC_Sum` decimal(10,2) NOT NULL,
  `IVC_CreationDate` datetime NOT NULL,
  `IVC_By` int(11) NOT NULL,
  `IVC_Status` enum('Unpayed','Payed','Pending','Debtor','Cancelled') NOT NULL,
  UNIQUE KEY `IVC_Id` (`IVC_Id`),
  KEY `id` (`IVC_Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=15 ;

--
-- Contenu de la table `___Invoices`
--

INSERT INTO `___Invoices` (`IVC_Id`, `IVC_HotelId`, `IVC_BookingId`, `IVC_DateShown`, `IVC_DueDate`, `IVC_Elements`, `IVC_Sum`, `IVC_CreationDate`, `IVC_By`, `IVC_Status`) VALUES
(14, 'AAA00', 15, '2017-03-22', '2017-04-21', '32,31,33', '0.00', '2017-03-22 06:05:11', 1, 'Unpayed');

-- --------------------------------------------------------

--
-- Structure de la table `___SalesTaxes`
--

CREATE TABLE IF NOT EXISTS `___SalesTaxes` (
  `STX_Id` int(11) NOT NULL AUTO_INCREMENT,
  `STX_HotelId` varchar(5) NOT NULL,
  `STX_TaxeName` varchar(50) NOT NULL,
  `STX_Amount` decimal(10,2) NOT NULL,
  `STX_AmountType` enum('Percentage','Money') NOT NULL,
  `STX_Creationdate` datetime NOT NULL,
  `STX_Status` enum('Active','Inactive') NOT NULL,
  UNIQUE KEY `STX_Id` (`STX_Id`),
  KEY `id` (`STX_Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=14 ;

--
-- Contenu de la table `___SalesTaxes`
--

INSERT INTO `___SalesTaxes` (`STX_Id`, `STX_HotelId`, `STX_TaxeName`, `STX_Amount`, `STX_AmountType`, `STX_Creationdate`, `STX_Status`) VALUES
(10, 'AAA00', 'TPS2', '14.00', 'Percentage', '2014-06-07 17:09:46', 'Active'),
(11, 'AAA00', 'TVA', '19.60', 'Percentage', '2014-07-15 11:56:47', 'Active'),
(12, 'AAA00', 'roo', '788.00', 'Percentage', '2016-07-04 23:21:33', 'Active');

SELECT    BIL_Id, IVC_Id, DATE_FORMAT(BIL_Date, "%Y-%m-%d") BIL_Date, BIL_Rate, BIL_Type, 
          BIL_Quantity, COALESCE(INV_Name, '') INV_Name, BIL_ApplicableTaxes
FROM      ___BillableDatas
JOIN      ___Invoices  
ON        ___BillableDatas.BIL_BookingId = ___Invoices.IVC_BookingId
JOIN      ___Bookings  
ON        ___BillableDatas.BIL_BookingId = ___Bookings.BOO_Id
LEFT JOIN ___Inventory 
ON        ___BillableDatas.BIL_Item = ___Inventory.INV_Id
LEFT JOIN ___SalesTaxes 
ON        FIND_IN_SET(BIL_ApplicableTaxes, STX_Id) > 0
WHERE     BIL_HotelId='AAA00' 
AND       IVC_Id=14
AND       FIND_IN_SET(BIL_Id, IVC_Elements) > 0 
ORDER BY  BIL_Id ASC
;

SELECT    BIL_Id, IVC_Id, DATE_FORMAT(BIL_Date, "%Y-%m-%d") BIL_Date, BIL_Rate, BIL_Type, BIL_Quantity,
          COALESCE(INV_Name, '') INV_Name, BIL_ApplicableTaxes,
          COALESCE((SELECT GROUP_CONCAT(STX_TaxeName)
                    FROM   ___SalesTaxes
                    WHERE  FIND_IN_SET(STX_Id, BIL_ApplicableTaxes) > 0
                  ), "0") AS ApplicableTaxesName,
          COALESCE((SELECT GROUP_CONCAT(STX_Amount)
                    FROM   ___SalesTaxes
                    WHERE  FIND_IN_SET(STX_Id, BIL_ApplicableTaxes) > 0
                  ), "0") AS ApplicableTaxesRate
FROM      ___BillableDatas
JOIN      ___Invoices  
ON        ___BillableDatas.BIL_BookingId = ___Invoices.IVC_BookingId
JOIN      ___Bookings  
ON        ___BillableDatas.BIL_BookingId = ___Bookings.BOO_Id
LEFT JOIN ___Inventory 
ON        ___BillableDatas.BIL_Item = ___Inventory.INV_Id
WHERE     BIL_HotelId='AAA00' 
AND       IVC_Id=14
AND       FIND_IN_SET(BIL_Id, IVC_Elements) > 0
ORDER BY  BIL_Id ASC
;

drop table if exists ___SalesTaxes;
drop table if exists ___Invoices;
drop table if exists ___Inventory;
drop table if exists ___Bookings;
drop table if exists ___BillableDatas;


absolute service time: 0,33 sec