-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version   5.1.41-community


/*!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 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;


--
-- Create schema szamitogeppizza
--

CREATE DATABASE IF NOT EXISTS szamitogeppizza;
USE szamitogeppizza;

--
-- Definition of table `alkalmazottak`
--

DROP TABLE IF EXISTS `alkalmazottak`;
CREATE TABLE `alkalmazottak` (
  `szemszam` INT(11) NOT NULL AUTO_INCREMENT,
  `nev` VARCHAR(40) DEFAULT NULL,
  `cim` VARCHAR(50) DEFAULT NULL,
  `tel` INT(11) DEFAULT NULL,
  `beosztas` VARCHAR(40) DEFAULT NULL,
  `felhasznalonev` VARCHAR(40) DEFAULT NULL,
  `jelszo` VARCHAR(40) DEFAULT NULL,
  `szgeptarid` INT(11) DEFAULT NULL,
  PRIMARY KEY (`szemszam`),
  KEY `szgeptarid` (`szgeptarid`),
  CONSTRAINT `alkalmazottak_ibfk_1` FOREIGN KEY (`szgeptarid`) REFERENCES `szamitogeptarak` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=140 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `alkalmazottak`
--

/*!40000 ALTER TABLE `alkalmazottak` DISABLE KEYS */;
INSERT INTO `alkalmazottak` (`szemszam`,`nev`,`cim`,`tel`,`beosztas`,`felhasznalonev`,`jelszo`,`szgeptarid`) VALUES
 (127,'asd','asd',123,'asd','tibi','tibi',1),
 (128,'Kovacs Julia','25731124-676b-11df-90c2-d175d4c769b8',0741245789,'gyogyszeresz','kjulia10','tunde',1),
 (129,'Fekete Reka','25731124-676b-11df-90c2-d175d4c769b8',0724589631,'gyogyszeresz','freka16','tunde',1),
 (130,'Kobolkuti Zoltan','25733761-676b-11df-90c2-d175d4c769b8',0745124879,'gyogyszeresz','zozi_1989','barcelona',1),
 (132,'Fulop Zsolt','25737b98-676b-11df-90c2-d175d4c769b8',0724569832,'beszerzo','fzsolt21','tunde',1),
 (133,'Bartha Izabella','2573a002-676b-11df-90c2-d175d4c769b8',0726548931,'konyvelo','biza93','tunde',1),
 (134,'Janosi Ibolya','2573c96c-676b-11df-90c2-d175d4c769b8',07541269853,'konyvel','jibi61','tunde',1),
 (135,'Lazar Annamaria','2573f33b-676b-11df-90c2-d175d4c769b8',07421548963,'beszerzo','lanna95','tunde',1),
 (136,'Laszlo Jozsef','25741aa8-676b-11df-90c2-d175d4c769b8',0726584713,'laborans','ljozsef43','tunde',1),
 (137,'Jozsef Laszlo','25744c28-676b-11df-90c2-d175d4c769b8',0723654789,'laborans','jlaszlo34','tunde',1),
 (138,'Pepela Timea','257479b5-676b-11df-90c2-d175d4c769b8',0763254187,'takarito','ptimea77','tunde',1),
 (139,'Szakacs Attila','2574a0f5-676b-11df-90c2-d175d4c769b8',0752164309,'igazgato','bea','bea',1);
/*!40000 ALTER TABLE `alkalmazottak` ENABLE KEYS */;


--
-- Definition of table `bevetelezesek`
--

DROP TABLE IF EXISTS `bevetelezesek`;
CREATE TABLE `bevetelezesek` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `forgalmazoid` INT(11) DEFAULT NULL,
  `szgeptarid` INT(11) DEFAULT NULL,
  `datum` DATE DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `forgalmazoid` (`forgalmazoid`,`szgeptarid`,`datum`),
  KEY `szgeptarid` (`szgeptarid`),
  CONSTRAINT `bevetelezesek_ibfk_1` FOREIGN KEY (`forgalmazoid`) REFERENCES `forgalmazok` (`id`),
  CONSTRAINT `bevetelezesek_ibfk_2` FOREIGN KEY (`szgeptarid`) REFERENCES `szamitogeptarak` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `bevetelezesek`
--

