USE dbeksamen
DROP TABLE IF EXISTS timeregistration
DROP TABLE IF EXISTS employeeprojects
DROP TABLE IF EXISTS employee
DROP TABLE IF EXISTS boss
DROP TABLE IF EXISTS project
DROP TABLE IF EXISTS customer

CREATE TABLE customer
(
id INT IDENTITY PRIMARY KEY,
customername VARCHAR(50)
)
CREATE TABLE project
(
id INT IDENTITY PRIMARY KEY,
projectname VARCHAR(50),
projectstatus CHAR(9) CHECK(projectstatus IN ('active','inactive')),
customerid INT NOT NULL REFERENCES customer,
fixedprice INT,
estimatedhours INT,
hourlyrate INT
)
CREATE TABLE boss
(
id INT IDENTITY PRIMARY KEY,
name VARCHAR(50)
)
CREATE TABLE employee
(
id INT IDENTITY PRIMARY KEY,
employeename VARCHAR(50),
bossid INT NOT NULL REFERENCES boss,
timeoff INT
)
CREATE TABLE employeeprojects
(
id INT IDENTITY PRIMARY KEY,
employeeid INT NOT NULL REFERENCES employee,
projectid INT NOT NULL REFERENCES project
)
CREATE TABLE timeregistration
(
id INT IDENTITY PRIMARY KEY,
starttime datetime,
endtime datetime,
employeeid INT NOT NULL REFERENCES employee,
projectid INT NOT NULL REFERENCES project,
note VARCHAR(500)
)

GO
INSERT INTO customer VALUES ('microsoft'), ('apple'), ('internal-projects')

INSERT INTO project VALUES ('projectOne', 'active', 1, 5000, 10, NULL),
('projectTwo', 'inactive', 1, NULL, NULL, 500),
('projectThree', 'active', 2, 15000, 30, NULL),
('projectFour', 'active', 2, 6000, 11, NULL)

INSERT INTO boss VALUES ('jackson'), ('peterson')

INSERT INTO employee VALUES ('Ravn', 1, 2), ('Frost', 2, 5), ('Dennis', 1, 3)

INSERT INTO employeeprojects VALUES (1, 1), (1,2), (2,3), (3,4)

INSERT INTO timeregistration VALUES ((CONVERT(datetime,'19-05-19 06:00:00 PM',5)), (CONVERT(datetime,'19-05-19 08:00:00 PM',5)), 1, 1, '2 hours on projectOne'),
((CONVERT(datetime,'13-05-19 09:00:00 PM',5)), (CONVERT(datetime,'13-05-19 11:00:00 PM',5)), 1, 2, '2 hours on projectTwo'),
((CONVERT(datetime,'13-05-19 09:00:00 PM',5)), (CONVERT(datetime,'13-05-19 11:00:00 PM',5)), 2, 3, '2 hours on projectThree'),
((CONVERT(datetime,'13-05-19 09:00:00 PM',5)), (CONVERT(datetime,'13-05-19 11:00:00 PM',5)), 3, 4, '2 hours on projectFour'),
((CONVERT(datetime,'13-05-19 03:00:00 PM',5)), (CONVERT(datetime,'13-05-19 06:00:00 PM',5)), 2, 3, '3 hours on projectThree')

-- Ex2) ---------------------------------------------------
GO
CREATE INDEX Employeetbl_timeoff
ON employee (timeoff ASC)

GO
SELECT * FROM employee WITH(INDEX(Employeetbl_timeoff))

CREATE INDEX Projecttbl_fixedprice
ON project (fixedprice ASC)

GO
SELECT * FROM project WITH(INDEX(Projecttbl_fixedprice))

-- Ex3) ---------------------------------------------------
GO
SELECT e.employeename
FROM employee e
JOIN timeregistration t ON e.id = t.employeeid
JOIN project p ON t.projectid = p.id
JOIN customer c ON c.id = p.customerid
WHERE c.customername = 'apple'

--Ex4) -----------------------------------------------------
GO
CREATE OR ALTER TRIGGER timeregistrationTrigger
ON timeregistration
instead OF INSERT
AS
BEGIN
     DECLARE @daysbefore INT
     DECLARE @employeeonproject INT
     DECLARE @projectstatus CHAR(9)

     SELECT @daysbefore = DATEDIFF(DAY, GETDATE(), (SELECT starttime FROM inserted))
     IF(@daysbefore <= -7)
     BEGIN
     RAISERROR('Noget gik galt', 16, 1)
     RETURN
     END

     SELECT @employeeonproject = ep.employeeid
     FROM employeeprojects ep
     WHERE ep.employeeid = (SELECT employeeid FROM inserted) AND ep.projectid = (SELECT projectid FROM inserted)
     IF(@employeeonproject IS NULL)
     BEGIN
     RAISERROR('Noget gik galt', 16, 1)
     RETURN
     END

     SELECT @projectstatus = p.projectstatus
     FROM project p
     WHERE p.id = (SELECT projectid FROM inserted)
     IF(@projectstatus = 'inactive')
     BEGIN
     RAISERROR('Noget gik galt', 16, 1)
     RETURN
     END

    INSERT INTO timeregistration VALUES (
        (SELECT starttime FROM inserted),
        (SELECT endtime FROM inserted),
        (SELECT employeeid FROM inserted),
        (SELECT projectid FROM inserted),
        (SELECT note FROM inserted))
