GEM2

drop table klippekort
drop table ordrelinje
drop table prisfraliste
drop table prisliste
drop table vare
drop table bestilling
drop table udlejningsprodukt
drop table kunde
drop table rabat

create table rabat(
rabatID int primary key NOT NULL,
pris int
)

Create table kunde(
kundeID int primary key NOT NULL,
navn varchar(30),
adresse varchar(30),
telefonnr int,
email varchar(30),
rabatID int foreign key references rabat(rabatID)
)

create table klippekort(
klippekortID int primary key NOT NULL,
antalklip int,
pris int,
kundeID int foreign key references kunde(KundeID)
)

create table bestilling(
bestillingID int primary key NOT NULL,
købsdato date default getdate(),
betalingstype varchar(30),
kundeID int foreign key references kunde(KundeID)
)

create table udlejningsprodukt(
udlejningsproduktID int primary key NOT NULL,
udltype varchar(30),
navn varchar(30),
pris int,
udlejet date default getdate(),
tilbagelevering date,
ledig bit default 1 -- Når den er 1 = ledig
)

create table vare(
vareID int primary key NOT NULL,
varetype varchar(30),
udløbsdato date CHECK (udløbsdato >= getDATE() ),
volume int,
navn varchar(30)
)

create table prisliste(
prislisteID int primary key NOT NULL,
navn varchar(30)
)

create table prisfraliste(
prisfralisteID int primary key NOT NULL,
pris int,
vareID int foreign key references vare(vareID) NOT NULL,
prislisteID int foreign key references prisliste(prislisteID) NOT NULL
)

create table ordrelinje(
ordrelinjeID int primary key NOT NULL,
antal int,
bestillingID int foreign key references bestilling(bestillingID),
udlejningsproduktID int foreign key references udlejningsprodukt(udlejningsproduktID),
prisfralisteID int foreign key references prisfraliste(prisfralisteID),
)

--RabatID, pris
insert into rabat values
(1, 50),
(2, 100),
(3,	150),
(4,	200),
(5, 250),
(6, 300)

--KundeID, navn , adresse, telefonnr, email, rabatID
insert into kunde values
(1, 'Daniel', '8900 Randersvej 100', 20202020, 'daniel@gmail.com', 1),
(2, 'Simon', '8210 Gellerupvej 200', 20204020, 'simon@gmail.com', 2),
(3, 'Dennis', '8230 Silkeborgvej 300', 20205020, 'dennis@gmail.com', 3)

-- vareID, varetype, udløbsdato, volume, navn
insert into vare values
(1, 'flasker', '2017-06-06', 75, 'Klosterbryg'),
(2, 'fustage', '2017-06-06', 75, 'Sweet Georgia Brown'),
(3, 'flasker', '2017-06-06', 75, 'Extra Pilsner'),
(4, 'flasker', '2017-06-06', 75, 'Celebration')

--prislisteID, navn
insert into prisliste values
(1, 'fredagsbar'),
(2, 'butik')

--prisfralisteID, pris, fremmedenøgle(vareID), fremmedenøgle(prislisteID)
insert into prisfraliste values
(1, 50, 1, 1),
(2, 36, 1, 2),
(3, 50, 2, 1),
(5, 50, 3, 1)

-- bestillingID, købsdato, betalingstype, fremmedenøgle(kundeID)
insert into bestilling values
(1, '2017-06-06', 'Dankort',	1),
(2, '2017-06-06', 'Mobilpay',	2),
(3, '2017-06-06', 'Kontant',	3)

-- ordrelinjeID, antal, fremmedenøgle(bestillingID), fremmedenøgle(udlejningsproduktID), fremmedenøgle(prisfralisteID)
insert into ordrelinje values
(1, 1, 1, null, 1), -- Dankort,		50 kr.
(2, 1, 2, null, 2), -- Mobilepay,	36 kr.
(3, 1, 3, null, 3), -- kontant		50 kr.
(4, 1, 3, null, 5)  -- kontant		50 kr.

drop proc vare_navn

