-- phpMyAdmin SQL Dump
-- version 3.1.3.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Erstellungszeit: 16. Oktober 2010 um 22:13
-- Server Version: 5.1.33
-- PHP-Version: 5.2.9

SET FOREIGN_KEY_CHECKS=0;

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

SET AUTOCOMMIT=0;
START TRANSACTION;


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Datenbank: `elela`
--

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

--
-- Tabellenstruktur für Tabelle `adr`
--

CREATE TABLE IF NOT EXISTS `adr` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Foto_ID` int(11) DEFAULT NULL,
  `Bezeichnung` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `Firma` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Strasse` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `PLZ` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Ort` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Land` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Telefon` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Mobil` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Fax` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `EMail` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Internet` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
  `KundNr` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Login` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Passwort` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `UsSTID` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `GebDatum` date DEFAULT NULL,
  `Bank` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `BLZ` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Konto` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Bemerkung` text COLLATE utf8_unicode_ci,
  `AendDatum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `Bezeichnung` (`Bezeichnung`),
  KEY `foto_ID` (`foto_ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

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

--
-- Tabellenstruktur für Tabelle `bauteil`
--

CREATE TABLE IF NOT EXISTS `bauteil` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `tree_typ_ID` int(11) NOT NULL,
  `Foto_ID` int(11) DEFAULT NULL,
  `Bezeichnung` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `Beschreibung` text COLLATE utf8_unicode_ci,
  `Handbuch` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Library` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Kennz` int(11) DEFAULT NULL,
  `AendDatum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `Bezeichnung` (`Bezeichnung`),
  KEY `tree_typ_ID` (`tree_typ_ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

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

--
-- Tabellenstruktur für Tabelle `bauteiladr`
--