END

--test til krav1)
INSERT INTO timeregistration VALUES ((CONVERT(datetime,'14-05-19 09:00:00 PM',5)), (CONVERT(datetime,'13-05-19 11:00:00 PM',5)), 1, 1, '2 hours on projectOne')
--test til krav 2)
INSERT INTO timeregistration VALUES ((CONVERT(datetime,'20-05-19 09:00:00 PM',5)), (CONVERT(datetime,'13-05-19 11:00:00 PM',5)), 2, 1, '2 hours on projectOne')
--test til krav 3)
INSERT INTO timeregistration VALUES ((CONVERT(datetime,'20-05-19 09:00:00 PM',5)), (CONVERT(datetime,'13-05-19 11:00:00 PM',5)), 1, 2, '2 hours on projectOne')

--Ex5) ------------------------------------------------------------------
GO
CREATE OR ALTER FUNCTION dbo.antalhverdageiMinutter(@aar INT ,@maaned INT)
-- returnerer antal minutter i hverdage i den pågældende måned
RETURNS INT
AS
BEGIN
DECLARE @res INT
DECLARE @firstdayofmonth DATE = datefromparts(@aar,@maaned,1)
DECLARE @firstdayofnextmonth DATE = dateadd(mm,1,@firstdayofmonth)
DECLARE @lastdateofmonth DATE = dateadd(dd,-1,@firstdayofnextmonth)
IF DAY(@lastdateofmonth) = 28
  SET @res = 20
ELSE
  IF DAY(@lastdateofmonth) = 29
    IF datename(DW,@firstdayofmonth) IN ('Saturday','Sunday')
      SET @res = 20
    ELSE
      SET @res = 21
  ELSE
    IF DAY(@lastdateofmonth) = 30
      IF datename(DW,@firstdayofmonth) = 'Saturday'
        SET @res = 20
      ELSE
        IF datename(DW,@firstdayofmonth) IN ('Friday','Sunday')
          SET @res = 21
       ELSE SET @res = 22
    ELSE
      IF DAY(@lastdateofmonth) = 31
        IF datename(DW,@firstdayofmonth) IN ('Friday','Saturday')
         SET @res = 21
      ELSE
        IF datename(DW,@firstdayofmonth) IN ('Thursday','Sunday')
         SET @res = 22
        ELSE SET @res = 23
RETURN 444*@res
END

GO
CREATE OR ALTER proc updatetimeoff
    @monthParameter INT,
    @yearParameter INT
AS
BEGIN
    DECLARE @employeeid INT
    DECLARE @timeoff INT
    DECLARE @starttime datetime
    DECLARE @endtime datetime
    DECLARE @lastid INT = 1
    DECLARE @minutes INT = 0
    DECLARE @finalminutes INT = 0
    DECLARE @numberOfRows INT = 0

    DECLARE MY_CURSOR cursor
    LOCAL static forward_only
    FOR
    SELECT e.id, e.timeoff, t.starttime, t.endtime
    FROM employee e
    JOIN timeregistration t ON t.employeeid = e.id

    OPEN MY_CURSOR
    fetch NEXT FROM MY_CURSOR INTO  @employeeid, @timeoff, @starttime, @endtime
    while (@@FETCH_STATUS = 0)
    BEGIN
        IF MONTH(@starttime) = @monthParameter AND YEAR(@starttime) = @yearParameter
        BEGIN
            IF @lastid = @employeeid
            BEGIN
                IF DATENAME(DW, @starttime) = 'Sunday'
                BEGIN
                    SELECT @minutes += DATEDIFF(MINUTE, @starttime, @endtime)
                END
                ELSE
                BEGIN
                    SELECT @minutes += (DATEDIFF(MINUTE, @starttime, @endtime)) * 2
                END
            END
            ELSE
            BEGIN
                UPDATE employee
                SET timeoff += (@minutes - dbo.antalhverdageiMinutter(@yearParameter, @monthParameter))
                WHERE id = @lastid
                SET @lastid += 1
                SET @minutes = 0
                SET @finalminutes = 0
                IF DATENAME(DW, @starttime) = 'Sunday'
                BEGIN
                    SELECT @minutes += DATEDIFF(MINUTE, @starttime, @endtime)
                END
                ELSE
                BEGIN
                    SELECT @minutes += (DATEDIFF(MINUTE, @starttime, @endtime)) * 2
                END
            END
        END
        fetch NEXT FROM MY_CURSOR INTO  @employeeid, @timeoff, @starttime, @endtime
    END

    close MY_CURSOR
END

EXEC updatetimeoff 5, 2019

SELECT * FROM employee

-- Ex8) ---------------------------------
GO
EXEC sp_addlogin 'admin','123'
EXEC sp_addlogin 'employee','123'

EXEC sp_grantdbaccess 'admin'
EXEC sp_grantdbaccess 'employee'

EXEC sp_addrole 'administrators'
EXEC sp_addrole 'employees'

EXEC sp_addrolemember 'administrators','admin'
EXEC sp_addrolemember 'employees','employee'

GRANT SELECT, INSERT, UPDATE, DELETE
ON DATABASE::dbeksamen
TO [admin]

GRANT SELECT, INSERT, UPDATE, DELETE
ON object::dbeksamen.dbo.timeregistration
TO [employee]