-- MySQL dump 10.11
--
-- Host: localhost    Database: Beverage2
-- ------------------------------------------------------
-- Server version	5.0.70-log

/*!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 utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `Accounting`
--

DROP TABLE IF EXISTS `Accounting`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `Accounting` (
  `Id` int(10) unsigned NOT NULL auto_increment,
  `Time` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `Remark` varchar(255) default NULL,
  PRIMARY KEY  (`Id`),
  KEY `ITime` (`Time`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `Accounting`
--

LOCK TABLES `Accounting` WRITE;
/*!40000 ALTER TABLE `Accounting` DISABLE KEYS */;
INSERT INTO `Accounting` VALUES (1,'2008-11-17 19:55:59','Eröffnungsinventur');
/*!40000 ALTER TABLE `Accounting` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `Beverage`
--

DROP TABLE IF EXISTS `Beverage`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `Beverage` (
  `Id` int(10) unsigned NOT NULL auto_increment,
  `Manufacturer` varchar(255) default NULL,
  `FullName` varchar(255) default NULL,
  `ShortName` varchar(255) NOT NULL,
  `CaseId` int(10) unsigned NOT NULL,
  `BottleId` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`Id`),
  KEY `ICaseId` (`CaseId`),
  KEY `IBottleId` (`BottleId`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `Beverage`
--

LOCK TABLES `Beverage` WRITE;
/*!40000 ALTER TABLE `Beverage` DISABLE KEYS */;
INSERT INTO `Beverage` VALUES (1,'Kohm','Lamina Orangensaft','O-Saft',2,3),(2,'Kohm','Lamina Apfelsaft (klar)','A-Saft',2,3),(3,'Winkels','Alwa Apfelschorle','Apfelschorle',1,5),(4,'Winkels','Alwa Medium','Wasser',1,4),(5,'Winkels','Alwa Classic','Wasser',1,4),(6,'Rothaus','Tannenzäpfle','Zäpfle',5,1),(7,'Höpfner','Grape Radler','Grape',4,2),(8,'Privatbrauerei Peter','Bionade Holunder','Bionade',5,1),(9,'Coca Cola','Cola','Cola',1,1),(10,'Becks','Becks Pilsener','Becks',5,1);
/*!40000 ALTER TABLE `Beverage` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `BeverageInventory`
--

DROP TABLE IF EXISTS `BeverageInventory`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `BeverageInventory` (
  `Id` int(10) unsigned NOT NULL auto_increment,
  `AccountingId` int(10) unsigned NOT NULL,
  `BeverageId` int(10) unsigned NOT NULL,
  `Debit` smallint(5) unsigned default NULL COMMENT 'Number of botttles',
  `Credit` smallint(5) unsigned default NULL COMMENT 'Number of botttles',
  `Correction` decimal(6,2) NOT NULL default '0.00' COMMENT 'Monetary correction if value of bottles differ from\n             their price',
  PRIMARY KEY  (`Id`),
  KEY `IAccountingId` (`AccountingId`),
  KEY `IBeverageId` (`BeverageId`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `BeverageInventory`
--

LOCK TABLES `BeverageInventory` WRITE;
/*!40000 ALTER TABLE `BeverageInventory` DISABLE KEYS */;
INSERT INTO `BeverageInventory` VALUES (1,1,8,43,NULL,'0.00'),(2,1,5,15,NULL,'0.00'),(3,1,4,100,NULL,'0.00'),(4,1,2,38,NULL,'0.00'),(5,1,1,39,NULL,'0.00'),(6,1,7,34,NULL,'0.00'),(7,1,6,115,NULL,'0.00');
/*!40000 ALTER TABLE `BeverageInventory` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `BeveragePurchasePrice`
--

DROP TABLE IF EXISTS `BeveragePurchasePrice`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `BeveragePurchasePrice` (
  `Id` int(10) unsigned NOT NULL auto_increment,
  `BeverageId` int(10) unsigned NOT NULL,
  `Price` decimal(4,2) NOT NULL COMMENT 'Price for whole case without deposit',
  `From` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `To` timestamp NULL default NULL,
  PRIMARY KEY  (`Id`),
  KEY `ITimeSpan` (`From`,`To`),
  KEY `ITo` (`To`),
  KEY `IBeverageIdFrom` (`BeverageId`,`From`)
) ENGINE=MyISAM AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `BeveragePurchasePrice`
--

LOCK TABLES `BeveragePurchasePrice` WRITE;
/*!40000 ALTER TABLE `BeveragePurchasePrice` DISABLE KEYS */;
INSERT INTO `BeveragePurchasePrice` VALUES (1,5,'3.19','2008-06-10 22:00:00',NULL),(2,5,'2.99','2007-04-22 22:00:00','2008-06-09 22:00:00'),(3,4,'3.19','2008-06-10 22:00:00',NULL),(4,4,'2.99','2007-04-22 22:00:00','2008-06-09 22:00:00'),(5,3,'9.49','2008-04-09 22:00:00',NULL),(6,2,'5.99','2007-11-19 23:00:00',NULL),(7,2,'6.29','2007-04-22 22:00:00','2007-11-18 23:00:00'),(8,1,'6.99','2007-04-22 22:00:00',NULL),(9,7,'13.99','2007-04-09 22:00:00',NULL),(10,7,'13.49','2007-11-08 23:00:00','2008-04-08 22:00:00'),(11,7,'13.59','2007-04-22 22:00:00','2007-11-07 23:00:00'),(12,6,'13.99','2007-04-22 22:00:00',NULL),(13,8,'18.99','2008-07-03 22:00:00',NULL),(14,8,'13.49','2007-11-08 23:00:00','2008-07-02 22:00:00'),(15,9,'7.70','2008-11-09 23:00:00',NULL),(16,10,'8.58','2008-10-23 22:00:00',NULL);
/*!40000 ALTER TABLE `BeveragePurchasePrice` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `BeverageSalePrice`
--

DROP TABLE IF EXISTS `BeverageSalePrice`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `BeverageSalePrice` (
  `Id` int(10) unsigned NOT NULL auto_increment,
  `BeverageId` int(10) unsigned NOT NULL,
  `SaleTypeId` int(10) unsigned NOT NULL,
  `SaleQuantity` smallint(5) unsigned NOT NULL default '1',
  `Price` decimal(4,2) NOT NULL COMMENT 'Price for whole sale quantity without deposit',
  `From` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `To` timestamp NULL default NULL,
  PRIMARY KEY  (`Id`),
  KEY `ISaleTypeId` (`SaleTypeId`),
  KEY `ITimeSpan` (`From`,`To`),
  KEY `ITo` (`To`),
  KEY `IBevTypeFrom` (`BeverageId`,`SaleTypeId`,`From`)
) ENGINE=MyISAM AUTO_INCREMENT=38 DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `BeverageSalePrice`
--

LOCK TABLES `BeverageSalePrice` WRITE;
/*!40000 ALTER TABLE `BeverageSalePrice` DISABLE KEYS */;
INSERT INTO `BeverageSalePrice` VALUES (1,6,1,1,'0.60','2008-05-26 22:00:00','2008-11-08 23:00:00'),(2,6,1,1,'0.59','2008-03-09 23:00:00','2008-05-25 22:00:00'),(3,6,1,1,'0.63','2007-04-22 22:00:00','2008-03-08 23:00:00'),(4,2,1,1,'1.02','2008-05-26 22:00:00','2008-11-08 23:00:00'),(5,2,1,1,'1.01','2008-03-09 23:00:00','2008-05-25 22:00:00'),(6,2,1,1,'1.06','2007-11-19 23:00:00','2008-03-08 23:00:00'),(7,2,1,1,'1.11','2007-04-22 22:00:00','2007-11-18 23:00:00'),(8,1,1,1,'1.19','2008-05-26 22:00:00','2008-11-08 23:00:00'),(9,1,1,1,'1.18','2008-03-09 23:00:00','2008-05-25 22:00:00'),(10,1,1,1,'1.23','2007-04-22 22:00:00','2008-03-08 23:00:00'),(11,7,1,1,'0.72','2008-05-26 22:00:00','2008-11-08 23:00:00'),(12,7,1,1,'0.71','2008-04-09 22:00:00','2008-05-25 22:00:00'),(13,7,1,1,'0.68','2008-03-09 23:00:00','2008-04-08 22:00:00'),(14,7,1,1,'0.73','2007-06-08 22:00:00','2008-03-08 23:00:00'),(15,7,1,1,'0.72','2007-04-22 22:00:00','2007-06-07 22:00:00'),(16,5,1,1,'0.27','2008-05-26 22:00:00','2008-06-09 22:00:00'),(17,5,1,1,'0.26','2008-03-09 23:00:00','2008-05-25 22:00:00'),(18,5,1,1,'0.30','2007-04-22 22:00:00','2008-03-08 23:00:00'),(19,5,1,1,'0.29','2008-06-10 22:00:00','2008-11-08 23:00:00'),(20,4,1,1,'0.29','2008-06-10 22:00:00','2008-11-08 23:00:00'),(21,4,1,1,'0.27','2008-05-26 22:00:00','2008-06-09 22:00:00'),(22,4,1,1,'0.26','2008-03-09 23:00:00','2008-05-25 22:00:00'),(23,4,1,1,'0.30','2007-04-22 22:00:00','2008-03-08 23:00:00'),(24,8,1,1,'0.81','2008-07-03 22:00:00','2008-11-08 23:00:00'),(25,8,1,1,'0.58','2008-05-26 22:00:00','2008-07-02 22:00:00'),(26,8,1,1,'0.57','2008-03-09 23:00:00','2008-05-25 22:00:00'),(27,8,1,1,'0.62','2007-11-08 23:00:00','2008-03-08 23:00:00'),(28,3,4,12,'9.49','2008-04-09 22:00:00',NULL),(29,9,1,1,'1.05','2008-11-09 23:00:00',NULL),(30,8,1,1,'0.82','2008-11-08 23:00:00',NULL),(31,7,1,1,'0.73','2008-11-08 23:00:00',NULL),(32,6,1,1,'0.61','2008-11-08 23:00:00',NULL),(33,5,1,1,'0.30','2008-11-08 23:00:00',NULL),(34,4,1,1,'0.30','2008-11-08 23:00:00',NULL),(35,2,1,1,'1.03','2008-11-08 23:00:00',NULL),(36,1,1,1,'1.20','2008-11-08 23:00:00',NULL),(37,10,1,1,'0.55','2008-10-23 22:00:00',NULL);
/*!40000 ALTER TABLE `BeverageSalePrice` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `BeverageSaleType`
--

DROP TABLE IF EXISTS `BeverageSaleType`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `BeverageSaleType` (
  `Id` int(10) unsigned NOT NULL auto_increment,
  `Description` varchar(255) NOT NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `BeverageSaleType`
--

LOCK TABLES `BeverageSaleType` WRITE;
/*!40000 ALTER TABLE `BeverageSaleType` DISABLE KEYS */;
INSERT INTO `BeverageSaleType` VALUES (1,'Flasche'),(2,'Flasche (ohne Aufpreis)'),(3,'Kiste'),(4,'Kiste (ohne Aufpreis)');
/*!40000 ALTER TABLE `BeverageSaleType` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `Bottle`
--

DROP TABLE IF EXISTS `Bottle`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `Bottle` (
  `Id` int(10) unsigned NOT NULL auto_increment,
  `Description` varchar(255) NOT NULL,
  `Capacity` decimal(3,2) NOT NULL,
  `Deposit` decimal(4,2) NOT NULL default '0.00',
  PRIMARY KEY  (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `Bottle`
--

LOCK TABLES `Bottle` WRITE;
/*!40000 ALTER TABLE `Bottle` DISABLE KEYS */;
INSERT INTO `Bottle` VALUES (1,'Glas','0.33','0.08'),(2,'Glas','0.50','0.08'),(3,'Glas','1.00','0.15'),(4,'Glas','0.70','0.15'),(5,'Plastik (Mehrweg)','1.00','0.15');
/*!40000 ALTER TABLE `Bottle` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `BottleInventory`
--

DROP TABLE IF EXISTS `BottleInventory`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `BottleInventory` (
  `Id` int(10) unsigned NOT NULL auto_increment,
  `AccountingId` int(10) unsigned NOT NULL,
  `BottleId` int(10) unsigned NOT NULL,
  `Debit` smallint(5) unsigned default NULL COMMENT 'Number of bottles',
  `Credit` smallint(5) unsigned default NULL COMMENT 'Number of bottles',
  PRIMARY KEY  (`Id`),
  KEY `IAccountingId` (`AccountingId`),
  KEY `IBottleId` (`BottleId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `BottleInventory`
--

LOCK TABLES `BottleInventory` WRITE;
/*!40000 ALTER TABLE `BottleInventory` DISABLE KEYS */;
/*!40000 ALTER TABLE `BottleInventory` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `Case`
--

DROP TABLE IF EXISTS `Case`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `Case` (
  `Id` int(10) unsigned NOT NULL auto_increment,
  `Description` varchar(255) NOT NULL,
  `Capacity` smallint(5) unsigned NOT NULL,
  `Deposit` decimal(4,2) NOT NULL default '0.00',
  PRIMARY KEY  (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `Case`
--

LOCK TABLES `Case` WRITE;
/*!40000 ALTER TABLE `Case` DISABLE KEYS */;
INSERT INTO `Case` VALUES (1,'12er',12,'1.50'),(2,'6er',6,'1.50'),(3,'20er (halb)',10,'0.75'),(4,'20er',20,'1.50'),(5,'24er',24,'1.50');
/*!40000 ALTER TABLE `Case` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `CaseInventory`
--

DROP TABLE IF EXISTS `CaseInventory`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `CaseInventory` (
  `Id` int(10) unsigned NOT NULL auto_increment,
  `AccountingId` int(10) unsigned NOT NULL,
  `CaseId` int(10) unsigned NOT NULL,
  `Debit` smallint(5) unsigned default NULL COMMENT 'Number of cases',
  `Credit` smallint(5) unsigned default NULL COMMENT 'Number of cases',
  PRIMARY KEY  (`Id`),
  KEY `IAccountingId` (`AccountingId`),
  KEY `ICaseId` (`CaseId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `CaseInventory`
--

LOCK TABLES `CaseInventory` WRITE;
/*!40000 ALTER TABLE `CaseInventory` DISABLE KEYS */;
/*!40000 ALTER TABLE `CaseInventory` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Temporary table structure for view `CurrentBeverageBalance`
--

DROP TABLE IF EXISTS `CurrentBeverageBalance`;
/*!50001 DROP VIEW IF EXISTS `CurrentBeverageBalance`*/;
/*!50001 CREATE TABLE `CurrentBeverageBalance` (
  `BeverageId` int(10) unsigned,
  `Name` varchar(255),
  `TotalBalance` decimal(29,0),
  `MonetaryBalance` decimal(37,6)
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `CurrentBottleBalance`
--

DROP TABLE IF EXISTS `CurrentBottleBalance`;
/*!50001 DROP VIEW IF EXISTS `CurrentBottleBalance`*/;
/*!50001 CREATE TABLE `CurrentBottleBalance` (
  `BottleId` int(10) unsigned,
  `Description` varchar(255),
  `TotalBalance` decimal(29,0),
  `MonetaryBalance` decimal(33,2)
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `CurrentCaseBalance`
--

DROP TABLE IF EXISTS `CurrentCaseBalance`;
/*!50001 DROP VIEW IF EXISTS `CurrentCaseBalance`*/;
/*!50001 CREATE TABLE `CurrentCaseBalance` (
  `CaseId` int(10) unsigned,
  `Description` varchar(255),
  `TotalBalance` decimal(29,0),
  `MonetaryBalance` decimal(33,2)
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `CurrentEquity`
--

DROP TABLE IF EXISTS `CurrentEquity`;
/*!50001 DROP VIEW IF EXISTS `CurrentEquity`*/;
/*!50001 CREATE TABLE `CurrentEquity` (
  `Equity` decimal(63,6)
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `CurrentMerchantBalance`
--

DROP TABLE IF EXISTS `CurrentMerchantBalance`;
/*!50001 DROP VIEW IF EXISTS `CurrentMerchantBalance`*/;
/*!50001 CREATE TABLE `CurrentMerchantBalance` (
  `Balance` decimal(29,2)
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `CurrentPettyCashBalance`
--

DROP TABLE IF EXISTS `CurrentPettyCashBalance`;
/*!50001 DROP VIEW IF EXISTS `CurrentPettyCashBalance`*/;
/*!50001 CREATE TABLE `CurrentPettyCashBalance` (
  `Balance` decimal(29,2)
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `CurrentPrice`
--

DROP TABLE IF EXISTS `CurrentPrice`;
/*!50001 DROP VIEW IF EXISTS `CurrentPrice`*/;
/*!50001 CREATE TABLE `CurrentPrice` (
  `BeverageId` int(10) unsigned,
  `SaleTypeId` int(10) unsigned,
  `Name` varchar(255),
  `SaleType` varchar(255),
  `PurchaseQuantity` smallint(5) unsigned,
  `SaleQuantity` smallint(5) unsigned,
  `Capacity` decimal(3,2),
  `PurchasePrice` decimal(4,2),
  `SalePrice` decimal(4,2),
  `ProfitPerSaleQuantity` decimal(15,6)
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `CurrentResidentBalance`
--

DROP TABLE IF EXISTS `CurrentResidentBalance`;
/*!50001 DROP VIEW IF EXISTS `CurrentResidentBalance`*/;
/*!50001 CREATE TABLE `CurrentResidentBalance` (
  `ResidentId` int(10) unsigned,
  `FamilyName` varchar(255),
  `FirstName` varchar(255),
  `RoomNo` smallint(5) unsigned,
  `Debit` decimal(29,2),
  `Credit` decimal(29,2)
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `CurrentRoomAllocation`
--

DROP TABLE IF EXISTS `CurrentRoomAllocation`;
/*!50001 DROP VIEW IF EXISTS `CurrentRoomAllocation`*/;
/*!50001 CREATE TABLE `CurrentRoomAllocation` (
  `ResidentId` int(10) unsigned,
  `RoomNo` smallint(5) unsigned,
  `FamilyName` varchar(255),
  `FirstName` varchar(255)
) ENGINE=MyISAM */;

