/*
SQLyog Ultimate v8.71 
MySQL - 5.0.95 : Database - roomx
*********************************************************************
*/




CREATE DATABASE /*!32312 IF NOT EXISTS*/`roomx` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `roomx`;

/*Table structure for table `booking` */


CREATE TABLE `booking` (
  `id` int(10) NOT NULL auto_increment,
  `room_id` varchar(20) NOT NULL,
  `guest_id` varchar(30) NOT NULL,
  `date_ci` datetime NOT NULL,
  `date_co` datetime NOT NULL,
  `num_guest` int(2) NOT NULL default '1',
  KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `booking` */

/*Table structure for table `calendar` */


CREATE TABLE `calendar` (
  `datefield` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `calendar` */

insert  into `calendar`(`datefield`) values ('2013-10-12');

/*Table structure for table `config` */


CREATE TABLE `config` (
  `o_m` varchar(10) NOT NULL default 'Hotel',
  `locked` tinyint(1) NOT NULL default '1',
  `cbr` tinyint(1) NOT NULL default '1',
  `rmbc` tinyint(1) NOT NULL default '1',
  `reception` varchar(5) NOT NULL,
  `company` varchar(512) NOT NULL,
  `clean` varchar(6) NOT NULL,
  `minibar` varchar(6) NOT NULL,
  `logo` varchar(100) NOT NULL,
  `logo64` mediumtext NOT NULL,
  `vat_1` decimal(5,2) NOT NULL default '19.60',
  `vat_2` decimal(5,2) NOT NULL default '5.50',
  `rounded` tinyint(1) NOT NULL default '0',
  `mail` varchar(50) NOT NULL,
  `version` varchar(10) NOT NULL,
  UNIQUE KEY `o_m` (`o_m`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `config` */

insert  into `config`(`o_m`,`locked`,`cbr`,`rmbc`,`reception`,`company`,`clean`,`minibar`,`logo`,`logo64`,`vat_1`,`vat_2`,`rounded`,`mail`,`version`) values ('Hotel',1,1,1,'100','Hotel California\r\n56 Route de Vannes\r\n56400 La Roche Bernard\r\nTel : (33)297565656\r\nEmail: hotel.california@orange.fr','*36','*37','modules/rx_general/images/logo-roomx.png','data:image/png;base64,[BLOB]','19.60','5.50',0,'admin@example.com','1.0.0-91');

/*Table structure for table `guest` */


CREATE TABLE `guest` (
  `id` int(10) NOT NULL auto_increment,
  `first_name` varchar(20) NOT NULL,
  `last_name` varchar(30) NOT NULL,
  `address` varchar(60) NOT NULL,
  `cp` varchar(15) NOT NULL,
  `city` varchar(20) NOT NULL,
  `phone` varchar(15) NOT NULL,
  `mobile` varchar(15) NOT NULL,
  `fax` varchar(15) NOT NULL,
  `mail` varchar(30) NOT NULL,
  KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `guest` */

/*Table structure for table `minibar` */


CREATE TABLE `minibar` (
  `digit` int(1) NOT NULL,
  `label` varchar(50) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `vat` decimal(5,2) NOT NULL default '0.00'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `minibar` */

insert  into `minibar`(`digit`,`label`,`price`,`vat`) values (1,'','0.00','0.00'),(2,'','0.00','0.00'),(3,'','0.00','0.00'),(4,'','0.00','0.00'),(5,'','0.00','0.00'),(6,'','0.00','0.00'),(7,'','0.00','0.00'),(8,'','0.00','0.00'),(9,'','0.00','0.00'),(0,'','0.00','0.00');

/*Table structure for table `models` */


CREATE TABLE `models` (
  `room_model` varchar(20) NOT NULL,
  `room_price` decimal(12,2) NOT NULL default '0.00',
  `room_guest` decimal(12,2) NOT NULL default '0.00',
  `room_vat` decimal(5,2) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `models` */

/*Table structure for table `register` */


CREATE TABLE `register` (
  `id` int(11) NOT NULL auto_increment,
  `room_id` varchar(15) NOT NULL,
  `guest_id` varchar(10) NOT NULL,
  `num_guest` int(1) NOT NULL default '1',
  `date_ci` datetime NOT NULL,
  `date_co` datetime NOT NULL,
  `status` tinyint(1) NOT NULL,
  `billing_file` varchar(100) NOT NULL,
  `paid` tinyint(1) NOT NULL,
  `total_room` decimal(12,2) NOT NULL,
  `total_bar` decimal(12,2) NOT NULL,
  `total_call` decimal(12,2) NOT NULL,
  `total_billing` decimal(12,2) NOT NULL,
  `remote_folio` varchar(255) NOT NULL,
  KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `register` */

/*Table structure for table `rooms` */


CREATE TABLE `rooms` (
  `id` int(11) NOT NULL auto_increment,
  `room_name` varchar(20) NOT NULL,
  `guest_name` varchar(60) NOT NULL,
  `model` varchar(20) NOT NULL,
  `extension` varchar(5) NOT NULL,
  `groupe` varchar(20) NOT NULL,
  `free` tinyint(1) NOT NULL default '1',
  `clean` tinyint(1) NOT NULL default '1',
  `mini_bar` varchar(100) NOT NULL,
  `RACI` varchar(255) NOT NULL,
  `RACO` varchar(255) NOT NULL,
  KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `rooms` */

/*Table structure for table `status` */


CREATE TABLE `status` (
  `date` date NOT NULL,
  `free` int(4) NOT NULL,
  `busy` int(4) NOT NULL,
  `booking` int(4) NOT NULL,
  UNIQUE KEY `date` (`date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `status` */

/* Procedure structure for procedure `fill_calendar` */

/*!50003 DROP PROCEDURE IF EXISTS  `fill_calendar` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `fill_calendar`(start_date DATE, end_date DATE)
BEGIN
  DECLARE crt_date DATE;
  SET crt_date=start_date;
  WHILE crt_date < end_date DO
    INSERT INTO calendar VALUES(crt_date);
    SET crt_date = ADDDATE(crt_date, INTERVAL 1 DAY);
  END WHILE;
END */$$
DELIMITER ;

