Notes

drop table Biz.UctyDodavatelov,Biz.UctyOdoberatelov,Biz.odoberatelia,Biz.dodavatelia,Biz.Tovar;
DROP TRIGGER Množstvo_out,Množstvo_in;
CREATE table Biz.dodavatelia(
id_dodavatela int(8) not null auto_increment unique Primary KEY ,
Nazov varchar(45) not null,
ico int(8) not null unique,
stat varchar(2) not null,
telefon_s_predvolbou varchar(15)
);

CREATE table Biz.odoberatelia(
id_odoberatela int(8) not null auto_increment unique Primary Key ,
ico int(8) not null unique,
Nazov varchar(45) not null,
stat varchar(2) not null,
telefon_s_predvolbou varchar(15)
);

create table Biz.Tovar(
id_tovaru int(8) not null unique auto_increment Primary Key,
nazov_tovaru varchar(20) not null,
Cena_kilo float(8) not null,
mnozstvo int(12) DEFAULT 0,
Typ boolean
);

Create table Biz.UctyDodavatelov(
id_transakcie int(8) not null unique auto_increment,
id_dodavatela int(8),
id_tovaru int(8) not null,
mnozstvo int(15) not null,
Datum datetime,
foreign key (id_dodavatela) references Biz.dodavatelia(id_dodavatela),
foreign key (id_tovaru) references Biz.Tovar(id_tovaru)
);

Create table Biz.UctyOdoberatelov(
id_transakcie int(8) not null unique auto_increment,
id_odoberatela int(8),
id_tovaru int(8) not null,
mnozstvo int(15) not null,
Datum datetime,
foreign key (id_odoberatela) references Biz.odoberatelia(id_odoberatela),
foreign key (id_tovaru) references Biz.Tovar(id_tovaru)
);

CREATE TRIGGER Množstvo_in AFTER INSERT ON Biz.UctyDodavatelov
FOR EACH ROW
update Biz.Tovar set Biz.Tovar.mnozstvo = Biz.Tovar.mnozstvo + new.mnozstvo where Biz.Tovar.id_tovaru= new.id_tovaru ;

CREATE TRIGGER Množstvo_out AFTER INSERT ON Biz.UctyOdoberatelov
FOR EACH ROW
update Biz.Tovar set Biz.Tovar.mnozstvo = Biz.Tovar.mnozstvo - new.mnozstvo where Biz.Tovar.id_tovaru= new.id_tovaru ;



INSERT INTO Biz.dodavatelia(Nazov, ico, stat, telefon_s_predvolbou)
VALUES
('Explosm',50245045,'SK','+4219660025'),
('Gold_Touch',45678921,'DE','+421258456'),
('Pro_Star_Garden',12345687,'CZ','+4201584456'),
('Red_Bears',98564213,'SK','+4218456951'),
('Mannings_Cafeterias',75395146,'CZ','+4209456852');

INSERT INTO Biz.odoberatelia(Nazov, ico, stat, telefon_s_predvolbou)
VALUES
('OmniArchitectural',68517823,'SK','+42105848852'),
('Pomeroy',75369852,'SK','+4215475862'),
('GammaRealty',25864835,'CZ','+4201456421'),
('Sagebrush',75298546,'CZ','+4201234852'),
('Elek-Tek',45321785,'DE','+429852147');

INSERT INTO Biz.Tovar(nazov_tovaru, Cena_kilo,Typ)
VALUES
('Jablko',1.24,true),
('Hruška',1.41,true),
('Hrozno',0.84,true),
('Uhorka',0.62,false),
('Citron',1.65,true),
('Jahoda',1.74,true),
('Pomaranc',0.75,true),
('Cinska_kapusta',0.30,false),
('Kapusta',0.40,false),
('Fazula',2.11,false);

insert into Biz.UctyDodavatelov(id_dodavatela, id_tovaru, mnozstvo,Datum)
Values
(1,1,150,timestamp(now())),
(3,1,100,timestamp(now())),
(1,2,100,timestamp(now())),
(2,2,24,timestamp(now())),
(3,7,75,timestamp(now()));
insert into Biz.UctyOdoberatelov(id_odoberatela, id_tovaru, mnozstvo,Datum)
Values
(1,1,20,timestamp(now())),
(2,2,40,timestamp(now())),
(2,2,15,timestamp(now())),
(1,1,30,timestamp(now())),
(3,7,70,timestamp(now()));




/*Výpis vsetkých transakcii od jedneho dodavatela*/
/*
select Biz.odoberatelia.Nazov Názov, Biz.UctyOdoberatelov.Datum Datum, Biz.UctyOdoberatelov.id_tovaru ID_tovaru, Biz.Tovar.nazov_tovaru, Biz.UctyOdoberatelov.id_transakcie ID_Transakcie, Biz.UctyOdoberatelov.mnozstvo Množstvo
from Biz.UctyOdoberatelov
inner JOIN Biz.Tovar ON Biz.UctyOdoberatelov.id_odoberatela = Biz.Tovar.id_tovaru
inner JOIN Biz.odoberatelia ON Biz.odoberatelia.id_odoberatela = Biz.UctyOdoberatelov.id_odoberatela
where Biz.odoberatelia.id_odoberatela = 3;
*/



/*Výpis vŠetkého čo je na sklade nedostotok (<10)*/
/*
select `nazov_tovaru` 'Názov', `Cena_kilo` 'Cena/kilo', `mnozstvo` from Biz.Tovar where mnozstvo < 10;
*/


/*Zmazanie dodavatela*/ /*Nedokončené !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!*/
/*
delete from Biz.UctyDodavatelov where id_dodavatela=1;
delete from Biz.dodavatelia where id_dodavatela=1;

*/
/*
select * from Biz.Tovar
*/