-- Opgave 2A
create proc vare_navn
@vareID int,
@vareType varchar(30),
@udløbsdato date,
@volume int,
@navn varchar(30),
@pris int,
@prislistenavn varchar(30)
as
Declare @prisfralisteID int
Declare @prislisteID int
Declare @tempID4PrisListe int -- TEMP BRUGES KUN VED NY PRISLISTE
Select @prislisteID = prisliste.prislisteID
from prisliste
if exists (select (prisliste.navn) from prisliste where prisliste.navn = @prislistenavn)
begin
set @prisfralisteID = (select MAX(prisfraliste.prisfralisteID)+1 from prisfraliste)
insert into vare values
(@vareID, @vareType, @udløbsdato, @volume, @navn)
insert into prisfraliste values
(@prisfralisteID, @pris, @vareID, @prislisteID)
END
else
begin
set @prisfralisteID = (select MAX(prisfraliste.prisfralisteID)+1 from prisfraliste)
set @tempID4PrisListe = (select MAX(prisliste.prislisteID)+1 from prisliste)
insert into prisliste values
(@tempID4PrisListe, @prislistenavn)
insert into vare values
(@vareID, @vareType, @udløbsdato, @volume, @navn)
insert into prisfraliste values
(@prisfralisteID, @pris, @vareID, @prislisteID)
END


exec vare_navn @vareID = 14, @vareType = 'flasker', @udløbsdato = '2017-06-06', @volume = 10,
@navn = 'blondie', @pris = 100, @prislistenavn = 'butik'

-- start på test til Opgave 2A
Select prisfraliste.prislisteID
from prisfraliste, prisliste
where prisfraliste.prislisteID = prisliste.prislisteID

Select *
from vare

select prisfraliste.vareID
from prisfraliste, vare
where prisfraliste.vareID = vare.vareID

Select *
from prisliste
-- Slut på test til opgave 2A


-- opgave 2B del 1
create procedure salgpaadagen
@dato date
as
select prisfraliste.pris
from prisfraliste
full join ordrelinje
on prisfraliste.prisfralisteID = ordrelinje.prisfralisteID
full join bestilling
on ordrelinje.bestillingID = bestilling.bestillingID
where bestilling.købsdato = @dato

exec salgpaadagen @dato = '2017-06-06'


-- opgave 2B del 2
Create proc salgprgruppe
@dato date,
@type varchar(30)
as
select prisfraliste.pris
from prisfraliste
full join vare
on vare.vareID = prisfraliste.vareID
full join ordrelinje
on prisfraliste.prisfralisteID = ordrelinje.prisfralisteID
full join bestilling
on ordrelinje.bestillingID = bestilling.bestillingID
where bestilling.købsdato = @dato and vare.varetype = @type

drop proc salgprgruppe

exec salgprgruppe @dato = '2017-06-06', @type = 'flasker'


--opgave 3A Løsning 1
create proc visVarePrisliste
@vare as varchar(30),
@type as varchar(30)
as
select vare.navn, prisfraliste.pris, prisliste.navn
from prisfraliste
full join vare
on vare.vareID = prisfraliste.vareID
full join prisliste
on prisliste.prislisteID = prisfraliste.prislisteID
where vare.navn = @vare and vare.varetype = @type

exec visVarePrisliste @vare = 'Klosterbryg', @type = 'flasker'

drop proc visVarePrisliste


--opgave 3A Løsning 2
create proc visVarePrisliste2
@vareID as varchar(30)
as
select vare.navn, prisfraliste.pris, prisliste.navn
from prisfraliste
full join vare
on vare.vareID = prisfraliste.vareID
full join prisliste
on prisliste.prislisteID = prisfraliste.prislisteID
where prisfraliste.vareID = @vareID

exec visVarePrisliste2 @vareID = 3

drop proc visVarePrisliste2


--opgave 3A Løsning 3
create proc visVarePrisliste3
@vare as varchar(30),
@type as varchar(30)
as
Declare @vareID int
	set @vareID =(Select vare.vareID from vare where vare.navn = @vare and vare.varetype = @type)
select vare.navn, prisfraliste.pris, prisliste.navn
from prisfraliste
full join vare
on @vareID = prisfraliste.vareID
full join prisliste
on prisliste.prislisteID = prisfraliste.prislisteID
where vare.navn = @vare and vare.varetype = @type

exec visVarePrisliste3 @vare = 'Klosterbryg', @type = 'flasker'

