-- Table structure for table `Barbaratje`
--

CREATE TABLE `Barbaratje` (
  `Klantid` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `Boekenweek2014`
--

CREATE TABLE `Boekenweek2014` (
  `Klantid` int(11) NOT NULL,
  PRIMARY KEY (`Klantid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `Bounces`
--

CREATE TABLE `Bounces` (
  `from` varchar(255) DEFAULT NULL,
  `to` varchar(255) DEFAULT NULL,
  `dt` datetime DEFAULT NULL,
  `message` text,
  `isbounce` tinyint(4) DEFAULT NULL,
  `uuid` char(36) DEFAULT NULL,
  KEY `NewIndex1` (`uuid`),
  KEY `dt` (`dt`),
  KEY `bauuid` (`isbounce`,`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `EenOpEenConfiguratie`
--

CREATE TABLE `EenOpEenConfiguratie` (
  `Key` char(20) NOT NULL,
  `Val` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `EenOpEenLog`
--

CREATE TABLE `EenOpEenLog` (
  `MessageId` char(32) NOT NULL,
  `Event` enum('send','open','click','reject','spam','hard_bounce','soft_bounce','deferral') DEFAULT NULL,
  `KlantId` int(11) DEFAULT NULL,
  `DT` datetime DEFAULT NULL,
  `RecommendedEan` char(13) DEFAULT NULL,
  `Tags` set('1OP1-Boek-Onder','1OP1-Boek-Boven','een-op-een') DEFAULT NULL,
  `Mobile` tinyint(4) DEFAULT NULL,
  `Browser` char(20) DEFAULT NULL,
  `OS` char(20) DEFAULT NULL,
  KEY `MessageId` (`MessageId`),
  KEY `KlantId` (`KlantId`,`Event`),
  KEY `DT` (`DT`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 DELAY_KEY_WRITE=1 ROW_FORMAT=COMPRESSED;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`sander`@`%`*/ /*!50003 TRIGGER `after_insert_eenopeenlog` AFTER INSERT ON `EenOpEenLog` 
    FOR EACH ROW BEGIN
	-- Unsubscribe klanten met een bounce
	IF NEW.`Event` = 'hard_bounce' OR NEW.`Event` = 'soft_bounce' OR NEW.`Event` = 'spam' THEN
		DELETE FROM Managementboek_Klanten.`Klantmailinglist` WHERE Klantid = NEW.Klantid;
	END IF;
    END */;;
DELIMITER ;

--
-- Table structure for table `EenOpEenPlanning`
--

CREATE TABLE `EenOpEenPlanning` (
  `PlanningId` int(11) NOT NULL AUTO_INCREMENT,
  `DT` date DEFAULT NULL,
  `SelectieId` int(11) DEFAULT NULL,
  `AanbevolenEan` char(13) DEFAULT NULL,
  `Intro` mediumtext,
  `IsGemaild` tinyint(4) DEFAULT NULL,
  `DTGemaild` date DEFAULT NULL,
  PRIMARY KEY (`PlanningId`),
  UNIQUE KEY `DT` (`DT`,`AanbevolenEan`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8;

--
-- Table structure for table `Klant_Bounces`
--

CREATE TABLE `Klant_Bounces` (
  `klantid` int(11) NOT NULL,
  `hard` int(11) NOT NULL,
  `soft` int(11) NOT NULL,
  PRIMARY KEY (`klantid`),
  KEY `NewIndex1` (`hard`),
  KEY `NewIndex2` (`soft`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `Klantenselectie`
--

CREATE TABLE `Klantenselectie` (
  `klantenselectie` int(11) NOT NULL,
  `titel` varchar(255) DEFAULT NULL,
  `query` mediumtext,
  `show` tinyint(1) NOT NULL,
  PRIMARY KEY (`klantenselectie`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `MailchimpCustomSubscribers`
--

CREATE TABLE `MailchimpCustomSubscribers` (
  `Email` varchar(255) NOT NULL,
  PRIMARY KEY (`Email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `MandrillEvents`
--

CREATE TABLE `MandrillEvents` (
  `DT` datetime DEFAULT NULL,
  `Event` enum('send','open','click','reject','spam','hard_bounce','soft_bounce','deferral') DEFAULT NULL,
  `Recipient` varchar(255) DEFAULT NULL,
  `MessageId` char(32) NOT NULL,
  `KlantId` int(11) DEFAULT NULL,
  `Tags` varchar(255) DEFAULT NULL,
  `Mobile` tinyint(4) DEFAULT NULL,
  `Browser` char(20) DEFAULT NULL,
  `OS` char(30) DEFAULT NULL,
  `Message` mediumtext,
  `URL` varchar(255) DEFAULT NULL,
  `Processed` tinyint(4) NOT NULL DEFAULT '0',
  KEY `MessageId` (`MessageId`),
  KEY `KlantId` (`KlantId`,`Event`),
  KEY `DT` (`DT`),
  KEY `Event` (`Event`),
  KEY `Recipient` (`Recipient`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 DELAY_KEY_WRITE=1 ROW_FORMAT=COMPRESSED;

--
-- Table structure for table `NKP_stadia`
--

CREATE TABLE `NKP_stadia` (
  `Klantid` int(11) NOT NULL,
  `Stadium` int(11) unsigned NOT NULL,
  `DT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`Klantid`,`Stadium`),
  KEY `DT` (`DT`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `NieuwsbriefOmzet`
--

CREATE TABLE `NieuwsbriefOmzet` (
  `nieuwsbriefid` int(11) NOT NULL,
  `omzet` double(8,2) DEFAULT NULL,
  `dt` datetime DEFAULT NULL,
  PRIMARY KEY (`nieuwsbriefid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `NieuwsbriefSchema`
--

CREATE TABLE `NieuwsbriefSchema` (
  `jaar` int(11) NOT NULL,
  `weeknr` int(11) NOT NULL,
  `type` enum('special','magazine','actie','event','beste') DEFAULT NULL,
  `dt` date DEFAULT NULL,
  `compleet` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`jaar`,`weeknr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Temporary table structure for view `NieuwsbriefSchemaPaths`
--

/*!50001 DROP VIEW IF EXISTS `NieuwsbriefSchemaPaths`*/;
/*!50001 CREATE TABLE `NieuwsbriefSchemaPaths` (
  `jaar` tinyint NOT NULL,
  `weeknr` tinyint NOT NULL,
  `type` tinyint NOT NULL,
  `dt` tinyint NOT NULL,
  `compleet` tinyint NOT NULL,
  `KantoorPath` tinyint NOT NULL,
  `LokaalPath` tinyint NOT NULL,
  `URL` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Table structure for table `Nieuwsbrief_Eans`
--

CREATE TABLE `Nieuwsbrief_Eans` (
  `nieuwsbriefid` int(10) unsigned NOT NULL,
  `ean` char(13) NOT NULL,
  PRIMARY KEY (`nieuwsbriefid`,`ean`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `Nieuwsbrief_Mail`
--

CREATE TABLE `Nieuwsbrief_Mail` (
  `nieuwsbriefid` int(11) NOT NULL,
  `klantid` int(11) NOT NULL,
  `uuid` char(36) NOT NULL,
  `dtverstuurd` datetime NOT NULL,
  `bounced` tinyint(4) NOT NULL DEFAULT '0',
  `dtbounce` datetime DEFAULT NULL,
  `bouncecode` int(11) NOT NULL,
  `isverstuurd` tinyint(1) NOT NULL,
  `inqueue` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`klantid`,`nieuwsbriefid`),
  UNIQUE KEY `NewIndex1` (`uuid`),
  KEY `NewIndex3` (`nieuwsbriefid`,`isverstuurd`,`inqueue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1;

--
-- Table structure for table `Nieuwsbrief_Run`
--

CREATE TABLE `Nieuwsbrief_Run` (
  `nieuwsbriefid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `subject` varchar(255) DEFAULT NULL,
  `titel` varchar(255) DEFAULT NULL,
  `dtopgeslagen` datetime DEFAULT NULL,
  `dtscheduled` datetime DEFAULT NULL,
  `dtstart` datetime DEFAULT NULL,
  `dteind` datetime DEFAULT NULL,
  `actief` tinyint(4) NOT NULL DEFAULT '0',
  `isverzonden` tinyint(4) NOT NULL DEFAULT '0',
  `isgeeindigd` tinyint(4) NOT NULL,
  `klantenselectie` int(11) DEFAULT NULL,
  `aantalontvangers` int(11) DEFAULT NULL,
  `aantalgegenereerd` int(11) NOT NULL DEFAULT '0',
  `aantalverzonden` int(10) unsigned NOT NULL,
  `aantalbounced` int(4) NOT NULL DEFAULT '0',
  `aantalgezien` int(11) NOT NULL,
  `template` varchar(255) NOT NULL,
  `xml` longtext NOT NULL,
  `status_mps` varchar(20) DEFAULT NULL,
  `status_eta` int(11) DEFAULT NULL,
  `heartbeat` datetime DEFAULT NULL,
  `gebruiker` varchar(255) DEFAULT NULL,
  `eanswel` mediumtext NOT NULL,
  `eansniet` mediumtext NOT NULL,
  PRIMARY KEY (`nieuwsbriefid`),
  KEY `NewIndex1` (`isverzonden`,`aantalbounced`),
  KEY `Actief` (`actief`)
) ENGINE=InnoDB AUTO_INCREMENT=3970 DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `Nieuwsbrief_Views`
--

CREATE TABLE `Nieuwsbrief_Views` (
  `uuid` char(36) NOT NULL,
  `dt` datetime NOT NULL,
  KEY `NewIndex1` (`uuid`),
  KEY `NewIndex2` (`dt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=COMPRESSED;

--
-- Table structure for table `Nieuwsbrief_Vinkjes`
--

CREATE TABLE `Nieuwsbrief_Vinkjes` (
  `Klantid` int(11) NOT NULL,
  `OptIn` tinyint(1) NOT NULL DEFAULT '0',
  `InitieleOptIn` tinyint(1) NOT NULL DEFAULT '0',
  `SoftOptIn` tinyint(1) NOT NULL DEFAULT '0',
  `OptOut` tinyint(1) NOT NULL DEFAULT '0',
  `ServicemailOptOut` tinyint(1) NOT NULL DEFAULT '0',
  `NKPgeclaimd` tinyint(1) NOT NULL DEFAULT '0',
  `Blacklist` tinyint(1) NOT NULL DEFAULT '0',
  `Bounce` tinyint(1) NOT NULL DEFAULT '0',
  `DTOptIn` datetime DEFAULT NULL,
  `DTOptOut` datetime DEFAULT NULL,
  `DTServicemailOptOut` datetime DEFAULT NULL,
  `DTBlacklist` datetime DEFAULT NULL,
  `GroupMagazine` tinyint(1) DEFAULT '1',
  `GroupEvents` tinyint(1) DEFAULT '1',
  `GroupActies` tinyint(1) DEFAULT '1',
  `GroupBeste` tinyint(1) DEFAULT '1',
  `GroupPersoonlijk` tinyint(1) DEFAULT '1',
  `GroupComputerboek` tinyint(1) NOT NULL DEFAULT '0',
  `GroupJuridischboek` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`Klantid`),
  KEY `OptIn` (`OptIn`),
  KEY `SoftOptIn` (`SoftOptIn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='	';

--
-- Table structure for table `Nieuwsbrief_Vinkjes_fubar_20140526`
--

CREATE TABLE `Nieuwsbrief_Vinkjes_fubar_20140526` (
  `Klantid` int(11) NOT NULL,
  `OptIn` tinyint(1) NOT NULL DEFAULT '0',
  `InitieleOptIn` tinyint(1) NOT NULL DEFAULT '0',
  `SoftOptIn` tinyint(1) NOT NULL DEFAULT '0',
  `OptOut` tinyint(1) NOT NULL DEFAULT '0',
  `ServicemailOptOut` tinyint(1) NOT NULL DEFAULT '0',
  `NKPgeclaimd` tinyint(1) NOT NULL DEFAULT '0',
  `Blacklist` tinyint(1) NOT NULL DEFAULT '0',
  `Bounce` tinyint(1) NOT NULL DEFAULT '0',
  `DTOptIn` datetime DEFAULT NULL,
  `DTOptOut` datetime DEFAULT NULL,
  `DTServicemailOptOut` datetime DEFAULT NULL,
  `DTBlacklist` datetime DEFAULT NULL,
  `GroupMagazine` tinyint(1) DEFAULT '1',
  `GroupEvents` tinyint(1) DEFAULT '1',
  `GroupActies` tinyint(1) DEFAULT '1',
  `GroupBeste` tinyint(1) DEFAULT '1',
  `GroupPersoonlijk` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`Klantid`),
  KEY `OptIn` (`OptIn`),
  KEY `SoftOptIn` (`SoftOptIn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='	';

--
-- Table structure for table `Nieuwsbriefnaam_Eans`
--

CREATE TABLE `Nieuwsbriefnaam_Eans` (
  `Nieuwsbriefnaam` varchar(100) NOT NULL,
  `Ean` char(13) CHARACTER SET latin1 NOT NULL,
  `Jaar` char(4) NOT NULL,
  PRIMARY KEY (`Nieuwsbriefnaam`,`Ean`,`Jaar`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `Nieuwsbriefnaam_Type`
--

CREATE TABLE `Nieuwsbriefnaam_Type` (
  `Nieuwsbriefnaam` varchar(100) NOT NULL,
  `Type` varchar(50) NOT NULL,
  `Jaar` char(4) NOT NULL,
  PRIMARY KEY (`Nieuwsbriefnaam`,`Type`,`Jaar`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Temporary table structure for view `NieuwsbrievSchemaPaths`
--

/*!50001 DROP VIEW IF EXISTS `NieuwsbrievSchemaPaths`*/;
/*!50001 CREATE TABLE `NieuwsbrievSchemaPaths` (
  `jaar` tinyint NOT NULL,
  `weeknr` tinyint NOT NULL,
  `type` tinyint NOT NULL,
  `dt` tinyint NOT NULL,
  `compleet` tinyint NOT NULL,
  `KantoorPath` tinyint NOT NULL,
  `LokaalPath` tinyint NOT NULL,
  `URL` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `Subscribers_Persoonlijk`
--

/*!50001 DROP VIEW IF EXISTS `Subscribers_Persoonlijk`*/;
/*!50001 CREATE TABLE `Subscribers_Persoonlijk` (
  `KlantId` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Table structure for table `VerstuurdeNieuwsbrieven`
--

CREATE TABLE `VerstuurdeNieuwsbrieven` (
  `path` varchar(255) NOT NULL,
  `online` tinyint(1) NOT NULL DEFAULT '0',
  `dt` datetime NOT NULL DEFAULT '2000-01-01 00:00:00',
  PRIMARY KEY (`path`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `WelInMailchimp`
--

CREATE TABLE `WelInMailchimp` (
  `Klantid` int(11) NOT NULL,
  PRIMARY KEY (`Klantid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping routines for database 'Managementboek_Nieuwsbrieven'
--
/*!50003 DROP FUNCTION IF EXISTS `email_domain` */;
DELIMITER ;;
CREATE DEFINER=`sander`@`%` FUNCTION `email_domain`( email varchar(255) ) RETURNS varchar(255) CHARSET utf8
    NO SQL
    DETERMINISTIC
BEGIN
	RETURN REVERSE(LEFT(REVERSE(email),LOCATE('@',REVERSE(email)) - 1));
    END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `nieuwsbrief_omzet` */;
DELIMITER ;;
CREATE DEFINER=`sander`@`%` FUNCTION `nieuwsbrief_omzet`( nid INT ) RETURNS double(8,2)
    READS SQL DATA
    DETERMINISTIC
BEGIN
	DECLARE rv DOUBLE( 8, 2 );
	
	SELECT omzet INTO rv FROM NieuwsbriefOmzet WHERE dt > DATE_SUB( NOW(), INTERVAL 1 HOUR ) AND nieuwsbriefid = nid;
	
	IF rv IS NULL THEN
		REPLACE INTO NieuwsbriefOmzet SELECT nid, ROUND( SUM( Managementboek_Klanten.bestelling_totaal( Bestellingid ) / 100 ), 2 ), NOW()
		FROM Managementboek_Klanten.BestellingenExtra E 
		JOIN Managementboek_Klanten.Bestellingen USING ( Id ) 
		JOIN Managementboek_Klanten.Bestelling USING ( Bestellingid )   WHERE E.Tag = CONCAT( "nb", nid );
		
		SELECT omzet INTO rv FROM NieuwsbriefOmzet WHERE dt > DATE_SUB( NOW(), INTERVAL 1 HOUR ) AND nieuwsbriefid = nid;
	END IF;
	
	RETURN IFNULL(rv,0);
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `generate_run` */;
DELIMITER ;;
CREATE DEFINER=`sander`@`%` PROCEDURE `generate_run`( nieuwsbriefid int )
    MODIFIES SQL DATA
BEGIN
	DECLARE done INT DEFAULT 0;
	DECLARE kid INT;
	DECLARE suuid CHAR( 36 );
	DECLARE ckid CURSOR FOR SELECT Klantid FROM TempNieuwsbriefOntvangers;
	
	DELETE FROM Nieuwsbrief_Mail WHERE `nieuwsbriefid` = nieuwsbriefid;
	OPEN ckid;
	
	REPEAT
		FETCH ckid INTO kid;
		IF NOT done THEN
			IF kid > 0 THEN
				SET suuid = UUID();
				INSERT INTO Nieuwsbrief_Mail ( nieuwsbriefid, klantid, `uuid` ) VALUES ( nieuwsbriefid, kid, suuid );
			END IF;
		END IF;
	UNTIL done END REPEAT;
	CLOSE ckid;
	DROP TABLE TempNieuwsbriefOntvangers;
    END ;;
DELIMITER ;

--
-- Current Database: `Managementboek_vogoo`
--

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

USE `Managementboek_vogoo`;

--
-- Temporary table structure for view `Ratings_met_ean`
--

/*!50001 DROP VIEW IF EXISTS `Ratings_met_ean`*/;
/*!50001 CREATE TABLE `Ratings_met_ean` (
  `Klantid` tinyint NOT NULL,
  `Ean` tinyint NOT NULL,
  `Rating` tinyint NOT NULL,
  `DTRating` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Table structure for table `vogoo_ads`
--

CREATE TABLE `vogoo_ads` (
  `ad_id` int(11) DEFAULT NULL,
  `category` int(11) DEFAULT NULL,
  `mini` int(11) DEFAULT NULL,
  KEY `ad_id` (`ad_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `vogoo_ads_products`
--

CREATE TABLE `vogoo_ads_products` (
  `ad_id` int(11) DEFAULT NULL,
  `category` int(11) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  KEY `ad_id` (`ad_id`),
  KEY `category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `vogoo_links`
--

CREATE TABLE `vogoo_links` (
  `item_id1` int(11) DEFAULT NULL,
  `item_id2` int(11) DEFAULT NULL,
  `category` int(11) DEFAULT NULL,
  `cnt_links` int(11) DEFAULT NULL,
  `cnt_slope` int(11) DEFAULT NULL,
  `diff_slope` float DEFAULT NULL,
  KEY `item_id1` (`item_id1`),
  KEY `item_id2` (`item_id2`),
  KEY `category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `vogoo_ratings`
--

CREATE TABLE `vogoo_ratings` (
  `member_id` bigint(11) unsigned NOT NULL DEFAULT '0',
  `product_id` int(11) DEFAULT NULL,
  `category` int(11) DEFAULT NULL,
  `rating` float DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Ean` char(13) NOT NULL DEFAULT '',
  PRIMARY KEY (`member_id`,`Ean`),
  KEY `member_id` (`member_id`),
  KEY `product_id` (`product_id`),
  KEY `category` (`category`),
  KEY `NewIndex1` (`product_id`,`category`,`rating`),
  KEY `Ean` (`Ean`,`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping routines for database 'Managementboek_vogoo'
--

--
-- Current Database: `Manpo`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `Manpo` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `Manpo`;

--
-- Table structure for table `AboBoeken`
--

CREATE TABLE `AboBoeken` (
  `EAN` char(13) NOT NULL,
  `Titelserie` varchar(100) NOT NULL,
  `Gefactureerd` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`EAN`,`Titelserie`),
  KEY `ibfk_abo_Titelserie` (`Titelserie`),
  CONSTRAINT `ibfk_abo_EAN` FOREIGN KEY (`EAN`) REFERENCES `Boeken` (`Ean`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `ibfk_abo_Titelserie` FOREIGN KEY (`Titelserie`) REFERENCES `Titelserie` (`Titelserie`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `AfwijkendeKorting`
--

CREATE TABLE `AfwijkendeKorting` (
  `EAN` char(13) NOT NULL,
  `Korting_0_9` decimal(5,2) DEFAULT NULL,
  `Korting_10_29` decimal(5,2) DEFAULT NULL,
  `Korting_30_99` decimal(5,2) DEFAULT NULL,
  `Korting_100_249` decimal(5,2) DEFAULT NULL,
  `Korting_250_499` decimal(5,2) DEFAULT NULL,
  `Korting_500_999` decimal(5,2) DEFAULT NULL,
  PRIMARY KEY (`EAN`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `AndereBindwijzen`
--

CREATE TABLE `AndereBindwijzen` (
  `Ean` char(13) NOT NULL,
  `Eangekoppeld` char(13) NOT NULL,
  PRIMARY KEY (`Ean`,`Eangekoppeld`),
  KEY `Eangekoppeld` (`Eangekoppeld`),
  CONSTRAINT `AndereBindwijzen_ibfk_1` FOREIGN KEY (`Ean`) REFERENCES `Boeken` (`Ean`),
  CONSTRAINT `AndereBindwijzen_ibfk_2` FOREIGN KEY (`Eangekoppeld`) REFERENCES `Boeken` (`Ean`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `AndereTalen`
--

CREATE TABLE `AndereTalen` (
  `Ean` char(13) NOT NULL,
  `Eangekoppeld` char(13) NOT NULL,
  PRIMARY KEY (`Ean`,`Eangekoppeld`),
  KEY `Eangekoppeld` (`Eangekoppeld`),
  CONSTRAINT `AndereTalen_ibfk_1` FOREIGN KEY (`Ean`) REFERENCES `Boeken` (`Ean`),
  CONSTRAINT `AndereTalen_ibfk_2` FOREIGN KEY (`Eangekoppeld`) REFERENCES `Boeken` (`Ean`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `Artikelgroep`
--

CREATE TABLE `Artikelgroep` (
  `Id` int(11) NOT NULL,
  `Artgroep` char(4) NOT NULL,
  `Defaultbtwcode` char(1) DEFAULT NULL,
  `Omschrijving` char(50) DEFAULT NULL,
  `Artikelgroep_grootboekrekening` int(11) DEFAULT NULL,
  `Artikelgroep_BTWTarief` decimal(6,2) DEFAULT '1.06',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `Auteurs`
--

CREATE TABLE `Auteurs` (
  `Auteurid` int(11) NOT NULL AUTO_INCREMENT,
  `Achternaam` varchar(150) NOT NULL DEFAULT '',
  `Voorletters` varchar(50) NOT NULL DEFAULT '',
  `Voorvoegsels` varchar(20) NOT NULL DEFAULT '',
  `Voornaam` varchar(150) NOT NULL DEFAULT '',
  `Email` varchar(255) DEFAULT NULL,
  `Steltprijsopemail` smallint(6) DEFAULT NULL,
  `Omschrijving` text,
  `Omschrijvingkort` text,
  `Website` text,
  `Websitelinken` smallint(6) DEFAULT NULL,
  `is_spreker` smallint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`Auteurid`),
  KEY `i1` (`Auteurid`),
  KEY `i2` (`Achternaam`)
) ENGINE=InnoDB AUTO_INCREMENT=62664 DEFAULT CHARSET=utf8;

--
-- Temporary table structure for view `Auteurs_met_naam`
--

/*!50001 DROP VIEW IF EXISTS `Auteurs_met_naam`*/;
/*!50001 CREATE TABLE `Auteurs_met_naam` (
  `Auteurid` tinyint NOT NULL,
  `Achternaam` tinyint NOT NULL,
  `Voorletters` tinyint NOT NULL,
  `Voorvoegsels` tinyint NOT NULL,
  `Voornaam` tinyint NOT NULL,
  `Email` tinyint NOT NULL,
  `Steltprijsopemail` tinyint NOT NULL,
  `Omschrijving` tinyint NOT NULL,
  `Omschrijvingkort` tinyint NOT NULL,
  `Website` tinyint NOT NULL,
  `Websitelinken` tinyint NOT NULL,
  `VolledigeNaam` tinyint NOT NULL,
  `NaamVolledig` tinyint NOT NULL,
  `is_spreker` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Table structure for table `BibliografischeWijzigingen`
--

CREATE TABLE `BibliografischeWijzigingen` (
  `EAN` char(13) NOT NULL,
  `DT` datetime NOT NULL,
  `Wijziging` varchar(250) NOT NULL,
  `Gebruiker` varchar(3) NOT NULL,
  PRIMARY KEY (`EAN`,`DT`),
  KEY `EAN` (`EAN`),
  CONSTRAINT `BibliografischeWijzigingen_ibfk_1` FOREIGN KEY (`EAN`) REFERENCES `Boeken` (`Ean`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `Bindwijze`
--

CREATE TABLE `Bindwijze` (
  `Bindwijze` varchar(50) NOT NULL,
  `Btwcode` char(1) NOT NULL DEFAULT '4',
  `Artgroepid` int(11) NOT NULL,
  `Unspsc` char(50) DEFAULT NULL,
  `Productsoort` char(50) DEFAULT NULL,
  `Isgewist` int(11) DEFAULT NULL,
  `Zichtbaar` tinyint(1) NOT NULL DEFAULT '0',
  `BoekStanus` int(11) DEFAULT NULL,
  `Porto` smallint(6) DEFAULT NULL,
  `Evenement` smallint(6) DEFAULT NULL,
  `Download` smallint(6) DEFAULT NULL,
  `Voorraadartikel` smallint(6) DEFAULT NULL,
  `Inpakken` smallint(6) DEFAULT NULL,
  `Ideal` smallint(6) DEFAULT NULL,
  `Socialdrm` smallint(6) DEFAULT NULL,
  `Bindwijze_unspsc` char(50) DEFAULT NULL,
  `Bindwijze_productsoort` char(50) DEFAULT NULL,
  `Bindwijze_porto` smallint(6) DEFAULT NULL,
  `Bindwijze_evenement` smallint(6) DEFAULT NULL,
  `Bindwijze_download` smallint(6) DEFAULT NULL,
  `Bindwijze_voorraadartikel` smallint(6) DEFAULT NULL,
  `Bindwijze_inpakken` smallint(6) DEFAULT NULL,
  `Bindwijze_ideal` smallint(6) DEFAULT NULL,
  `Bindwijze_socialdrm` smallint(6) DEFAULT NULL,
  `Bindwijze_url` char(50) DEFAULT NULL,
  `Bindwijze_producttype` char(100) DEFAULT NULL,
  `Bindwijze_lidwoord` char(20) DEFAULT NULL,
  `Bindwijze_enkelvoud` char(100) DEFAULT NULL,
  `Bindwijze_meervoud` char(100) DEFAULT NULL,
  `Bindwijze_grootboekrekening` int(11) DEFAULT NULL,
  `Bindwijze_vertaling` char(50) DEFAULT NULL,
  `Bindwijze_leesid` tinyint(1) NOT NULL DEFAULT '0',
  `Bindwijze_periodiek` tinyint(1) NOT NULL DEFAULT '0',
  `Bindwijze_boekenweekgeschenk` tinyint(1) DEFAULT '0',
  `Bindwijze_incompany` tinyint(1) NOT NULL DEFAULT '0',
  `Bindwijze_bestelbaar` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`Bindwijze`),
  KEY `NewIndex1` (`Productsoort`),
  KEY `Productsoort` (`Bindwijze_productsoort`),
  KEY `Bindwijze_incompany` (`Bindwijze_incompany`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`thijs`@`%`*/ /*!50003 TRIGGER Manpo.after_insert_bindwijze AFTER INSERT ON Manpo.Bindwijze
FOR EACH ROW BEGIN
	INSERT INTO `Managementboek_Boeken`.`Bindwijze` ( `Bindwijze`, `Btwcode`, `Artgroepid`, `Unspsc`, `Productsoort`, `Isgewist`, `Porto`, `Evenement`, `Download`, `Voorraadartikel`, `Inpakken`, `Ideal`, `Socialdrm`, `Bindwijze_unspsc`, `Bindwijze_productsoort`, `Bindwijze_porto`, `Bindwijze_evenement`, `Bindwijze_download`, `Bindwijze_voorraadartikel`, `Bindwijze_inpakken`, `Bindwijze_ideal`, `Bindwijze_socialdrm`, `Bindwijze_url`, `Bindwijze_producttype`, `Bindwijze_lidwoord`, `Bindwijze_enkelvoud`, `Bindwijze_meervoud`, `Bindwijze_grootboekrekening`, `Bindwijze_vertaling`, `Bindwijze_leesid`, `Bindwijze_periodiek` )
	VALUES (
		NEW.`Bindwijze`,
		NEW.`Btwcode`,
		NEW.`Artgroepid`,
		NEW.`Unspsc`,
		NEW.`Productsoort`,
		NEW.`Isgewist`,
		NEW.`Porto`,
		NEW.`Evenement`,
		NEW.`Download`,
		NEW.`Voorraadartikel`,
		NEW.`Inpakken`,
		NEW.`Ideal`,
		NEW.`Socialdrm`,
		NEW.`Bindwijze_unspsc`,
		NEW.`Bindwijze_productsoort`,
		NEW.`Bindwijze_porto`,
		NEW.`Bindwijze_evenement`,
		NEW.`Bindwijze_download`,
		NEW.`Bindwijze_voorraadartikel`,
		NEW.`Bindwijze_inpakken`,
		NEW.`Bindwijze_ideal`,
		NEW.`Bindwijze_socialdrm`,
		NEW.`Bindwijze_url`,
		NEW.`Bindwijze_producttype`,
		NEW.`Bindwijze_lidwoord`,
		NEW.`Bindwijze_enkelvoud`,
		NEW.`Bindwijze_meervoud`,
		NEW.`Bindwijze_grootboekrekening`,
		NEW.`Bindwijze_vertaling`,
		NEW.`Bindwijze_leesid`,
		NEW.`Bindwijze_periodiek` )
	ON DUPLICATE KEY UPDATE `Btwcode` = VALUES( `Btwcode` ),
		`Artgroepid` = VALUES( `Artgroepid` ),
		`Unspsc` = VALUES( `Unspsc` ),
		`Productsoort` = VALUES( `Productsoort` ),
		`Isgewist` = VALUES( `Isgewist` ),
		`Porto` = VALUES( `Porto` ),
		`Evenement` = VALUES( `Evenement` ),
		`Download` = VALUES( `Download` ),
		`Voorraadartikel` = VALUES( `Voorraadartikel` ),
		`Inpakken` = VALUES( `Inpakken` ),
		`Ideal` = VALUES( `Ideal` ),
		`Socialdrm` = VALUES( `Socialdrm` ),
		`Bindwijze_unspsc` = VALUES( `Bindwijze_unspsc` ),
		`Bindwijze_productsoort` = VALUES( `Bindwijze_productsoort` ),
		`Bindwijze_porto` = VALUES( `Bindwijze_porto` ),
		`Bindwijze_evenement` = VALUES( `Bindwijze_evenement` ),
		`Bindwijze_download` = VALUES( `Bindwijze_download` ),
		`Bindwijze_voorraadartikel` = VALUES( `Bindwijze_voorraadartikel` ),
		`Bindwijze_inpakken` = VALUES( `Bindwijze_inpakken` ),
		`Bindwijze_ideal` = VALUES( `Bindwijze_ideal` ),
		`Bindwijze_socialdrm` = VALUES( `Bindwijze_socialdrm` ),
		`Bindwijze_url` = VALUES( `Bindwijze_url` ),
		`Bindwijze_producttype` = VALUES( `Bindwijze_producttype` ),
		`Bindwijze_lidwoord` = VALUES( `Bindwijze_lidwoord` ),
		`Bindwijze_enkelvoud` = VALUES( `Bindwijze_enkelvoud` ),
		`Bindwijze_meervoud` = VALUES( `Bindwijze_meervoud` ),
		`Bindwijze_grootboekrekening` = VALUES( `Bindwijze_grootboekrekening` ),
		`Bindwijze_vertaling` = VALUES( `Bindwijze_vertaling` ),
		`Bindwijze_leesid` = VALUES( `Bindwijze_leesid` ),
		`Bindwijze_periodiek` = VALUES( `Bindwijze_periodiek` );
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`thijs`@`%`*/ /*!50003 TRIGGER Manpo.after_update_bindwijze AFTER UPDATE ON Manpo.Bindwijze
FOR EACH ROW BEGIN
	INSERT INTO `Managementboek_Boeken`.`Bindwijze` ( `Bindwijze`, `Btwcode`, `Artgroepid`, `Unspsc`, `Productsoort`, `Isgewist`, `Porto`, `Evenement`, `Download`, `Voorraadartikel`, `Inpakken`, `Ideal`, `Socialdrm`, `Bindwijze_unspsc`, `Bindwijze_productsoort`, `Bindwijze_porto`, `Bindwijze_evenement`, `Bindwijze_download`, `Bindwijze_voorraadartikel`, `Bindwijze_inpakken`, `Bindwijze_ideal`, `Bindwijze_socialdrm`, `Bindwijze_url`, `Bindwijze_producttype`, `Bindwijze_lidwoord`, `Bindwijze_enkelvoud`, `Bindwijze_meervoud`, `Bindwijze_grootboekrekening`, `Bindwijze_vertaling`, `Bindwijze_leesid`, `Bindwijze_periodiek` )
	VALUES (
		NEW.`Bindwijze`,
		NEW.`Btwcode`,
		NEW.`Artgroepid`,
		NEW.`Unspsc`,
		NEW.`Productsoort`,
		NEW.`Isgewist`,
		NEW.`Porto`,
		NEW.`Evenement`,
		NEW.`Download`,
		NEW.`Voorraadartikel`,
		NEW.`Inpakken`,
		NEW.`Ideal`,
		NEW.`Socialdrm`,
		NEW.`Bindwijze_unspsc`,
		NEW.`Bindwijze_productsoort`,
		NEW.`Bindwijze_porto`,
		NEW.`Bindwijze_evenement`,
		NEW.`Bindwijze_download`,
		NEW.`Bindwijze_voorraadartikel`,
		NEW.`Bindwijze_inpakken`,
		NEW.`Bindwijze_ideal`,
		NEW.`Bindwijze_socialdrm`,
		NEW.`Bindwijze_url`,
		NEW.`Bindwijze_producttype`,
		NEW.`Bindwijze_lidwoord`,
		NEW.`Bindwijze_enkelvoud`,
		NEW.`Bindwijze_meervoud`,
		NEW.`Bindwijze_grootboekrekening`,
		NEW.`Bindwijze_vertaling`,
		NEW.`Bindwijze_leesid`,
		NEW.`Bindwijze_periodiek` )
	ON DUPLICATE KEY UPDATE `Btwcode` = VALUES( `Btwcode` ),
		`Artgroepid` = VALUES( `Artgroepid` ),
		`Unspsc` = VALUES( `Unspsc` ),
		`Productsoort` = VALUES( `Productsoort` ),
		`Isgewist` = VALUES( `Isgewist` ),
		`Porto` = VALUES( `Porto` ),
		`Evenement` = VALUES( `Evenement` ),
		`Download` = VALUES( `Download` ),
		`Voorraadartikel` = VALUES( `Voorraadartikel` ),
		`Inpakken` = VALUES( `Inpakken` ),
		`Ideal` = VALUES( `Ideal` ),
		`Socialdrm` = VALUES( `Socialdrm` ),
		`Bindwijze_unspsc` = VALUES( `Bindwijze_unspsc` ),
		`Bindwijze_productsoort` = VALUES( `Bindwijze_productsoort` ),
		`Bindwijze_porto` = VALUES( `Bindwijze_porto` ),
		`Bindwijze_evenement` = VALUES( `Bindwijze_evenement` ),
		`Bindwijze_download` = VALUES( `Bindwijze_download` ),
		`Bindwijze_voorraadartikel` = VALUES( `Bindwijze_voorraadartikel` ),
		`Bindwijze_inpakken` = VALUES( `Bindwijze_inpakken` ),
		`Bindwijze_ideal` = VALUES( `Bindwijze_ideal` ),
		`Bindwijze_socialdrm` = VALUES( `Bindwijze_socialdrm` ),
		`Bindwijze_url` = VALUES( `Bindwijze_url` ),
		`Bindwijze_producttype` = VALUES( `Bindwijze_producttype` ),
		`Bindwijze_lidwoord` = VALUES( `Bindwijze_lidwoord` ),
		`Bindwijze_enkelvoud` = VALUES( `Bindwijze_enkelvoud` ),
		`Bindwijze_meervoud` = VALUES( `Bindwijze_meervoud` ),
		`Bindwijze_grootboekrekening` = VALUES( `Bindwijze_grootboekrekening` ),
		`Bindwijze_vertaling` = VALUES( `Bindwijze_vertaling` ),
		`Bindwijze_leesid` = VALUES( `Bindwijze_leesid` ),
		`Bindwijze_periodiek` = VALUES( `Bindwijze_periodiek` );
END */;;
DELIMITER ;

--
-- Table structure for table `BoekAuteurs`
--

CREATE TABLE `BoekAuteurs` (
  `Ean` char(13) NOT NULL,
  `Auteurid` int(11) NOT NULL,
  `AuteurPositie` int(11) DEFAULT NULL,
  PRIMARY KEY (`Ean`,`Auteurid`),
  KEY `Auteurid` (`Auteurid`),
  CONSTRAINT `BoekAuteurs_ibfk_1` FOREIGN KEY (`Ean`) REFERENCES `Boeken` (`Ean`),
  CONSTRAINT `BoekAuteurs_ibfk_2` FOREIGN KEY (`Auteurid`) REFERENCES `Auteurs` (`Auteurid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `BoekNUR`
--

CREATE TABLE `BoekNUR` (
  `Ean` char(13) NOT NULL DEFAULT '',
  `NUR` char(3) NOT NULL DEFAULT '',
  PRIMARY KEY (`Ean`,`NUR`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `BoekPrijzen`
--

CREATE TABLE `BoekPrijzen` (
  `PrijsId` int(11) NOT NULL AUTO_INCREMENT,
  `Ean` char(13) NOT NULL,
  `IsAanbieding` tinyint(1) NOT NULL DEFAULT '0',
  `Prijs` decimal(10,2) NOT NULL DEFAULT '0.00',
  `PrijsExBtw` decimal(10,2) NOT NULL DEFAULT '0.00',
  `PrijsExBtw2` decimal(10,2) DEFAULT '0.00',
  `Valuta` varchar(4) NOT NULL DEFAULT 'EUR',
  `BtwTarief` varchar(10) NOT NULL,
  `BtwTarief2` varchar(10) DEFAULT NULL,
  `PrijsVanaf` date DEFAULT NULL,
  `PrijsTot` date DEFAULT NULL,
  `Laatstgewijzigd` datetime DEFAULT NULL COMMENT 'dt vanuit .onx bestand (gegenereerd)',
  `Boeksoort_code` char(1) DEFAULT NULL COMMENT 'vanuit onix',
  `Boeksoort` varchar(25) DEFAULT NULL COMMENT 'vanuit onix',
  PRIMARY KEY (`PrijsId`),
  KEY `ean` (`Ean`),
  KEY `prijsvanaf` (`PrijsVanaf`),
  KEY `datumtot` (`PrijsTot`),
  KEY `aanmaakdatum` (`Laatstgewijzigd`),
  CONSTRAINT `Ean` FOREIGN KEY (`Ean`) REFERENCES `Boeken` (`Ean`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `BoekQuotes`
--

CREATE TABLE `BoekQuotes` (
  `quoteId` int(11) NOT NULL AUTO_INCREMENT,
  `Ean` char(13) CHARACTER SET latin1 DEFAULT NULL,
  `quote` text,
  PRIMARY KEY (`quoteId`)
) ENGINE=InnoDB AUTO_INCREMENT=959 DEFAULT CHARSET=utf8;

--
-- Table structure for table `BoekRanking`
--

CREATE TABLE `BoekRanking` (
  `EAN` char(13) NOT NULL,
  `Website` enum('Managementboek','Computerboek','Juridischboek','Overig') NOT NULL,
  `Rubriek` varchar(255) NOT NULL,
  `7d` int(10) unsigned NOT NULL DEFAULT '9999999',
  `30d` int(10) unsigned NOT NULL DEFAULT '9999999',
  `90d` int(10) unsigned NOT NULL DEFAULT '9999999',
  `year` int(10) unsigned NOT NULL DEFAULT '9999999',
  `hoogste_pos` int(10) unsigned NOT NULL DEFAULT '9999999',
  `dagen_top10` int(10) unsigned NOT NULL DEFAULT '0',
  `dagen_top100` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`EAN`,`Website`,`Rubriek`),
  KEY `week` (`7d`),
  KEY `maand` (`30d`),
  KEY `hoogste` (`hoogste_pos`),
  KEY `Website` (`Website`),
  CONSTRAINT `ibfk_ean` FOREIGN KEY (`EAN`) REFERENCES `Boeken` (`Ean`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `BoekRanking_Historie`
--

CREATE TABLE `BoekRanking_Historie` (
  `EAN` char(13) NOT NULL,
  `Website` enum('Managementboek','Computerboek','Juridischboek','Overig') NOT NULL,
  `Datum` date NOT NULL,
  `Ranking` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`EAN`,`Website`,`Datum`),
  KEY `Datum` (`Datum`),
  KEY `EAN` (`EAN`),
  KEY `Website` (`Website`,`Datum`,`Ranking`),
  CONSTRAINT `ibfk_historisch_ean` FOREIGN KEY (`EAN`) REFERENCES `Boeken` (`Ean`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `BoekRubrieken`
--

CREATE TABLE `BoekRubrieken` (
  `Ean` char(13) NOT NULL,
  `Website` enum('Managementboek','Computerboek','Juridischboek','Overig') NOT NULL,
  `Volgorde` int(11) unsigned NOT NULL,
  `Rubriek` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Ean`,`Website`,`Volgorde`),
  KEY `Website` (`Website`,`Rubriek`),
  CONSTRAINT `BoekRubrieken_ibfk_1` FOREIGN KEY (`Website`, `Rubriek`) REFERENCES `Rubrieken` (`Website`, `Rubriek`) ON UPDATE CASCADE,
  CONSTRAINT `BoekRubrieken_ibfk_2` FOREIGN KEY (`Ean`) REFERENCES `Boeken` (`Ean`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `BoekVervangtEAN`
--

CREATE TABLE `BoekVervangtEAN` (
  `Ean` char(13) NOT NULL,
  `Vervangt` char(13) NOT NULL,
  PRIMARY KEY (`Ean`,`Vervangt`),
  KEY `i2` (`Vervangt`),
  CONSTRAINT `BoekVervangtEAN_ibfk_1` FOREIGN KEY (`Ean`) REFERENCES `Boeken` (`Ean`),
  CONSTRAINT `BoekVervangtEAN_ibfk_2` FOREIGN KEY (`Vervangt`) REFERENCES `Boeken` (`Ean`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Temporary table structure for view `Boek_met_auteur`
--

/*!50001 DROP VIEW IF EXISTS `Boek_met_auteur`*/;
/*!50001 CREATE TABLE `Boek_met_auteur` (
  `EAN` tinyint NOT NULL,
  `Titel` tinyint NOT NULL,
  `Bindwijze` tinyint NOT NULL,
  `TitelSuffix` tinyint NOT NULL,
  `AuteurID` tinyint NOT NULL,
  `Auteurnaam` tinyint NOT NULL,
  `URL` tinyint NOT NULL,
  `KortingMP` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Table structure for table `Boekbrieven`
--

CREATE TABLE `Boekbrieven` (
  `Brief_id` int(11) NOT NULL DEFAULT '0',
  `Brieftitel` char(255) NOT NULL DEFAULT '',
  `Omschrijving` char(255) DEFAULT NULL,
  `Volgorde` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`Brief_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `Boeken`
--

CREATE TABLE `Boeken` (
  `Ean` char(13) NOT NULL,
  `Titel` varchar(255) NOT NULL DEFAULT '',
  `Enanderen` tinyint(1) NOT NULL DEFAULT '0',
  `Ondertitel` varchar(255) DEFAULT NULL,
  `Titelserie` varchar(255) DEFAULT NULL,
  `Bindwijze` varchar(50) DEFAULT NULL,
  `Aantalblz` smallint(6) DEFAULT NULL,
  `Aantalblz_Romeins` smallint(6) DEFAULT NULL,
  `Jaaruitgifte` smallint(6) DEFAULT NULL,
  `Valuta` varchar(4) NOT NULL DEFAULT 'EUR',
  `Prijs` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT 'Ook nog echte prijstabel',
  `EuroPrijs` decimal(10,2) DEFAULT '0.00',
  `AanbiedingsEuroPrijs` decimal(10,2) DEFAULT NULL,
  `AanbiedingVanaf` date DEFAULT NULL,
  `AanbiedingTotEnMet` date DEFAULT NULL,
  `Aanbiedingssoort` enum('Automatisch','Handmatig') DEFAULT NULL,
  `Serieprijs` decimal(10,2) DEFAULT NULL,
  `Seriekorting` decimal(10,2) DEFAULT NULL,
  `Boekstatus` smallint(6) NOT NULL DEFAULT '0',
  `IsAanbieding` tinyint(1) NOT NULL DEFAULT '0',
  `Mlrubriek` varchar(100) DEFAULT NULL COMMENT 'Controleren/Overleggen',
  `UitgeverID` int(11) DEFAULT NULL,
  `Inhoudsopgave` text,
  `Spookfout` varchar(128) DEFAULT NULL,
  `Abstract` text,
  `Samenvatting` text,
  `Addendum` text,
  `Addendum_van` date DEFAULT NULL,
  `Addendum_tot` date DEFAULT NULL,
  `Aanmaakdatum` datetime NOT NULL,
  `Laatstgewijzigd` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Extraaandacht` tinyint(1) NOT NULL DEFAULT '0',
  `MarketingLevel` smallint(6) NOT NULL DEFAULT '0',
  `Taalvermelding` varchar(10) DEFAULT NULL,
  `Artgroep` varchar(4) DEFAULT NULL,
  `Cd` smallint(6) DEFAULT NULL,
  `Druk` smallint(6) DEFAULT NULL,
  `Artgroepid` int(11) DEFAULT NULL,
  `Btwcode` char(1) DEFAULT NULL,
  `Boeksoort` char(1) DEFAULT NULL,
  `Aangepastekortingmp` decimal(5,2) DEFAULT NULL,
  `Btwcode_tbv_export` char(1) DEFAULT NULL,
  `Magazijnsoort` char(50) DEFAULT NULL,
  `Verschijningsdatum` date DEFAULT NULL,
  `Afwijkendbestelpuntid` int(11) DEFAULT NULL,
  `Afwijkendbestelwijzeid` int(11) DEFAULT NULL,
  `Afwijkendminimalebestelhoeveelheid` int(11) DEFAULT NULL,
  `Voorraadartikel` tinyint(1) NOT NULL DEFAULT '1',
  `Aangepastemagazijnlocatienummer` char(20) CHARACTER SET ascii DEFAULT NULL,
  `Hoogte` int(11) DEFAULT NULL,
  `Breedte` int(11) DEFAULT NULL,
  `Dikte` int(11) DEFAULT NULL,
  `Gewicht` int(11) DEFAULT NULL,
  `Geenprijsupdatecb` tinyint(1) NOT NULL DEFAULT '0',
  `Geenstatusupdatecb` tinyint(1) NOT NULL DEFAULT '0',
  `Geenprijsupdatefeeds` tinyint(1) DEFAULT NULL COMMENT 'NULLABLE - Uitgever heeft hem ook',
  `Historieovernemen` tinyint(1) NOT NULL DEFAULT '1',
  `Herdrukdatum` date DEFAULT NULL,
  `Nieuw` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Controleren - Kees wist het ook niet',
  `Inpakken` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Controleren',
  `Porto` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Controleren',
  `Evenement` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Controleren',
  `Download` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Controleren',
  `Ideal` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Controleren',
  `Socialdrm` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Controleren',
  `Computer-boek` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Dit vinkje (geüpdatet in een trigger) geeft aan of een boek ook op Computerboek voorkomt of niet. Dit is een handigheidje om snel magazijnlocaties uit te kunnen rekenen.',
  `Uitsluiten_ranking_man` tinyint(1) NOT NULL DEFAULT '0',
  `Uitsluiten_ranking_com` tinyint(1) NOT NULL DEFAULT '0',
  `Uitsluiten_ranking_jur` tinyint(1) NOT NULL DEFAULT '0',
  `Boeken_inprijsopgeheven` tinyint(1) NOT NULL DEFAULT '0',
  `Boeken_retour_aangevraagd` date DEFAULT NULL,
  `Boeken_retour_goedgekeurd` date DEFAULT NULL,
  `Boeken_retour_afgewezen` date DEFAULT NULL,
  `Boeken_ramsj_goedgekeurd` date DEFAULT NULL,
  `Boeken_ramsj_afgewezen` date DEFAULT NULL,
  `Boeken_ramsj_uitsluiten` tinyint(1) NOT NULL DEFAULT '0',
  `Boeken_inramsj` tinyint(1) NOT NULL DEFAULT '0',
  `Boeken_inramsj_datum` date DEFAULT NULL,
  `Boeken_uitramsj_datum` date DEFAULT NULL,
  `Boeken_cvdm_gemaild` date DEFAULT NULL,
  `Boeken_cvdm_datum` date DEFAULT NULL,
  `Boeken_cvdm_aantal` int(11) DEFAULT '0',
  `Boeken_minimalevoorraad` int(11) unsigned DEFAULT NULL,
  `Boeken_maximalevoorraad` int(11) unsigned DEFAULT NULL,
  `Boeken_ROL` int(11) unsigned DEFAULT NULL,
  `Boeken_ROQ` int(11) unsigned DEFAULT NULL,
  `Afwijkende_ROQ` int(10) unsigned DEFAULT NULL,
  `Afwijkende_ROL` int(10) unsigned DEFAULT NULL,
  `Boeken_laatst_verkocht` date DEFAULT NULL COMMENT 'SPN: Laatst verkocht aan klant op datum',
  `Boeken_laatst_ingeklaard` date DEFAULT NULL COMMENT 'SPN: Laatst ingeklaard op datum',
  `Boeken_laatst_ingekocht` date DEFAULT NULL,
  `Boeken_nietmeerbestellen` tinyint(1) NOT NULL DEFAULT '0',
  `Boeken_afbouwen` tinyint(1) NOT NULL DEFAULT '0',
  `Boeken_geenporto` tinyint(1) NOT NULL DEFAULT '0',
  `Boeken_verborgenboek` tinyint(1) NOT NULL DEFAULT '0',
  `Boeken_projectcode` int(11) DEFAULT NULL,
  `Boeken_levertijd` int(11) DEFAULT NULL,
  `Boeken_laatst_onelijn` date DEFAULT NULL,
  `Boeken_reeksnummer` varchar(15) DEFAULT '',
  `Imprint` varchar(255) DEFAULT NULL,
  `CommercieleImprint` varchar(255) DEFAULT NULL,
  `Artikelcode_uitgever` varchar(10) DEFAULT NULL,
  `ISSN` char(10) DEFAULT NULL,
  `Afkorting` varchar(255) DEFAULT NULL,
  `AanvullendeInformatie` mediumtext,
  `ProductcodeUitgever` varchar(20) DEFAULT NULL,
  `ExterneURL` varchar(255) DEFAULT NULL,
  `Periodiek_start` date DEFAULT NULL,
  `Periodiek_eind` date DEFAULT NULL,
  `Periodiek_frequentie` int(11) DEFAULT '0',
  `Periodiek_verschijntonregelmatig` tinyint(4) DEFAULT '0',
  `Periodiek_NUGI` int(11) DEFAULT '0',
  `Periodiek_RWC` int(11) DEFAULT '0',
  `Periodiek_aantalbanden` int(11) DEFAULT '0',
  `Periodiek_onlinetoegang` tinyint(4) DEFAULT '0',
  `Periodiek_minimumlooptijd` int(4) DEFAULT '0',
  `Periodiek_looptijd` int(4) DEFAULT '0',
  `Periodiek_staking_maanden` int(11) DEFAULT NULL,
  `Periodiek_staking_type` enum('kalenderjaar','ingangsdatum') DEFAULT NULL,
  `Periodiek_prijsperpagina` tinyint(1) NOT NULL DEFAULT '0',
  `Markup_bedrag` decimal(10,2) DEFAULT '0.00',
  `Markup_percentage` tinyint(1) DEFAULT '0',
  `Consignatie` tinyint(4) DEFAULT '0',
  `Periodiek_verschijningsdata` text NOT NULL,
  `Tekst_Bevestigingsmail` text,
  `Tekst_Bevestigingsmail_van` date DEFAULT NULL,
  `Tekst_Bevestigingsmail_tot` date DEFAULT NULL,
  `Boeken_inkoop_door_manpo` tinyint(4) DEFAULT NULL COMMENT 'NULLABLE - Uitgever heeft hem ook',
  PRIMARY KEY (`Ean`),
  UNIQUE KEY `Ean` (`Ean`),
  KEY `Titel` (`Titel`),
  KEY `Valuta` (`Valuta`),
  KEY `Boeken_inramsj` (`Boeken_inramsj`),
  KEY `UitgeveID` (`UitgeverID`),
  KEY `Jaar` (`Jaaruitgifte`),
  KEY `Aanbieding` (`AanbiedingsEuroPrijs`),
  KEY `Boekstatus` (`Boekstatus`),
  KEY `Aanmaakdatum` (`Aanmaakdatum`),
  KEY `Laatstgewijzigd` (`Laatstgewijzigd`),
  KEY `Extraaandacht` (`Extraaandacht`),
  KEY `MarketingLevel` (`MarketingLevel`),
  KEY `Titelserie` (`Titelserie`),
  KEY `Bindwijze` (`Bindwijze`),
  KEY `EuroPrijs` (`EuroPrijs`),
  KEY `Boeken_verborgenboek` (`Boeken_verborgenboek`),
  KEY `Consigniliere` (`Consignatie`),
  KEY `Verschijningsdatum` (`Verschijningsdatum`),
  KEY `Artikelcode_uitgever` (`Artikelcode_uitgever`),
  CONSTRAINT `Boeken_ibfk_1` FOREIGN KEY (`Bindwijze`) REFERENCES `Bindwijze` (`Bindwijze`),
  CONSTRAINT `Boeken_ibfk_2` FOREIGN KEY (`UitgeverID`) REFERENCES `Uitgevers` (`UitgeverID`),
  CONSTRAINT `Boeken_ibfk_3` FOREIGN KEY (`Titelserie`) REFERENCES `Titelserie` (`Titelserie`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`thijs`@`%`*/ /*!50003 TRIGGER `before_insert_boeken` BEFORE INSERT ON `Boeken` 
    FOR EACH ROW BEGIN
	SET NEW.IsAanbieding = Ifnull(( NEW.AanbiedingsEuroPrijs > 0
		AND NOW() BETWEEN IFNULL(NULLIF(NEW.AanbiedingVanaf,'0000-00-00'),'2000-01-01')
			AND IFNULL(NULLIF(NEW.AanbiedingTotEnMet,'0000-00-00'),'2050-12-31') ), 0 );
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`thijs`@`%`*/ /*!50003 TRIGGER Manpo.after_insert_boeken AFTER INSERT ON Manpo.Boeken
	FOR EACH ROW BEGIN
	
		REPLACE INTO Manpo.Mapped_EAN ( U8, L1 ) VALUES ( NEW.EAN, NEW.EAN );
	END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`thijs`@`%`*/ /*!50003 TRIGGER `before_update_boeken` BEFORE UPDATE ON `Boeken` 
FOR EACH ROW BEGIN
	SET NEW.EuroPrijs = calc_prijs( OLD.Prijs, OLD.Valuta );
	if NEW.Boekstatus NOT IN ( 2, 5 ) THEN
		SET NEW.Herdrukdatum = null;
	END IF;
	IF NEW.Boekstatus != OLD.Boekstatus THEN
		REPLACE INTO StatusLog ( EAN, DT, Boekstatus_oud, Boekstatus_nieuw )
		VALUES ( NEW.EAN, NOW(), OLD.Boekstatus, NEW.Boekstatus );
	end IF;
	
	SET NEW.IsAanbieding = IFNULL(( NEW.AanbiedingsEuroPrijs > 0
		AND NOW() BETWEEN IFNULL(NULLIF(NEW.AanbiedingVanaf,'0000-00-00'),'2000-01-01')
			AND IFNULL(NULLIF(NEW.AanbiedingTotEnMet,'0000-00-00'),'2050-12-31') ), 0);
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`thijs`@`%`*/ /*!50003 TRIGGER Manpo.after_update_boeken AFTER UPDATE ON Manpo.Boeken
	FOR EACH ROW BEGIN
	
		if OLD.EAN != NEW.EAN THEN
			UPDATE Manpo.Mapped_EAN
			SET U8 = NEW.EAN, L1 = NEW.EAN
			WHERE U8 = OLD.EAN;
		end if;
	END */;;
DELIMITER ;

--
-- Temporary table structure for view `BoekenOpBestellijstOfNCOI`
--

/*!50001 DROP VIEW IF EXISTS `BoekenOpBestellijstOfNCOI`*/;
/*!50001 CREATE TABLE `BoekenOpBestellijstOfNCOI` (
  `EAN` tinyint NOT NULL,
  `naam` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `Boeken_in_ramsj`
--

/*!50001 DROP VIEW IF EXISTS `Boeken_in_ramsj`*/;
/*!50001 CREATE TABLE `Boeken_in_ramsj` (
  `Ean` tinyint NOT NULL,
  `Verschijningsdatum` tinyint NOT NULL,
  `Jaaruitgifte` tinyint NOT NULL,
  `Titel` tinyint NOT NULL,
  `Taalvermelding` tinyint NOT NULL,
  `Achternaam` tinyint NOT NULL,
  `Prijs` tinyint NOT NULL,
  `EuroPrijs` tinyint NOT NULL,
  `Valuta` tinyint NOT NULL,
  `AanbiedingsEuroPrijs` tinyint NOT NULL,
  `AanbiedingVanaf` tinyint NOT NULL,
  `AanbiedingTotEnMet` tinyint NOT NULL,
  `Boekstatus` tinyint NOT NULL,
  `Boeken_laatst_verkocht` tinyint NOT NULL,
  `Boeken_laatst_ingeklaard` tinyint NOT NULL,
  `Boeken_laatst_onelijn` tinyint NOT NULL,
  `BeschikbareVoorraad` tinyint NOT NULL,
  `Boeken_cvdm_gemaild` tinyint NOT NULL,
  `Boeken_cvdm_aantal` tinyint NOT NULL,
  `Boeken_inramsj_datum` tinyint NOT NULL,
  `Boeken_nietmeerbestellen` tinyint NOT NULL,
  `Boeken_inprijsopgeheven` tinyint NOT NULL,
  `KortingMP` tinyint NOT NULL,
  `Jaren_Verkocht` tinyint NOT NULL,
  `Jaren_Ingeklaard` tinyint NOT NULL,
  `Jaren_oud` tinyint NOT NULL,
  `GewensteKorting` tinyint NOT NULL,
  `GewensteActieprijs` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `Boeken_met_prijs`
--

/*!50001 DROP VIEW IF EXISTS `Boeken_met_prijs`*/;
/*!50001 CREATE TABLE `Boeken_met_prijs` (
  `EAN` tinyint NOT NULL,
  `IsAanbieding` tinyint NOT NULL,
  `HuidigeEuroPrijs` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `Boeken_uit_ramsj`
--

/*!50001 DROP VIEW IF EXISTS `Boeken_uit_ramsj`*/;
/*!50001 CREATE TABLE `Boeken_uit_ramsj` (
  `Ean` tinyint NOT NULL,
  `Titel` tinyint NOT NULL,
  `Computer-boek` tinyint NOT NULL,
  `Achternaam` tinyint NOT NULL,
  `Taalvermelding` tinyint NOT NULL,
  `Verschijningsdatum` tinyint NOT NULL,
  `Aanmaakdatum` tinyint NOT NULL,
  `Jaaruitgifte` tinyint NOT NULL,
  `Prijs` tinyint NOT NULL,
  `EuroPrijs` tinyint NOT NULL,
  `Valuta` tinyint NOT NULL,
  `AanbiedingsEuroPrijs` tinyint NOT NULL,
  `AanbiedingVanaf` tinyint NOT NULL,
  `AanbiedingTotEnMet` tinyint NOT NULL,
  `Boekstatus` tinyint NOT NULL,
  `Boeken_laatst_verkocht` tinyint NOT NULL,
  `Boeken_laatst_ingeklaard` tinyint NOT NULL,
  `Boeken_laatst_ingekocht` tinyint NOT NULL,
  `Boeken_laatst_onelijn` tinyint NOT NULL,
  `BeschikbareVoorraad` tinyint NOT NULL,
  `Boeken_cvdm_gemaild` tinyint NOT NULL,
  `Boeken_cvdm_aantal` tinyint NOT NULL,
  `Boeken_nietmeerbestellen` tinyint NOT NULL,
  `Boeken_inprijsopgeheven` tinyint NOT NULL,
  `Boeken_ramsj_uitsluiten` tinyint NOT NULL,
  `KortingMP` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `Boeken_wijzig_ramsj`
--

/*!50001 DROP VIEW IF EXISTS `Boeken_wijzig_ramsj`*/;
/*!50001 CREATE TABLE `Boeken_wijzig_ramsj` (
  `Ean` tinyint NOT NULL,
  `Verschijningsdatum` tinyint NOT NULL,
  `Jaaruitgifte` tinyint NOT NULL,
  `Titel` tinyint NOT NULL,
  `Taalvermelding` tinyint NOT NULL,
  `Achternaam` tinyint NOT NULL,
  `Prijs` tinyint NOT NULL,
  `EuroPrijs` tinyint NOT NULL,
  `Valuta` tinyint NOT NULL,
  `AanbiedingsEuroPrijs` tinyint NOT NULL,
  `AanbiedingVanaf` tinyint NOT NULL,
  `AanbiedingTotEnMet` tinyint NOT NULL,
  `Boekstatus` tinyint NOT NULL,
  `Boeken_laatst_verkocht` tinyint NOT NULL,
  `Boeken_laatst_ingeklaard` tinyint NOT NULL,
  `BeschikbareVoorraad` tinyint NOT NULL,
  `Boeken_cvdm_gemaild` tinyint NOT NULL,
  `Boeken_cvdm_aantal` tinyint NOT NULL,
  `Boeken_nietmeerbestellen` tinyint NOT NULL,
  `Boeken_inprijsopgeheven` tinyint NOT NULL,
  `FysiekeVoorraad` tinyint NOT NULL,
  `KortingMP` tinyint NOT NULL,
  `Jaren_Verkocht` tinyint NOT NULL,
  `Jaren_Ingeklaard` tinyint NOT NULL,
  `Jaren_oud` tinyint NOT NULL,
  `GewensteKorting` tinyint NOT NULL,
  `GewensteActieprijs` tinyint NOT NULL,
  `OudeGewensteActieprijs` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Table structure for table `Boektrefwoorden`
--

CREATE TABLE `Boektrefwoorden` (
  `Ean` char(13) NOT NULL,
  `Website` enum('Managementboek','Computerboek','Juridischboek','Overig') NOT NULL,
  `TrefwoordID` int(11) NOT NULL,
  PRIMARY KEY (`Ean`,`Website`,`TrefwoordID`),
  KEY `i2` (`TrefwoordID`),
  CONSTRAINT `Boektrefwoorden_ibfk_1` FOREIGN KEY (`TrefwoordID`) REFERENCES `Trefwoorden` (`TrefwoordID`),
  CONSTRAINT `Boektrefwoorden_ibfk_2` FOREIGN KEY (`Ean`) REFERENCES `Boeken` (`Ean`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `Btwcodeartikelgroep`
--

CREATE TABLE `Btwcodeartikelgroep` (
  `Artgroepid` int(11) NOT NULL,
  `Btwcode` char(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Temporary table structure for view `CMS_861`
--

/*!50001 DROP VIEW IF EXISTS `CMS_861`*/;
/*!50001 CREATE TABLE `CMS_861` (
  `Ean` tinyint NOT NULL,
  `Valuta` tinyint NOT NULL,
  `Uitgeverid` tinyint NOT NULL,
  `Taalvermelding` tinyint NOT NULL,
  `NaamUitgever` tinyint NOT NULL,
  `Bestelpunt` tinyint NOT NULL,
  `BestelpuntID` tinyint NOT NULL,
  `BestelwijzeID` tinyint NOT NULL,
  `Bestelwijze` tinyint NOT NULL,
  `Titel` tinyint NOT NULL,
  `Prijs` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Table structure for table `Cb_Titelserie`
--

CREATE TABLE `Cb_Titelserie` (
  `CbSerie` varchar(255) NOT NULL,
  `ManpoSerie` varchar(255) NOT NULL,
  PRIMARY KEY (`CbSerie`),
  KEY `ManpoSerie` (`ManpoSerie`),
  CONSTRAINT `Cb_Titelserie_ibfk_1` FOREIGN KEY (`ManpoSerie`) REFERENCES `Titelserie` (`Titelserie`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `Cb_Titelserie_initieel`
--

CREATE TABLE `Cb_Titelserie_initieel` (
  `CbSerie` varchar(255) NOT NULL,
  `ManpoSerie` varchar(255) NOT NULL,
  PRIMARY KEY (`CbSerie`),
  KEY `ManpoSerie` (`ManpoSerie`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `EventAuteurs`
--

CREATE TABLE `EventAuteurs` (
  `Ean` char(13) NOT NULL,
  `AuteurId` int(11) NOT NULL,
  `Volgnummer` tinyint(2) NOT NULL,
  `Prijs` decimal(7,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`Ean`,`AuteurId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `EventBoeken`
--

CREATE TABLE `EventBoeken` (
  `Ean` char(13) CHARACTER SET utf8 NOT NULL,
  `BoekEan` char(13) CHARACTER SET utf8 NOT NULL,
  `Volgnummer` tinyint(2) NOT NULL,
  PRIMARY KEY (`Ean`,`BoekEan`),
  KEY `BoekEan` (`BoekEan`),
  CONSTRAINT `EventBoeken_ibfk_1` FOREIGN KEY (`Ean`) REFERENCES `Events` (`Ean`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `EventBoeken_ibfk_2` FOREIGN KEY (`BoekEan`) REFERENCES `Boeken` (`Ean`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `EventKosten`
--

CREATE TABLE `EventKosten` (
  `KostenId` int(11) NOT NULL AUTO_INCREMENT,
  `Ean` char(13) NOT NULL,
  `Omschrijving` varchar(200) NOT NULL,
  `Prijs` decimal(7,2) NOT NULL,
  PRIMARY KEY (`KostenId`),
  KEY `Ean` (`Ean`),
  CONSTRAINT `EventKosten_ibfk_1` FOREIGN KEY (`Ean`) REFERENCES `Events` (`Ean`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=707 DEFAULT CHARSET=utf8;

--
-- Table structure for table `EventLocaties`
--

CREATE TABLE `EventLocaties` (
  `LocatieId` int(11) NOT NULL AUTO_INCREMENT,
  `Locatienaam` varchar(75) NOT NULL,
  `Locatieomschrijving` text NOT NULL,
  `Googlemaps` varchar(75) NOT NULL,
  `LocatieAfbeelding` varchar(100) NOT NULL,
  PRIMARY KEY (`LocatieId`)
) ENGINE=InnoDB AUTO_INCREMENT=215 DEFAULT CHARSET=latin1;

--
-- Table structure for table `Eventlog`
--

CREATE TABLE `Eventlog` (
  `LOG_ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `EAN` char(13) NOT NULL,
  `DT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Wie` varchar(100) NOT NULL,
  `Log` text NOT NULL,
  PRIMARY KEY (`LOG_ID`),
  KEY `ean` (`EAN`)
) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8 COMMENT='Op verzoek van Kees; hij gaat er nooit in kijken.';

--
-- Table structure for table `Events`
--

CREATE TABLE `Events` (
  `Ean` char(13) NOT NULL,
  `Leervorm` varchar(50) NOT NULL,
  `Tijd` varchar(75) NOT NULL,
  `ProgrammaOmschrijving` text NOT NULL,
  `Programma` text NOT NULL,
  `Preview` varchar(75) NOT NULL COMMENT 'url naar preview',
  `Pakketwaarde` text NOT NULL,
  `Voorwaarden` text NOT NULL,
  `Impressie` varchar(75) NOT NULL COMMENT 'url naar impressie',
  `LocatieId` int(11) NOT NULL,
  `Afgesloten` tinyint(1) NOT NULL DEFAULT '0',
  `AantalPlaatsen` int(11) DEFAULT '0',
  `WervingsKop` text NOT NULL,
  `Prijsindicatie` tinyint(1) NOT NULL DEFAULT '0',
  `Programmaduur` varchar(35) NOT NULL DEFAULT '',
  `GewenstAantalDeelnemers` varchar(35) NOT NULL DEFAULT '',
  PRIMARY KEY (`Ean`),
  CONSTRAINT `Events_ibfk_1` FOREIGN KEY (`Ean`) REFERENCES `Boeken` (`Ean`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `ExterneHistorischeAanbiedingsprijs`
--

CREATE TABLE `ExterneHistorischeAanbiedingsprijs` (
  `EAN` char(13) NOT NULL,
  `DT` date NOT NULL,
  `Partij` enum('CB','Wiley','Gardners','Pearson') NOT NULL,
  `Aanbiedingsprijs` decimal(10,2) DEFAULT NULL,
  `Aanbiedingvanaf` date DEFAULT NULL,
  `Aanbiedingtotenmet` date DEFAULT NULL,
  PRIMARY KEY (`EAN`,`DT`,`Partij`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 DELAY_KEY_WRITE=1 ROW_FORMAT=COMPRESSED;

--
-- Table structure for table `ExterneHistorischePrijs`
--

CREATE TABLE `ExterneHistorischePrijs` (
  `EAN` char(13) NOT NULL,
  `DT` date NOT NULL,
  `Partij` enum('CB','Wiley','Gardners','Pearson') NOT NULL,
  `Oud` decimal(10,2) DEFAULT NULL,
  `Nieuw` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`EAN`,`DT`,`Partij`),
  KEY `jemigdepemig` (`EAN`,`Partij`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 DELAY_KEY_WRITE=1 ROW_FORMAT=COMPRESSED;

--
-- Table structure for table `ExternePrijzen`
--

CREATE TABLE `ExternePrijzen` (
  `EAN` char(13) NOT NULL,
  `Valuta` char(5) DEFAULT NULL,
  `Listprijs` decimal(10,2) DEFAULT NULL,
  `CB` decimal(10,2) DEFAULT NULL,
  `Wiley` decimal(10,2) DEFAULT NULL,
  `Gardners` decimal(10,2) DEFAULT NULL,
  `Pearson` decimal(10,2) DEFAULT NULL,
  `Nielsen` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`EAN`),
  KEY `Valuta` (`Valuta`),
  KEY `Listprijs` (`Listprijs`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`thijs`@`%`*/ /*!50003 TRIGGER `before_update_externeprijzen` BEFORE UPDATE ON `ExternePrijzen` 
    FOR EACH ROW BEGIN
	SET NEW.Listprijs = NULL;
	IF NEW.Valuta = 'EUR' THEN
		SET NEW.Listprijs = NEW.CB;
	END IF;
	
	IF NEW.Valuta = 'PEUR' THEN
		SET NEW.Listprijs = NEW.Pearson;
	END IF;
	
	IF NEW.Valuta IN ( 'WIL', 'ORLL' ) THEN
		IF NEW.Wiley IS NOT NULL THEN
			SET NEW.Listprijs = NEW.Wiley;
		ELSE
			SET NEW.Listprijs = NEW.Gardners;
		END IF;
	END IF;
	
	if NEW.Valuta IN ( 'GA', 'GBP', 'M', 'THOM', 'TSO' ) THEN
		SET NEW.Listprijs = NEW.Gardners;
	end if;
	END */;;
DELIMITER ;

--
-- Table structure for table `GAEVB`
--

CREATE TABLE `GAEVB` (
  `OrigineelEAN` char(13) NOT NULL,
  `EAN` char(13) NOT NULL,
  PRIMARY KEY (`OrigineelEAN`,`EAN`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `GekoppeldEAN`
--

CREATE TABLE `GekoppeldEAN` (
  `Ean` char(13) NOT NULL,
  `Eangekoppeld` char(13) NOT NULL,
  PRIMARY KEY (`Ean`,`Eangekoppeld`),
  KEY `i1` (`Ean`),
  KEY `i2` (`Eangekoppeld`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `GewijzigdeVelden`
--

CREATE TABLE `GewijzigdeVelden` (
  `EAN` char(13) NOT NULL,
  `Veldnaam` char(40) NOT NULL,
  `Feed` enum('CB','CHB','CHV') CHARACTER SET ascii NOT NULL DEFAULT 'CB',
  `DT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`EAN`,`Veldnaam`,`Feed`),
  CONSTRAINT `ibfk_gewijzigd_ean` FOREIGN KEY (`EAN`) REFERENCES `Boeken` (`Ean`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `GewijzigdeVeldenIgnored`
--

CREATE TABLE `GewijzigdeVeldenIgnored` (
  `EAN` char(13) NOT NULL,
  `Veldnaam` char(40) NOT NULL,
  `Feed` enum('CB','CHB','CHV') NOT NULL DEFAULT 'CB',
  `Value` varchar(255) NOT NULL,
  `DT` datetime DEFAULT NULL,
  PRIMARY KEY (`EAN`,`Veldnaam`,`Feed`,`Value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `ImageOverig`
--

CREATE TABLE `ImageOverig` (
  `Type` enum('Auteur') NOT NULL,
  `ID` int(11) NOT NULL,
  `ImageExists` tinyint(1) NOT NULL DEFAULT '1',
  `Width` int(11) unsigned NOT NULL DEFAULT '0',
  `Height` int(11) unsigned NOT NULL DEFAULT '0',
  `Hash` char(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `DT` datetime NOT NULL,
  `R` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `G` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `B` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`Type`,`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `ImageSizes`
--

CREATE TABLE `ImageSizes` (
  `Ean` char(13) NOT NULL,
  `ImageExists` tinyint(4) NOT NULL DEFAULT '0',
  `Width` int(11) DEFAULT NULL,
  `Height` int(11) DEFAULT NULL,
  `IsOnregelmatig` tinyint(1) DEFAULT NULL,
  `Hash` char(64) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
  `DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `R` tinyint(3) unsigned DEFAULT NULL,
  `G` tinyint(3) unsigned DEFAULT NULL,
  `B` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`Ean`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `Koers`
--

CREATE TABLE `Koers` (
  `munt` varchar(10) NOT NULL,
  `waarde` double NOT NULL,
  `datum` date NOT NULL,
  `timestamp` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`munt`,`datum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `Kortingsstaffel`
--

CREATE TABLE `Kortingsstaffel` (
  `Boeksoort` enum('A','S','W','O','G','E') NOT NULL,
  `UitgeverID` int(11) DEFAULT NULL,
  `Korting_0_9` decimal(5,2) DEFAULT NULL,
  `Korting_10_29` decimal(5,2) DEFAULT NULL,
  `Korting_30_99` decimal(5,2) DEFAULT NULL,
  `Korting_100_249` decimal(5,2) DEFAULT NULL,
  `Korting_250_499` decimal(5,2) DEFAULT NULL,
  `Korting_500_999` decimal(5,2) DEFAULT NULL,
  UNIQUE KEY `Boeksoort-uitgever` (`Boeksoort`,`UitgeverID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `Leveringswijze`
--

CREATE TABLE `Leveringswijze` (
  `Id` int(11) NOT NULL,
  `Omschrijving` char(100) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `Locatie_cached`
--

CREATE TABLE `Locatie_cached` (
  `EAN` char(13) NOT NULL,
  `Locatie` char(4) CHARACTER SET ascii NOT NULL,
  PRIMARY KEY (`EAN`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `Mapped_EAN`
--

CREATE TABLE `Mapped_EAN` (
  `U8` char(13) NOT NULL,
  `L1` char(13) CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`U8`),
  UNIQUE KEY `uq_ean_latin1` (`L1`),
  CONSTRAINT `ibfk_mappedean_utf8` FOREIGN KEY (`U8`) REFERENCES `Boeken` (`Ean`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `OnregelmatigeCovers`
--

CREATE TABLE `OnregelmatigeCovers` (
  `ean` char(13) NOT NULL,
  `is_onregelmatig` tinyint(1) DEFAULT NULL,
  `geen_schaduw` tinyint(1) DEFAULT NULL,
  `datum` datetime DEFAULT NULL,
  PRIMARY KEY (`ean`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `PolitieBoeken`
--

CREATE TABLE `PolitieBoeken` (
  `Ean` char(13) NOT NULL,
  PRIMARY KEY (`Ean`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `ProductIDs`
--

CREATE TABLE `ProductIDs` (
  `ProductID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `Ean` char(13) NOT NULL,
  PRIMARY KEY (`ProductID`),
  UNIQUE KEY `UQ_ean` (`Ean`)
) ENGINE=InnoDB AUTO_INCREMENT=93560642 DEFAULT CHARSET=utf8;

--
-- Table structure for table `Producttype`
--

CREATE TABLE `Producttype` (
  `Productsoort` varchar(50) NOT NULL,
  `Producttype` varchar(100) DEFAULT NULL,
  `URLPrefix` varchar(50) NOT NULL,
  `IsPakket` tinyint(4) NOT NULL DEFAULT '0',
  `IsDownload` tinyint(4) NOT NULL DEFAULT '0',
  `IsSeminar` tinyint(4) NOT NULL DEFAULT '0',
  `IsAbo` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`Productsoort`),
  KEY `NewIndex1` (`Producttype`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `Recensenten`
--

CREATE TABLE `Recensenten` (
  `Recensentid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `Achternaam` varchar(150) NOT NULL DEFAULT '',
  `Voorvoegsels` varchar(20) DEFAULT NULL,
  `Voorletters` varchar(50) DEFAULT NULL,
  `Voornaam` varchar(150) DEFAULT NULL,
  `Bedrijfsnaam` varchar(255) DEFAULT NULL,
  `Titels` varchar(50) DEFAULT NULL,
  `Omschrijving` text,
  `Adres` varchar(255) DEFAULT NULL,
  `Postcode` varchar(7) DEFAULT NULL,
  `Woonplaats` varchar(255) DEFAULT NULL,
  `Telefoonwerk` varchar(25) DEFAULT NULL,
  `Telefoonprive` varchar(25) DEFAULT NULL,
  `Telefoonmobiel` varchar(25) DEFAULT NULL,
  `Fax` varchar(25) DEFAULT NULL,
  `Email` varchar(255) DEFAULT NULL,
  `Expemail` smallint(6) DEFAULT '0',
  `Bedrijfsurl` text,
  `Wwwadres` text,
  `Websitelinken` smallint(6) DEFAULT NULL,
  `Initialen` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`Recensentid`),
  KEY `i1` (`Recensentid`)
) ENGINE=InnoDB AUTO_INCREMENT=987 DEFAULT CHARSET=utf8;

--
-- Temporary table structure for view `Recensenten_met_naam`
--

/*!50001 DROP VIEW IF EXISTS `Recensenten_met_naam`*/;
/*!50001 CREATE TABLE `Recensenten_met_naam` (
  `RecensentID` tinyint NOT NULL,
  `Achternaam` tinyint NOT NULL,
  `Voorvoegsels` tinyint NOT NULL,
  `Voorletters` tinyint NOT NULL,
  `Voornaam` tinyint NOT NULL,
  `Bedrijfsnaam` tinyint NOT NULL,
  `Titels` tinyint NOT NULL,
  `Omschrijving` tinyint NOT NULL,
  `Adres` tinyint NOT NULL,
  `Postcode` tinyint NOT NULL,
  `Woonplaats` tinyint NOT NULL,
  `Telefoonwerk` tinyint NOT NULL,
  `Telefoonprive` tinyint NOT NULL,
  `Telefoonmobiel` tinyint NOT NULL,
  `Fax` tinyint NOT NULL,
  `Email` tinyint NOT NULL,
  `ExpEmail` tinyint NOT NULL,
  `Bedrijfsurl` tinyint NOT NULL,
  `Wwwadres` tinyint NOT NULL,
  `Websitelinken` tinyint NOT NULL,
  `VolledigeNaam` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Table structure for table `Recensies`
--

CREATE TABLE `Recensies` (
  `EAN` char(13) NOT NULL,
  `Recensentid` int(11) unsigned NOT NULL,
  `Aanmaakdatum` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `Laatstgewijzigd` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `Bronvermelding` varchar(255) DEFAULT NULL,
  `Urlbron` varchar(255) DEFAULT NULL,
  `Abstract` text NOT NULL,
  `Recensietekst` text NOT NULL,
  PRIMARY KEY (`EAN`,`Recensentid`),
  KEY `i1` (`EAN`),
  KEY `i2` (`Recensentid`),
  CONSTRAINT `ibfk_recensie_ean` FOREIGN KEY (`EAN`) REFERENCES `Boeken` (`Ean`) ON UPDATE CASCADE,
  CONSTRAINT `ibfk_recensie_recensent` FOREIGN KEY (`Recensentid`) REFERENCES `Recensenten` (`Recensentid`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `Recensieverzoeken`
--

CREATE TABLE `Recensieverzoeken` (
  `EAN` char(13) NOT NULL,
  `Datum` datetime NOT NULL,
  `Gebruiker` char(15) NOT NULL,
  PRIMARY KEY (`EAN`),
  CONSTRAINT `ibfk_recensieverzoek_EAN` FOREIGN KEY (`EAN`) REFERENCES `Boeken` (`Ean`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `Rubrieken`
--

CREATE TABLE `Rubrieken` (
  `Website` enum('Managementboek','Computerboek','Juridischboek','Overig') NOT NULL,
  `Rubriek` varchar(255) NOT NULL,
  `Volgnummer` int(11) NOT NULL DEFAULT '1',
  `Uitsluiten_ranking` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`Website`,`Rubriek`),
  KEY `Uitsluiten` (`Uitsluiten_ranking`),
  KEY `Website` (`Website`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Temporary table structure for view `Series_zonder_uitgever`
--

/*!50001 DROP VIEW IF EXISTS `Series_zonder_uitgever`*/;
/*!50001 CREATE TABLE `Series_zonder_uitgever` (
  `Titelserie` tinyint NOT NULL,
  `UitgeverID` tinyint NOT NULL,
  `Mogelijke Uitgevers` tinyint NOT NULL,
  `EANs` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Table structure for table `SprekerRubrieken`
--

CREATE TABLE `SprekerRubrieken` (
  `AuteurID` int(11) NOT NULL,
  `Website` enum('Managementboek','Computerboek','Juridischboek','Overig') NOT NULL,
  `Rubriek` varchar(255) NOT NULL,
  `Volgorde` int(11) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`AuteurID`,`Website`,`Rubriek`),
  UNIQUE KEY `uq_spreker_volgorde` (`AuteurID`,`Website`,`Volgorde`),
  KEY `ibfq_spreker_rubrieken` (`Website`,`Rubriek`),
  CONSTRAINT `ibfq_spreker_rubrieken` FOREIGN KEY (`Website`, `Rubriek`) REFERENCES `Rubrieken` (`Website`, `Rubriek`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `infq_spreker_auteur` FOREIGN KEY (`AuteurID`) REFERENCES `Auteurs` (`Auteurid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `SprekerThemas`
--

CREATE TABLE `SprekerThemas` (
  `Auteurid` int(11) NOT NULL,
  `Thema` varchar(100) NOT NULL,
  `Volgorde` int(11) DEFAULT NULL,
  PRIMARY KEY (`Auteurid`,`Thema`),
  KEY `Thema` (`Thema`),
  CONSTRAINT `SprekerThemas_ibfk_1` FOREIGN KEY (`Auteurid`) REFERENCES `Auteurs` (`Auteurid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `StatusLog`
--

CREATE TABLE `StatusLog` (
  `EAN` char(13) NOT NULL,
  `DT` datetime NOT NULL,
  `Boekstatus_oud` int(11) DEFAULT NULL,
  `Boekstatus_nieuw` int(11) NOT NULL,
  PRIMARY KEY (`EAN`,`DT`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `SummaryEANs`
--

CREATE TABLE `SummaryEANs` (
  `EAN` char(13) NOT NULL,
  `Summary` char(13) NOT NULL,
  PRIMARY KEY (`EAN`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `Tellingen`
--

CREATE TABLE `Tellingen` (
  `Ean` char(13) NOT NULL,
  `7d` bigint(13) NOT NULL DEFAULT '0',
  `30d` bigint(13) NOT NULL DEFAULT '0',
  `60d` bigint(13) NOT NULL DEFAULT '0',
  `90d` bigint(13) NOT NULL DEFAULT '0',
  `year` bigint(13) NOT NULL DEFAULT '0',
  `lastyear` bigint(13) NOT NULL DEFAULT '0',
  `ever` bigint(13) NOT NULL DEFAULT '0',
  PRIMARY KEY (`Ean`),
  KEY `7d` (`7d`),
  KEY `30d` (`30d`),
  KEY `90d` (`90d`),
  KEY `year` (`year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 DELAY_KEY_WRITE=1 ROW_FORMAT=COMPRESSED;

--
-- Table structure for table `TellingenTemp`
--

CREATE TABLE `TellingenTemp` (
  `Ean` char(13) NOT NULL,
  `7d` bigint(13) NOT NULL DEFAULT '0',
  `30d` bigint(13) NOT NULL DEFAULT '0',
  `60d` bigint(13) NOT NULL DEFAULT '0',
  `90d` bigint(13) NOT NULL DEFAULT '0',
  `year` bigint(13) NOT NULL DEFAULT '0',
  `lastyear` bigint(13) NOT NULL DEFAULT '0',
  `ever` bigint(13) NOT NULL DEFAULT '0',
  PRIMARY KEY (`Ean`),
  KEY `7d` (`7d`),
  KEY `30d` (`30d`),
  KEY `90d` (`90d`),
  KEY `year` (`year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 DELAY_KEY_WRITE=1 ROW_FORMAT=COMPRESSED;

--
-- Table structure for table `Testimonials`
--

CREATE TABLE `Testimonials` (
  `EAN` char(13) NOT NULL,
  `Testimonials` text NOT NULL,
  PRIMARY KEY (`EAN`),
  CONSTRAINT `ibfk_testimonials_ean` FOREIGN KEY (`EAN`) REFERENCES `Boeken` (`Ean`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `Titelserie`
--

CREATE TABLE `Titelserie` (
  `Titelserie` varchar(255) NOT NULL DEFAULT '',
  `Titelserie_Omschrijving` text,
  `UitgeverID` int(11) DEFAULT NULL,
  `is_abo` tinyint(1) NOT NULL DEFAULT '1',
  `is_herdrukservice` tinyint(4) NOT NULL DEFAULT '0',
  `bereken_serieprijs` tinyint(1) NOT NULL DEFAULT '0',
  `Juridica` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`Titelserie`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `TrefwoordThema`
--

CREATE TABLE `TrefwoordThema` (
  `TrefwoordID` int(11) NOT NULL,
  `Thema` varchar(127) DEFAULT NULL,
  PRIMARY KEY (`TrefwoordID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `Trefwoorden`
--

CREATE TABLE `Trefwoorden` (
  `TrefwoordID` int(11) NOT NULL AUTO_INCREMENT,
  `Trefwoord` varchar(75) NOT NULL,
  PRIMARY KEY (`TrefwoordID`),
  UNIQUE KEY `Trefwoord` (`Trefwoord`)
) ENGINE=InnoDB AUTO_INCREMENT=6347 DEFAULT CHARSET=utf8;

--
-- Table structure for table `UNSPSC`
--

CREATE TABLE `UNSPSC` (
  `UNSPSC` char(10) NOT NULL,
  `Omschrijving` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`UNSPSC`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `Uitgevers`
--

CREATE TABLE `Uitgevers` (
  `UitgeverID` int(11) NOT NULL AUTO_INCREMENT,
  `Klantnummer` varchar(50) DEFAULT NULL,
  `Naamuitgever` varchar(50) NOT NULL DEFAULT '',
  `Omschrijving` text,
  `Adres` varchar(255) DEFAULT NULL,
  `Postcode` varchar(7) DEFAULT NULL,
  `Woonplaats` varchar(255) DEFAULT NULL,
  `Telefoon` varchar(20) DEFAULT NULL,
  `Fax` varchar(20) DEFAULT NULL,
  `Email` varchar(255) DEFAULT NULL,
  `Contactpersoon` varchar(50) DEFAULT NULL,
  `Kortingmp_a` decimal(5,2) DEFAULT NULL,
  `Kortingmp_s` decimal(5,2) DEFAULT NULL,
  `Kortingmp_w` decimal(5,2) DEFAULT NULL,
  `Kortingmp_o` decimal(5,2) DEFAULT NULL,
  `Kortingmp_g` decimal(5,2) DEFAULT NULL,
  `Kortingmp_e` decimal(5,2) DEFAULT NULL,
  `Kortingmp_NCOI` decimal(5,2) DEFAULT NULL,
  `Magazijnlocatienummer` char(4) CHARACTER SET ascii DEFAULT NULL,
  `Magazijnlocatienummerbulk` char(4) CHARACTER SET ascii DEFAULT NULL,
  `Magazijnlocatienummercom` char(4) CHARACTER SET ascii DEFAULT NULL,
  `Magazijnlocatienummercombulk` char(4) CHARACTER SET ascii DEFAULT NULL,
  `Verborgen` tinyint(1) NOT NULL DEFAULT '0',
  `Crediteurnummer` int(11) DEFAULT NULL,
  `BestelpuntID` int(11) DEFAULT NULL,
  `BestelwijzeID` int(11) DEFAULT NULL,
  `NCOIBestelpuntID` int(11) DEFAULT NULL,
  `Minimalebestelhoeveelheid` int(11) DEFAULT NULL,
  `Feed` enum('CB','Pearson','Wiley','Gardners') NOT NULL DEFAULT 'CB',
  `FeedCode` char(8) DEFAULT NULL,
  `Aanhef` text,
  `Afsluiting` text,
  `Emailvoorvragen` char(255) DEFAULT NULL,
  `Email_marketing` varchar(200) NOT NULL,
  `Afspraak` smallint(1) NOT NULL DEFAULT '0',
  `Ruzie` smallint(1) NOT NULL DEFAULT '0',
  `MagONE` smallint(1) NOT NULL DEFAULT '0',
  `Retourneer_min` int(11) unsigned NOT NULL DEFAULT '90',
  `Retourneer_max` int(11) unsigned NOT NULL DEFAULT '330',
  `Email_ebook` char(255) DEFAULT NULL,
  `Automatisch_rapporteren` smallint(6) DEFAULT NULL,
  `Rapportageviacb` smallint(6) DEFAULT NULL,
  `Uitgevers_rapportagevanaf` date DEFAULT NULL,
  `Uitgevers_levertijd` int(11) DEFAULT NULL,
  `Email_abo_bestellen` varchar(255) DEFAULT NULL,
  `Email_abo_reclameren` varchar(255) DEFAULT NULL,
  `Abo_aanhef` text,
  `Abo_afsluiting` text,
  `Abo_korting` double DEFAULT NULL,
  `Abo_klantnummer_mainpress` varchar(75) DEFAULT NULL,
  `UitgeverValuta` char(4) NOT NULL DEFAULT 'EUR',
  `Uitgever_inkoop_door_manpo` tinyint(1) DEFAULT NULL,
  `Uitgever_geenprijsupdatefeeds` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`UitgeverID`),
  KEY `i1` (`UitgeverID`),
  KEY `Naamuitgever` (`Naamuitgever`)
) ENGINE=InnoDB AUTO_INCREMENT=7151 DEFAULT CHARSET=utf8;

--
-- Table structure for table `Valuta`
--

CREATE TABLE `Valuta` (
  `Soort` varchar(4) NOT NULL DEFAULT '',
  `Omschrijving` varchar(100) NOT NULL DEFAULT '',
  `Formuleconstante` double NOT NULL DEFAULT '0',
  `Formulevariabele` double NOT NULL DEFAULT '0',
  `InkoopValuta` varchar(4) DEFAULT NULL,
  `BTWCorrectie` tinyint(4) NOT NULL DEFAULT '0',
  `InkoopDelenDoorBTW` tinyint(4) NOT NULL DEFAULT '0',
  `Omrekenkoers` double NOT NULL DEFAULT '0',
  `vereist_rendement` decimal(5,2) DEFAULT NULL,
  `currencytoday` decimal(10,4) DEFAULT NULL,
  `calculate_plus` decimal(10,4) DEFAULT NULL,
  `calculate_times` decimal(10,4) DEFAULT NULL,
  KEY `i1` (`Soort`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `Verschijningsdata`
--

CREATE TABLE `Verschijningsdata` (
  `VerschijningsdataID` int(11) NOT NULL AUTO_INCREMENT,
  `Label` varchar(50) NOT NULL,
  `Verschijningsdata` text,
  PRIMARY KEY (`VerschijningsdataID`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8;

--
-- Table structure for table `Wisselkoers`
--

CREATE TABLE `Wisselkoers` (
  `Valuta` char(4) NOT NULL,
  `DT` datetime DEFAULT NULL,
  `Koers` decimal(10,5) DEFAULT NULL,
  PRIMARY KEY (`Valuta`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `_locatiebackup`
--

CREATE TABLE `_locatiebackup` (
  `EAN` char(13) NOT NULL,
  `man` char(4) CHARACTER SET ascii DEFAULT NULL,
  `com` char(4) CHARACTER SET ascii DEFAULT NULL,
  PRIMARY KEY (`EAN`),
  KEY `man` (`man`),
  KEY `com` (`com`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `_one_uitgevers`
--

CREATE TABLE `_one_uitgevers` (
  `UitgeverID` int(11) NOT NULL,
  PRIMARY KEY (`UitgeverID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `_toegestane_bindwijzen`
--

CREATE TABLE `_toegestane_bindwijzen` (
  `Bindwijze` char(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Temporary table structure for view `event_sprekers`
--

/*!50001 DROP VIEW IF EXISTS `event_sprekers`*/;
/*!50001 CREATE TABLE `event_sprekers` (
  `ean` tinyint NOT NULL,
  `auteurid` tinyint NOT NULL,
  `vlg` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Dumping routines for database 'Manpo'
--
/*!50003 DROP FUNCTION IF EXISTS `bereken_ean_controlegetal` */;
DELIMITER ;;
CREATE DEFINER=`sander`@`%` FUNCTION `bereken_ean_controlegetal`(_ean VARCHAR(12)) RETURNS varchar(13) CHARSET latin1
    READS SQL DATA
BEGIN
DECLARE _correct_ean TINYINT;
DECLARE _even_sum SMALLINT;
DECLARE _odd_sum SMALLINT;
DECLARE _check_digit SMALLINT;
DECLARE _check_number SMALLINT;
IF NOT _ean REGEXP '^[0-9]+$' THEN RETURN NULL; END IF;
CASE LENGTH(_ean)
WHEN 12 THEN
SELECT SUBSTR(_ean, 2, 1)+SUBSTR(_ean, 4, 1)+SUBSTR(_ean, 6, 1)+SUBSTR(_ean, 8, 1)+SUBSTR(_ean, 10, 1)+SUBSTR(_ean, 12, 1) INTO _even_sum;
SELECT SUBSTR(_ean, 1, 1)+SUBSTR(_ean, 3, 1)+SUBSTR(_ean, 5, 1)+SUBSTR(_ean, 7, 1)+SUBSTR(_ean, 9, 1)+SUBSTR(_ean, 11, 1) INTO _odd_sum;
WHEN 7 THEN
SELECT SUBSTR(_ean, 2, 1)+SUBSTR(_ean, 4, 1)+SUBSTR(_ean, 6, 1) INTO _even_sum;
SELECT SUBSTR(_ean, 1, 1)+SUBSTR(_ean, 3, 1)+SUBSTR(_ean, 5, 1)+SUBSTR(_ean, 7, 1) INTO _odd_sum;
ELSE RETURN NULL;
END CASE;
SELECT ( (10-(3*_even_sum + _odd_sum)%10)%10 ) INTO _check_digit;
RETURN concat(_ean,_check_digit);
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `bereken_locatie` */;
DELIMITER ;;
CREATE DEFINER=`thijs`@`%` FUNCTION `bereken_locatie`( afwijkend varCHAR(100), iscom TINYINT(1), soort VARCHAR(4), taal VARCHAR(20), manpick varCHAR(4), manbulk varCHAR(4), compick varCHAR(4), combulk varCHAR(4) ) RETURNS char(4) CHARSET ascii
    DETERMINISTIC
BEGIN
		
		RETURN IF( LENGTH(afwijkend) = 4, afwijkend,
			IF( iscom = 0,
				IF( soort = 'bulk',
					IF( LENGTH(manbulk) = 4, manbulk, IF( taal = 'en', '1101', '0101' ) ),
					IF( LENGTH(manpick) = 4, manpick, IF( taal = 'en', '4307', '3501' ) )
				),
				IF( soort = 'bulk',
					IF( LENGTH(combulk) = 4, combulk, IF( taal = 'en', '6401', '6308' ) ),
					IF( LENGTH(compick) = 4, compick, IF( taal = 'en', '6201', '5401' ) )
				)
			)
		);
		
    END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `boek_dagen_in_top` */;
DELIMITER ;;
CREATE DEFINER=`thijs`@`%` FUNCTION `boek_dagen_in_top`( iEAN Char(13) CHARACTER SET UTF8, iWebsite CHar(30) CHARACTER SET UTF8, iTop INT(11) ) RETURNS int(11)
    READS SQL DATA
BEGIN
	DECLARE rv INT;
	
/*	return ( SELECT COUNT( DISTINCT Datum )
		FROM BoekRanking_Historie
		WHERE EAN = iEAN AND Website = iWebsite
			AND Ranking < iTop ); */
			
	SELECT COUNT(DISTINCT Datum) INTO RV
	FROM Manpo.`BoekRanking_Historie` H JOIN Manpo.GAEVB USING ( Ean )
	WHERE GAEVB.OrigineelEAN = iEAN AND H.`Ranking` <= iTop AND H.Website = iWebsite;		
	
	RETURN rv;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `boek_hoogste_pos` */;
DELIMITER ;;
CREATE DEFINER=`sander`@`%` FUNCTION `boek_hoogste_pos`( iEAN CHAR(13), iWebsite CHAR(20) ) RETURNS int(11)
    READS SQL DATA
BEGIN
	DECLARE rv INT;
	
	SELECT IFNULL( MIN(Ranking), 9999999 ) INTO rv FROM Manpo.`BoekRanking_Historie` WHERE Ranking > 0 AND Ranking < 500000 AND Website = 'Managementboek' AND Ean = iEAN;
	
	RETURN rv;
    END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `calc_inkoopprijs` */;
DELIMITER ;;
CREATE DEFINER=`sander`@`%` FUNCTION `calc_inkoopprijs`( _ean CHAR(13) ) RETURNS decimal(8,2)
    READS SQL DATA
    DETERMINISTIC
BEGIN
	DECLARE _btwtarief DECIMAL(10,2);
	DECLARE _prijs DECIMAL(10,2);
	DECLARE _valuta CHAR(10);
	DECLARE _boeksoort CHAR(1);
	DECLARE _kortingmp DECIMAL(10,2);
	DECLARE _omrekenkoers DECIMAL(10,2);
	DECLARE _rv DECIMAL(10,2);
	SELECT Prijs, Valuta, Boeksoort, 
	IFNULL(`B`.`Aangepastekortingmp`,(CASE `B`.`Boeksoort` WHEN 'A' THEN `G`.`Kortingmp_a` WHEN 'S' THEN `G`.`Kortingmp_s` WHEN 'W' THEN `G`.`Kortingmp_w` WHEN 'O' THEN `G`.`Kortingmp_o` WHEN 'E' THEN `G`.`Kortingmp_e` ELSE `G`.`Kortingmp_g` END)) AS `KortingMP`,
	V.`Omrekenkoers`,
	A.`Artikelgroep_BTWTarief`
	
	INTO _prijs, _valuta, _boeksoort, _kortingmp, _omrekenkoers, _btwtarief
	FROM Manpo.Boeken B JOIN Manpo.`Uitgevers` G USING ( UitgeverId ) 
	JOIN Manpo.`Valuta` V ON ( B.`Valuta` = V.Soort ) 
	JOIN Manpo.`Bindwijze` W ON ( B.Bindwijze = W.Bindwijze )
	JOIN Manpo.`Artikelgroep` A ON ( W.`Artgroepid` = A.`Id` )
	WHERE Ean = _ean;
	
	IF ( _valuta = 'EUR' OR _valuta = 'DITM' ) THEN
		SET _rv = _prijs * ((100 - _kortingmp)*0.01)/_btwtarief;
	ELSE
		SET _rv = _prijs * ((100 - _kortingmp)*0.01)/_omrekenkoers;
	END IF;
	
	RETURN _rv;
    END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `calc_korting` */;
DELIMITER ;;
CREATE DEFINER=`sander`@`%` FUNCTION `calc_korting`( _ean CHAR(13) ) RETURNS decimal(8,2)
    READS SQL DATA
    DETERMINISTIC
BEGIN
	DECLARE _btwtarief DECIMAL(10,2);
	DECLARE _prijs DECIMAL(10,2);
	DECLARE _valuta CHAR(10);
	DECLARE _boeksoort CHAR(1);
	DECLARE _kortingmp DECIMAL(10,2);
	DECLARE _omrekenkoers DECIMAL(10,2);
	DECLARE _rv DECIMAL(10,2);
	SELECT Prijs, Valuta, Boeksoort, 
	IFNULL(`B`.`Aangepastekortingmp`,(CASE `B`.`Boeksoort` WHEN 'A' THEN `G`.`Kortingmp_a` WHEN 'S' THEN `G`.`Kortingmp_s` WHEN 'W' THEN `G`.`Kortingmp_w` WHEN 'O' THEN `G`.`Kortingmp_o` WHEN 'E' THEN `G`.`Kortingmp_e` ELSE `G`.`Kortingmp_g` END)) AS `KortingMP`,
	V.`Omrekenkoers`,
	A.`Artikelgroep_BTWTarief`
	
	INTO _prijs, _valuta, _boeksoort, _kortingmp, _omrekenkoers, _btwtarief
	FROM Manpo.Boeken B JOIN Manpo.`Uitgevers` G USING ( UitgeverId ) 
	JOIN Manpo.`Valuta` V ON ( B.`Valuta` = V.Soort ) 
	JOIN Manpo.`Bindwijze` W ON ( B.Bindwijze = W.Bindwijze )
	JOIN Manpo.`Artikelgroep` A ON ( W.`Artgroepid` = A.`Id` )
	WHERE Ean = _ean;
	
	RETURN _kortingmp;
    END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `calc_prijs` */;
DELIMITER ;;
CREATE DEFINER=`sander`@`%` FUNCTION `calc_prijs`( _prijs DECIMAL( 8, 3 ) , _valuta VARCHAR(4)  ) RETURNS decimal(8,2)
    READS SQL DATA
    DETERMINISTIC
BEGIN
	DECLARE prijs2 NUMERIC(8,3);
	IF _prijs = 0 THEN 
		SET prijs2 = 0;
	ELSE
		SELECT ROUND( Formuleconstante + _prijs * Formulevariabele, 2) INTO prijs2 FROM Valuta WHERE Soort = _valuta;
	END IF;
	RETURN prijs2;
    END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `clean_url` */;
DELIMITER ;;
CREATE DEFINER=`thijs`@`%` FUNCTION `clean_url`( _in VARCHAR(255) CHARSET utf8 COLLATE utf8_general_ci ) RETURNS varchar(255) CHARSET utf8
    DETERMINISTIC
BEGIN
	declare _readable INT;
	declare _c INT;
	DECLARE _out VARCHAR(255) CHARSET utf8;
	DECLARE _buf VARCHAR(255) CHARSET utf8;
	
	SET _readable = 0;
	SET _out = '';
	set _buf = LOWER( _in );
	
	-- Fucking superscript-getalletjes
	SET _buf = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(_buf,CHAR(14844336 USING utf8),"0"),CHAR(49849 USING utf8),"1"),CHAR(49842 USING utf8),"2"),CHAR(49843 USING utf8),"3"),CHAR(14844340 USING utf8),"4"),CHAR(14844341 USING utf8),"5"),CHAR(14844342 USING utf8),"6"),CHAR(14844343 USING utf8),"7"),CHAR(14844344 USING utf8),"8"),CHAR(14844345 USING utf8),"9");
	
	while LENGTH(_buf) > 0
	DO
		SET _c = ORD(CONVERT(_buf USING UCS2));
		IF _c BETWEEN ORD('a') AND ORD('z')
			OR _c BETWEEN ORD('0') AND ORD('9')
			-- Unicode. YAY!
			OR _c BETWEEN 0x00C0 AND 0x00D6
			OR _c BETWEEN 0x00D8 AND 0x00F6
			OR _c BETWEEN 0x00F8 AND 0x00FF
			OR _c BETWEEN 0x0100 AND 0x017F  -- Latin Extended-A
			OR _c BETWEEN 0x0180 AND 0x024F  -- Latin Extended-B
			OR _c BETWEEN 0x1E00 AND 0x1EFF  -- Latin Extended Additional
		THEN
			IF _readable = 0 AND LENGTH(_out) > 0
			THEN
				SET _out = CONCAT( _out, '-' );
			END IF;
			
			SET _out = CONCAT( _out, SUBSTR(_buf,1,1) );
			SET _readable = 1;
		ELSE
			SET _readable = 0;
		END IF;
		
		SET _buf = SUBSTR( _buf, 2 );
		
	END WHILE;
	
	RETURN _out;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `clean_url1` */;
DELIMITER ;;
CREATE DEFINER=`thijs`@`%` FUNCTION `clean_url1`( _in VARCHAR(255) CHARSET utf8 COLLATE utf8_general_ci ) RETURNS varchar(255) CHARSET utf8
    DETERMINISTIC
BEGIN
	declare _readable INT;
	declare _c INT;
	DECLARE _out VARCHAR(255) CHARSET utf8;
	DECLARE _buf VARCHAR(255) CHARSET utf8;
	
	SET _readable = 0;
	SET _out = '';
	SET _buf = LOWER( _in );
	
	-- Fucking superscript-getalletjes
	SET _buf = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(_buf,CHAR(14844336 USING utf8),"0"),CHAR(49849 USING utf8),"1"),CHAR(49842 USING utf8),"2"),CHAR(49843 USING utf8),"3"),CHAR(14844340 USING utf8),"4"),CHAR(14844341 USING utf8),"5"),CHAR(14844342 USING utf8),"6"),CHAR(14844343 USING utf8),"7"),CHAR(14844344 USING utf8),"8"),CHAR(14844345 USING utf8),"9");
	
	WHILE LENGTH(_buf) > 0
	DO
		SET _c = ORD(CONVERT(_buf USING UCS2));
		IF _c BETWEEN ORD('a') AND ORD('z')
			OR _c BETWEEN ORD('0') AND ORD('9')
			-- Unicode. YAY!
			OR _c BETWEEN 0x00C0 AND 0x00D6
			OR _c BETWEEN 0x00D8 AND 0x00F6
			OR _c BETWEEN 0x00F8 AND 0x00FF
			OR _c BETWEEN 0x0100 AND 0x017F  -- Latin Extended-A
			OR _c BETWEEN 0x0180 AND 0x024F  -- Latin Extended-B
			OR _c BETWEEN 0x1E00 AND 0x1EFF  -- Latin Extended Additional
		THEN
			IF _readable = 0 AND LENGTH(_out) > 0
			THEN
				SET _out = CONCAT( _out, '-' );
			END IF;
			
			SET _out = CONCAT( _out, SUBSTR(_buf,1,1) );
			SET _readable = 1;
		ELSE
			SET _readable = 0;
		END IF;
		
		SET _buf = SUBSTR( _buf, 2 );
		
	END WHILE;
	
	RETURN _out;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `is_computerboek` */;
DELIMITER ;;
CREATE DEFINER=`thijs`@`%` FUNCTION `is_computerboek`( _EAN CHAR(13) ) RETURNS int(11)
    READS SQL DATA
BEGIN
		DECLARE A INT(11);
		DECLARE B INT(11);
		DECLARE C INT(11);
		
		SELECT
			SUM(IF( R.Website = 'Managementboek' AND R.Rubriek = 'Computerboeken', 1, 0 )) as a,
			SUM(IF( R.Website = 'Managementboek' AND R.Rubriek != 'Computerboeken', 1, 0 )) as b,
			SUM(IF( R.Website = 'Computerboek', 1, 0 )) as c
		INTO A, B, C
		FROM Manpo.BoekRubrieken R
		WHERE R.EAN = _EAN;
		
		RETURN IF( A > 0 OR ( B = 0 AND C > 0 ), 1, 0 );
	END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `Locatie` */;
DELIMITER ;;
CREATE DEFINER=`thijs`@`%` FUNCTION `Locatie`( _EAN CHAR(13) ) RETURNS char(4) CHARSET ascii
    READS SQL DATA
BEGIN
		
		RETURN IFNULL(( SELECT bereken_locatie( B.Aangepastemagazijnlocatienummer,
				B.`Computer-boek`, B.Magazijnsoort, B.Taalvermelding,
				U.Magazijnlocatienummer, U.Magazijnlocatienummerbulk, U.Magazijnlocatienummercom, U.Magazijnlocatienummercombulk )
			FROM Boeken B
				JOIN Uitgevers U USING ( UitgeverID )
			WHERE B.EAN = _EAN ), '99999' );
	END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `StatusTekst` */;
DELIMITER ;;
CREATE DEFINER=`sander`@`%` FUNCTION `StatusTekst`( S INT ) RETURNS varchar(255) CHARSET utf8
    DETERMINISTIC
BEGIN
	DECLARE rv VARCHAR(255);
	
	IF S = 0 THEN
		SET rv = "Leverbaar";
	ELSEIF S = 1 THEN
		SET rv = "Uitverkocht";
	ELSEIF S = 2 THEN
		SET rv = "n.n.v.";
	ELSEIF S = 5 THEN
		SET rv = "Herdruk";
	ELSEIF S = 99 THEN
		SET rv = "Archief";
	ELSE
		SET rv = "";
	END IF;	
	
	RETURN rv;
    END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `create_boekenextra` */;
DELIMITER ;;
CREATE DEFINER=`sander`@`%` PROCEDURE `create_boekenextra`()
    MODIFIES SQL DATA
BEGIN
	DECLARE avg_rating FLOAT;
	DECLARE num_rating FLOAT;
	
/*	SELECT AVG( gemiddeld_stemmen ) INTO avg_rating FROM Managementboek_Klanten.BoekenTags WHERE aantal_stemmen > 0;
	SELECT 0.5*AVG( aantal_stemmen ) INTO num_rating FROM Managementboek_Klanten.BoekenTags WHERE aantal_stemmen > 0;
	UPDATE Managementboek_Klanten.BoekenTags SET stemmen_ranking = 0 WHERE aantal_stemmen = 0;	
	UPDATE Managementboek_Klanten.BoekenTags SET stemmen_ranking = 10000*( ( num_rating * avg_rating ) + ( Managementboek_Klanten.aantal_stemmen( Ean )* Managementboek_Klanten.gemiddeld_stemmen( Ean ) ) ) / ( num_rating + Managementboek_Klanten.aantal_stemmen( Ean ) ) WHERE Managementboek_Klanten.aantal_stemmen( Ean ) > 0;
	
	CREATE TABLE IF NOT EXISTS Managementboek_Boeken.BoekenPrijsBuckets ( Ean CHAR(13) character set latin1, Extra VARCHAR( 255 ), KEY( Ean ) ) ENGINE=INNODB;
	TRUNCATE Managementboek_Boeken.BoekenPrijsBuckets;
	REPLACE INTO Managementboek_Boeken.BoekenPrijsBuckets SELECT Ean, 'Tot €25' FROM Managementboek_Boeken.Boeken B WHERE IF( B.Aanbiedingsprijs > 0 && NOW() > B.Aanbiedingvanaf && NOW() <= IF( B.Aanbiedingtotenmet = '0000-00-00', NOW(), B.Aanbiedingtotenmet ), Aanbiedingsprijs, calc_prijs( Prijs, Valuta ) ) <= 25;
	REPLACE INTO Managementboek_Boeken.BoekenPrijsBuckets SELECT Ean, 'Tot €50' FROM Managementboek_Boeken.Boeken B WHERE IF( B.Aanbiedingsprijs > 0 && NOW() > B.Aanbiedingvanaf && NOW() <= IF( B.Aanbiedingtotenmet = '0000-00-00', NOW(), B.Aanbiedingtotenmet ), Aanbiedingsprijs, calc_prijs( Prijs, Valuta ) ) <= 50;	
	REPLACE INTO Managementboek_Boeken.BoekenPrijsBuckets SELECT Ean, 'Tot €75' FROM Managementboek_Boeken.Boeken B WHERE IF( B.Aanbiedingsprijs > 0 && NOW() > B.Aanbiedingvanaf && NOW() <= IF( B.Aanbiedingtotenmet = '0000-00-00', NOW(), B.Aanbiedingtotenmet ), Aanbiedingsprijs, calc_prijs( Prijs, Valuta ) ) <= 75;
	REPLACE INTO Managementboek_Boeken.BoekenPrijsBuckets SELECT Ean, 'Tot €100' FROM Managementboek_Boeken.Boeken B WHERE IF( B.Aanbiedingsprijs > 0 && NOW() > B.Aanbiedingvanaf && NOW() <= IF( B.Aanbiedingtotenmet = '0000-00-00', NOW(), B.Aanbiedingtotenmet ), Aanbiedingsprijs, calc_prijs( Prijs, Valuta ) ) <= 100;	
	REPLACE INTO Managementboek_Boeken.BoekenPrijsBuckets SELECT Ean, 'Meer dan €100' FROM Managementboek_Boeken.Boeken B WHERE IF( B.Aanbiedingsprijs > 0 && NOW() > B.Aanbiedingvanaf && NOW() <= IF( B.Aanbiedingtotenmet = '0000-00-00', NOW(), B.Aanbiedingtotenmet ), Aanbiedingsprijs, calc_prijs( Prijs, Valuta ) ) >= 100;
	CREATE TABLE IF NOT EXISTS Managementboek_Boeken.BoekenExtra ( Ean CHAR(13) character set latin1, Extra VARCHAR( 255 ), KEY( Ean ) ) ENGINE=INNODB;
	TRUNCATE Managementboek_Boeken.BoekenExtra;
	REPLACE INTO Managementboek_Boeken.BoekenExtra SELECT Ean, "Bestseller top 100" FROM Managementboek_Klanten.Boekranking_Plat WHERE `30d` <= 100;
	-- REPLACE INTO Managementboek_Boeken.BoekenExtra SELECT Ean, "Bestseller top 1000" FROM Managementboek_Klanten.Boekranking_Plat WHERE `30d` <= 1000;
	
	REPLACE INTO Managementboek_Boeken.BoekenExtra SELECT Ean, "Aanbieding" FROM Managementboek_Klanten.BoekenTags WHERE aanbieding > 0;
	REPLACE INTO Managementboek_Boeken.BoekenExtra SELECT Ean, "Nieuw" FROM Managementboek_Klanten.BoekenTags WHERE nieuw > 0;	
	REPLACE INTO Managementboek_Boeken.BoekenExtra SELECT Ean, "Met recensies" FROM Managementboek_Klanten.BoekenTags WHERE aantal_recensies > 0;
	REPLACE INTO Managementboek_Boeken.BoekenExtra SELECT Ean, "Met lezersrecensies" FROM Managementboek_Klanten.BoekenTags WHERE lezersrecensies > 0;
	REPLACE INTO Managementboek_Boeken.BoekenExtra SELECT Ean, "Verwacht" FROM Managementboek_Klanten.BoekenTags WHERE verwacht > 0;
	REPLACE INTO Managementboek_Boeken.BoekenExtra SELECT Ean, "Top 100 stemmen" FROM Managementboek_Klanten.BoekenTags WHERE top100 > 0;
	REPLACE INTO Managementboek_Boeken.BoekenExtra SELECT Ean, "Top 100 waardering" FROM Managementboek_Klanten.BoekenTags WHERE stemmen_top_100 > 0;
	REPLACE INTO Managementboek_Boeken.BoekenExtra SELECT Ean, "Evergreen" FROM Managementboek_Klanten.BoekenTags WHERE bestseller > 0;
	REPLACE INTO Managementboek_Boeken.BoekenExtra SELECT Ean, "Voormalig nummer 1" FROM Managementboek_Klanten.BoekenTags WHERE nr1 > 0;
	REPLACE INTO Managementboek_Boeken.BoekenExtra SELECT Ean, "Met auteursbijdrage" FROM Managementboek_Klanten.BoekenTags WHERE auteursbijdragen > 0;
	
	DELETE FROM Managementboek_Boeken.BoekThemas;
	REPLACE INTO Managementboek_Boeken.BoekThemas SELECT Ean, 'Advisering' FROM Managementboek_Boeken.Trefwoorden T JOIN Managementboek_Boeken.Boektrefwoorden BT ON ( T.Id = BT.Trefwid ) WHERE Omschrijving IN ( 'advisering' );
	REPLACE INTO Managementboek_Boeken.BoekThemas SELECT Ean, 'Coaching' FROM Managementboek_Boeken.Trefwoorden T JOIN Managementboek_Boeken.Boektrefwoorden BT ON ( T.Id = BT.Trefwid ) WHERE Omschrijving IN ( 'coaching', 'coachen' );
	REPLACE INTO Managementboek_Boeken.BoekThemas SELECT Ean, 'Communicatie' FROM Managementboek_Boeken.Trefwoorden T JOIN Managementboek_Boeken.Boektrefwoorden BT ON ( T.Id = BT.Trefwid ) WHERE Omschrijving IN ( 'communicatie' );
	REPLACE INTO Managementboek_Boeken.BoekThemas SELECT Ean, 'HRM' FROM Managementboek_Boeken.Boeken WHERE Rubriek IN ( 'personeelsmanagement' );
	REPLACE INTO Managementboek_Boeken.BoekThemas SELECT Ean, 'Leidinggeven' FROM Managementboek_Boeken.Trefwoorden T JOIN Managementboek_Boeken.Boektrefwoorden BT ON ( T.Id = BT.Trefwid ) WHERE Omschrijving IN ( 'leidinggeven' );
	REPLACE INTO Managementboek_Boeken.BoekThemas SELECT Ean, 'Marketing' FROM Managementboek_Boeken.Trefwoorden T JOIN Managementboek_Boeken.Boektrefwoorden BT ON ( T.Id = BT.Trefwid ) WHERE Omschrijving IN ( 'marketing' );
	REPLACE INTO Managementboek_Boeken.BoekThemas SELECT Ean, 'Training' FROM Managementboek_Boeken.Trefwoorden T JOIN Managementboek_Boeken.Boektrefwoorden BT ON ( T.Id = BT.Trefwid ) WHERE Omschrijving IN ( 'trainen','training','werkvormen','workshop' );
	REPLACE INTO Managementboek_Boeken.BoekThemas SELECT Ean, 'ZZP' FROM Managementboek_Boeken.Trefwoorden T JOIN Managementboek_Boeken.Boektrefwoorden BT ON ( T.Id = BT.Trefwid ) WHERE Omschrijving IN ( 'freelancen','zzp','acquisitie','ondernemerschap' );
*/	
    END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `daily_procedure` */;
DELIMITER ;;
CREATE DEFINER=`sander`@`%` PROCEDURE `daily_procedure`()
BEGIN
		UPDATE Mainpress_Globals.LastRun SET `status` = 'running...', DT = NULL, `start` = NOW() WHERE Script='Manpo.daily';		   
		-- [CMS-632] indien voorraad = 0 en CBstatus is uitverkocht, dan boekstatus => uitverkocht.
		UPDATE Manpo.Boeken MB
			JOIN CbBoeken.Boeken CB USING ( Ean )
			JOIN Manpo.Mapped_EAN MAP ON ( MAP.U8 = MB.EAN )
			JOIN Voorraadbeheer.`Voorraad` V ON ( V.EAN = MAP.L1 )
		SET MB.`Boekstatus` = 1
		WHERE V.`FysiekeVoorraad` = 0
			AND CB.`Boekstatus` = 1
			AND MB.Boekstatus NOT IN ( 1, 99 )
			AND MB.Geenstatusupdatecb = 0;
		
		-- Archief en Voorraad > 0 --> Boekstatus 1 (Niet voor NCOI) [CMS-639]
		UPDATE Manpo.Boeken MB
			JOIN Manpo.Bindwijze W USING ( Bindwijze )
			JOIN Manpo.Mapped_EAN MAP ON ( MAP.U8 = MB.EAN )
			JOIN Voorraadbeheer.`Voorraad` V ON ( V.EAN = MAP.L1 )
		SET MB.Boekstatus = 1
		WHERE V.`FysiekeVoorraad` > 0 AND MB.Boekstatus IN ( 99 ) 
		  AND MB.`UitgeverID` != 849 
		  AND Bindwijze_evenement=0
		  AND Bindwijze_download=0;
		
		-- Evenementen niet uitverkocht, maar in het archief! 
		UPDATE Manpo.Boeken JOIN Manpo.Bindwijze USING ( Bindwijze ) SET Boeken.Boekstatus=99 WHERE Bindwijze_evenement=1 AND Boeken.Boekstatus=1;
		
		-- Niet meer bijbestellen en geen voorraad meer -> Uitverkocht
		UPDATE Manpo.Boeken B
			JOIN Manpo.Mapped_EAN MAP ON ( MAP.U8 = B.EAN )
			JOIN Voorraadbeheer.`Voorraad` V ON ( V.EAN = MAP.L1 )
		SET Boekstatus=1, B.Laatstgewijzigd=NOW()
		WHERE B.Boeken_nietmeerbestellen = 1
			AND V.BeschikbareVoorraad <= 0
			AND B.Boekstatus=0;
		
		-- RAMSJ
		UPDATE Manpo.`Boeken` B SET Boeken_inramsj=1 WHERE B.`Boeken_nietmeerbestellen`=1 AND B.`Geenprijsupdatecb`=1 AND B.`Boeken_inprijsopgeheven`=1 AND B.`Boeken_inramsj`=0;
		UPDATE Manpo.`Boeken` B
			JOIN Manpo.Mapped_EAN MAP ON ( MAP.U8 = B.EAN )
			JOIN Voorraadbeheer.`Voorraad` V ON ( V.EAN = MAP.L1 )
		SET Boeken_cvdm_aantal = V.FysiekeVoorraad
		WHERE Boeken_inramsj=1 AND Boeken_cvdm_aantal=0;
		
		-- Fix ebook boeksoort
		UPDATE Manpo.Boeken BK 
		  JOIN Manpo.`Bindwijze` BW USING ( Bindwijze )
		  JOIN Manpo.`Uitgevers` U USING ( Uitgeverid )
		  SET BK.Boeksoort='E'
		  WHERE ( Productsoort = 'ebook' OR Bindwijze IN ( 'ebook', 'e-book' ) )
		  AND Boeksoort != 'E';
		  
		-- CMS-890
		UPDATE Manpo.Boeken Bk
			JOIN Manpo.Mapped_EAN MAP ON ( MAP.U8 = Bk.EAN )
			JOIN Voorraadbeheer.`Voorraad` V ON ( V.EAN = MAP.L1 )
		SET Bk.`Aangepastekortingmp` = NULL, Bk.`Laatstgewijzigd` = NOW()
		WHERE V.`FysiekeVoorraad` <= 0
			AND Bk.`Aangepastekortingmp` >= 75
			AND Bk.`Boekstatus` != 99;
		
		-- Aanbiedingsvinkjes zetten
		UPDATE 
		  Manpo.Boeken B 
		SET
		  IsAanbieding = (
		    B.`AanbiedingsEuroPrijs` > 0 
		    AND NOW() BETWEEN IFNULL(
		      B.`AanbiedingVanaf`,
		      '2000-01-01'
		    ) 
		    AND IFNULL(
		      B.`AanbiedingTotEnMet`,
		      '2050-01-01'
		    )
		  ) 
		WHERE B.`Boekstatus` NOT IN (99) 
		  AND (
		    B.`AanbiedingsEuroPrijs` > 0 
		    AND NOW() BETWEEN IFNULL(
		      B.`AanbiedingVanaf`,
		      '2000-01-01'
		    ) 
		    AND IFNULL(
		      B.`AanbiedingTotEnMet`,
		      '2050-01-01'
		    )
		  ) != B.`IsAanbieding`;
  
  
		UPDATE Mainpress_Globals.LastRun SET `status` = 'ok', DT=NOW() WHERE Script='Manpo.daily';		   
    END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `update_boekranking` */;
DELIMITER ;;
CREATE DEFINER=`thijs`@`%` PROCEDURE `update_boekranking`()
    MODIFIES SQL DATA
BEGIN
	DECLARE eof BOOLEAN DEFAULT FALSE;
	DECLARE _Website CHAR(32);
	DECLARE _Rubriek CHAR(255);
	DECLARE _EAN CHAR(13);
	DECLARE rank INT DEFAULT 1;
	
	DECLARE cursor_w CURSOR FOR
		SELECT DISTINCT Website FROM Manpo.Rubrieken;
	
	DECLARE cursor_r CURSOR FOR
		SELECT ''
		UNION
		SELECT Rubriek FROM Manpo.Rubrieken
			WHERE Website = _Website;
	
	DECLARE cursor_e_7 CURSOR FOR
		SELECT DISTINCT BR.EAN
		FROM Manpo.Boeken B
			JOIN Manpo.Uitgevers U USING ( UitgeverID )
			JOIN Manpo.BoekRubrieken BR ON ( BR.EAN = B.EAN AND BR.Website = _Website AND ( BR.Rubriek = _Rubriek OR _Rubriek = '' ) )
			LEFT JOIN Manpo.Rubrieken RR ON ( RR.Website = BR.Website AND RR.Rubriek = BR.Rubriek )
			LEFT JOIN Manpo.Tellingen TT ON ( TT.EAN = B.EAN )
		WHERE B.Boekstatus != 99 AND B.Prijs > 0 AND B.Boeken_verborgenboek = 0
			AND U.Ruzie = 0
		ORDER BY ( TT.EAN IS NULL ) ASC,
			( IFNULL(RR.Uitsluiten_ranking,0) = 0 ) DESC,
			( _Website != 'Managementboek' OR B.Uitsluiten_ranking_man = 0 ) DESC,
			( _Website != 'Computerboek'   OR B.Uitsluiten_ranking_com = 0 ) DESC,
			( _Website != 'Juridischboek'  OR B.Uitsluiten_ranking_jur = 0 ) DESC,
			TT.7d DESC, TT.`year` DESC,
			B.Verschijningsdatum DESC,
			B.Aanmaakdatum DESC;
	DECLARE cursor_e_30 CURSOR FOR
		SELECT DISTINCT BR.EAN
		FROM Manpo.Boeken B
			JOIN Manpo.Uitgevers U USING ( UitgeverID )
			JOIN Manpo.BoekRubrieken BR ON ( BR.EAN = B.EAN AND BR.Website = _Website AND ( BR.Rubriek = _Rubriek OR _Rubriek = '' ) )
			LEFT JOIN Manpo.Rubrieken RR ON ( RR.Website = BR.Website AND RR.Rubriek = BR.Rubriek )
			LEFT JOIN Manpo.Tellingen TT ON ( TT.EAN = B.EAN )
		WHERE B.Boekstatus != 99 AND B.Prijs > 0 AND B.Boeken_verborgenboek = 0
			AND U.Ruzie = 0
		ORDER BY ( TT.EAN IS NULL ) ASC,
			( IFNULL(RR.Uitsluiten_ranking,0) = 0 ) DESC,
			( _Website != 'Managementboek' OR B.Uitsluiten_ranking_man = 0 ) DESC,
			( _Website != 'Computerboek'   OR B.Uitsluiten_ranking_com = 0 ) DESC,
			( _Website != 'Juridischboek'  OR B.Uitsluiten_ranking_jur = 0 ) DESC,
			TT.30d DESC, TT.`year` DESC,
			B.Verschijningsdatum DESC,
			B.Aanmaakdatum DESC;
	DECLARE cursor_e_90 CURSOR FOR
		SELECT DISTINCT BR.EAN
		FROM Manpo.Boeken B
			JOIN Manpo.Uitgevers U USING ( UitgeverID )
			JOIN Manpo.BoekRubrieken BR ON ( BR.EAN = B.EAN AND BR.Website = _Website AND ( BR.Rubriek = _Rubriek OR _Rubriek = '' ) )
			LEFT JOIN Manpo.Rubrieken RR ON ( RR.Website = BR.Website AND RR.Rubriek = BR.Rubriek )
			LEFT JOIN Manpo.Tellingen TT ON ( TT.EAN = B.EAN )
		WHERE B.Boekstatus != 99 AND B.Prijs > 0 AND B.Boeken_verborgenboek = 0
			AND U.Ruzie = 0
		ORDER BY ( TT.EAN IS NULL ) ASC,
			( IFNULL(RR.Uitsluiten_ranking,0) = 0 ) DESC,
			( _Website != 'Managementboek' OR B.Uitsluiten_ranking_man = 0 ) DESC,
			( _Website != 'Computerboek'   OR B.Uitsluiten_ranking_com = 0 ) DESC,
			( _Website != 'Juridischboek'  OR B.Uitsluiten_ranking_jur = 0 ) DESC,
			TT.90d DESC, TT.`year` DESC,
			B.Verschijningsdatum DESC,
			B.Aanmaakdatum DESC;
	DECLARE cursor_e_year CURSOR FOR
		SELECT DISTINCT BR.EAN
		FROM Manpo.Boeken B
			JOIN Manpo.Uitgevers U USING ( UitgeverID )
			JOIN Manpo.BoekRubrieken BR ON ( BR.EAN = B.EAN AND BR.Website = _Website AND ( BR.Rubriek = _Rubriek OR _Rubriek = '' ) )
			LEFT JOIN Manpo.Rubrieken RR ON ( RR.Website = BR.Website AND RR.Rubriek = BR.Rubriek )
			LEFT JOIN Manpo.Tellingen TT ON ( TT.EAN = B.EAN )
		WHERE B.Boekstatus != 99 AND B.Prijs > 0 AND B.Boeken_verborgenboek = 0
			AND U.Ruzie = 0
		ORDER BY ( TT.EAN IS NULL ) ASC,
			( IFNULL(RR.Uitsluiten_ranking,0) = 0 ) DESC,
			( _Website != 'Managementboek' OR B.Uitsluiten_ranking_man = 0 ) DESC,
			( _Website != 'Computerboek'   OR B.Uitsluiten_ranking_com = 0 ) DESC,
			( _Website != 'Juridischboek'  OR B.Uitsluiten_ranking_jur = 0 ) DESC,
			TT.`year` DESC,
			B.Verschijningsdatum DESC,
			B.Aanmaakdatum DESC;
		
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET eof = TRUE;
	
	SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
	START TRANSACTION;
	
	
	UPDATE Manpo.BoekRanking R
		SET R.7d = 9999999,
			R.30d = 9999999,
			R.90d = 9999999,
			R.year = 9999999;
	
	open cursor_w;
	loop_w: loop
		fetch cursor_w into _Website;
		IF eof THEN
			SET eof = FALSE;
			CLOSE cursor_w;
			LEAVE loop_w;
		END IF;
		
		OPEN cursor_r;
		loop_r: LOOP
		
			FETCH cursor_r INTO _Rubriek;
			IF eof THEN
				SET eof = FALSE;
				CLOSE cursor_r;
				LEAVE loop_r;
			END IF;
			/** week **/
			SET rank = 1;
			OPEN cursor_e_7;
			loop_e_7: LOOP
				FETCH cursor_e_7 INTO _EAN;
				IF eof THEN
					SET eof = FALSE;
					CLOSE cursor_e_7;
					LEAVE loop_e_7;
				END IF;
				
				INSERT INTO Manpo.BoekRanking ( EAN, Website, Rubriek, 7d )
					VALUES ( _EAN, _Website, _Rubriek, rank )
					ON DUPLiCATE KEY UPDATE 7d = VALUES(7d);
				
				SET rank = rank + 1;
			END LOOP loop_e_7;
			/** maand **/
			SET rank = 1;
			OPEN cursor_e_30;
			loop_e_30: LOOP
				FETCH cursor_e_30 INTO _EAN;
				IF eof THEN
					SET eof = FALSE;
					CLOSE cursor_e_30;
					LEAVE loop_e_30;
				END IF;
				
				INSERT INTO Manpo.BoekRanking ( EAN, Website, Rubriek, 30d )
					VALUES ( _EAN, _Website, _Rubriek, rank )
					ON DUPLiCATE KEY UPDATE 30d = VALUES(30d);
				
				SET rank = rank + 1;
			END LOOP loop_e_30;
			/** kwartaal **/
			SET rank = 1;
			OPEN cursor_e_90;
			loop_e_90: LOOP
				FETCH cursor_e_90 INTO _EAN;
				IF eof THEN
					SET eof = FALSE;
					CLOSE cursor_e_90;
					LEAVE loop_e_90;
				END IF;
				
				INSERT INTO Manpo.BoekRanking ( EAN, Website, Rubriek, 90d )
					VALUES ( _EAN, _Website, _Rubriek, rank )
					ON DUPLiCATE KEY UPDATE 90d = VALUES(90d);
				
				SET rank = rank + 1;
			END LOOP loop_e_90;
			/** jaar **/
			SET rank = 1;
			OPEN cursor_e_year;
			loop_e_year: LOOP
				FETCH cursor_e_year INTO _EAN;
				IF eof THEN
					SET eof = FALSE;
					CLOSE cursor_e_year;
					LEAVE loop_e_year;
				END IF;
				
				INSERT INTO Manpo.BoekRanking ( EAN, Website, Rubriek, `year` )
					VALUES ( _EAN, _Website, _Rubriek, rank )
					ON DUPLiCATE KEY UPDATE `year` = VALUES(`year`);
				
				SET rank = rank + 1;
			END LOOP loop_e_year;
		
		END LOOP loop_r;
		
		
		/** Werk de historische rankings voor deze wobsite ook bij **/
		DELETE FROM Manpo.BoekRanking_Historie WHERE Website = _Website AND Datum = CURDATE();
		INSERT INTO Manpo.BoekRanking_Historie ( EAN, Website, Datum, Ranking )
			SELECT R.EAN, _Website, CURDATE(), R.30d
			FROM Manpo.BoekRanking R
			WHERE R.Website = _Website AND R.Rubriek = ""
				AND R.30d BETWEEN 1 AND 100;
	
	end loop loop_w;
	
	/** Update de hoogst bereikte positie **/
	-- UPDATE Manpo.BoekRanking SET hoogste_pos = LEAST( hoogste_pos, 30d );
	-- Gebruikt nog de hoogste pos uit MANAGEMENTBOEK. Ooit eens fixen! :-(
	UPDATE Manpo.`BoekRanking` SET hoogste_pos = Managementboek_Boeken.hoogste_pos(Ean) 
		WHERE `Website`='Managementboek' AND `Rubriek`='' 
		AND Managementboek_Boeken.hoogste_pos(Ean) < 10000;
	
	/** En de dagtellingen (alleen rubriekloos) **/
	INSERT INTO Manpo.BoekRanking ( EAN, Website, Rubriek, dagen_top10, dagen_top100 )
		SELECT
			EAN, Website, "" as Rubriek,
			SUM( IF(Ranking BETWEEN 1 AND 10,  1, 0) ) AS t10,
			SUM( IF(Ranking BETWEEN 1 AND 100, 1, 0) ) AS t100
		FROM Manpo.BoekRanking_Historie
			JOIN Manpo.Boeken B USING ( EAN )
		WHERE B.Boekstatus != 99 AND B.Prijs > 0 AND B.Boeken_verborgenboek = 0
		GROUP BY EAN
		HAVING t10 > 0 OR t100 > 0
	ON DUPLICATE KEY UPDATE dagen_top10 = VALUES(dagen_top10), dagen_top100 = VALUES(dagen_top100);
	/** Haal verborgen / archiefboeken weer weg **/
	DELETE FROM Manpo.BoekRanking
		WHERE `year` = 9999999
			AND hoogste_pos > 1500
			AND dagen_top100 = 0
			AND dagen_top10 = 0;
	
	COMMIT;
END ;;
DELIMITER ;

--
-- Current Database: `Marketingbox`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `Marketingbox` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `Marketingbox`;

--
-- Table structure for table `wp_categories`
--

CREATE TABLE `wp_categories` (
  `cat_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `cat_name` varchar(55) NOT NULL DEFAULT '',
  `category_nicename` varchar(200) NOT NULL DEFAULT '',
  `category_description` longtext NOT NULL,
  `category_parent` bigint(20) NOT NULL DEFAULT '0',
  `category_count` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`cat_ID`),
  KEY `category_nicename` (`category_nicename`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

--
-- Table structure for table `wp_comments`
--

CREATE TABLE `wp_comments` (
  `comment_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `comment_post_ID` int(11) NOT NULL DEFAULT '0',
  `comment_author` tinytext NOT NULL,
  `comment_author_email` varchar(100) NOT NULL DEFAULT '',
  `comment_author_url` varchar(200) NOT NULL DEFAULT '',
  `comment_author_IP` varchar(100) NOT NULL DEFAULT '',
  `comment_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comment_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comment_content` text NOT NULL,
  `comment_karma` int(11) NOT NULL DEFAULT '0',
  `comment_approved` enum('0','1','spam') NOT NULL DEFAULT '1',
  `comment_agent` varchar(255) NOT NULL DEFAULT '',
  `comment_type` varchar(20) NOT NULL DEFAULT '',
  `comment_parent` bigint(20) NOT NULL DEFAULT '0',
  `user_id` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`comment_ID`),
  KEY `comment_approved` (`comment_approved`),
  KEY `comment_post_ID` (`comment_post_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=161 DEFAULT CHARSET=latin1;

--
-- Table structure for table `wp_linkcategories`
--

CREATE TABLE `wp_linkcategories` (
  `cat_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `cat_name` tinytext NOT NULL,
  `auto_toggle` enum('Y','N') NOT NULL DEFAULT 'N',
  `show_images` enum('Y','N') NOT NULL DEFAULT 'Y',
  `show_description` enum('Y','N') NOT NULL DEFAULT 'N',
  `show_rating` enum('Y','N') NOT NULL DEFAULT 'Y',
  `show_updated` enum('Y','N') NOT NULL DEFAULT 'Y',
  `sort_order` varchar(64) NOT NULL DEFAULT 'rand',
  `sort_desc` enum('Y','N') NOT NULL DEFAULT 'N',
  `text_before_link` varchar(128) NOT NULL DEFAULT '<li>',
  `text_after_link` varchar(128) NOT NULL DEFAULT '<br />',
  `text_after_all` varchar(128) NOT NULL DEFAULT '</li>',
  `list_limit` int(11) NOT NULL DEFAULT '-1',
  PRIMARY KEY (`cat_id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

--
-- Table structure for table `wp_links`
--

CREATE TABLE `wp_links` (
  `link_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `link_url` varchar(255) NOT NULL DEFAULT '',
  `link_name` varchar(255) NOT NULL DEFAULT '',
  `link_image` varchar(255) NOT NULL DEFAULT '',
  `link_target` varchar(25) NOT NULL DEFAULT '',
  `link_category` bigint(20) NOT NULL DEFAULT '0',
  `link_description` varchar(255) NOT NULL DEFAULT '',
  `link_visible` enum('Y','N') NOT NULL DEFAULT 'Y',
  `link_owner` int(11) NOT NULL DEFAULT '1',
  `link_rating` int(11) NOT NULL DEFAULT '0',
  `link_updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `link_rel` varchar(255) NOT NULL DEFAULT '',
  `link_notes` mediumtext NOT NULL,
  `link_rss` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`link_id`),
  KEY `link_category` (`link_category`),
  KEY `link_visible` (`link_visible`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

--
-- Table structure for table `wp_options`
--

CREATE TABLE `wp_options` (
  `option_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `blog_id` int(11) NOT NULL DEFAULT '0',
  `option_name` varchar(64) NOT NULL DEFAULT '',
  `option_can_override` enum('Y','N') NOT NULL DEFAULT 'Y',
  `option_type` int(11) NOT NULL DEFAULT '1',
  `option_value` longtext NOT NULL,
  `option_width` int(11) NOT NULL DEFAULT '20',
  `option_height` int(11) NOT NULL DEFAULT '8',
  `option_description` tinytext NOT NULL,
  `option_admin_level` int(11) NOT NULL DEFAULT '1',
  `autoload` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`option_id`,`blog_id`,`option_name`),
  KEY `option_name` (`option_name`)
) ENGINE=MyISAM AUTO_INCREMENT=75 DEFAULT CHARSET=latin1;

--
-- Table structure for table `wp_post2cat`
--

CREATE TABLE `wp_post2cat` (
  `rel_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) NOT NULL DEFAULT '0',
  `category_id` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`rel_id`),
  KEY `post_id` (`post_id`,`category_id`)
) ENGINE=MyISAM AUTO_INCREMENT=42 DEFAULT CHARSET=latin1;

--
-- Table structure for table `wp_postmeta`
--

CREATE TABLE `wp_postmeta` (
  `meta_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) NOT NULL DEFAULT '0',
  `meta_key` varchar(255) DEFAULT NULL,
  `meta_value` longtext,
  PRIMARY KEY (`meta_id`),
  KEY `post_id` (`post_id`),
  KEY `meta_key` (`meta_key`)
) ENGINE=MyISAM AUTO_INCREMENT=98 DEFAULT CHARSET=latin1;

--
-- Table structure for table `wp_posts`
--

CREATE TABLE `wp_posts` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_author` bigint(20) NOT NULL DEFAULT '0',
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content` longtext NOT NULL,
  `post_title` text NOT NULL,
  `post_category` int(4) NOT NULL DEFAULT '0',
  `post_excerpt` text NOT NULL,
  `post_status` enum('publish','draft','private','static','object','attachment') NOT NULL DEFAULT 'publish',
  `comment_status` enum('open','closed','registered_only') NOT NULL DEFAULT 'open',
  `ping_status` enum('open','closed') NOT NULL DEFAULT 'open',
  `post_password` varchar(20) NOT NULL DEFAULT '',
  `post_name` varchar(200) NOT NULL DEFAULT '',
  `to_ping` text NOT NULL,
  `pinged` text NOT NULL,
  `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content_filtered` text NOT NULL,
  `post_parent` bigint(20) NOT NULL DEFAULT '0',
  `guid` varchar(255) NOT NULL DEFAULT '',
  `menu_order` int(11) NOT NULL DEFAULT '0',
  `post_type` varchar(100) NOT NULL DEFAULT '',
  `post_mime_type` varchar(100) NOT NULL DEFAULT '',
  `comment_count` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `post_name` (`post_name`)
) ENGINE=MyISAM AUTO_INCREMENT=38 DEFAULT CHARSET=latin1;

--
-- Table structure for table `wp_usermeta`
--

CREATE TABLE `wp_usermeta` (
  `umeta_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL DEFAULT '0',
  `meta_key` varchar(255) DEFAULT NULL,
  `meta_value` longtext,
  PRIMARY KEY (`umeta_id`),
  KEY `user_id` (`user_id`),
  KEY `meta_key` (`meta_key`)
) ENGINE=MyISAM AUTO_INCREMENT=55 DEFAULT CHARSET=latin1;

--
-- Table structure for table `wp_users`
--

CREATE TABLE `wp_users` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_login` varchar(60) NOT NULL DEFAULT '',
  `user_pass` varchar(64) NOT NULL DEFAULT '',
  `user_nicename` varchar(50) NOT NULL DEFAULT '',
  `user_email` varchar(100) NOT NULL DEFAULT '',
  `user_url` varchar(100) NOT NULL DEFAULT '',
  `user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `user_activation_key` varchar(60) NOT NULL DEFAULT '',
  `user_status` int(11) NOT NULL DEFAULT '0',
  `display_name` varchar(250) NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`),
  KEY `user_login_key` (`user_login`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

--
-- Dumping routines for database 'Marketingbox'
--

--
-- Current Database: `NCOI`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `NCOI` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `NCOI`;

--
-- Table structure for table `2011_Batches`
--

CREATE TABLE `2011_Batches` (
  `Batchid` int(11) NOT NULL,
  `DTIngediend` datetime DEFAULT NULL,
  `DTGeprint` datetime DEFAULT NULL,
  `DTVerzonden` datetime DEFAULT NULL,
  `DTGefactureerd` datetime DEFAULT NULL,
  `Status` enum('ingediend','geprint','verzonden','gefactureerd','ingetrokken') NOT NULL,
  `Spoed` char(10) DEFAULT NULL,
  `Kostenpost` int(11) DEFAULT NULL,
  PRIMARY KEY (`Batchid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2011_CreditatieLog`
--

CREATE TABLE `2011_CreditatieLog` (
  `DT` datetime DEFAULT NULL,
  `Pakbonid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2011_Orderregels`
--

CREATE TABLE `2011_Orderregels` (
  `Pakbonid` int(11) NOT NULL,
  `Ean` char(13) NOT NULL,
  `Aantal` int(11) NOT NULL,
  `Debiteur` int(11) NOT NULL,
  `PeriodeID` int(11) NOT NULL,
  PRIMARY KEY (`Pakbonid`,`Ean`),
  KEY `FK_Orderregels_Voorraad` (`Ean`,`Debiteur`,`PeriodeID`),
  CONSTRAINT `FK_Orderregels_Pakbonnen` FOREIGN KEY (`Pakbonid`) REFERENCES `2011_Pakbonnen` (`Pakbonid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `2011_PakbonOrders`
--

CREATE TABLE `2011_PakbonOrders` (
  `Pakbonid` int(11) NOT NULL,
  `Ordernummer` int(11) NOT NULL,
  PRIMARY KEY (`Pakbonid`,`Ordernummer`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2011_Pakbonnen`
--

CREATE TABLE `2011_Pakbonnen` (
  `Barcode` varchar(18) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `Pakbonid` int(11) NOT NULL AUTO_INCREMENT,
  `Batchid` int(11) NOT NULL,
  `Status` enum('ingediend','geprint','verzonden','gefactureerd','geretourneerd','gecrediteerd','ingetrokken') NOT NULL,
  `Kostenpost` int(11) DEFAULT NULL,
  `Land` varchar(20) NOT NULL,
  PRIMARY KEY (`Pakbonid`),
  UNIQUE KEY `Barcode_Uniq` (`Barcode`),
  KEY `FK_Pakbonnen_Batches` (`Batchid`),
  CONSTRAINT `FK_Pakbonnen_Batches` FOREIGN KEY (`Batchid`) REFERENCES `2011_Batches` (`Batchid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10165659 DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2011_Transacties`
--

CREATE TABLE `2011_Transacties` (
  `Type` enum('forecast','voorraad') NOT NULL,
  `Ean` char(13) NOT NULL,
  `Kostenplaats` int(11) DEFAULT NULL,
  `Exactid` int(11) NOT NULL,
  `DT` datetime NOT NULL,
  `Delta` int(11) DEFAULT NULL,
  `Gebruiker` varchar(64) DEFAULT NULL,
  `Reden` mediumtext,
  `Commentaar` mediumtext,
  KEY `NewIndex1` (`Type`,`Ean`,`DT`,`Kostenplaats`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2011_Voorraad`
--

CREATE TABLE `2011_Voorraad` (
  `EAN` char(13) NOT NULL,
  `ExactID` int(11) NOT NULL,
  `Kostenplaats` int(11) NOT NULL,
  `Voorraad` int(11) NOT NULL,
  `Forecast` int(11) NOT NULL,
  `Afgesloten` enum('Neen','Ja') NOT NULL DEFAULT 'Neen',
  `LaatzienVanaf` timestamp NOT NULL DEFAULT '2008-07-31 22:00:00',
  PRIMARY KEY (`EAN`,`Kostenplaats`,`ExactID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2011_VoorraadMutaties`
--

CREATE TABLE `2011_VoorraadMutaties` (
  `EAN` char(13) CHARACTER SET latin1 NOT NULL,
  `Kostenplaats` int(11) DEFAULT NULL,
  `ExactID` int(11) NOT NULL,
  `PeriodeID` int(11) NOT NULL,
  `DT` datetime NOT NULL,
  `AantalOud` int(11) NOT NULL,
  `AantalNieuw` int(11) NOT NULL,
  `ForecastOud` int(11) NOT NULL,
  `ForecastNieuw` int(11) NOT NULL,
  `Reden` enum('Inklaren','Verkoop','Beschadigd','Telfout','Retour','forecast','Intrekken','Afsluiten') CHARACTER SET latin1 NOT NULL DEFAULT 'Inklaren',
  `Gebruiker` varchar(64) CHARACTER SET latin1 NOT NULL,
  KEY `NewIndex1` (`EAN`),
  KEY `FK_VoorraadMutaties` (`EAN`,`Kostenplaats`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2012_Batches`
--

CREATE TABLE `2012_Batches` (
  `Batchid` int(11) NOT NULL,
  `DTIngediend` datetime DEFAULT NULL,
  `DTGeprint` datetime DEFAULT NULL,
  `DTVerzonden` datetime DEFAULT NULL,
  `DTGefactureerd` datetime DEFAULT NULL,
  `Status` enum('ingediend','geprint','verzonden','gefactureerd','ingetrokken') NOT NULL,
  `Spoed` char(10) DEFAULT NULL,
  `Kostenpost` int(11) DEFAULT NULL,
  PRIMARY KEY (`Batchid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2012_BoekLocaties`
--

CREATE TABLE `2012_BoekLocaties` (
  `EAN` char(13) NOT NULL,
  `Rij` int(11) DEFAULT NULL,
  `Kast` int(11) DEFAULT NULL,
  `Plank` int(11) DEFAULT NULL,
  PRIMARY KEY (`EAN`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2012_CreditatieLog`
--

CREATE TABLE `2012_CreditatieLog` (
  `DT` datetime DEFAULT NULL,
  `Pakbonid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2012_Orderregels`
--

CREATE TABLE `2012_Orderregels` (
  `Pakbonid` int(11) NOT NULL,
  `Ean` char(13) NOT NULL,
  `Aantal` int(11) NOT NULL,
  `Debiteur` int(11) NOT NULL,
  `PeriodeID` int(11) NOT NULL,
  PRIMARY KEY (`Pakbonid`,`Ean`),
  KEY `FK_Orderregels_Voorraad` (`Ean`,`Debiteur`,`PeriodeID`),
  CONSTRAINT `FK_Orderregels` FOREIGN KEY (`Pakbonid`) REFERENCES `2012_Pakbonnen` (`Pakbonid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `2012_PakbonOrders`
--

CREATE TABLE `2012_PakbonOrders` (
  `Pakbonid` int(11) NOT NULL,
  `Ordernummer` int(11) NOT NULL,
  PRIMARY KEY (`Pakbonid`,`Ordernummer`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2012_Pakbonnen`
--

CREATE TABLE `2012_Pakbonnen` (
  `Barcode` varchar(18) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `Pakbonid` int(11) NOT NULL AUTO_INCREMENT,
  `Batchid` int(11) NOT NULL,
  `Status` enum('ingediend','geprint','verzonden','gefactureerd','geretourneerd','gecrediteerd','ingetrokken') NOT NULL,
  `Kostenpost` int(11) DEFAULT NULL,
  `Land` varchar(20) NOT NULL,
  PRIMARY KEY (`Pakbonid`),
  UNIQUE KEY `Barcode_Uniq` (`Barcode`),
  KEY `FK_Pakbonnen_Batches` (`Batchid`),
  KEY `NewIndex1` (`Batchid`),
  CONSTRAINT `FK_Pakbonnen` FOREIGN KEY (`Batchid`) REFERENCES `2012_Batches` (`Batchid`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=88119 DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2012_Transacties`
--

CREATE TABLE `2012_Transacties` (
  `Type` enum('forecast','voorraad') NOT NULL,
  `Ean` char(13) NOT NULL,
  `Kostenplaats` int(11) DEFAULT NULL,
  `Exactid` int(11) NOT NULL,
  `DT` datetime NOT NULL,
  `Delta` int(11) DEFAULT NULL,
  `Gebruiker` varchar(64) DEFAULT NULL,
  `Reden` mediumtext,
  `Commentaar` mediumtext,
  KEY `NewIndex1` (`Type`,`Ean`,`DT`,`Kostenplaats`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2012_Voorraad`
--

CREATE TABLE `2012_Voorraad` (
  `EAN` char(13) NOT NULL,
  `ExactID` int(11) NOT NULL,
  `Kostenplaats` int(11) NOT NULL,
  `Voorraad` int(11) NOT NULL,
  `Forecast` int(11) NOT NULL,
  `Afgesloten` enum('Neen','Ja') NOT NULL DEFAULT 'Neen',
  `LaatzienVanaf` timestamp NOT NULL DEFAULT '2008-07-31 22:00:00',
  PRIMARY KEY (`EAN`,`Kostenplaats`,`ExactID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2012_VoorraadMutaties`
--

CREATE TABLE `2012_VoorraadMutaties` (
  `EAN` char(13) CHARACTER SET latin1 NOT NULL,
  `Kostenplaats` int(11) DEFAULT NULL,
  `ExactID` int(11) NOT NULL,
  `PeriodeID` int(11) NOT NULL,
  `DT` datetime NOT NULL,
  `AantalOud` int(11) NOT NULL,
  `AantalNieuw` int(11) NOT NULL,
  `ForecastOud` int(11) NOT NULL,
  `ForecastNieuw` int(11) NOT NULL,
  `Reden` enum('Inklaren','Verkoop','Beschadigd','Telfout','Retour','forecast','Intrekken','Afsluiten') CHARACTER SET latin1 NOT NULL DEFAULT 'Inklaren',
  `Gebruiker` varchar(64) CHARACTER SET latin1 NOT NULL,
  KEY `NewIndex1` (`EAN`),
  KEY `FK_VoorraadMutaties` (`EAN`,`Kostenplaats`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2013_Batches`
--

CREATE TABLE `2013_Batches` (
  `Batchid` int(11) NOT NULL,
  `DTIngediend` datetime DEFAULT NULL,
  `DTGeprint` datetime DEFAULT NULL,
  `DTVerzonden` datetime DEFAULT NULL,
  `DTGefactureerd` datetime DEFAULT NULL,
  `Status` enum('ingediend','geprint','verzonden','gefactureerd','ingetrokken') NOT NULL,
  `Spoed` char(10) DEFAULT NULL,
  `Kostenpost` int(11) DEFAULT NULL,
  PRIMARY KEY (`Batchid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2013_BoekLocaties`
--

CREATE TABLE `2013_BoekLocaties` (
  `EAN` char(13) CHARACTER SET latin1 NOT NULL,
  `Rij` int(11) DEFAULT NULL,
  `Kast` int(11) DEFAULT NULL,
  `Plank` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `2013_CreditatieLog`
--

CREATE TABLE `2013_CreditatieLog` (
  `DT` datetime DEFAULT NULL,
  `Pakbonid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2013_FacturatieLog`
--

CREATE TABLE `2013_FacturatieLog` (
  `DT` datetime DEFAULT NULL,
  `Pakbonid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2013_Orderregels`
--

CREATE TABLE `2013_Orderregels` (
  `Pakbonid` int(11) NOT NULL,
  `Ean` char(13) NOT NULL,
  `Aantal` int(11) NOT NULL,
  `Debiteur` int(11) NOT NULL,
  `PeriodeID` int(11) NOT NULL,
  PRIMARY KEY (`Pakbonid`,`Ean`),
  KEY `FK_Orderregels_Voorraad` (`Ean`,`Debiteur`,`PeriodeID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `2013_PakbonAdressen`
--

CREATE TABLE `2013_PakbonAdressen` (
  `Barcode` varchar(40) NOT NULL,
  `Naam1` varchar(40) DEFAULT NULL,
  `Naam2` varchar(40) DEFAULT NULL,
  `Straat` varchar(200) DEFAULT NULL,
  `Huisnummer` int(11) DEFAULT NULL,
  `Toevoeging` varchar(20) DEFAULT NULL,
  `Postcode` varchar(20) DEFAULT NULL,
  `Plaats` varchar(50) DEFAULT NULL,
  `Land` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`Barcode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `2013_PakbonOrders`
--

CREATE TABLE `2013_PakbonOrders` (
  `Pakbonid` int(11) NOT NULL,
  `Ordernummer` int(11) NOT NULL,
  PRIMARY KEY (`Pakbonid`,`Ordernummer`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2013_Pakbonnen`
--

CREATE TABLE `2013_Pakbonnen` (
  `Barcode` varchar(18) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `Pakbonid` int(11) NOT NULL AUTO_INCREMENT,
  `Batchid` int(11) NOT NULL,
  `Status` enum('ingediend','geprint','verzonden','gefactureerd','geretourneerd','gecrediteerd','ingetrokken') NOT NULL,
  `Kostenpost` int(11) DEFAULT NULL,
  `Land` varchar(20) NOT NULL,
  PRIMARY KEY (`Pakbonid`),
  UNIQUE KEY `Barcode_Uniq` (`Barcode`),
  KEY `FK_Pakbonnen_Batches` (`Batchid`),
  KEY `NewIndex1` (`Batchid`)
) ENGINE=InnoDB AUTO_INCREMENT=88540 DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2013_Retour`
--

CREATE TABLE `2013_Retour` (
  `PakbonID` int(11) NOT NULL,
  `DTGeretourneerd` datetime NOT NULL,
  `DTGecrediteerd` datetime DEFAULT NULL,
  `Opmerkingen` text NOT NULL,
  PRIMARY KEY (`PakbonID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2013_Transacties`
--

CREATE TABLE `2013_Transacties` (
  `Type` enum('forecast','voorraad') NOT NULL,
  `Ean` char(13) NOT NULL,
  `Kostenplaats` int(11) DEFAULT NULL,
  `Exactid` int(11) NOT NULL,
  `DT` datetime NOT NULL,
  `Delta` int(11) DEFAULT NULL,
  `Gebruiker` varchar(64) DEFAULT NULL,
  `Reden` mediumtext,
  `Commentaar` mediumtext,
  KEY `NewIndex1` (`Type`,`Ean`,`DT`,`Kostenplaats`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2013_Voorraad`
--

CREATE TABLE `2013_Voorraad` (
  `EAN` char(13) NOT NULL,
  `ExactID` int(11) NOT NULL,
  `Kostenplaats` int(11) NOT NULL,
  `Voorraad` int(11) NOT NULL,
  `Forecast` int(11) NOT NULL,
  `Afgesloten` enum('Neen','Ja') NOT NULL DEFAULT 'Neen',
  `LaatzienVanaf` timestamp NOT NULL DEFAULT '2008-07-31 22:00:00',
  PRIMARY KEY (`EAN`,`Kostenplaats`,`ExactID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2013_VoorraadMutaties`
--

CREATE TABLE `2013_VoorraadMutaties` (
  `EAN` char(13) CHARACTER SET latin1 NOT NULL,
  `Kostenplaats` int(11) DEFAULT NULL,
  `ExactID` int(11) NOT NULL,
  `PeriodeID` int(11) NOT NULL,
  `DT` datetime NOT NULL,
  `AantalOud` int(11) NOT NULL,
  `AantalNieuw` int(11) NOT NULL,
  `ForecastOud` int(11) NOT NULL,
  `ForecastNieuw` int(11) NOT NULL,
  `Reden` enum('Inklaren','Verkoop','Beschadigd','Telfout','Retour','forecast','Intrekken','Afsluiten') CHARACTER SET latin1 NOT NULL DEFAULT 'Inklaren',
  `Gebruiker` varchar(64) CHARACTER SET latin1 NOT NULL,
  KEY `NewIndex1` (`EAN`),
  KEY `FK_VoorraadMutaties` (`EAN`,`Kostenplaats`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2014_Batches`
--

CREATE TABLE `2014_Batches` (
  `Batchid` int(11) NOT NULL,
  `DTIngediend` datetime DEFAULT NULL,
  `DTGeprint` datetime DEFAULT NULL,
  `DTVerzonden` datetime DEFAULT NULL,
  `DTGefactureerd` datetime DEFAULT NULL,
  `Status` enum('ingediend','geprint','verzonden','gefactureerd','ingetrokken') NOT NULL,
  `Spoed` char(10) DEFAULT NULL,
  `Kostenpost` int(11) DEFAULT NULL,
  PRIMARY KEY (`Batchid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2014_BoekLocaties`
--

CREATE TABLE `2014_BoekLocaties` (
  `EAN` char(13) NOT NULL,
  `Rij` int(11) DEFAULT NULL,
  `Kast` int(11) DEFAULT NULL,
  `Plank` int(11) DEFAULT NULL,
  PRIMARY KEY (`EAN`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2014_CreditatieLog`
--

CREATE TABLE `2014_CreditatieLog` (
  `DT` datetime DEFAULT NULL,
  `Pakbonid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2014_FacturatieLog`
--

CREATE TABLE `2014_FacturatieLog` (
  `DT` datetime DEFAULT NULL,
  `Pakbonid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2014_Orderregels`
--

CREATE TABLE `2014_Orderregels` (
  `Pakbonid` int(11) NOT NULL,
  `Ean` char(13) NOT NULL,
  `Aantal` int(11) NOT NULL,
  `Debiteur` int(11) NOT NULL,
  `PeriodeID` int(11) NOT NULL,
  PRIMARY KEY (`Pakbonid`,`Ean`),
  KEY `FK_Orderregels_Voorraad` (`Ean`,`Debiteur`,`PeriodeID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `2014_PakbonAdressen`
--

CREATE TABLE `2014_PakbonAdressen` (
  `Barcode` varchar(40) NOT NULL,
  `Naam1` varchar(40) DEFAULT NULL,
  `Naam2` varchar(40) DEFAULT NULL,
  `Straat` varchar(200) DEFAULT NULL,
  `Huisnummer` int(11) DEFAULT NULL,
  `Toevoeging` varchar(20) DEFAULT NULL,
  `Postcode` varchar(20) DEFAULT NULL,
  `Plaats` varchar(50) DEFAULT NULL,
  `Land` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`Barcode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `2014_PakbonOrders`
--

CREATE TABLE `2014_PakbonOrders` (
  `Pakbonid` int(11) NOT NULL,
  `Ordernummer` int(11) NOT NULL,
  PRIMARY KEY (`Pakbonid`,`Ordernummer`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2014_Pakbonnen`
--

CREATE TABLE `2014_Pakbonnen` (
  `Barcode` varchar(18) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `Pakbonid` int(11) NOT NULL AUTO_INCREMENT,
  `Batchid` int(11) NOT NULL,
  `Status` enum('ingediend','geprint','verzonden','gefactureerd','geretourneerd','gecrediteerd','ingetrokken') NOT NULL,
  `Kostenpost` int(11) DEFAULT NULL,
  `Land` varchar(20) NOT NULL,
  PRIMARY KEY (`Pakbonid`),
  UNIQUE KEY `Barcode_Uniq` (`Barcode`),
  KEY `FK_Pakbonnen_Batches` (`Batchid`),
  KEY `NewIndex1` (`Batchid`)
) ENGINE=InnoDB AUTO_INCREMENT=91204 DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2014_Retour`
--

CREATE TABLE `2014_Retour` (
  `PakbonID` int(11) NOT NULL,
  `DTGeretourneerd` datetime NOT NULL,
  `DTGecrediteerd` datetime DEFAULT NULL,
  `Opmerkingen` text NOT NULL,
  PRIMARY KEY (`PakbonID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2014_Transacties`
--

CREATE TABLE `2014_Transacties` (
  `Type` enum('forecast','voorraad') NOT NULL,
  `Ean` char(13) NOT NULL,
  `Kostenplaats` int(11) DEFAULT NULL,
  `Exactid` int(11) NOT NULL,
  `DT` datetime NOT NULL,
  `Delta` int(11) DEFAULT NULL,
  `Gebruiker` varchar(64) DEFAULT NULL,
  `Reden` mediumtext,
  `Commentaar` mediumtext,
  KEY `NewIndex1` (`Type`,`Ean`,`DT`,`Kostenplaats`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2014_Voorraad`
--

CREATE TABLE `2014_Voorraad` (
  `EAN` char(13) NOT NULL,
  `ExactID` int(11) NOT NULL,
  `Kostenplaats` int(11) NOT NULL,
  `Voorraad` int(11) NOT NULL,
  `Forecast` int(11) NOT NULL,
  `Afgesloten` enum('Neen','Ja') NOT NULL DEFAULT 'Neen',
  `LaatzienVanaf` timestamp NOT NULL DEFAULT '2008-07-31 22:00:00',
  PRIMARY KEY (`EAN`,`Kostenplaats`,`ExactID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2014_VoorraadMutaties`
--

CREATE TABLE `2014_VoorraadMutaties` (
  `EAN` char(13) CHARACTER SET latin1 NOT NULL,
  `Kostenplaats` int(11) DEFAULT NULL,
  `ExactID` int(11) NOT NULL,
  `PeriodeID` int(11) NOT NULL,
  `DT` datetime NOT NULL,
  `AantalOud` int(11) NOT NULL,
  `AantalNieuw` int(11) NOT NULL,
  `ForecastOud` int(11) NOT NULL,
  `ForecastNieuw` int(11) NOT NULL,
  `Reden` enum('Inklaren','Verkoop','Beschadigd','Telfout','Retour','forecast','Intrekken','Afsluiten') CHARACTER SET latin1 NOT NULL DEFAULT 'Inklaren',
  `Gebruiker` varchar(64) CHARACTER SET latin1 NOT NULL,
  KEY `NewIndex1` (`EAN`),
  KEY `FK_VoorraadMutaties` (`EAN`,`Kostenplaats`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2015_Batches`
--

CREATE TABLE `2015_Batches` (
  `Batchid` int(11) NOT NULL,
  `DTIngediend` datetime DEFAULT NULL,
  `DTGeprint` datetime DEFAULT NULL,
  `DTVerzonden` datetime DEFAULT NULL,
  `DTGefactureerd` datetime DEFAULT NULL,
  `Status` enum('ingediend','geprint','verzonden','gefactureerd','ingetrokken') NOT NULL,
  `Spoed` char(50) DEFAULT NULL,
  `Kostenpost` int(11) DEFAULT NULL,
  PRIMARY KEY (`Batchid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2015_BoekLocaties`
--

CREATE TABLE `2015_BoekLocaties` (
  `EAN` char(13) NOT NULL,
  `Rij` int(11) DEFAULT NULL,
  `Kast` int(11) DEFAULT NULL,
  `Plank` int(11) DEFAULT NULL,
  PRIMARY KEY (`EAN`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2015_CreditatieLog`
--

CREATE TABLE `2015_CreditatieLog` (
  `DT` datetime DEFAULT NULL,
  `Pakbonid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2015_FacturatieLog`
--

CREATE TABLE `2015_FacturatieLog` (
  `DT` datetime DEFAULT NULL,
  `Pakbonid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2015_Orderregels`
--

CREATE TABLE `2015_Orderregels` (
  `Pakbonid` int(11) NOT NULL,
  `Ean` char(13) NOT NULL,
  `Aantal` int(11) NOT NULL,
  `Debiteur` int(11) NOT NULL,
  `PeriodeID` int(11) NOT NULL,
  PRIMARY KEY (`Pakbonid`,`Ean`),
  KEY `FK_Orderregels_Voorraad` (`Ean`,`Debiteur`,`PeriodeID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `2015_PakbonAdressen`
--

CREATE TABLE `2015_PakbonAdressen` (
  `Barcode` varchar(40) NOT NULL,
  `Naam1` varchar(40) DEFAULT NULL,
  `Naam2` varchar(40) DEFAULT NULL,
  `Straat` varchar(200) DEFAULT NULL,
  `Huisnummer` int(11) DEFAULT NULL,
  `Toevoeging` varchar(20) DEFAULT NULL,
  `Postcode` varchar(20) DEFAULT NULL,
  `Plaats` varchar(50) DEFAULT NULL,
  `Land` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`Barcode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `2015_PakbonOrders`
--

CREATE TABLE `2015_PakbonOrders` (
  `Pakbonid` int(11) NOT NULL,
  `Ordernummer` int(11) NOT NULL,
  PRIMARY KEY (`Pakbonid`,`Ordernummer`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2015_Pakbonnen`
--

CREATE TABLE `2015_Pakbonnen` (
  `Barcode` varchar(18) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `Pakbonid` int(11) NOT NULL AUTO_INCREMENT,
  `Batchid` int(11) NOT NULL,
  `Status` enum('ingediend','geprint','verzonden','gefactureerd','geretourneerd','gecrediteerd','ingetrokken') NOT NULL,
  `Kostenpost` int(11) DEFAULT NULL,
  `Land` varchar(20) NOT NULL,
  PRIMARY KEY (`Pakbonid`),
  UNIQUE KEY `Barcode_Uniq` (`Barcode`),
  KEY `FK_Pakbonnen_Batches` (`Batchid`),
  KEY `NewIndex1` (`Batchid`)
) ENGINE=InnoDB AUTO_INCREMENT=97046 DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2015_Retour`
--

CREATE TABLE `2015_Retour` (
  `PakbonID` int(11) NOT NULL,
  `DTGeretourneerd` datetime NOT NULL,
  `DTGecrediteerd` datetime DEFAULT NULL,
  `Opmerkingen` text NOT NULL,
  PRIMARY KEY (`PakbonID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2015_Transacties`
--

CREATE TABLE `2015_Transacties` (
  `Type` enum('forecast','voorraad') NOT NULL,
  `Ean` char(13) NOT NULL,
  `Kostenplaats` int(11) DEFAULT NULL,
  `Exactid` int(11) NOT NULL,
  `DT` datetime NOT NULL,
  `Delta` int(11) DEFAULT NULL,
  `Gebruiker` varchar(64) DEFAULT NULL,
  `Reden` mediumtext,
  `Commentaar` mediumtext,
  KEY `NewIndex1` (`Type`,`Ean`,`DT`,`Kostenplaats`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2015_Voorraad`
--

CREATE TABLE `2015_Voorraad` (
  `EAN` char(13) NOT NULL,
  `ExactID` int(11) NOT NULL,
  `Kostenplaats` int(11) NOT NULL,
  `Voorraad` int(11) NOT NULL,
  `Forecast` int(11) NOT NULL,
  `Afgesloten` enum('Neen','Ja') NOT NULL DEFAULT 'Neen',
  `LaatzienVanaf` timestamp NOT NULL DEFAULT '2008-07-31 22:00:00',
  PRIMARY KEY (`EAN`,`Kostenplaats`,`ExactID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2015_VoorraadMutaties`
--

CREATE TABLE `2015_VoorraadMutaties` (
  `EAN` char(13) CHARACTER SET latin1 NOT NULL,
  `Kostenplaats` int(11) DEFAULT NULL,
  `ExactID` int(11) NOT NULL,
  `PeriodeID` int(11) NOT NULL,
  `DT` datetime NOT NULL,
  `AantalOud` int(11) NOT NULL,
  `AantalNieuw` int(11) NOT NULL,
  `ForecastOud` int(11) NOT NULL,
  `ForecastNieuw` int(11) NOT NULL,
  `Reden` enum('Inklaren','Verkoop','Beschadigd','Telfout','Retour','forecast','Intrekken','Afsluiten') CHARACTER SET latin1 NOT NULL DEFAULT 'Inklaren',
  `Gebruiker` varchar(64) CHARACTER SET latin1 NOT NULL,
  KEY `NewIndex1` (`EAN`),
  KEY `FK_VoorraadMutaties` (`EAN`,`Kostenplaats`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2016_Batches`
--

CREATE TABLE `2016_Batches` (
  `Batchid` int(11) NOT NULL,
  `DTIngediend` datetime DEFAULT NULL,
  `DTGeprint` datetime DEFAULT NULL,
  `DTVerzonden` datetime DEFAULT NULL,
  `DTGefactureerd` datetime DEFAULT NULL,
  `Status` enum('ingediend','geprint','verzonden','gefactureerd','ingetrokken') NOT NULL,
  `Spoed` char(50) DEFAULT NULL,
  `Kostenpost` int(11) DEFAULT NULL,
  PRIMARY KEY (`Batchid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2016_BoekLocaties`
--

CREATE TABLE `2016_BoekLocaties` (
  `EAN` char(13) NOT NULL,
  `Rij` int(11) DEFAULT NULL,
  `Kast` int(11) DEFAULT NULL,
  `Plank` int(11) DEFAULT NULL,
  PRIMARY KEY (`EAN`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2016_CreditatieLog`
--

CREATE TABLE `2016_CreditatieLog` (
  `DT` datetime DEFAULT NULL,
  `Pakbonid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2016_FacturatieLog`
--

CREATE TABLE `2016_FacturatieLog` (
  `DT` datetime DEFAULT NULL,
  `Pakbonid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2016_Orderregels`
--

CREATE TABLE `2016_Orderregels` (
  `Pakbonid` int(11) NOT NULL,
  `Ean` char(13) NOT NULL,
  `Aantal` int(11) NOT NULL,
  `Debiteur` int(11) NOT NULL,
  `PeriodeID` int(11) NOT NULL,
  PRIMARY KEY (`Pakbonid`,`Ean`),
  KEY `FK_Orderregels_Voorraad` (`Ean`,`Debiteur`,`PeriodeID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `2016_PakbonAdressen`
--

CREATE TABLE `2016_PakbonAdressen` (
  `Barcode` varchar(40) NOT NULL,
  `Naam1` varchar(40) DEFAULT NULL,
  `Naam2` varchar(40) DEFAULT NULL,
  `Straat` varchar(200) DEFAULT NULL,
  `Huisnummer` int(11) DEFAULT NULL,
  `Toevoeging` varchar(20) DEFAULT NULL,
  `Postcode` varchar(20) DEFAULT NULL,
  `Plaats` varchar(50) DEFAULT NULL,
  `Land` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`Barcode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `2016_PakbonOrders`
--

CREATE TABLE `2016_PakbonOrders` (
  `Pakbonid` int(11) NOT NULL,
  `Ordernummer` int(11) NOT NULL,
  PRIMARY KEY (`Pakbonid`,`Ordernummer`),
  KEY `Ordernummer` (`Ordernummer`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2016_Pakbonnen`
--

CREATE TABLE `2016_Pakbonnen` (
  `Barcode` varchar(18) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `Pakbonid` int(11) NOT NULL AUTO_INCREMENT,
  `Batchid` int(11) NOT NULL,
  `Status` enum('ingediend','geprint','verzonden','gefactureerd','geretourneerd','gecrediteerd','ingetrokken') NOT NULL,
  `Kostenpost` int(11) DEFAULT NULL,
  `Land` varchar(20) NOT NULL,
  PRIMARY KEY (`Pakbonid`),
  UNIQUE KEY `Barcode_Uniq` (`Barcode`),
  KEY `FK_Pakbonnen_Batches` (`Batchid`),
  KEY `NewIndex1` (`Batchid`)
) ENGINE=InnoDB AUTO_INCREMENT=108763 DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2016_Retour`
--

CREATE TABLE `2016_Retour` (
  `PakbonID` int(11) NOT NULL,
  `DTGeretourneerd` datetime NOT NULL,
  `DTGecrediteerd` datetime DEFAULT NULL,
  `Opmerkingen` text NOT NULL,
  PRIMARY KEY (`PakbonID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2016_Transacties`
--

CREATE TABLE `2016_Transacties` (
  `Type` enum('forecast','voorraad') NOT NULL,
  `Ean` char(13) NOT NULL,
  `Kostenplaats` int(11) DEFAULT NULL,
  `Exactid` int(11) NOT NULL,
  `DT` datetime NOT NULL,
  `Delta` int(11) DEFAULT NULL,
  `Gebruiker` varchar(64) DEFAULT NULL,
  `Reden` mediumtext,
  `Commentaar` mediumtext,
  KEY `NewIndex1` (`Type`,`Ean`,`DT`,`Kostenplaats`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2016_Voorraad`
--

CREATE TABLE `2016_Voorraad` (
  `EAN` char(13) NOT NULL,
  `ExactID` int(11) NOT NULL DEFAULT '0',
  `Kostenplaats` int(11) NOT NULL DEFAULT '1',
  `Voorraad` int(11) NOT NULL,
  `Forecast` int(11) NOT NULL,
  `Afgesloten` enum('Neen','Ja') NOT NULL DEFAULT 'Neen',
  `LaatzienVanaf` timestamp NOT NULL DEFAULT '2008-07-31 22:00:00',
  PRIMARY KEY (`EAN`,`Kostenplaats`,`ExactID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `2016_VoorraadMutaties`
--

CREATE TABLE `2016_VoorraadMutaties` (
  `EAN` char(13) CHARACTER SET latin1 NOT NULL,
  `Kostenplaats` int(11) NOT NULL DEFAULT '1',
  `ExactID` int(11) NOT NULL DEFAULT '0',
  `PeriodeID` int(11) NOT NULL DEFAULT '1',
  `DT` datetime NOT NULL,
  `AantalOud` int(11) NOT NULL,
  `AantalNieuw` int(11) NOT NULL,
  `ForecastOud` int(11) NOT NULL,
  `ForecastNieuw` int(11) NOT NULL,
  `Reden` enum('Inklaren','Verkoop','Beschadigd','Telfout','Retour','forecast','Intrekken','Afsluiten') CHARACTER SET latin1 NOT NULL DEFAULT 'Inklaren',
  `Gebruiker` varchar(64) CHARACTER SET latin1 NOT NULL,
  KEY `NewIndex1` (`EAN`),
  KEY `FK_VoorraadMutaties` (`EAN`,`Kostenplaats`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `Batches`
--

CREATE TABLE `Batches` (
  `Batchid` int(11) NOT NULL,
  `DTIngediend` datetime DEFAULT NULL,
  `DTGeprint` datetime DEFAULT NULL,
  `DTVerzonden` datetime DEFAULT NULL,
  `DTGefactureerd` datetime DEFAULT NULL,
  `Status` enum('ingediend','geprint','verzonden','gefactureerd','ingetrokken') NOT NULL,
  `Spoed` char(50) DEFAULT NULL,
  `Kostenpost` int(11) DEFAULT NULL,
  PRIMARY KEY (`Batchid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `Bedrijven`
--

CREATE TABLE `Bedrijven` (
  `BedrijfID` varchar(10) COLLATE latin1_general_ci NOT NULL,
  `Naam` varchar(256) COLLATE latin1_general_ci NOT NULL,
  `Wobsite` varchar(256) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`BedrijfID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `BoekLocaties`
--

CREATE TABLE `BoekLocaties` (
  `EAN` char(13) NOT NULL,
  `Rij` int(11) DEFAULT NULL,
  `Kast` int(11) DEFAULT NULL,
  `Plank` int(11) DEFAULT NULL,
  PRIMARY KEY (`EAN`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Temporary table structure for view `Boeken_met_prijs`
--

/*!50001 DROP VIEW IF EXISTS `Boeken_met_prijs`*/;
/*!50001 CREATE TABLE `Boeken_met_prijs` (
  `Ean` tinyint NOT NULL,
  `Titel` tinyint NOT NULL,
  `NaamUitgever` tinyint NOT NULL,
  `Boeksoort` tinyint NOT NULL,
  `OriginelePrijs` tinyint NOT NULL,
  `Percentage` tinyint NOT NULL,
  `EuroPrijsMetKorting` tinyint NOT NULL,
  `KortingMP` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Table structure for table `CreditatieLog`
--

CREATE TABLE `CreditatieLog` (
  `DT` datetime DEFAULT NULL,
  `Pakbonid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `FacturatieLog`
--

CREATE TABLE `FacturatieLog` (
  `DT` datetime DEFAULT NULL,
  `Pakbonid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `FacturatieLog_backup_210116`
--

CREATE TABLE `FacturatieLog_backup_210116` (
  `DT` datetime DEFAULT NULL,
  `Pakbonid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Temporary table structure for view `ForecastTransacties`
--

/*!50001 DROP VIEW IF EXISTS `ForecastTransacties`*/;
/*!50001 CREATE TABLE `ForecastTransacties` (
  `EAN` tinyint NOT NULL,
  `ExactID` tinyint NOT NULL,
  `DT` tinyint NOT NULL,
  `Mutatie` tinyint NOT NULL,
  `Reden` tinyint NOT NULL,
  `Gebruiker` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `Forecasts`
--

/*!50001 DROP VIEW IF EXISTS `Forecasts`*/;
/*!50001 CREATE TABLE `Forecasts` (
  `EAN` tinyint NOT NULL,
  `Forecast` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Table structure for table `HandmatigeRetouren`
--

CREATE TABLE `HandmatigeRetouren` (
  `DT` datetime(3) NOT NULL,
  `Ean` char(13) NOT NULL,
  `Aantal` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Temporary table structure for view `Klanten`
--

/*!50001 DROP VIEW IF EXISTS `Klanten`*/;
/*!50001 CREATE TABLE `Klanten` (
  `KlantID` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `Klanten2016`
--

/*!50001 DROP VIEW IF EXISTS `Klanten2016`*/;
/*!50001 CREATE TABLE `Klanten2016` (
  `KlantID` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `KlantenNTI`
--

/*!50001 DROP VIEW IF EXISTS `KlantenNTI`*/;
/*!50001 CREATE TABLE `KlantenNTI` (
  `KlantID` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Table structure for table `Klanten_copy`
--

CREATE TABLE `Klanten_copy` (
  `KlantId` int(11) NOT NULL,
  PRIMARY KEY (`KlantId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `KortingEan`
--

CREATE TABLE `KortingEan` (
  `Ean` char(13) NOT NULL,
  `EanPercentage` double DEFAULT NULL,
  PRIMARY KEY (`Ean`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `KortingUitgever`
--

CREATE TABLE `KortingUitgever` (
  `UitgeverID` int(11) NOT NULL,
  `Percentage` double DEFAULT NULL,
  PRIMARY KEY (`UitgeverID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `KortingUitgever2016`
--

CREATE TABLE `KortingUitgever2016` (
  `UitgeverID` int(11) NOT NULL,
  `Percentage` double DEFAULT NULL,
  PRIMARY KEY (`UitgeverID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `KortingUitgever3`
--

CREATE TABLE `KortingUitgever3` (
  `UitgeverID` int(11) NOT NULL,
  `Percentage` double DEFAULT NULL,
  PRIMARY KEY (`UitgeverID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `OUDVoorraad`
--

CREATE TABLE `OUDVoorraad` (
  `EAN` char(13) NOT NULL,
  `ExactID` int(11) NOT NULL,
  `PeriodeID` int(11) NOT NULL,
  `Voorraad` int(11) NOT NULL,
  `Forecast` int(11) NOT NULL,
  `Afgesloten` enum('Neen','Ja') NOT NULL DEFAULT 'Neen',
  `LaatzienVanaf` timestamp NOT NULL DEFAULT '2008-07-31 22:00:00',
  PRIMARY KEY (`EAN`,`ExactID`,`PeriodeID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `Orderregels`
--

CREATE TABLE `Orderregels` (
  `Pakbonid` int(11) NOT NULL,
  `Ean` char(13) NOT NULL,
  `Aantal` int(11) NOT NULL,
  `Debiteur` int(11) NOT NULL,
  `PeriodeID` int(11) NOT NULL,
  PRIMARY KEY (`Pakbonid`,`Ean`),
  KEY `FK_Orderregels_Voorraad` (`Ean`,`Debiteur`,`PeriodeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `PakbonAdressen`
--

CREATE TABLE `PakbonAdressen` (
  `Barcode` varchar(40) NOT NULL,
  `Naam1` varchar(40) DEFAULT NULL,
  `Naam2` varchar(40) DEFAULT NULL,
  `Straat` varchar(200) DEFAULT NULL,
  `Huisnummer` int(11) DEFAULT NULL,
  `Toevoeging` varchar(20) DEFAULT NULL,
  `Postcode` varchar(20) DEFAULT NULL,
  `Plaats` varchar(50) DEFAULT NULL,
  `Land` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`Barcode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `PakbonOrders`
--

CREATE TABLE `PakbonOrders` (
  `Pakbonid` int(11) NOT NULL,
  `Ordernummer` int(11) NOT NULL,
  PRIMARY KEY (`Pakbonid`,`Ordernummer`),
  KEY `Ordernummer` (`Ordernummer`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `Pakbonnen`
--

CREATE TABLE `Pakbonnen` (
  `Barcode` varchar(18) NOT NULL,
  `Pakbonid` int(11) NOT NULL AUTO_INCREMENT,
  `Batchid` int(11) NOT NULL,
  `Status` enum('ingediend','geprint','verzonden','gefactureerd','geretourneerd','gecrediteerd','ingetrokken') NOT NULL,
  `Kostenpost` int(11) NOT NULL DEFAULT '1',
  `Land` varchar(20) NOT NULL,
  PRIMARY KEY (`Pakbonid`),
  UNIQUE KEY `Barcode_Uniq` (`Barcode`),
  KEY `FK_Pakbonnen_Batches` (`Batchid`),
  KEY `NewIndex1` (`Batchid`)
) ENGINE=InnoDB AUTO_INCREMENT=473 DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `Pakbonnen_backup_210116`
--

CREATE TABLE `Pakbonnen_backup_210116` (
  `Barcode` varchar(18) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `Pakbonid` int(11) NOT NULL DEFAULT '0',
  `Batchid` int(11) NOT NULL,
  `Status` enum('ingediend','geprint','verzonden','gefactureerd','geretourneerd','gecrediteerd','ingetrokken') CHARACTER SET latin1 NOT NULL,
  `Kostenpost` int(11) DEFAULT NULL,
  `Land` varchar(20) CHARACTER SET latin1 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `Periode`
--

CREATE TABLE `Periode` (
  `PeriodeID` int(11) NOT NULL AUTO_INCREMENT,
  `Van` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Tot` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `Status` enum('inactief','actief') NOT NULL DEFAULT 'inactief',
  `Beschrijving` text NOT NULL,
  PRIMARY KEY (`PeriodeID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `Personen`
--

CREATE TABLE `Personen` (
  `ExactID` int(11) NOT NULL,
  `BedrijfID` varchar(10) COLLATE latin1_general_ci NOT NULL,
  `Username` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
  `Password` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
  `Voornaam` varchar(64) COLLATE latin1_general_ci NOT NULL,
  `Tussenvoegsel` varchar(64) COLLATE latin1_general_ci NOT NULL,
  `Achternaam` varchar(64) COLLATE latin1_general_ci NOT NULL,
  `Status` enum('Forecast','Login','Geblokkeerd') COLLATE latin1_general_ci NOT NULL DEFAULT 'Login',
  PRIMARY KEY (`ExactID`),
  KEY `FK_Personen` (`BedrijfID`),
  CONSTRAINT `FK_Personen` FOREIGN KEY (`BedrijfID`) REFERENCES `Bedrijven` (`BedrijfID`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `Retour`
--

CREATE TABLE `Retour` (
  `PakbonID` int(11) NOT NULL,
  `DTGeretourneerd` datetime NOT NULL,
  `DTGecrediteerd` datetime DEFAULT NULL,
  `Opmerkingen` text NOT NULL,
  PRIMARY KEY (`PakbonID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `Settings`
--

CREATE TABLE `Settings` (
  `Key` char(5) NOT NULL,
  `Value` int(11) DEFAULT NULL,
  PRIMARY KEY (`Key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `Transacties`
--

CREATE TABLE `Transacties` (
  `Type` enum('forecast','voorraad') NOT NULL,
  `Ean` char(13) NOT NULL,
  `Kostenplaats` int(11) DEFAULT NULL,
  `Exactid` int(11) NOT NULL,
  `DT` datetime NOT NULL,
  `Delta` int(11) DEFAULT NULL,
  `Gebruiker` varchar(64) DEFAULT NULL,
  `Reden` mediumtext,
  `Commentaar` mediumtext,
  KEY `NewIndex1` (`Type`,`Ean`,`DT`,`Kostenplaats`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `Voorraad`
--

CREATE TABLE `Voorraad` (
  `EAN` char(13) NOT NULL,
  `ExactID` int(11) NOT NULL DEFAULT '0',
  `Kostenplaats` int(11) NOT NULL DEFAULT '1',
  `Voorraad` int(11) NOT NULL,
  `Forecast` int(11) NOT NULL,
  `Afgesloten` enum('Neen','Ja') NOT NULL DEFAULT 'Neen',
  `DTV` datetime(3) DEFAULT NULL,
  `DTF` datetime(3) DEFAULT NULL,
  `LaatzienVanaf` timestamp NOT NULL DEFAULT '2008-07-31 22:00:00',
  PRIMARY KEY (`EAN`,`Kostenplaats`,`ExactID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Temporary table structure for view `VoorraadEnForecast`
--

/*!50001 DROP VIEW IF EXISTS `VoorraadEnForecast`*/;
/*!50001 CREATE TABLE `VoorraadEnForecast` (
  `EAN` tinyint NOT NULL,
  `Exactid` tinyint NOT NULL,
  `Kostenplaats` tinyint NOT NULL,
  `Voorraad` tinyint NOT NULL,
  `Forecast` tinyint NOT NULL,
  `LaatzienVanaf` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `VoorraadEnForecast_dev`
--

/*!50001 DROP VIEW IF EXISTS `VoorraadEnForecast_dev`*/;
/*!50001 CREATE TABLE `VoorraadEnForecast_dev` (
  `EAN` tinyint NOT NULL,
  `Exactid` tinyint NOT NULL,
  `Kostenplaats` tinyint NOT NULL,
  `Voorraad` tinyint NOT NULL,
  `Forecast` tinyint NOT NULL,
  `LaatzienVanaf` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Table structure for table `VoorraadMutaties`
--

CREATE TABLE `VoorraadMutaties` (
  `EAN` char(13) NOT NULL,
  `Kostenplaats` int(11) NOT NULL DEFAULT '1',
  `ExactID` int(11) NOT NULL DEFAULT '0',
  `PeriodeID` int(11) NOT NULL DEFAULT '1',
  `DT` datetime(3) NOT NULL,
  `AantalOud` int(11) NOT NULL,
  `AantalNieuw` int(11) NOT NULL,
  `ForecastOud` int(11) NOT NULL,
  `ForecastNieuw` int(11) NOT NULL,
  `Reden` enum('Inklaren','Verkoop','Beschadigd','Telfout','Retour','forecast','Intrekken','Afsluiten') NOT NULL DEFAULT 'Inklaren',
  `Gebruiker` varchar(64) NOT NULL,
  KEY `NewIndex1` (`EAN`),
  KEY `FK_VoorraadMutaties` (`EAN`,`Kostenplaats`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Temporary table structure for view `VoorraadStand`
--

/*!50001 DROP VIEW IF EXISTS `VoorraadStand`*/;
/*!50001 CREATE TABLE `VoorraadStand` (
  `EAN` tinyint NOT NULL,
  `Forecast` tinyint NOT NULL,
  `Voorraad` tinyint NOT NULL,
  `Ingediend` tinyint NOT NULL,
  `Geprint` tinyint NOT NULL,
  `Verzonden` tinyint NOT NULL,
  `Gefactureerd` tinyint NOT NULL,
  `Geretourneerd` tinyint NOT NULL,
  `Gecrediteerd` tinyint NOT NULL,
  `Totaal` tinyint NOT NULL,
  `Verschil` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Table structure for table `XMLFactuurDownload`
--

CREATE TABLE `XMLFactuurDownload` (
  `FactuurId` int(11) NOT NULL,
  `DTXMLDownload` datetime NOT NULL,
  PRIMARY KEY (`FactuurId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `backup_mutaties`
--

CREATE TABLE `backup_mutaties` (
  `EAN` char(13) CHARACTER SET latin1 NOT NULL,
  `ExactID` int(11) NOT NULL,
  `PeriodeID` int(11) NOT NULL,
  `DT` datetime NOT NULL,
  `AantalOud` int(11) NOT NULL,
  `AantalNieuw` int(11) NOT NULL,
  `ForecastOud` int(11) NOT NULL,
  `ForecastNieuw` int(11) NOT NULL,
  `Reden` enum('Inklaren','Verkoop','Beschadigd','Telfout','Retour','forecast','Intrekken','Afsluiten') CHARACTER SET latin1 NOT NULL DEFAULT 'Inklaren',
  `Gebruiker` varchar(64) CHARACTER SET latin1 NOT NULL,
  KEY `NewIndex1` (`EAN`),
  KEY `FK_VoorraadMutaties` (`EAN`,`ExactID`,`PeriodeID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `backup_voorraad`
--

CREATE TABLE `backup_voorraad` (
  `EAN` char(13) NOT NULL,
  `ExactID` int(11) NOT NULL,
  `PeriodeID` int(11) NOT NULL,
  `Voorraad` int(11) NOT NULL,
  `Forecast` int(11) NOT NULL,
  `Afgesloten` enum('Neen','Ja') NOT NULL DEFAULT 'Neen',
  `LaatzienVanaf` timestamp NOT NULL DEFAULT '1988-01-23 23:00:00',
  PRIMARY KEY (`EAN`,`ExactID`,`PeriodeID`),
  KEY `PeriodeID` (`PeriodeID`),
  KEY `FK_Voorraad_Personen` (`ExactID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `nog_een_backup`
--

CREATE TABLE `nog_een_backup` (
  `EAN` char(13) CHARACTER SET latin1 NOT NULL,
  `ExactID` int(11) NOT NULL,
  `PeriodeID` int(11) NOT NULL,
  `DT` datetime NOT NULL,
  `AantalOud` int(11) NOT NULL,
  `AantalNieuw` int(11) NOT NULL,
  `ForecastOud` int(11) NOT NULL,
  `ForecastNieuw` int(11) NOT NULL,
  `Reden` enum('Inklaren','Verkoop','Beschadigd','Telfout','Retour','forecast','Intrekken','Afsluiten') CHARACTER SET latin1 NOT NULL DEFAULT 'Inklaren',
  `Gebruiker` varchar(64) CHARACTER SET latin1 NOT NULL,
  KEY `NewIndex1` (`EAN`),
  KEY `FK_VoorraadMutaties` (`EAN`,`ExactID`,`PeriodeID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `tmpMutaties`
--

CREATE TABLE `tmpMutaties` (
  `EAN` char(13) CHARACTER SET latin1 NOT NULL,
  `ExactID` int(11) NOT NULL,
  `PeriodeID` int(11) NOT NULL,
  `DT` datetime NOT NULL,
  `AantalOud` int(11) NOT NULL,
  `AantalNieuw` int(11) NOT NULL,
  `ForecastOud` int(11) NOT NULL,
  `ForecastNieuw` int(11) NOT NULL,
  `Reden` enum('Inklaren','Verkoop','Beschadigd','Telfout','Retour','forecast','Intrekken','Afsluiten') CHARACTER SET latin1 NOT NULL DEFAULT 'Inklaren',
  `Gebruiker` varchar(64) CHARACTER SET latin1 NOT NULL,
  KEY `NewIndex1` (`EAN`),
  KEY `FK_VoorraadMutaties` (`EAN`,`ExactID`,`PeriodeID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Dumping routines for database 'NCOI'
--
/*!50003 DROP FUNCTION IF EXISTS `act_fc` */;
ALTER DATABASE `NCOI` CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
DELIMITER ;;
CREATE DEFINER=`main`@`%` FUNCTION `act_fc`( xEAN CHAR(13), xExactID INT ) RETURNS int(11)
    READS SQL DATA
    DETERMINISTIC
RETURN ( SELECT `Forecast` - IFNULL( 
   ( SELECT ForecastNieuw FROM NCOI.tmpMutaties as VM 
       WHERE VM.EAN = Voorraad.EAN 
       AND VM.ExactID = Voorraad.ExactID 
       AND Voorraad.PeriodeID = Voorraad.PeriodeID 
       AND DT <= Voorraad.LaatzienVanaf 
     ORDER BY DT DESC LIMIT 1 )
,0) AS `Forecast`
FROM NCOI.Voorraad
    WHERE EAN = xEAN
    AND ExactID = xExactID
    AND PeriodeID = 1) ;;
DELIMITER ;
ALTER DATABASE `NCOI` CHARACTER SET utf8 COLLATE utf8_general_ci ;
/*!50003 DROP FUNCTION IF EXISTS `BoekAuteurNaam` */;
DELIMITER ;;
CREATE DEFINER=`sander`@`%` FUNCTION `BoekAuteurNaam`( _ean CHAR(13) ) RETURNS varchar(255) CHARSET utf8
    READS SQL DATA
BEGIN
	DECLARE _rv VARCHAR(255) DEFAULT "";
	
	SELECT Achternaam INTO _rv FROM 
		Manpo.`BoekAuteurs` BA  
		JOIN Manpo.`Auteurs` A USING ( AuteurId )
		WHERE BA.Ean = _ean AND BA.`AuteurPositie` = 1;
		
	RETURN _rv;
    END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `ForecastVanaf` */;
ALTER DATABASE `NCOI` CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
DELIMITER ;;
CREATE DEFINER=`sander`@`%` FUNCTION `ForecastVanaf`( iEAN CHAR(13), iExact INT, Vanaf DATETIME ) RETURNS int(11)
    READS SQL DATA
    DETERMINISTIC
BEGIN
	DECLARE rv INT;
	SELECT IFNULL( SUM( Delta ), 0 ) INTO rv FROM NCOI.Transacties WHERE `Type` = 'forecast' AND Ean = iEAN AND Exactid = iExact AND DT > Vanaf;
	RETURN rv;
    END ;;
DELIMITER ;
ALTER DATABASE `NCOI` CHARACTER SET utf8 COLLATE utf8_general_ci ;
/*!50003 DROP FUNCTION IF EXISTS `ForecastVanafKP` */;
ALTER DATABASE `NCOI` CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
DELIMITER ;;
CREATE DEFINER=`sander`@`%` FUNCTION `ForecastVanafKP`( iEAN CHAR(13), iKostenplaats INT, Vanaf DATETIME ) RETURNS int(11)
    READS SQL DATA
    DETERMINISTIC
BEGIN
	DECLARE rv INT;
	SELECT IFNULL( SUM( Delta ), 0 ) INTO rv FROM NCOI.Transacties WHERE `Type` = 'forecast' AND Ean = iEAN AND Kostenplaats = iKostenplaats AND DT > Vanaf;
	RETURN rv;
    END ;;
DELIMITER ;
ALTER DATABASE `NCOI` CHARACTER SET utf8 COLLATE utf8_general_ci ;
/*!50003 DROP FUNCTION IF EXISTS `Gecrediteerd` */;
ALTER DATABASE `NCOI` CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
DELIMITER ;;
CREATE DEFINER=`sander`@`%` FUNCTION `Gecrediteerd`( iEAN CHAR( 13 ), iExact INT ) RETURNS int(11)
    READS SQL DATA
    DETERMINISTIC
BEGIN
	DECLARE rv INT;
	SELECT SUM(Aantal) INTO rv FROM NCOI.Orderregels LEFT JOIN NCOI.Pakbonnen USING ( Pakbonid ) LEFT JOIN NCOI.Voorraad ON ( Orderregels.Ean = Voorraad.EAN AND Orderregels.Debiteur = Voorraad.ExactID )
	LEFT JOIN NCOI.Batches USING ( Batchid )
	WHERE Orderregels.Ean = iEAN
	AND Pakbonnen.Status = 'gecrediteerd'
	AND Orderregels.Debiteur = iExact
	AND Batches.DTIngediend >= IFNULL( Voorraad.LaatzienVanaf, '2008-01-01' );
	RETURN IFNULL(rv,0);
    END ;;
DELIMITER ;
ALTER DATABASE `NCOI` CHARACTER SET utf8 COLLATE utf8_general_ci ;
/*!50003 DROP FUNCTION IF EXISTS `GecrediteerdEAN` */;
DELIMITER ;;
CREATE DEFINER=`thijs`@`%` FUNCTION `GecrediteerdEAN`( iEAN CHAR(13) ) RETURNS int(11)
    READS SQL DATA
BEGIN
	DECLARE rv INT;
	SELECT SUM(Aantal) INTO rv
	FROM NCOI.CreditatieLog
		LEFT JOIN NCOI.Pakbonnen USING ( PakbonID )
		LEFT JOIN NCOI.Orderregels USING ( Pakbonid )
	WHERE Orderregels.Ean = iEAN;
	RETURN IFNULL(rv,0);
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GecrediteerdKP` */;
ALTER DATABASE `NCOI` CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
DELIMITER ;;
CREATE DEFINER=`sander`@`%` FUNCTION `GecrediteerdKP`( iEAN CHAR( 13 ), iKostenplaats INT ) RETURNS int(11)
    READS SQL DATA
    DETERMINISTIC
BEGIN
/*
	DECLARE rv INT;
	SELECT SUM(Aantal) INTO rv FROM NCOI.Orderregels LEFT JOIN NCOI.Pakbonnen USING ( Pakbonid ) LEFT JOIN NCOI.Voorraad ON ( Orderregels.Ean = Voorraad.EAN AND Pakbonnen.Kostenpost = Voorraad.Kostenplaats )
	LEFT JOIN NCOI.Batches USING ( Batchid )
	WHERE Orderregels.Ean = iEAN
	AND Pakbonnen.Status = 'gecrediteerd'
	AND Pakbonnen.Kostenpost = iKostenplaats
	AND Batches.DTIngediend >= IFNULL( Voorraad.LaatzienVanaf, '2008-01-01' );
	RETURN IFNULL(rv,0);
*/
	DECLARE rv INT;
	SELECT SUM(Aantal) INTO rv FROM NCOI.CreditatieLog LEFT JOIN NCOI.Pakbonnen USING ( PakbonID ) LEFT JOIN NCOI.Orderregels USING ( Pakbonid ) LEFT JOIN NCOI.Voorraad ON ( Orderregels.Ean = Voorraad.EAN AND Pakbonnen.Kostenpost = Voorraad.Kostenplaats )
	LEFT JOIN NCOI.Batches USING ( Batchid )
	WHERE Orderregels.Ean = iEAN
	AND Pakbonnen.Kostenpost = iKostenplaats
	AND Batches.DTIngediend >= IFNULL( Voorraad.LaatzienVanaf, '2008-01-01' );
	RETURN IFNULL(rv,0);
    END ;;
DELIMITER ;
ALTER DATABASE `NCOI` CHARACTER SET utf8 COLLATE utf8_general_ci ;
/*!50003 DROP FUNCTION IF EXISTS `Gefactureerd` */;
ALTER DATABASE `NCOI` CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
DELIMITER ;;
CREATE DEFINER=`sander`@`%` FUNCTION `Gefactureerd`( iEAN CHAR( 13 ), iExact INT ) RETURNS int(11)
    READS SQL DATA
    DETERMINISTIC
BEGIN
	DECLARE rv INT;
	SELECT SUM(Aantal) INTO rv FROM NCOI.Orderregels LEFT JOIN NCOI.Pakbonnen USING ( Pakbonid ) LEFT JOIN NCOI.Voorraad ON ( Orderregels.Ean = Voorraad.EAN AND Orderregels.Debiteur = Voorraad.ExactID )
	LEFT JOIN NCOI.Batches USING ( Batchid )
	WHERE Orderregels.Ean = iEAN
	AND Pakbonnen.Status = 'gefactureerd'
	AND Orderregels.Debiteur = iExact
	AND Batches.DTIngediend >= IFNULL( Voorraad.LaatzienVanaf, '2008-01-01' );
	RETURN IFNULL(rv,0);
    END ;;
DELIMITER ;
ALTER DATABASE `NCOI` CHARACTER SET utf8 COLLATE utf8_general_ci ;
/*!50003 DROP FUNCTION IF EXISTS `GefactureerdEAN` */;
DELIMITER ;;
CREATE DEFINER=`thijs`@`%` FUNCTION `GefactureerdEAN`( iEAN CHAR(13) ) RETURNS int(11)
    READS SQL DATA
BEGIN
	DECLARE rv INT;
	SELECT SUM(Aantal) INTO rv
	FROM NCOI.Orderregels
		LEFT JOIN NCOI.Pakbonnen USING ( Pakbonid )
	WHERE Orderregels.Ean = iEAN
		AND Pakbonnen.Status IN ( 'gefactureerd', 'gecrediteerd' ) /* WAS: Gefactureerd */;
	RETURN IFNULL(rv,0);
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GefactureerdKP` */;
ALTER DATABASE `NCOI` CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
DELIMITER ;;
CREATE DEFINER=`sander`@`%` FUNCTION `GefactureerdKP`( iEAN CHAR( 13 ), iKostenplaats INT ) RETURNS int(11)
    READS SQL DATA
BEGIN
	DECLARE rv INT;
	SELECT SUM(Aantal) INTO rv FROM NCOI.Orderregels LEFT JOIN NCOI.Pakbonnen USING ( Pakbonid ) LEFT JOIN NCOI.Voorraad ON ( Orderregels.Ean = Voorraad.EAN AND Pakbonnen.Kostenpost = Voorraad.Kostenplaats )
	LEFT JOIN NCOI.Batches USING ( Batchid )
	WHERE Orderregels.Ean = iEAN
	AND Pakbonnen.Status IN ( 'gefactureerd', 'gecrediteerd' ) /* WAS: Gefactureerd */
	AND Pakbonnen.Kostenpost = iKostenplaats
	AND Batches.DTIngediend >= IFNULL( Voorraad.LaatzienVanaf, '2008-01-01' );
	RETURN IFNULL(rv,0);
    END ;;
DELIMITER ;
ALTER DATABASE `NCOI` CHARACTER SET utf8 COLLATE utf8_general_ci ;
/*!50003 DROP FUNCTION IF EXISTS `Geprint` */;
ALTER DATABASE `NCOI` CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
DELIMITER ;;
CREATE DEFINER=`sander`@`%` FUNCTION `Geprint`( iEAN CHAR( 13 ), iExact INT ) RETURNS int(11)
    READS SQL DATA
    DETERMINISTIC
BEGIN
	DECLARE rv INT;
	SELECT SUM(Aantal) INTO rv FROM NCOI.Orderregels LEFT JOIN NCOI.Pakbonnen USING ( Pakbonid ) LEFT JOIN NCOI.Voorraad ON ( Orderregels.Ean = Voorraad.EAN AND Orderregels.Debiteur = Voorraad.ExactID )
	LEFT JOIN NCOI.Batches USING ( Batchid )
	WHERE Orderregels.Ean = iEAN
	AND Pakbonnen.Status = 'geprint'
	AND Orderregels.Debiteur = iExact
	AND Batches.DTIngediend >= IFNULL( Voorraad.LaatzienVanaf, '2008-01-01' );
	RETURN IFNULL(rv,0);
    END ;;
DELIMITER ;
ALTER DATABASE `NCOI` CHARACTER SET utf8 COLLATE utf8_general_ci ;
/*!50003 DROP FUNCTION IF EXISTS `GeprintEAN` */;
DELIMITER ;;
CREATE DEFINER=`thijs`@`%` FUNCTION `GeprintEAN`( iEAN CHAR(13) ) RETURNS int(11)
    READS SQL DATA
BEGIN
	DECLARE rv INT;
	SELECT SUM(Aantal) INTO rv
	FROM NCOI.Orderregels
		LEFT JOIN NCOI.Pakbonnen USING ( Pakbonid )
	WHERE Orderregels.Ean = iEAN
		AND Pakbonnen.Status = 'geprint';
	RETURN IFNULL(rv,0);
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GeprintKP` */;
ALTER DATABASE `NCOI` CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
DELIMITER ;;
CREATE DEFINER=`sander`@`%` FUNCTION `GeprintKP`( iEAN CHAR( 13 ), iKostenplaats INT ) RETURNS int(11)
    READS SQL DATA
    DETERMINISTIC
BEGIN
	DECLARE rv INT;
	SELECT SUM(Aantal) INTO rv FROM NCOI.Orderregels LEFT JOIN NCOI.Pakbonnen USING ( Pakbonid ) LEFT JOIN NCOI.Voorraad ON ( Orderregels.Ean = Voorraad.EAN AND Pakbonnen.Kostenpost = Voorraad.Kostenplaats )
	LEFT JOIN NCOI.Batches USING ( Batchid )
	WHERE Orderregels.Ean = iEAN
	AND Pakbonnen.Status = 'geprint'
	AND Pakbonnen.Kostenpost = iKostenplaats
	AND Batches.DTIngediend >= IFNULL( Voorraad.LaatzienVanaf, '2008-01-01' );
	RETURN IFNULL(rv,0);
    END ;;
DELIMITER ;
ALTER DATABASE `NCOI` CHARACTER SET utf8 COLLATE utf8_general_ci ;
/*!50003 DROP FUNCTION IF EXISTS `Geretourneerd` */;
ALTER DATABASE `NCOI` CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
DELIMITER ;;
CREATE DEFINER=`sander`@`%` FUNCTION `Geretourneerd`( iEAN CHAR( 13 ), iExact INT ) RETURNS int(11)
    READS SQL DATA
    DETERMINISTIC
BEGIN
	DECLARE rv INT;
	SELECT SUM(Aantal) INTO rv FROM NCOI.Retour LEFT JOIN NCOI.Pakbonnen USING ( PakbonID ) LEFT JOIN NCOI.Orderregels USING ( Pakbonid ) LEFT JOIN NCOI.Voorraad ON ( Orderregels.Ean = Voorraad.EAN AND Orderregels.Debiteur = Voorraad.ExactID )
	LEFT JOIN NCOI.Batches USING ( Batchid )
	WHERE Orderregels.Ean = iEAN
	AND Orderregels.Debiteur = iExact
	AND Batches.DTIngediend >= IFNULL( Voorraad.LaatzienVanaf, '2008-01-01' );
	RETURN IFNULL(rv,0);
    END ;;
DELIMITER ;
ALTER DATABASE `NCOI` CHARACTER SET utf8 COLLATE utf8_general_ci ;
/*!50003 DROP FUNCTION IF EXISTS `GeretourneerdEAN` */;
DELIMITER ;;
CREATE DEFINER=`thijs`@`%` FUNCTION `GeretourneerdEAN`( iEAN CHAR( 13 ) ) RETURNS int(11)
    READS SQL DATA
BEGIN
	DECLARE rv INT;
	SELECT SUM(Aantal) INTO rv
	FROM NCOI.Retour
		LEFT JOIN NCOI.Pakbonnen USING ( PakbonID )
		LEFT JOIN NCOI.Orderregels USING ( Pakbonid )
	WHERE Orderregels.Ean = iEAN;
	RETURN IFNULL(rv,0);
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GeretourneerdKP` */;
ALTER DATABASE `NCOI` CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
DELIMITER ;;
CREATE DEFINER=`sander`@`%` FUNCTION `GeretourneerdKP`( iEAN CHAR( 13 ), iKostenplaats INT ) RETURNS int(11)
    READS SQL DATA
    DETERMINISTIC
BEGIN
	DECLARE rv INT;
	SELECT SUM(Aantal) INTO rv FROM NCOI.Retour LEFT JOIN NCOI.Pakbonnen USING ( PakbonID ) LEFT JOIN NCOI.Orderregels USING ( Pakbonid ) LEFT JOIN NCOI.Voorraad ON ( Orderregels.Ean = Voorraad.EAN AND Pakbonnen.Kostenpost = Voorraad.Kostenplaats )
	LEFT JOIN NCOI.Batches USING ( Batchid )
	WHERE Orderregels.Ean = iEAN
	AND Pakbonnen.Kostenpost = iKostenplaats
	AND Batches.DTIngediend >= IFNULL( Voorraad.LaatzienVanaf, '2008-01-01' );
	RETURN IFNULL(rv,0);
    END ;;
DELIMITER ;
ALTER DATABASE `NCOI` CHARACTER SET utf8 COLLATE utf8_general_ci ;
/*!50003 DROP FUNCTION IF EXISTS `Ingediend` */;
ALTER DATABASE `NCOI` CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
DELIMITER ;;
CREATE DEFINER=`sander`@`%` FUNCTION `Ingediend`( iEAN CHAR( 13 ), iExact INT ) RETURNS int(11)
    READS SQL DATA
    DETERMINISTIC
BEGIN
	DECLARE rv INT;
	SELECT SUM(Aantal) INTO rv FROM NCOI.Orderregels LEFT JOIN NCOI.Pakbonnen USING ( Pakbonid ) LEFT JOIN NCOI.Voorraad ON ( Orderregels.Ean = Voorraad.EAN AND Orderregels.Debiteur = Voorraad.ExactID )
	LEFT JOIN NCOI.Batches USING ( Batchid )
	WHERE Orderregels.Ean = iEAN
	AND Pakbonnen.Status = 'ingediend'
	AND Orderregels.Debiteur = iExact
	AND Batches.DTIngediend >= IFNULL( Voorraad.LaatzienVanaf, '2008-01-01' );
	RETURN IFNULL(rv,0);
    END ;;
DELIMITER ;
ALTER DATABASE `NCOI` CHARACTER SET utf8 COLLATE utf8_general_ci ;
/*!50003 DROP FUNCTION IF EXISTS `IngediendEAN` */;
DELIMITER ;;
CREATE DEFINER=`thijs`@`%` FUNCTION `IngediendEAN`( iEAN CHAR(13) ) RETURNS int(11)
    READS SQL DATA
BEGIN
	DECLARE rv INT;
	SELECT SUM(Aantal) INTO rv
	FROM NCOI.Orderregels
		LEFT JOIN NCOI.Pakbonnen USING ( Pakbonid )
	WHERE Orderregels.Ean = iEAN
		AND Pakbonnen.Status = 'ingediend';
	RETURN IFNULL(rv,0);
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `IngediendKP` */;
ALTER DATABASE `NCOI` CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
DELIMITER ;;
CREATE DEFINER=`sander`@`%` FUNCTION `IngediendKP`( iEAN CHAR( 13 ), iKostenplaats INT ) RETURNS int(11)
    READS SQL DATA
    DETERMINISTIC
BEGIN
	DECLARE rv INT;
	SELECT SUM(Aantal) INTO rv FROM NCOI.Orderregels LEFT JOIN NCOI.Pakbonnen USING ( Pakbonid ) LEFT JOIN NCOI.Voorraad ON ( Orderregels.Ean = Voorraad.EAN AND Pakbonnen.Kostenpost = Voorraad.Kostenplaats )
	LEFT JOIN NCOI.Batches USING ( Batchid )
	WHERE Orderregels.Ean = iEAN
	AND Pakbonnen.Status = 'ingediend'
	AND Pakbonnen.Kostenpost = iKostenplaats
	AND Batches.DTIngediend >= IFNULL( Voorraad.LaatzienVanaf, '2008-01-01' );
	RETURN IFNULL(rv,0);
    END ;;
DELIMITER ;
ALTER DATABASE `NCOI` CHARACTER SET utf8 COLLATE utf8_general_ci ;
/*!50003 DROP FUNCTION IF EXISTS `Ouranos` */;
ALTER DATABASE `NCOI` CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
DELIMITER ;;
CREATE DEFINER=`main`@`%` FUNCTION `Ouranos`( xEAN CHAR(13), xEx INT ) RETURNS int(11)
    READS SQL DATA
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE rv INT DEFAULT 0;
  DECLARE vorig INT DEFAULT 0;
  DECLARE oud,nieuw INT;

  DECLARE cur CURSOR FOR SELECT ForecastOud, ForecastNieuw FROM VoorraadMutaties WHERE ForecastOud != ForecastNieuw AND EAN = xEAN AND ExactID = xEx ORDER BY DT ASC;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur;

  REPEAT
    FETCH cur INTO Oud, Nieuw;
    IF NOT done THEN
       IF Oud != vorig THEN
          SET rv = 1; SET done = 1;
       ELSE
          SET vorig = Nieuw;
       END IF;
    END IF;
  UNTIL done END REPEAT;

  CLOSE cur;

  RETURN rv;
END ;;
DELIMITER ;
ALTER DATABASE `NCOI` CHARACTER SET utf8 COLLATE utf8_general_ci ;
/*!50003 DROP FUNCTION IF EXISTS `TotaalOrderregels` */;
ALTER DATABASE `NCOI` CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
DELIMITER ;;
CREATE DEFINER=`sander`@`%` FUNCTION `TotaalOrderregels`( iEAN CHAR( 13 ), iExact INT ) RETURNS int(11)
    READS SQL DATA
    DETERMINISTIC
BEGIN
	DECLARE rv INT;
	SELECT SUM(Aantal) INTO rv FROM NCOI.Orderregels LEFT JOIN NCOI.Pakbonnen USING ( Pakbonid ) LEFT JOIN NCOI.Voorraad ON ( Orderregels.Ean = Voorraad.EAN AND Orderregels.Debiteur = Voorraad.ExactID )
	LEFT JOIN NCOI.Batches USING ( Batchid )
	WHERE Orderregels.Ean = iEAN
	AND Orderregels.Debiteur = iExact
	AND Batches.DTIngediend >= IFNULL( Voorraad.LaatzienVanaf, '2008-01-01' );
	RETURN IFNULL(rv,0);
    END ;;
DELIMITER ;
ALTER DATABASE `NCOI` CHARACTER SET utf8 COLLATE utf8_general_ci ;
/*!50003 DROP FUNCTION IF EXISTS `TotaalOrderregelsKP` */;
ALTER DATABASE `NCOI` CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
DELIMITER ;;
CREATE DEFINER=`sander`@`%` FUNCTION `TotaalOrderregelsKP`( iEAN CHAR( 13 ), iKostenplaats INT ) RETURNS int(11)
    READS SQL DATA
    DETERMINISTIC
BEGIN
	DECLARE rv INT;
	SELECT SUM(Aantal) INTO rv FROM NCOI.Orderregels LEFT JOIN NCOI.Pakbonnen USING ( Pakbonid ) LEFT JOIN NCOI.Voorraad ON ( Orderregels.Ean = Voorraad.EAN AND Pakbonnen.Kostenpost = Voorraad.Kostenplaats )
	LEFT JOIN NCOI.Batches USING ( Batchid )
	WHERE Orderregels.Ean = iEAN
	AND Pakbonnen.Kostenpost = iKostenplaats
	AND Batches.DTIngediend >= IFNULL( Voorraad.LaatzienVanaf, '2008-01-01' );
	RETURN IFNULL(rv,0);
    END ;;
DELIMITER ;
ALTER DATABASE `NCOI` CHARACTER SET utf8 COLLATE utf8_general_ci ;
/*!50003 DROP FUNCTION IF EXISTS `Verzonden` */;
ALTER DATABASE `NCOI` CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
DELIMITER ;;
CREATE DEFINER=`sander`@`%` FUNCTION `Verzonden`( iEAN CHAR( 13 ), iExact INT ) RETURNS int(11)
    READS SQL DATA
    DETERMINISTIC
BEGIN
	DECLARE rv INT;
	SELECT SUM(Aantal) INTO rv FROM NCOI.Orderregels LEFT JOIN NCOI.Pakbonnen USING ( Pakbonid ) LEFT JOIN NCOI.Voorraad ON ( Orderregels.Ean = Voorraad.EAN AND Orderregels.Debiteur = Voorraad.ExactID )
	LEFT JOIN NCOI.Batches USING ( Batchid )
	WHERE Orderregels.Ean = iEAN
	AND Orderregels.Debiteur = iExact
	AND Pakbonnen.Status = 'verzonden'
	AND Batches.DTIngediend >= IFNULL( Voorraad.LaatzienVanaf, '2008-01-01' );
	RETURN IFNULL(rv,0);
    END ;;
DELIMITER ;
ALTER DATABASE `NCOI` CHARACTER SET utf8 COLLATE utf8_general_ci ;
/*!50003 DROP FUNCTION IF EXISTS `VerzondenEAN` */;
DELIMITER ;;
CREATE DEFINER=`thijs`@`%` FUNCTION `VerzondenEAN`( iEAN CHAR(13) ) RETURNS int(11)
    READS SQL DATA
BEGIN
	DECLARE rv INT;
	SELECT SUM(Aantal) INTO rv
	FROM NCOI.Orderregels
		LEFT JOIN NCOI.Pakbonnen USING ( Pakbonid )
	WHERE Orderregels.Ean = iEAN
		AND Pakbonnen.Status = 'verzonden';
	RETURN IFNULL(rv,0);
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `VerzondenKP` */;
ALTER DATABASE `NCOI` CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
DELIMITER ;;
CREATE DEFINER=`sander`@`%` FUNCTION `VerzondenKP`( iEAN CHAR( 13 ), iKostenplaats INT ) RETURNS int(11)
    READS SQL DATA
    DETERMINISTIC
BEGIN
	DECLARE rv INT;
	SELECT SUM(Aantal) INTO rv FROM NCOI.Orderregels LEFT JOIN NCOI.Pakbonnen USING ( Pakbonid ) LEFT JOIN NCOI.Voorraad ON ( Orderregels.Ean = Voorraad.EAN AND Pakbonnen.Kostenpost = Voorraad.Kostenplaats )
	LEFT JOIN NCOI.Batches USING ( Batchid )
	WHERE Orderregels.Ean = iEAN
	AND Pakbonnen.Kostenpost = iKostenplaats
	AND Pakbonnen.Status = 'verzonden'
	AND Batches.DTIngediend >= IFNULL( Voorraad.LaatzienVanaf, '2008-01-01' );
	RETURN IFNULL(rv,0);
    END ;;
DELIMITER ;
ALTER DATABASE `NCOI` CHARACTER SET utf8 COLLATE utf8_general_ci ;
/*!50003 DROP PROCEDURE IF EXISTS `dup` */;
ALTER DATABASE `NCOI` CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
DELIMITER ;;
CREATE DEFINER=`main`@`%` PROCEDURE `dup`( IN xEAN CHAR(13), IN Exact_from INT, IN Exact_to INT )
BEGIN
  DECLARE done, x INT DEFAULT 0;
  DECLARE fcoud,voorraadoud,fcnieuw,voorraadnieuw INT DEFAULT 0;
  
  DECLARE xDT TIMESTAMP;
  DECLARE voud, vnieuw, foud, fnieuw INT;
  DECLARE xreden, xgebruiker CHAR(30);
  
  DECLARE cur1 CURSOR FOR SELECT DT, AantalOud, AantalNieuw, ForecastOud, ForecastNieuw, Reden, Gebruiker FROM NCOI.VoorraadMutaties WHERE ExactID IN ( Exact_from, Exact_to ) AND EAN = xEAN ORDER BY DT;
  
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  
  OPEN cur1;
  
  REPEAT
    FETCH cur1 INTO xDT, voud, vnieuw, foud, fnieuw, xreden, xgebruiker;
    
    IF NOT done THEN
      SET fcoud = fcnieuw;
      SET fcnieuw = fcoud + fnieuw - foud;
      
      SET voorraadoud = voorraadnieuw;
      SET voorraadnieuw = voorraadoud + vnieuw - voud;
      
      INSERT INTO NCOI.tmpMutaties VALUES ( xEAN, Exact_to, 1, xDT, voorraadoud, voorraadnieuw, fcoud, fcnieuw, xreden, xgebruiker );
      
      SET x = x + 1;
      
    END IF;
  UNTIL done END REPEAT;
  
  CLOSE cur1;
END ;;
DELIMITER ;
ALTER DATABASE `NCOI` CHARACTER SET utf8 COLLATE utf8_general_ci ;
/*!50003 DROP PROCEDURE IF EXISTS `move` */;
ALTER DATABASE `NCOI` CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
DELIMITER ;;
CREATE DEFINER=`main`@`%` PROCEDURE `move`( IN Exact_from INT, IN Exact_to INT )
BEGIN
  DECLARE done,x INT DEFAULT 0;
  DECLARE a CHAR(13);
  DECLARE cur1 CURSOR FOR SELECT EAN FROM NCOI.Voorraad WHERE ExactID = Exact_to;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  DROP TABLE IF EXISTS tmpMutaties;
  CREATE TABLE tmpMutaties LIKE VoorraadMutaties;

  OPEN cur1;

  REPEAT
    FETCH cur1 INTO a;
    IF NOT done THEN
       CALL `NCOI`.`dup`( a, Exact_from, Exact_to );
       SET x = x+ 1;
    END IF;
  UNTIL done END REPEAT;

	SELECT * FROM (
		SELECT * FROM (
			SELECT 
				EAN, 
				Voorraad.LaatZienVanaf, 
				AantalNieuw, 
				Voorraad.Voorraad, 
				ForecastNieuw, 
				`Forecast` - IFNULL( ( SELECT ForecastNieuw FROM NCOI.tmpMutaties as VM WHERE VM.EAN = Voorraad.EAN AND VM.ExactID = Voorraad.ExactID AND Voorraad.PeriodeID = Voorraad.PeriodeID AND DT <= Voorraad.LaatzienVanaf ORDER BY DT DESC LIMIT 1 ),0) AS `Forecast`
			FROM tmpMutaties 
			JOIN NCOI.Voorraad USING ( EAN, ExactID, PeriodeID )
			ORDER BY DT DESC
		) x 
		GROUP BY x.EAN
	) y
	WHERE y.AantalNieuw != y.Voorraad OR y.ForecastNieuw != y.Forecast;

  CLOSE cur1;
END ;;
DELIMITER ;
ALTER DATABASE `NCOI` CHARACTER SET utf8 COLLATE utf8_general_ci ;

--
-- Current Database: `NCOIB`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `NCOIB` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `NCOIB`;

--
-- Table structure for table `Batches`
--

CREATE TABLE `Batches` (
  `Batchid` int(11) NOT NULL,
  `DTIngediend` datetime DEFAULT NULL,
  `DTGeprint` datetime DEFAULT NULL,
  `DTVerzonden` datetime DEFAULT NULL,
  `DTGefactureerd` datetime DEFAULT NULL,
  `Status` enum('ingediend','geprint','verzonden','gefactureerd','ingetrokken') NOT NULL,
  `Spoed` char(10) DEFAULT NULL,
  `Kostenpost` int(11) DEFAULT NULL,
  PRIMARY KEY (`Batchid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `Bedrijven`
--

CREATE TABLE `Bedrijven` (
  `BedrijfID` varchar(10) COLLATE latin1_general_ci NOT NULL,
  `Naam` varchar(256) COLLATE latin1_general_ci NOT NULL,
  `Wobsite` varchar(256) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`BedrijfID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `BoekLocaties`
--

CREATE TABLE `BoekLocaties` (
  `EAN` char(13) NOT NULL,
  `Rij` int(11) DEFAULT NULL,
  `Kast` int(11) DEFAULT NULL,
  `Plank` int(11) DEFAULT NULL,
  PRIMARY KEY (`EAN`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `CreditatieLog`
--

CREATE TABLE `CreditatieLog` (
  `DT` datetime DEFAULT NULL,
  `Pakbonid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `FacturatieLog`
--

CREATE TABLE `FacturatieLog` (
  `DT` datetime DEFAULT NULL,
  `Pakbonid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Temporary table structure for view `ForecastTransacties`
--

/*!50001 DROP VIEW IF EXISTS `ForecastTransacties`*/;
/*!50001 CREATE TABLE `ForecastTransacties` (
  `EAN` tinyint NOT NULL,
  `ExactID` tinyint NOT NULL,
  `DT` tinyint NOT NULL,
  `Mutatie` tinyint NOT NULL,
  `Reden` tinyint NOT NULL,
  `Gebruiker` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `Forecasts`
--

/*!50001 DROP VIEW IF EXISTS `Forecasts`*/;
/*!50001 CREATE TABLE `Forecasts` (
  `EAN` tinyint NOT NULL,
  `Forecast` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Table structure for table `KortingUitgever`
--

CREATE TABLE `KortingUitgever` (
  `UitgeverID` int(11) NOT NULL,
  `Percentage` double DEFAULT NULL,
  PRIMARY KEY (`UitgeverID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `OUDVoorraad`
--

CREATE TABLE `OUDVoorraad` (
  `EAN` char(13) NOT NULL,
  `ExactID` int(11) NOT NULL,
  `PeriodeID` int(11) NOT NULL,
  `Voorraad` int(11) NOT NULL,
  `Forecast` int(11) NOT NULL,
  `Afgesloten` enum('Neen','Ja') NOT NULL DEFAULT 'Neen',
  `LaatzienVanaf` timestamp NOT NULL DEFAULT '2008-07-31 22:00:00',
  PRIMARY KEY (`EAN`,`ExactID`,`PeriodeID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `Orderregels`
--

CREATE TABLE `Orderregels` (
  `Pakbonid` int(11) NOT NULL,
  `Ean` char(13) NOT NULL,
  `Aantal` int(11) NOT NULL,
  `Debiteur` int(11) NOT NULL,
  `PeriodeID` int(11) NOT NULL,
  PRIMARY KEY (`Pakbonid`,`Ean`),
  KEY `FK_Orderregels_Voorraad` (`Ean`,`Debiteur`,`PeriodeID`),
  CONSTRAINT `FK_Orderregels_Pakbonnen` FOREIGN KEY (`Pakbonid`) REFERENCES `Pakbonnen` (`Pakbonid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