--
-- Table structure for table `MerchantAccount`
--

DROP TABLE IF EXISTS `MerchantAccount`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `MerchantAccount` (
  `Id` int(10) unsigned NOT NULL auto_increment,
  `AccountingId` int(10) unsigned NOT NULL,
  `Debit` decimal(6,2) unsigned default NULL,
  `Credit` decimal(6,2) unsigned default NULL,
  PRIMARY KEY  (`Id`),
  KEY `IAccountingId` (`AccountingId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `MerchantAccount`
--

LOCK TABLES `MerchantAccount` WRITE;
/*!40000 ALTER TABLE `MerchantAccount` DISABLE KEYS */;
/*!40000 ALTER TABLE `MerchantAccount` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `PettyCash`
--

DROP TABLE IF EXISTS `PettyCash`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `PettyCash` (
  `Id` int(10) unsigned NOT NULL auto_increment,
  `AccountingId` int(10) unsigned NOT NULL,
  `Debit` decimal(6,2) unsigned default NULL,
  `Credit` decimal(6,2) unsigned default NULL,
  PRIMARY KEY  (`Id`),
  KEY `IAccountingId` (`AccountingId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `PettyCash`
--

LOCK TABLES `PettyCash` WRITE;
/*!40000 ALTER TABLE `PettyCash` DISABLE KEYS */;
/*!40000 ALTER TABLE `PettyCash` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `Resident`
--

DROP TABLE IF EXISTS `Resident`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `Resident` (
  `Id` int(10) unsigned NOT NULL auto_increment,
  `FamilyName` varchar(255) NOT NULL,
  `FirstName` varchar(255) NOT NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `Resident`
--

LOCK TABLES `Resident` WRITE;
/*!40000 ALTER TABLE `Resident` DISABLE KEYS */;
INSERT INTO `Resident` VALUES (1,'Doe','Jane'),(2,'Doe','Jane'),(6,'Doe','Jim'),(12,'Smith','Paul'),(13,'Who','Max'),(14,'Smith','Anne'),(16,'Doe','Anne'),(17,'Doe','Anne'),(18,'Doe','Anne'),(20,'Doe','Anne'),(22,'Doe','Anne'),(23,'Doe','Anne'),(24,'Doe','Anne'),(27,'Doe','Anne'),(30,'Doe','Anne'),(31,'Doe','Anne'),(33,'Doe','Anne'),(34,'Doe','Anne'),(35,'Doe','Anne'),(37,'Doe','Anne'),(38,'Doe','Anne'),(39,'Doe','Anne'),(40,'Doe','Anne'),(41,'Doe','Anne');
/*!40000 ALTER TABLE `Resident` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `ResidentAccount`
--

DROP TABLE IF EXISTS `ResidentAccount`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `ResidentAccount` (
  `Id` int(10) unsigned NOT NULL auto_increment,
  `AccountingId` int(10) unsigned NOT NULL,
  `ResidentId` int(10) unsigned NOT NULL,
  `Debit` decimal(6,2) unsigned default NULL,
  `Credit` decimal(6,2) unsigned default NULL,
  PRIMARY KEY  (`Id`),
  KEY `IAccountingId` (`AccountingId`),
  KEY `IResidentId` (`ResidentId`)
) ENGINE=MyISAM AUTO_INCREMENT=23 DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `ResidentAccount`
--

LOCK TABLES `ResidentAccount` WRITE;
/*!40000 ALTER TABLE `ResidentAccount` DISABLE KEYS */;
INSERT INTO `ResidentAccount` VALUES (1,1,23,'0.00',NULL),(2,1,2,NULL,'11.20'),(3,1,34,'0.72',NULL),(4,1,20,NULL,'16.51'),(5,1,30,'0.80',NULL),(6,1,22,NULL,'8.27'),(7,1,6,'98.63',NULL),(8,1,24,NULL,'19.79'),(9,1,31,'4.18',NULL),(10,1,37,'2.17',NULL),(11,1,33,'70.45',NULL),(12,1,38,NULL,'4.21'),(13,1,13,'28.75',NULL),(14,1,14,NULL,'4.01'),(15,1,12,'11.52',NULL),(16,1,16,NULL,'27.64'),(17,1,27,NULL,'5.90'),(18,1,1,NULL,'6.17'),(19,1,18,NULL,'9.23'),(20,1,17,NULL,'16.31');
/*!40000 ALTER TABLE `ResidentAccount` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `RoomAllocation`
--

DROP TABLE IF EXISTS `RoomAllocation`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `RoomAllocation` (
  `Id` int(10) unsigned NOT NULL auto_increment,
  `ResidentId` int(10) unsigned NOT NULL,
  `RoomNo` smallint(5) unsigned NOT NULL,
  `From` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `To` timestamp NULL default NULL,
  PRIMARY KEY  (`Id`),
  KEY `ITimeSpan` (`From`,`To`),
  KEY `ITo` (`To`),
  KEY `IRoomNo` (`RoomNo`),
  KEY `IResidentIdFrom` (`ResidentId`,`From`),
  KEY `IRoomNoFrom` (`RoomNo`,`From`),
  CONSTRAINT `RoomAllocation_ibfk_1` FOREIGN KEY (`ResidentId`) REFERENCES `Resident` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `RoomAllocation`
--

LOCK TABLES `RoomAllocation` WRITE;
/*!40000 ALTER TABLE `RoomAllocation` DISABLE KEYS */;
INSERT INTO `RoomAllocation` VALUES (17,22,16,'2006-09-30 22:00:00',NULL),(18,6,15,'2006-09-30 22:00:00',NULL),(19,24,14,'2007-08-31 22:00:00',NULL),(20,39,13,'2008-11-04 23:00:00',NULL),(21,31,12,'2008-03-31 22:00:00',NULL),(22,37,11,'2008-09-30 22:00:00',NULL),(23,33,10,'2008-02-14 23:00:00',NULL),(24,38,9,'2008-06-01 22:00:00',NULL),(25,13,8,'2005-06-30 22:00:00',NULL),(26,14,7,'2006-03-01 23:00:00',NULL),(27,16,5,'2006-04-30 22:00:00',NULL),(28,12,6,'2008-05-16 22:00:00',NULL),(29,27,4,'2007-09-30 22:00:00',NULL),(30,1,3,'2007-03-31 22:00:00','2008-11-29 23:00:00'),(31,18,2,'2006-09-30 22:00:00',NULL),(32,17,1,'2008-03-31 22:00:00',NULL),(33,2,1,'2005-03-31 22:00:00',NULL),(34,23,2,'2007-09-14 22:00:00',NULL),(35,35,20,'2008-11-06 23:00:00',NULL),(36,34,19,'2008-09-30 22:00:00',NULL),(37,30,17,'2007-09-15 22:00:00',NULL),(38,20,18,'2006-09-30 22:00:00',NULL),(39,12,9,'2004-12-03 23:00:00','2008-05-15 22:00:00'),(40,41,3,'2008-11-30 23:00:00',NULL),(41,40,10,'2008-11-30 23:00:00',NULL);
/*!40000 ALTER TABLE `RoomAllocation` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Final view structure for view `CurrentBeverageBalance`
--

/*!50001 DROP TABLE `CurrentBeverageBalance`*/;
/*!50001 DROP VIEW IF EXISTS `CurrentBeverageBalance`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `CurrentBeverageBalance` AS select `Inv`.`BeverageId` AS `BeverageId`,`Beverage`.`FullName` AS `Name`,(ifnull(sum(`Inv`.`Debit`),0) - ifnull(sum(`Inv`.`Credit`),0)) AS `TotalBalance`,(((ifnull(sum(`Inv`.`Debit`),0) - ifnull(sum(`Inv`.`Credit`),0)) * `Price`.`PurchasePrice`) / `Price`.`PurchaseQuantity`) AS `MonetaryBalance` from (`BeverageInventory` `Inv` join (`Beverage` join `CurrentPrice` `Price`) on(((`Inv`.`BeverageId` = `Beverage`.`Id`) and (`Inv`.`BeverageId` = `Price`.`BeverageId`)))) group by `Inv`.`BeverageId` having (`TotalBalance` <> 0) order by `Beverage`.`ShortName` */;

--
-- Final view structure for view `CurrentBottleBalance`
--

/*!50001 DROP TABLE `CurrentBottleBalance`*/;
/*!50001 DROP VIEW IF EXISTS `CurrentBottleBalance`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `CurrentBottleBalance` AS select `Inv`.`BottleId` AS `BottleId`,`Bottle`.`Description` AS `Description`,(ifnull(sum(`Inv`.`Debit`),0) - ifnull(sum(`Inv`.`Credit`),0)) AS `TotalBalance`,((ifnull(sum(`Inv`.`Debit`),0) - ifnull(sum(`Inv`.`Credit`),0)) * `Bottle`.`Deposit`) AS `MonetaryBalance` from (`BottleInventory` `Inv` join `Bottle` on((`Inv`.`BottleId` = `Bottle`.`Id`))) group by `Inv`.`BottleId` having (`TotalBalance` <> 0) order by `Bottle`.`Description` */;

--
-- Final view structure for view `CurrentCaseBalance`
--

/*!50001 DROP TABLE `CurrentCaseBalance`*/;
/*!50001 DROP VIEW IF EXISTS `CurrentCaseBalance`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `CurrentCaseBalance` AS select `Inv`.`CaseId` AS `CaseId`,`Case`.`Description` AS `Description`,(ifnull(sum(`Inv`.`Debit`),0) - ifnull(sum(`Inv`.`Credit`),0)) AS `TotalBalance`,((ifnull(sum(`Inv`.`Debit`),0) - ifnull(sum(`Inv`.`Credit`),0)) * `Case`.`Deposit`) AS `MonetaryBalance` from (`CaseInventory` `Inv` join `Case` on((`Inv`.`CaseId` = `Case`.`Id`))) group by `Inv`.`CaseId` having (`TotalBalance` <> 0) order by `Case`.`Description` */;

--
-- Final view structure for view `CurrentEquity`
--

/*!50001 DROP TABLE `CurrentEquity`*/;
/*!50001 DROP VIEW IF EXISTS `CurrentEquity`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `CurrentEquity` AS select (((((select `CurrentPettyCashBalance`.`Balance` AS `Balance` from `CurrentPettyCashBalance`) + (select sum(`CurrentBeverageBalance`.`MonetaryBalance`) AS `SUM( MonetaryBalance )` from `CurrentBeverageBalance`)) + (select sum(`CurrentResidentBalance`.`Credit`) AS `SUM( Credit )` from `CurrentResidentBalance` where (`CurrentResidentBalance`.`Credit` is not null))) - (select sum(`CurrentResidentBalance`.`Debit`) AS `SUM( Debit )` from `CurrentResidentBalance` where (`CurrentResidentBalance`.`Debit` is not null))) - (select `CurrentMerchantBalance`.`Balance` AS `Balance` from `CurrentMerchantBalance`)) AS `Equity` */;

--
-- Final view structure for view `CurrentMerchantBalance`
--

/*!50001 DROP TABLE `CurrentMerchantBalance`*/;
/*!50001 DROP VIEW IF EXISTS `CurrentMerchantBalance`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `CurrentMerchantBalance` AS select (ifnull(sum(`MerchantAccount`.`Credit`),0) - ifnull(sum(`MerchantAccount`.`Debit`),0)) AS `Balance` from `MerchantAccount` */;

--
-- Final view structure for view `CurrentPettyCashBalance`
--

/*!50001 DROP TABLE `CurrentPettyCashBalance`*/;
/*!50001 DROP VIEW IF EXISTS `CurrentPettyCashBalance`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `CurrentPettyCashBalance` AS select (ifnull(sum(`PettyCash`.`Debit`),0) - ifnull(sum(`PettyCash`.`Credit`),0)) AS `Balance` from `PettyCash` */;

--
-- Final view structure for view `CurrentPrice`
--

/*!50001 DROP TABLE `CurrentPrice`*/;
/*!50001 DROP VIEW IF EXISTS `CurrentPrice`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `CurrentPrice` AS select `Beverage`.`Id` AS `BeverageId`,`SaleType`.`Id` AS `SaleTypeId`,`Beverage`.`ShortName` AS `Name`,`SaleType`.`Description` AS `SaleType`,`Case`.`Capacity` AS `PurchaseQuantity`,`Sale`.`SaleQuantity` AS `SaleQuantity`,`Bottle`.`Capacity` AS `Capacity`,`Purchase`.`Price` AS `PurchasePrice`,`Sale`.`Price` AS `SalePrice`,(`Sale`.`Price` - (`Purchase`.`Price` * (`Sale`.`SaleQuantity` / `Case`.`Capacity`))) AS `ProfitPerSaleQuantity` from (`Beverage` join ((((`Case` join `Bottle`) join `BeveragePurchasePrice` `Purchase`) join `BeverageSalePrice` `Sale`) join `BeverageSaleType` `SaleType`) on(((`Case`.`Id` = `Beverage`.`CaseId`) and (`Bottle`.`Id` = `Beverage`.`BottleId`) and (`Purchase`.`BeverageId` = `Beverage`.`Id`) and (`Sale`.`BeverageId` = `Beverage`.`Id`) and (`Sale`.`SaleTypeId` = `SaleType`.`Id`)))) where (((now() between `Sale`.`From` and `Sale`.`To`) or ((now() > `Sale`.`From`) and isnull(`Sale`.`To`))) and ((now() between `Purchase`.`From` and `Purchase`.`To`) or ((now() > `Purchase`.`From`) and isnull(`Purchase`.`To`)))) order by `Beverage`.`ShortName` */;

--
-- Final view structure for view `CurrentResidentBalance`
--

/*!50001 DROP TABLE `CurrentResidentBalance`*/;
/*!50001 DROP VIEW IF EXISTS `CurrentResidentBalance`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `CurrentResidentBalance` AS select `Resident`.`Id` AS `ResidentId`,`Resident`.`FamilyName` AS `FamilyName`,`Resident`.`FirstName` AS `FirstName`,`RoomAllocation`.`RoomNo` AS `RoomNo`,if(((ifnull(sum(`ResidentAccount`.`Credit`),0) - ifnull(sum(`ResidentAccount`.`Debit`),0)) > 0),(ifnull(sum(`ResidentAccount`.`Credit`),0) - ifnull(sum(`ResidentAccount`.`Debit`),0)),NULL) AS `TotalDebit`,if(((ifnull(sum(`ResidentAccount`.`Debit`),0) - ifnull(sum(`ResidentAccount`.`Credit`),0)) > 0),(ifnull(sum(`ResidentAccount`.`Debit`),0) - ifnull(sum(`ResidentAccount`.`Credit`),0)),NULL) AS `TotalCredit` from ((`ResidentAccount` join `Resident` on((`ResidentAccount`.`ResidentId` = `Resident`.`Id`))) left join `RoomAllocation` on((`Resident`.`Id` = `RoomAllocation`.`ResidentId`))) where ((now() between `RoomAllocation`.`From` and `RoomAllocation`.`To`) or ((now() > `RoomAllocation`.`From`) and isnull(`RoomAllocation`.`To`))) group by `Resident`.`Id` having ( `TotalDebit` <> 0 or `TotalCredit` <> 0 ) order by `RoomAllocation`.`RoomNo` */;

--
-- Final view structure for view `CurrentRoomAllocation`
--

/*!50001 DROP TABLE `CurrentRoomAllocation`*/;
/*!50001 DROP VIEW IF EXISTS `CurrentRoomAllocation`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `CurrentRoomAllocation` AS select `Resident`.`Id` AS `ResidentId`,`RoomAllocation`.`RoomNo` AS `RoomNo`,`Resident`.`FamilyName` AS `FamilyName`,`Resident`.`FirstName` AS `FirstName` from (`Resident` join `RoomAllocation` on((`Resident`.`Id` = `RoomAllocation`.`ResidentId`))) where ((now() between `RoomAllocation`.`From` and `RoomAllocation`.`To`) or ((now() > `RoomAllocation`.`From`) and isnull(`RoomAllocation`.`To`))) order by `RoomAllocation`.`RoomNo` */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2008-11-19 17:56:30