drop proc visVarePrisliste3


--opgave 3B
create proc pris
@id int
as
select SUM(prisfraliste.pris) as Pris
from prisfraliste
full join ordrelinje
on prisfraliste.prisfralisteID = ordrelinje.prisfralisteID
full join bestilling
on ordrelinje.bestillingID = bestilling.bestillingID
where bestilling.bestillingID = @id

exec pris @id = 3

drop proc pris

--opgave 3C Løsning 1 - Hent rabatten for en bestemt bestilling
create proc getRabat
@bestillingId int
as
select rabat.pris
from rabat
full join kunde
on rabat.rabatID = kunde.rabatID
full join bestilling
on kunde.kundeID = bestilling.bestillingID
where bestilling.bestillingID = @bestillingId

exec getRabat @bestillingId = 2

drop proc getRabat

--opgave 3C Løsning 2 - pris for bestillingen minus(-) rabatten
create proc totalpris
@id int
as
select sum(prisfraliste.pris) - rabat.pris as Pris
from prisfraliste
full join ordrelinje
on prisfraliste.prisfralisteID = ordrelinje.prisfralisteID
full join bestilling
on ordrelinje.bestillingID = bestilling.bestillingID
full join kunde
on bestilling.kundeID = kunde.kundeID
full join rabat
on kunde.rabatID = rabat.rabatID
where bestilling.bestillingID = @id
group by prisfraliste.pris, rabat.pris

exec totalpris @id = 2

drop proc totalpris

--Opgave3D
create proc opg3D
@prisliste varchar(30),
@vareNavn varchar(30)
as
Select prisliste.navn, prisfraliste.pris
from prisfraliste
full join prisliste
on prisfraliste.prislisteID = prisliste.prislisteID
full join vare
on prisfraliste.vareID = vare.vareID
where prisliste.navn = @prisliste and vare.navn = @vareNavn

exec opg3D @prisliste = 'fredagsbar', @vareNavn = 'Celebration'

drop proc opg3D

--opgave 4 // Opdatere varene
create proc opdatereVare
@vareID int,
@varetype varchar(30),
@udløbsdato date,
@volume int,
@navn varchar(30)
as
update vare
set
vare.varetype = @varetype,
vare.udløbsdato = @udløbsdato,
vare.volume = @volume,
vare.navn = @navn
where vare.vareID = @vareID

exec opdatereVare @vareid = 1, @varetype = 'flasker', @udløbsdato = '2017-06-10', @volume = 5000, @navn = 'DanielBryg'

drop proc opdatereVare

select *
from vare






-- opgave 4 // Slet vare
create proc sletVare
@vareid int
as
declare @temp int
set @temp = (Select (prisfraliste.prisfralisteID) from prisfraliste where prisfraliste.vareID = @vareid)
delete ordrelinje
from prisfraliste
where ordrelinje.prisfralisteID = prisfraliste.prisfralisteID and prisfraliste.vareID = @vareid
delete prisfraliste
where prisfraliste.vareID = @vareid
delete vare
where vare.vareID = @vareid

exec sletVare @vareid = 1
exec sletVare @vareid = 2
exec sletVare @vareid = 3
exec sletVare @vareid = 4

drop proc sletVare

select *
from vare

select *
from bestilling

select *
from ordrelinje

select *
from prisliste

--opgave 5
Create trigger checkPrisListeStatus on prisfraliste for delete
as
Declare @temp as int
Declare @prisFraListeID as int
Declare @prisListeID as int
set @temp = (select vareID from deleted)
set @prisFraListeID = (select (prisfraliste.prisfralisteID) from prisfraliste where prisfraliste.vareID = 3)
set @prisListeID = (select (prisfraliste.prislisteID) from prisfraliste where prisfraliste.prisfralisteID = @prisFraListeID)

IF(select count(prisfraliste.vareID) from prisfraliste where prisfraliste.prisfralisteID = @prisFraListeID) = (0)
begin
PRINT  @temp
PRINT  @prisFraListeID
PRINT  @prisListeID
delete prisliste
from prisliste
where prisliste.prislisteID = 0
end
else
begin
PRINT 'Rolig nu der stadig vare tilbage!'
end
go

drop trigger checkPrisListeStatus