CREATE TABLE IF NOT EXISTS `bauteiladr` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Bauteil_ID` int(11) NOT NULL,
  `Adr_ID` int(11) NOT NULL,
  `BauteilLager_ID` int(11) DEFAULT NULL,
  `BestellNr` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `EPreis` double DEFAULT NULL,
  `EPMenge` int(11) DEFAULT NULL,
  `VPEMenge` int(11) DEFAULT NULL,
  `Bemerkung` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `AendDatum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `Bauteil_ID` (`Bauteil_ID`),
  KEY `Adr_ID` (`Adr_ID`),
  KEY `BauteilLager_ID` (`BauteilLager_ID`),
  KEY `BAUTEILADR_BESTELLNR_IX` (`BestellNr`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

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

--
-- Tabellenstruktur für Tabelle `bauteillager`
--

CREATE TABLE IF NOT EXISTS `bauteillager` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Bauteil_ID` int(11) NOT NULL,
  `Case_ID` int(11) DEFAULT NULL,
  `Kiste_ID` int(11) DEFAULT NULL,
  `Adr_ID` int(11) DEFAULT NULL,
  `Bezeichnung` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `SMD` int(11) NOT NULL DEFAULT '0',
  `SMD_Text` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Menge` int(11) DEFAULT NULL,
  `MengeMin` int(11) DEFAULT NULL,
  `EPreis` double DEFAULT NULL,
  `VPreis` double DEFAULT NULL,
  `Haltbar` date DEFAULT NULL,
  `Lagernr` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `BWare` int(11) DEFAULT NULL,
  `RoHS` int(11) DEFAULT NULL,
  `Bemerkung` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Handbuch` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Library` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Kennz` int(11) DEFAULT NULL,
  `Gewicht` double DEFAULT NULL,
  `Barcode` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `AendDatum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `Kiste_ID` (`Kiste_ID`),
  KEY `Case_ID` (`Case_ID`),
  KEY `Bauteil_ID` (`Bauteil_ID`),
  KEY `BAUTEILLAGER_SMD_Text_IX` (`SMD_Text`),
  KEY `BAUTEILLAGER_LAGERNR_IX` (`Lagernr`),
  KEY `BAUTEILLAGER_BARCODE_IX` (`Barcode`),
  KEY `BAUTEILLAGER_BEZ_IX` (`Bezeichnung`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

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

--
-- Tabellenstruktur für Tabelle `bestellen`
--

CREATE TABLE IF NOT EXISTS `bestellen` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Bauteil_ID` int(11) DEFAULT NULL,
  `BauteilLager_ID` int(11) DEFAULT NULL,
  `BauteilAdr_ID` int(11) DEFAULT NULL,
  `Projekt_ID` int(11) DEFAULT NULL,
  `Bezeichnung` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Menge` int(11) DEFAULT NULL,
  `Status` int(11) DEFAULT NULL,
  `SDatum` date DEFAULT NULL,
  `Archiv` int(11) DEFAULT NULL,
  `Bemerkung` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Lief1_Adr_ID` int(11) DEFAULT NULL,
  `Lief1_BestellNr` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Lief1_EPreis` double DEFAULT NULL,
  `Lief1_GPreis` double DEFAULT NULL,
  `Lief2_Adr_ID` int(11) DEFAULT NULL,
  `Lief2_BestellNr` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Lief2_EPreis` double DEFAULT NULL,
  `Lief2_GPreis` double DEFAULT NULL,
  `AendDatum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

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

--
-- Tabellenstruktur für Tabelle `case`
--

CREATE TABLE IF NOT EXISTS `case` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Foto_ID` int(11) DEFAULT NULL,
  `Bezeichnung` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `Typ` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Pins` int(11) DEFAULT NULL,
  `Raster` float DEFAULT NULL,
  `Text` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `SMD` int(11) NOT NULL,
  `AendDatum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

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

--
-- Tabellenstruktur für Tabelle `foto`
--

CREATE TABLE IF NOT EXISTS `foto` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ID_ID` int(11) DEFAULT NULL,
  `Bezeichnung` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Tabelle` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `BildTyp` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `Bild` blob,
  `AendDatum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

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

--
-- Tabellenstruktur für Tabelle `historie`
--

CREATE TABLE IF NOT EXISTS `historie` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Projekt_ID` int(11) DEFAULT NULL,
  `Foto_ID` int(11) DEFAULT NULL,
  `Adr_ID` int(11) DEFAULT NULL,
  `Datum` date DEFAULT NULL,
  `Projekt` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Bezeichnung` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Beschreibung` text COLLATE utf8_unicode_ci,
  `Version` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Status` int(11) DEFAULT NULL,
  `FStatus` int(11) DEFAULT NULL,
  `SNr` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `GNr` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `FDatum` date DEFAULT NULL,
  `FVersion` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Kennz` int(11) DEFAULT NULL,
  `Aufwand` double DEFAULT NULL,
  `Handbuch` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
  `AendDatum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

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

--
-- Tabellenstruktur für Tabelle `kiste`
--

CREATE TABLE IF NOT EXISTS `kiste` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Foto_ID` int(11) DEFAULT NULL,
  `Bezeichnung` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `Nummer` int(11) DEFAULT NULL,
  `PosX` int(11) DEFAULT NULL,
  `PosY` int(11) DEFAULT NULL,
  `Lagerort` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Groesse` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Text` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ParEtikett` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
  `AendDatum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

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

--
-- Tabellenstruktur für Tabelle `param`
--

CREATE TABLE IF NOT EXISTS `param` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ParNr` int(11) DEFAULT NULL,
  `ParName` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Land` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ValInt` int(11) DEFAULT NULL,
  `ValFloat` double DEFAULT NULL,
  `ValText` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
  `AendDatum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `ParNr` (`ParNr`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

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

--
-- Tabellenstruktur für Tabelle `projekt`
--

CREATE TABLE IF NOT EXISTS `projekt` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ID_ID` int(11) DEFAULT NULL,
  `Foto_ID` int(11) DEFAULT NULL,
  `Bauteil_ID` int(11) DEFAULT NULL,
  `BauteilLager_ID` int(11) DEFAULT NULL,
  `Adr_ID` int(11) DEFAULT NULL,
  `Bezeichnung` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `Beschreibung` text COLLATE utf8_unicode_ci,
  `Kennz` int(11) DEFAULT NULL,
  `MengeFertig` int(11) DEFAULT NULL,
  `MengeOffen` int(11) DEFAULT NULL,
  `Termin` date DEFAULT NULL,
  `Handbuch` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
  `OptA` int(11) DEFAULT NULL,
  `OptB` int(11) DEFAULT NULL,
  `OptC` int(11) DEFAULT NULL,
  `AendDatum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `projekt_Bezeichnung_IX` (`Bezeichnung`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

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

--
-- Tabellenstruktur für Tabelle `projektpos`
--

CREATE TABLE IF NOT EXISTS `projektpos` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Projekt_ID` int(11) NOT NULL,
  `Bauteil_ID` int(11) DEFAULT NULL,
  `BauteilLager_ID` int(11) DEFAULT NULL,
  `Bezeichnung` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Bemerkung` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Symbol` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Wert` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Case` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Menge` int(11) DEFAULT NULL,
  `EPreis` double DEFAULT NULL,
  `GPreis` double DEFAULT NULL,
  `Kennz` int(11) DEFAULT NULL,
  `Variante` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `PosX` int(11) DEFAULT NULL,
  `PosY` int(11) DEFAULT NULL,
  `Top` int(11) DEFAULT NULL,
  `SMD` int(11) DEFAULT NULL,
  `Status` int(11) DEFAULT NULL,
  `AendDatum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `Projekt_ID` (`Projekt_ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

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

--
-- Tabellenstruktur für Tabelle `translate`
--

CREATE TABLE IF NOT EXISTS `translate` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `BezName` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `DE` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
  `DE_Waren` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
  `EN` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ES` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
  `FR` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
  `IT` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
  `NL` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
  `PL` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
  `RU` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

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

--
-- Tabellenstruktur für Tabelle `tree_typ`
--

CREATE TABLE IF NOT EXISTS `tree_typ` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ID_ID` int(11) NOT NULL,
  `Foto_ID` int(11) DEFAULT NULL,
  `Bezeichnung` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `BarFormat` varchar(40) COLLATE utf8_unicode_ci NULL,
  `Text` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `AendDatum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `ID_ID` (`ID_ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

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

--
-- Tabellenstruktur für Tabelle `wissen`
--

CREATE TABLE IF NOT EXISTS `wissen` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Foto_ID` int(11) DEFAULT NULL,
  `Bezeichnung` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `Beschreibung` text COLLATE utf8_unicode_ci,
  `Thema` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Kennz` int(11) DEFAULT NULL,
  `Handbuch` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
  `AendDatum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

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

--
-- Version Datenbank hinzufügen
--
DELETE FROM param WHERE ParNr=1;
INSERT INTO `param` (`ID`, `ParNr`, `ParName`, `Land`, `ValInt`, `ValFloat`, `ValText`) VALUES
(1, 1, 'DB-Version', NULL, NULL, NULL, 'V1.2.11406');

SET FOREIGN_KEY_CHECKS=1;

COMMIT;