/*!40000 ALTER TABLE `bevetelezesek` DISABLE KEYS */;
INSERT INTO `bevetelezesek` (`id`,`forgalmazoid`,`szgeptarid`,`datum`) VALUES
(50,1111,11114,'2010-12-06'),
(51,1112,11111,'2010-12-07'),
(52,1113,11115,'2010-09-05'),
(53,1114,11113,'2010-05-09'),
(54,1115,11116,'2010-06-09'),
(55,1114,11112,'2010-11-06');
/*!40000 ALTER TABLE `bevetelezesek` ENABLE KEYS */;


--
-- Definition of table `bevetelezestetel`
--

DROP TABLE IF EXISTS `bevetelezestetel`;
CREATE TABLE `bevetelezestetel` (
  `bevid` INT(11) NOT NULL DEFAULT '0',
  `szgepid` INT(11) NOT NULL DEFAULT '0',
  `mennyiseg` DOUBLE DEFAULT NULL,
  `ar` DOUBLE DEFAULT NULL,
  PRIMARY KEY (`bevid`,`szgepid`),
  KEY `szgepid` (`szgepid`),
  CONSTRAINT `bevetelezestetel_ibfk_1` FOREIGN KEY (`bevid`) REFERENCES `bevetelezesek` (`id`),
  CONSTRAINT `bevetelezestetel_ibfk_2` FOREIGN KEY (`szgepid`) REFERENCES `szamitogepek` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `bevetelezestetel`
--

/*!40000 ALTER TABLE `bevetelezestetel` DISABLE KEYS */;
INSERT INTO `bevetelezestetel` (`bevid`,`szgepid`,`mennyiseg`,`ar`) VALUES
(50,1,13,10),
(51,3,25,100),
(52,6,9,123),
(53,7,13,156.3),
(54,3,10,10.5),
(55,2,15,8.9);
/*!40000 ALTER TABLE `bevetelezestetel` ENABLE KEYS */;


--
-- Definition of table `forgalmazok`
--

DROP TABLE IF EXISTS `forgalmazok`;
CREATE TABLE `forgalmazok` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `nev` VARCHAR(40) DEFAULT NULL,
  `cim` VARCHAR(50) DEFAULT NULL,
  `tel` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `forgalmazok`
--

/*!40000 ALTER TABLE `forgalmazok` DISABLE KEYS */;
INSERT INTO `forgalmazok` (`id`,`nev`,`cim`,`tel`) VALUES
(1111,'doramed','regen',1521463),
(1112,'euromed','marosvasarhely',5684716),
(1113,'pharma','budapest',1521463),
(1114,'afamia','valami',1521463),
(1115,'helcor','valami',1521463),
(1116,'alcon','valami',1521463),
(1117,'altana','valami',1521463);
/*!40000 ALTER TABLE `forgalmazok` ENABLE KEYS */;


--
-- Definition of table `szamitogepek`
--

DROP TABLE IF EXISTS `szamitogepek`;
CREATE TABLE `szamitogepek` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `ar` DOUBLE DEFAULT NULL,
  `nev` VARCHAR(40) DEFAULT NULL,
  `gyarto` VARCHAR(40) DEFAULT NULL,
  `forgalmazoid` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `forgalmazoid` (`forgalmazoid`),
  CONSTRAINT `szamitogepek_ibfk_1` FOREIGN KEY (`forgalmazoid`) REFERENCES `forgalmazok` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `szamitogepek`
--

/*!40000 ALTER TABLE `szamitogepek` DISABLE KEYS */;
INSERT INTO `szamitogepek` (`id`,`ar`,`nev`,`gyarto`,`forgalmazoid`) VALUES
(1,10.3,'paracetamol','richter',1111),
(2,15.3,'algocalmin','biogal',1112),
(3,10.3,'furazolidon','richter',1114),
(4,10.3,'calciujex','eghis',1117),
(5,10.3,'pafenon','richter',1113),
(6,10.3,'pabal','wyeth',1115),
(7,10.3,'sabril','richter',1116),
(8,10.3,'loperamid','bigal',1112),
(9,10.3,'indometacin','eghis',1111);
/*!40000 ALTER TABLE `szamitogepek` ENABLE KEYS */;


--
-- Definition of table `szamitogepkategoriak`
--

DROP TABLE IF EXISTS `szamitogepkategoriak`;
CREATE TABLE `szamitogepkategoriak` (
  `szamitogepid` INT(11) NOT NULL DEFAULT '0',
  `kategoriaid` INT(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`szamitogepid`,`kategoriaid`),
  KEY `kategoriaid` (`kategoriaid`),
  CONSTRAINT `szamitogepkategoriak_ibfk_1` FOREIGN KEY (`szamitogepid`) REFERENCES `szamitogepek` (`id`),
  CONSTRAINT `szamitogepkategoriak_ibfk_2` FOREIGN KEY (`kategoriaid`) REFERENCES `kategoriak` (`katid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `szamitogepkategoriak`
--

/*!40000 ALTER TABLE `szamitogepkategoriak` DISABLE KEYS */;
INSERT INTO `szamitogepkategoriak` (`szamitogepid`,`kategoriaid`) VALUES
 (1,13),
 (2,15),
 (3,10),
 (4,12),
 (5,16),
 (6,11),
 (7,13),
 (8,14);
/*!40000 ALTER TABLE `szamitogepkategoriak` ENABLE KEYS */;


--
-- Definition of table `szamitogepraktar`
--

DROP TABLE IF EXISTS `szamitogepraktar`;
CREATE TABLE `szamitogepraktar` (
  `szgeptarid` INT(11) NOT NULL DEFAULT '0',
  `szgepid` INT(11) NOT NULL DEFAULT '0',
  `mennyiseg` DOUBLE DEFAULT NULL,
  PRIMARY KEY (`szgeptarid`,`szgepid`),
  KEY `szgepid` (`szgepid`),
  CONSTRAINT `szamitogepraktar_ibfk_1` FOREIGN KEY (`szgeptarid`) REFERENCES `szamitogeptarak` (`id`),
  CONSTRAINT `szamitogepraktar_ibfk_2` FOREIGN KEY (`szgepid`) REFERENCES `szamitogepek` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `szamitogepraktar`
--

/*!40000 ALTER TABLE `szamitogepraktar` DISABLE KEYS */;
/*!40000 ALTER TABLE `szamitogepraktar` ENABLE KEYS */;


--
-- Definition of table `szamitogeptarak`
--

DROP TABLE IF EXISTS `szamitogeptarak`;
CREATE TABLE `szamitogeptarak` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `cim` VARCHAR(50) DEFAULT NULL,
  `tel` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `szamitogeptarak`
--

/*!40000 ALTER TABLE `szamitogeptarak` DISABLE KEYS */;
INSERT INTO `szamitogeptarak` (`id`,`cim`,`tel`) VALUES
(11111,'marosvasarhely',123),
(11112,'marosvasarhely',123),
(11113,'brasso',123),
(11114,'regen',123),
(11115,'marosszentgyorgy',123),
(11116,'ernye',123);
/*!40000 ALTER TABLE `szamitogeptarak` ENABLE KEYS */;


--
-- Definition of table `kategoriak`
--

DROP TABLE IF EXISTS `kategoriak`;
CREATE TABLE `kategoriak` (
  `katid` INT(11) NOT NULL,
  `nev` VARCHAR(40) DEFAULT NULL,
  `leiras` VARCHAR(500) DEFAULT NULL,
  PRIMARY KEY (`katid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `kategoriak`
--

/*!40000 ALTER TABLE `kategoriak` DISABLE KEYS */;
INSERT INTO `kategoriak` (`katid`,`nev`,`leiras`) VALUES
(10,'fejfajas',''),
(11,'influenza',''),
(12,'torokfajas',''),
(13,'izuletfajas',''),
(14,'ful-orr-gege',''),
(15,'szemproblema',''),
(16,'hajhullas-korpasodas',''),
(17,'borproblemak','');
/*!40000 ALTER TABLE `kategoriak` ENABLE KEYS */;


--
-- Definition of table `kiadasok`
--

DROP TABLE IF EXISTS `kiadasok`;
CREATE TABLE `kiadasok` (
  `ugyfelid` INT(11) DEFAULT NULL,
  `szgeptarid` INT(11) DEFAULT NULL,
  `datum` DATE DEFAULT NULL,
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ugyfelid` (`ugyfelid`,`szgeptarid`,`datum`),
  KEY `szgeptarid` (`szgeptarid`),
  CONSTRAINT `kiadasok_ibfk_1` FOREIGN KEY (`ugyfelid`) REFERENCES `ugyfelek` (`szemszam`),
  CONSTRAINT `kiadasok_ibfk_2` FOREIGN KEY (`szgeptarid`) REFERENCES `szamitogeptarak` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `kiadasok`
--

/*!40000 ALTER TABLE `kiadasok` DISABLE KEYS */;
INSERT INTO `kiadasok` (`ugyfelid`,`szgeptarid`,`datum`,`id`) VALUES
(2223,11114,'2010-03-10',70),
(2224,11115,'2010-11-05',71),
(2222,11116,'2010-06-05',72),
(2225,11114,'2010-06-10',73);
/*!40000 ALTER TABLE `kiadasok` ENABLE KEYS */;


--
-- Definition of table `kiadastetel`
--

DROP TABLE IF EXISTS `kiadastetel`;
CREATE TABLE `kiadastetel` (
  `kiadid` INT(11) NOT NULL DEFAULT '0',
  `szgepid` INT(11) NOT NULL DEFAULT '0',
  `mennyiseg` DOUBLE DEFAULT NULL,
  `ar` DOUBLE DEFAULT NULL,
  PRIMARY KEY (`kiadid`,`szgepid`),
  KEY `szgepid` (`szgepid`),
  CONSTRAINT `kiadastetel_ibfk_1` FOREIGN KEY (`kiadid`) REFERENCES `kiadasok` (`id`),
  CONSTRAINT `kiadastetel_ibfk_2` FOREIGN KEY (`szgepid`) REFERENCES `szamitogepek` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `kiadastetel`
--

/*!40000 ALTER TABLE `kiadastetel` DISABLE KEYS */;
INSERT INTO `kiadastetel` (`kiadid`,`szgepid`,`mennyiseg`,`ar`) VALUES
(70,4,3,12),
(71,5,4,6),
(72,4,3,12),
(73,2,6,13);
/*!40000 ALTER TABLE `kiadastetel` ENABLE KEYS */;


--
-- Definition of table `ugyfelek`
--

DROP TABLE IF EXISTS `ugyfelek`;
CREATE TABLE `ugyfelek` (
  `szemszam` INT(11) NOT NULL AUTO_INCREMENT,
  `nev` VARCHAR(50) DEFAULT NULL,
  `cim` VARCHAR(50) DEFAULT NULL,
  `tel` INT(11) DEFAULT NULL,
  PRIMARY KEY (`szemszam`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `ugyfelek`
--

/*!40000 ALTER TABLE `ugyfelek` DISABLE KEYS */;
INSERT INTO `ugyfelek` (`szemszam`,`nev`,`cim`,`tel`) VALUES
(2222,'Egyed Zoltan','valami',123456),
(2223,'Jozsa Tunde','valami',654123),
(2224,'Todor Tibor','valami',654123),
(2225,'Kutasi Beata','valami',654123);
/*!40000 ALTER TABLE `ugyfelek` ENABLE KEYS */;


--
-- Definition of procedure `feltolt_alkalmazottak`
--

DROP PROCEDURE IF EXISTS `feltolt_alkalmazottak`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `feltolt_alkalmazottak`(n INTEGER,m INTEGER)
BEGIN
SET @x = 0;
REPEAT SET @x=@x+1;
  INSERT INTO alkalmazottak(szemszam,nev,cim,tel,beosztas,felhasznalonev,jelszo,szgeptarid)
  VALUES(0,
  (SELECT UUID()),
  (SELECT UUID()),
  (SELECT RAND()),
  (SELECT UUID()),
  (SELECT UUID()),
  (SELECT UUID()),
  m);
UNTIL @x>=n END REPEAT;
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$

DELIMITER ;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `gyogyszertorles`(n INTEGER)
BEGIN
DELETE FROM szamitogepkategoriak
WHERE szamitogepkategoriak.szamitogepid=n;
END $$

DELIMITER ;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `gyogyszertorles2`(n INTEGER)
BEGIN
DELETE FROM bevetelezestetel
WHERE bevetelezestetel.szgepid=n;
END $$

DELIMITER ;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `gyogyszertorles3`(n INTEGER)
BEGIN
DELETE FROM kiadastetel
WHERE kiadastetel.szgepid=n;
END $$

DELIMITER ;


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